generating random numbers

[Phil Hellmuth]

I want to pull a group of random numbers within a certain range, but
don't want to check after each grab whether I've previously pulled the
number. For example, let's say I want to generate three random numbers
between 1 and 100. I'll store the numbers in an array. Can I do this
without having to search through the array each time I generate a new
number to ensure I haven't already pulled the number?

Thanks in advance.

 

[Guest]

I would suggest the following code to generate a list of random numbers. The
function is set up to return the random values in two ways. It will return a
CSV of the random numbers generated (ie: 1,4,5,6) ... plus if you pass a
variant to the function (the last parameter), the variante will take the form
of an array and will be populated with the random values that were selected.
Note that the forth parameter of the function can be used to indicate whether
or not you wish to have unique values in the set ...

Example Usage:

strMyString = RandomSet(3, 1, 100, True, varMyArray)
The above will set strMyString to a CSV with 3 unique values between 1 and
100, plus the variant variable varMyArray will take the form of an array and
have the same 3 unique values in it.

RandomSet 3, 1, 100, True, varMyArray
Same as above, except since the returned string is not used it will be
disgarded.

RandomSet 3, 1, 100, False, varMyArray
3 random values are selected ... they DO NOT have to be unique ...

Here is the code ...

Public Function RandomSet(lngNumberOfUniqueValues As Long, _
lngMinimum As Long, _
lngMaximum As Long, _
Optional blUnique = True, _
Optional varArray As Variant) As String
'Generates a set of UNIQUE random numbers from a pool of numbers. The
'set is returned as a CSV. IF an array variable is passed to varArray,
'the array is populated with the values.

Dim aLngPool() As Long 'Array to hold the pool of numbers
Dim lngRow As Long 'used as a pointer to a "ROW" of the array
Dim lngTemp As Long 'Used as a temp storage of values
Dim strTemp As String 'Used as a string builder variable
Dim x As Long 'used as a counter

'Size the array to hold ALL the possible values, I think of first
'element of the array as the number of ROWS, the second as the number
'of COLUMNS.
'ReDim aLngPool(1 To (lngMaximum - lngMinimum) + 1, 1 To 2)
ReDim aLngPool(lngMinimum To lngMaximum, 1 To 2)

'Initialize the random number generator
Randomize

'Populate the array with the pool of values, paired with a random number
'this is done so the array can be sorted by the random number later on.
For x = LBound(aLngPool, 1) To UBound(aLngPool, 1)

aLngPool(x, 1) = Int(Rnd * 100000)

If blUnique = True Then
aLngPool(x, 2) = x
Else
aLngPool(x, 2) = Int((lngMaximum - lngMinimum + 1) * Rnd +
lngMinimum)
End If

Next x

'Now that the array is populated, sort the array by the random number
'which is in "column" 1 of the array. We can skip this section if
'unique values are not needed.
If blUnique = True Then
x = UBound(aLngPool, 1)
Do Until x = LBound(aLngPool, 1)
For lngRow = LBound(aLngPool, 1) To x - 1

'Compare the random numbers to determine if the "Rows"
should be swaped
If aLngPool(lngRow, 1) > aLngPool(lngRow + 1, 1) Then

'Swap elements of the array
lngTemp = aLngPool(lngRow, 1)
aLngPool(lngRow, 1) = aLngPool(lngRow + 1, 1)
aLngPool(lngRow + 1, 1) = lngTemp

lngTemp = aLngPool(lngRow, 2)
aLngPool(lngRow, 2) = aLngPool(lngRow + 1, 2)
aLngPool(lngRow + 1, 2) = lngTemp

End If
Next lngRow
x = x - 1
Loop
End If

'Now that the pool of numbers is sorted by the random number, return
'the quanity of unique numbers desired by the caller.
For x = LBound(aLngPool, 1) To LBound(aLngPool, 1) +
lngNumberOfUniqueValues - 1
strTemp = strTemp & "," & aLngPool(x, 2)
Next x

'Return the string
RandomSet = Mid(strTemp, 2)

'Populate the array, if an array is passed
If Not IsMissing(varArray) Then
varArray = Split(Mid(strTemp, 2), ",")
If IsArray(varArray) Then
For x = LBound(varArray) To UBound(varArray)
varArray(x) = Val(varArray(x))
Next x
End If
End If

End Function



Regards,
Brent Spaulding
datAdrenaline | Access MVP

 

[Chris L.]

I want to pull a group of random numbers within a certain range, but
don't want to check after each grab whether I've previously pulled the
number. For example, let's say I want to generate three random numbers
between 1 and 100. I'll store the numbers in an array. Can I do this
without having to search through the array each time I generate a new
number to ensure I haven't already pulled the number?

Thanks in advance.

If you're pulling the numbers out of a table, you'll have to mark them
as USED somehow (new column in the table?)

If you're generating the numbers with RND / RANDOM functions, you'll
have to "remember" which ones you've pulled. As you said you will
store them on an array. I think you can't avoid checking the array to
read what numbers are there. Other options would be storing the
already used numbers on an indexed table. Or using collections instead
of arrays (easier to determine if a number is on the collection).

On collections see http://support.microsoft.com/kb/210035/en-us

 

[Phil Hellmuth]

Thanks for the response. However, when calling the Split function
(varArray = Split(Mid(strTemp, 2), ",")), I encounter an error:

Variable uses an Automation type not supported in Visual Basic (run time
#458)

Are you familiar with this and/or any work around?

 

[Guest]

That error will come up when you pass an array variable to the LAST
parameter, which expects a VARIANT ... which will take the form of an array.

For example, the following code will raise the '438' error ...

Public Sub foo()
Dim aArray() As Long
RandomSet 3, 1, 100, , aArray
End Sub

Because the 5th parameter needs to be a variant datatype ... like this:

Public Sub bar()
Dim varArray As Variant
RandomSet 3, 1, 100, , varArray
End Sub

In the Bar() sub, the variant variable will act just like an array, since a
variant can take on any form.. in this case it takes on the form of an Array
.... if you don't like working with a variant as an array you can do something
like this too:

Public Sub bar_2()

Dim aArray() As Long
Dim varArray As Variant
Dim x As Long

RandomSet 3, 1, 100, , varArray

If IsArray(varArray) Then
Redim aArray(UBound(varArray))
For x = LBound(varArray) To UBound(varArray)
aArray(x) = varArray(x)
Next x
End If

End Sub

But to me is seems a little bit of a waste to dump the contents of one array
into another, but I can understand from a strict datatyping perspective as to
why you would want the results in an explicitly declared array of the proper
datatype.

I hope this helps out ... if not, let me know!

Regards,
Brent Spaulding
datAdrenaline | Access MVP

 

[Phil Hellmuth]

That did the trick. Thanks so much.

 

[Guest]

Your Welcome! ... Glad to be of service