Compile error: Constant expression required for Random Sample code

[willow1985]

I have a code I have been using for awhile now that I had assistance writing, however now after an excel update to 365 the code no longer works and get the below error. I am unsure how to modify the code.

Sub RS()
'
' RS Macro
'
 
      If Application.CountIf(Sheets("Random Sample").Range("E1"), "") > 0 Then
        MsgBox "Please Enter User Name"
        Exit Sub
    End If

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes
 
  sCellText = Sheets("Random Sample").Range("C1").Value
 
  Dim R As Long, Cnt As Long, RandomIndex As Long, Arr As Variant, Tmp As Variant

 ' the below line is where the error is occuring
 Const HowMany = sCellText
  Randomize
  Arr = Sheets("DATA").ListObjects("DATA").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Arr)
      If Arr(R, 5) = "N" Then .Item(CStr(Arr(R, 1))) = 1
    Next
    Arr = .Keys
  End With
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Sheets("Random Sample").Range("B3").Resize(HowMany) = Application.Transpose(Arr)

End Sub

If anyone could help me with this it would be very much appreciated!

 

[Fluff]

What sort of data does

Sheets("Random Sample").Range("C1").Value

hold?

 

[Rick Rothstein]

The value assigned in a Const statement must be a constant... it cannot be or be derived from a calculation. This is because VB physically replaces the object name with the value to be assigned to it throughout the code during the pre-compile process which occurs before any lines of code are executed. You are going to have to make HowMany a normal variable.

 

[willow1985]

What sort of data does

Sheets("Random Sample").Range("C1").Value

hold?

There are 3 sheets: one live database with workorder numbers, one sheet to keep record of which workorders where previously audited and the last (Random Sample) sheet where the macro's function is to grab a random number of workorder numbers from the database sheet.

Hope this makes sense...

Traveller Spot Audit Generator.xlsm
	A	B	C	D	E
1	Weekly Audit Sample Size:		10	User:
2	Row #	W/O Number
3	#N/A	91025
4	#N/A	109114
5	#N/A	110439
6	#N/A	110459
7	#N/A	110549
8	#N/A	110807
9	#N/A	111014
10	#N/A	111247
11	#N/A	111319
12	#N/A	111362
13	#N/A	111444
14	#N/A	111483
Random Sample

Cell Formulas
Range	Formula
A3:A14	A3	=MATCH(B3,DATA!A:A,0)

Named Ranges
Name	Refers To	Cells
DATA!Query_from_Uniflyte_Inc__WPG	=DATA!$A$1:$D$339	A3:A14

Traveller Spot Audit Generator.xlsm
	A	B	C	D	E
1	W/O Number	Date Received	Date Shipped	Serial Number	Previously Audited
2	113576	2021-04-26	2021-05-18	0509-0001	N
3	113917	2021-05-27	2021-05-31	07ALT0045	N
4	113638	2021-04-29	2021-05-18	0912-0009	N
5	113403	2021-04-07	2021-04-28	1088-125	N
6	113548	2021-04-22	2021-04-27	10-95-123	N
7	113691	2021-05-07	2021-05-20	112752	N
8	112158	2020-12-11	2021-04-30	11418	N
9	113074	2021-03-10	2021-04-29	11800	N
10	113291	2021-03-24	2021-05-31	11953	N
11	112339	2020-12-22	2021-04-28	12401	N
12	113476	2021-04-14	2021-05-07	1260	N
13	113602	2021-04-29	2021-05-12	12-76-81	N
14	113226	2021-03-22	2021-05-19	1384509/003	N
15	113200	2021-03-18	2021-05-07	13850	N
16	113343	2021-03-29	2021-05-14	139	N
17	113314	2021-03-26	2021-05-18	1431358/001	N
18	113508	2021-04-19	2021-06-01	181	N
19	113775	2021-05-13	2021-05-27	1-84-31	N
20	111806	2020-11-13	2021-04-29	21242	N
21	113126	2021-03-15	2021-04-28	21508	N
DATA

Cell Formulas
Range	Formula
E2:E21	E2	=IFERROR(INDEX('Previously Audited'!B:B,MATCH([@[W/O Number]],'Previously Audited'!A:A,FALSE)),"N")

Traveller Spot Audit Generator.xlsm
	A	B
1	WorkOrder	Previously Audited
2	111376	Y
3	111264	Y
4	110499	Y
5	111155	Y
6	110138	Y
7	111109	Y
8	110836	Y
9	111288	Y
10	111364	Y
11	110622	Y
12	107365	Y
13	110244	Y
Previously Audited

 

[willow1985]

The value assigned in a Const statement must be a constant... it cannot be or be derived from a calculation. This is because VB physically replaces the object name with the value to be assigned to it throughout the code during the pre-compile process which occurs before any lines of code are executed. You are going to have to make HowMany a normal variable.

The data it is grabbing is not derived from a calculation....

I have posted all the data so not sure why I am getting the error then

 

[Fluff]

On this line

Const HowMany = sCellText

sCellText is a variable & you cannot assign a variable to a constant as it can change.
As you don't seem to be using the HowMany variable, just delete the line

 

[Rick Rothstein]

The data it is grabbing is not derived from a calculation....

In the following line of code, sCellText is not a constant... it gets its value from a "calculation"...

sCellText = Sheets("Random Sample").Range("C1").Value

Remember, I said VB will substitute constants for the object name you used in the Const statement BEFORE any code lines are executed... sCellText will not have a value assigned to it until code lines are executed.

 

[Marc L]

Hi,​

​

and End Select codeline is missing so this code as it is can't work whatever the Excel version …​

 

[willow1985]

On this line

Const HowMany = sCellText

sCellText is a variable & you cannot assign a variable to a constant as it can change.
As you don't seem to be using the HowMany variable, just delete the line

I would require the HowMany variable as C1 contains the number of WorkOrders the code must randomly select.
I am not sure what is missing or what happened but the macro used to work. Any idea how to fix it and still have it select the number recorded in C1?

 

[Rick Rothstein]

Any idea how to fix it and still have it select the number recorded in C1?

Simply declare HowMany in a Dim statement, not a Const statement.