Here is a code to get random list in array, you can pass top and bottom value.
Function GetRandList(ListCount As Long, Bottom As Long, Top As Long) As Variant
Dim lngCOunt As Long
If Bottom < Top Then
With CreateObject(“Scripting.Dictionary”)
Do While .Count <> ListCount
.Item(Int(Rnd() * (Top – Bottom + 1) + Bottom)) = 0
Loop
GetRandList = .Keys
End With
Else
GetRandList = Null
End If
End Function
Sub MTest()
Dim VarArr
VarArr = GetRandList(10, 1, 10)
End Sub
Excel VBA Products 
It appears to need a reference to one of the scripting libraries. I’m not sure which, though. I tried setting references to all the ones I have (Scriplet Library, Script Control and Scripting RunTime) and it still won’t compile, The line
With CreateObject(“Scripting.Dictionary”)
generates the error message
Variable not defined
I’m using Windows 7 sp1 and Access 2007.
its late binding , you dont need to add any reference ,
i think the problem is in syntax , you can retype inverted comma
Thanks for your comment
Rajan.
I modified your code somewhat to better indicate what’s going on and to print out the resulting array afterwards. And you are right about the late binding. Once I fixed the double quotation marks the line was no longer a problem. I deleted the references and it worked just fine.
Function GetRandList(lngListCount As Long, lngBottom As Long, lngTop As Long) As Variant
Dim lngCOunt As Long
Debug.Print “lngListCount: ” & lngListCount
Debug.Print “lngBottom: ” & lngBottom
Debug.Print “lngTop: ” & lngTop
If lngBottom < lngTop Then
With CreateObject("Scripting.Dictionary")
Debug.Print "Scripting.Dictionary Item count: " & .Count
Do While .Count lngListCount
.Item(Int(Rnd() * (lngTop – lngBottom + 1) + lngBottom)) = 0
Loop
GetRandList = .Keys
End With
Else
GetRandList = Null
End If
End Function
Sub MTest()
Dim VarArr As Variant
Dim i As Variant, j As Long
VarArr = GetRandList(10, 1, 10)
j = 1
Debug.Print “Array values”
For Each i In VarArr
Debug.Print j & “: ” & i
j = j + 1
Next
j = 1
VarArr = GetRandList(20, 1, 30)
Debug.Print “Array values”
For Each i In VarArr
Debug.Print j & “: ” & i
j = j + 1
Next
End Sub
On other little thing: your test program sets the ListCount and Top values to the same. My second iteration assigns different values to them, and the results are nice. In this case, give me 20 items whose values range from 1 to 30.
it can be a typo in this line
Do While .Count lngListCount
any way, is there any difference in your’s and my program except Debug.Print
Rajan.
Tiny changes are all. I gave the variables prefixes to identify their data type, added some debug.print statements, and changed the test program to print out the resulting array once it’s built. I also added a second iteration, in which the ListCount and Top values are different, to show that you could ask for say 20 values ranging from 1 to any number such as 100.
ah..Right
Thanks for you Comments
you can also visit here to have a look on discussion about this thread
http://www.linkedin.com/groupItem?view=&gid=3843467&type=member&item=175282658&commentID=99831727&report%2Esuccess=8ULbKyXO6NDvmoK7o030UNOYGZKrvdhBhypZ_w8EpQrrQI-BBjkmxwkEOwBjLE28YyDIxcyEO7_TA_giuRN#commentID_99831727
Rajan