Collection for random numbers

[Giordano Bruno]

I create a single value which is the sum of various random numbers. I repeat this up to 100,000 times and currently I insert them in a worksheet and sort the results. I can then calculate the minimum, maximum, mean, mode, 5th percentile, etc. I'm wondering if I could speed up the process by sending the results to a Collection, a Dictionary or an Array. Does anyone have any thoughts about this?

 

[StephenCrump]

It's not clear how you're currently doing this, or what "process" it is that you're looking to speed up.

But if you are using VBA code to write a single result to Excel, times 100,000 iterations, yes, it would be much faster to store the 100,000 results in a VBA array and write this array once to Excel.

 

[Giordano Bruno]

Hi Stephen,
many thanks for the response. Yes, I am writing each value as it is created and it seemed to me that this was inefficient. Thank you for your reply, it is very much appreciated.

 

[StephenCrump]

'Compare
Sub Slow()

    Dim i As Long
    Dim t As Double
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    t = Timer
    
    For i = 1 To 100000
        Range("A" & i).Value = i
    Next i
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox Timer - t & " seconds"

End Sub
Sub Fast()

    Dim x(1 To 100000, 1 To 1) As Long, i As Long
    Dim t As Double

    t = Timer
    
    For i = 1 To 100000
        x(i, 1) = i
    Next i
    
    Range("A1:A100000").Value = x
    MsgBox Timer - t & " seconds"

End Sub

 

[Giordano Bruno]

Impressive Stephen. I have a very old computer with a large key at the back that you have to wind up, yet the "Fast" version ran almost instantaneously. It took 3.5% of the time taken by the slower version which is an exact match of what I was using. Thanks you very much for the example it is very useful. Is it possible to modify the "Fast" version to run a variable number of times? It didn't like Dim x(1 To y, 1 To 1)

 

[StephenCrump]

You can't declare using a variable size. But you can do this:

Dim x() 'allocate me an array, size to be specified later

'...
y = 100000  'say
ReDim x(1 To y, 1 To 1)

 

[bebo021999]

The alternative solution:
1) ask user to input the limit numbers
2) generate random numbers from 1 to limitation
3) Sort the result
For 10,000 rows, it took under 1 second. For 1 milion rows, 7 seconds.

Option Explicit
Sub test()
Dim t As Double, ib As Double
t = Timer
ib = InputBox("How many random numbers do you like to get?")
If Not IsNumeric(ib) Or ib > Cells(Rows.Count, "A").Row Then
    MsgBox "Invalid number"
    Exit Sub
End If
Columns(1).ClearContents ' clear existing value in column A
With Range("A2:A" & ib + 1)
    .Formula = "=randbetween(1," & ib & ")" ' generate random values from 1 to input value
    .Value = .Value ' remove the formula then get the value only
    .Sort Range("A1") ' sort the result. Remove it if you dont want sorted values.
End With
MsgBox Timer - t & " seconds"
End Sub

 

[StephenCrump]

I have a very old computer with a large key at the back that you have to wind up ...

You should at least upgrade to steam-power.

 

[johnnyL]

I think I lost track of what the goal of the OP is.

OP mentions Mean, Median, etc. The rest of the thread took off to generate all random numbers between 2 digits. ???

 

[bebo021999]

I think I lost track of what the goal of the OP is.
OP mentions Mean, Median, etc. The rest of the thread took off to generate all random numbers between 2 digits. ???

I guess OP need help in 1st step: generate random list.
The final step (Mean, median,...) he could manage himself.