Steve R.
Retired
- Local time
- Today, 01:44
- Joined
- Jul 5, 2006
- Messages
- 5,602
Generating a random number by itself is easy to accomplish. However, it becomes complex if you want to randomize an ordered list, where the total number of items in that list may be unknown or you want to randomly select five(5) people out of a pool of, lets say, thirty(30) people. With some adaption, the code below can perform those randomization tasks.
The program works by creating a virtual jar (destination array) and filling it with virtual numbered beans. The beans are then randomly pulled out one at a time to create a numbered list.
As a short example:
Source Array Position Number.....Destination Array Position Number
1 ..........................................29
2 ..........................................16
3 ..........................................09
In the case of the ordered list, you would be moving the contents of record #1 to position #29. The contents of record #2 to position #16. The contents of record $3 to position #09.
In the case of a group of people, such as a jury pool, you would be selecting persons #29, #16, and #09.
Hopefully you will find the code useful. Place the following in a module's declaration section.
Place the following in the module's subroutine section.
Finally, in a forms code section place the following to call the randomization code.
The program works by creating a virtual jar (destination array) and filling it with virtual numbered beans. The beans are then randomly pulled out one at a time to create a numbered list.
As a short example:
Source Array Position Number.....Destination Array Position Number
1 ..........................................29
2 ..........................................16
3 ..........................................09
In the case of the ordered list, you would be moving the contents of record #1 to position #29. The contents of record #2 to position #16. The contents of record $3 to position #09.
In the case of a group of people, such as a jury pool, you would be selecting persons #29, #16, and #09.
Hopefully you will find the code useful. Place the following in a module's declaration section.
Code:
Public lonDestination 'Using "as Long" upsets the compiler. Needs to be public to keep the variable "alive".Place the following in the module's subroutine section.
Code:
Public Sub RandomNumberList(lonNumber As Long)
Rem lonNumber represent the number of random numbers to be generated. Defines how many elements are needed in the array.
Rem The calling program must check lonNumber so that "0" and "1" are rejected to prevent this subroutine from activating.
Rem Pretend that this is a large jar holding beans. Each bean contains a number. You reach in and pull out a bean and read its number.
Rem This program was originally designed to move a value contained in the source array into the position number defined by the pulled bean to get a randomized list.
Rem As written here, the source array will only contain the value of "0" since no data is being put into into it. So depending on what you are doing, the source array may not be needed at all.
Rem The destination array gives you the randomized list of numbers.
Dim I As Long
Dim K As Long 'K = Number of beans left in the jar.
Dim J As Long
Dim L As Long
Dim M As Long
Dim N As Long
ReDim lonSource(lonNumber) As Long 'Source array - Depending on your program the Source Array may NOT be needed.
ReDim lonDestination(lonNumber) As Long 'Destination Array - The array holding the list of randomized numbers
Rem -------------------------------------------------------------------------------
Rem Initializs the source array
For I = 1 To lonNumber
lonSource(I) = I
Next I
Rem --------------------------------------------------------------------------------
K = lonNumber 'K = Initially the number of "beans" placed in the jar.
Rem *********************************************************************************
For I = 1 To lonNumber 'I = The Number of the iteration to be performed. lonNumber = total iterations
Randomize
J = Int((K * Rnd) + 1) 'J = The number of the bean that is pulled out.
lonDestination(I) = lonSource(J) 'Store the selected value in the destination Array
Rem -------------------------------------------------------------------------
Rem After storing value in the destination array, need to remove one bean from the jar.
Rem Move the beans up in the jar.
If J > K Then Debug.Print "Error, J cannot exceed K"
If J = K Then 'The last bean was selected, consequently the remaing beens are unaffected.
lonSource(J) = 0 'setting the value to "0" makes error detection easier.
Else 'The last bean was not selected
L = K - J 'L = Number of moves. J = Selected Position. Move the beans up by one space in the "jar"
For M = J To K - 1
lonSource(M) = lonSource(M + 1) 'Move the value contained in slot "M + 1" up one slot into postion "M"
Next M
lonSource(K) = 0 'setting the value to "0" makes error detection easier.
End If
K = K - 1 'One bean was removed, so decrease the number of the beans in the jar.
Next I
End SubFinally, in a forms code section place the following to call the randomization code.
Code:
Public Sub Command22_Click()
If Me.Text3 > 1 Then 'Number of random numbers to be generated. You will need to provide a source number.
Call RandomNumberList(Me.Text3) 'Generate a list of random numbers that total the number found in Me.text3
Else
Rem provide an error message and error recovery code of some sort.
MsgBox "Can't have a value less than 2", vbCritical, "Can't Execute"
End If
End Sub