Get Random List :


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

9 Comments Add yours

  1. Arthur Fuller says:

    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.

  2. 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.

  3. Arthur Fuller says:

    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.

  4. 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.

    1. Arthur Fuller says:

      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.

  5. ah..Right
    Thanks for you Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s