Random numbers

[Ed]

Hi

I have the following code that i found on the MVPs
website. It creates a random set of numbers using a form
in VB. Does any have any sugeestions for using this in
excel? i.e. I want a macro that uses the basis of this
code to generate some unique random numbers and input them
into specified cells in excel.

Many thanks for your help


Private Sub Form_Load()

Randomize Time

End Sub


Private Sub Command1_Click()

'Set the number of elements needed. This
'demos uses 52 to simulate cards in a deck.
'Remember that this is a demo ... myArray()
'goes out of scope once this procedure ends.
'To make it persistent, move the Dim statement
'to the form's General Declarations area.

Dim x As Integer

'declare an array
Dim myArray(1 To 52) As Integer

'lists are for info only
List1.Clear
List2.Clear

'fill the array with consecutive numbers from 1 to 52
For x = 1 To UBound(myArray)
myArray(x) = x

'debug/info only - not needed for routine
List1.AddItem myArray(x)
Next

'randomize the array values
RandomizeArray myArray

'debug/info only - not needed for routine
For x = 1 To UBound(myArray)
List2.AddItem x & vbTab & myArray(x)
Next

End Sub

Private Sub RandomizeArray(ArrayIn As Variant)

Dim x As Long
Dim RandomIndex As Long
Dim tmp As Variant

'only if an array was passed
If VarType(ArrayIn) >= vbArray Then

'loop through the array elements
For x = UBound(ArrayIn) To LBound(ArrayIn) Step -1

'select another random array index
RandomIndex = Int((x - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn))

'and reassign its content to the current array
member,
'swapping the current member value to the other
spot
tmp = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(x)
ArrayIn(x) = tmp

Next

Else

'The passed argument was not an
'array; error handler goes here

End If

End Sub


Private Sub List1_Scroll()

'if List2 is scrolled, keep List1 in sync
List2.TopIndex = List1.TopIndex

End Sub


Private Sub List2_Scroll()

'if List1 is scrolled, keep List2 in sync
List1.TopIndex = List2.TopIndex

End Sub

 

[Jane Pratt [MSFT]]

Hi Ed

You can include a loop which inserts the values into your workbook:
For i = 1 To 52
Cells(i, 1) = myArray(i)
Next
(This uses the same range of values as those in the original code).

Here is the full code:

Private Sub InsertRandomData()
Randomize Time

'Set the number of elements needed. This
'demos uses 52 to simulate cards in a deck.
'Remember that this is a demo ... myArray()
'goes out of scope once this procedure ends.
'To make it persistent, move the Dim statement
'to the form's General Declarations area.

Dim x As Integer

'declare an array
Dim myArray(1 To 52) As Integer

'fill the array with consecutive numbers from 1 to 52
For x = 1 To UBound(myArray)
myArray(x) = x
Next

'randomize the array values
RandomizeArray myArray

For i = 1 To 52
Cells(i, 1) = myArray(i)
Next
End Sub

Private Sub RandomizeArray(ArrayIn As Variant)

Dim x As Long
Dim RandomIndex As Long
Dim tmp As Variant

'only if an array was passed
If VarType(ArrayIn) >= vbArray Then

'loop through the array elements
For x = UBound(ArrayIn) To LBound(ArrayIn) Step -1

'select another random array index
RandomIndex = Int((x - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn))

'and reassign its content to the current array member,
'swapping the current member value to the other spot
tmp = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(x)
ArrayIn(x) = tmp

Next

Else

'The passed argument was not an
'array; error handler goes here

End If

End Sub

Hope this helps!

Regards
Jane Pratt
Developer Tools Support
Microsoft UK

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.