Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random numbers
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |