Generate repeatable range of random normal variables

[bigck2]

Hello,

I'd like to create a column range with a bunch of random normal variables, and I'd like to control the seed so that I could reproduce the numbers if needed.

All I was able to find online was this post on stackoverflow:

Repeating random variables in VBA - Stack Overflow

Modifying the code to test and meet my needs:

Sub Test()

    Dim Mymean As Integer
    Dim Mysd As Integer
    
    Mymean = 90
    Mysd = 15
    


    Stupid_Step = Rnd(-1)
    
    Randomize 10
    
    
    With Range(Cells(1, 1), Cells(100, 1))
        .FormulaR1C1 = "=NORMINV( RAND(), " & Mymean & ", " & Mysd & ")"
        .Value = .Value
    End With
    
    
    Randomize 10
    
    With Range(Cells(1, 3), Cells(100, 3))
        .FormulaR1C1 = "=NORMINV( RAND(), " & Mymean & ", " & Mysd & ")"
        .Value = .Value
    End With


End Sub

However, the random numbers generated in column 1 and column 3 do not match. Please let me know what I am missing to do this.

I also tried using the macro recorder and using the Data Analysis Tool pak. The code it generated was:

Sub Macro3()


     Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$A$1"), 1, 100 _
        , 2, 1, 90, 15        
        
End Sub

That code seemed really simple, but when I tried to modify it like this:

Sub Macro3_Modified()


     Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$C$1"), 1, 100 _
        , 2, 1, 90, 15        
        
End Sub

I get an error message:

"Sorry, we couldn't fine "\\co-file\FolderRedirection$\ckiniry\Desktop\Robert - Rolling Forecast\Test Clean Data with R\Cleaned Data\ATPVBAEN.XLAM". Is it possible it was moved, renamed, or deleted"

After I hit OK, I get the error message:

Run-time error '1004:

Microsoft Excel cannot access the file "\\co-file\FolderRedirection$\ckiniry\Desktop\Robert - Rolling Forecast\Test Clean Data with R\Cleaned Data\ATPVBAEN.XLAM" There are several possible reasons:

-The file name or path does not exist.
-The file is being used by another program.


Any help would be appreciated! Thanks!

-Chris

 

[RickXL]

Hi,

To repeat the random number sequence you need to use Rnd(-1) to reset the sequence generator:

Sub Rand1()
    Dim Mymean      As Integer
    Dim Mysd        As Integer
    Dim Stupid_Step As Double
    Dim r           As Range
    
    Mymean = 90
    Mysd = 15

    Stupid_Step = Rnd(-1)
    For Each r In Range(Cells(1, 1), Cells(100, 1))
        r = WorksheetFunction.NormInv(Rnd, Mymean, Mysd)
    Next
    
    Stupid_Step = Rnd(-1)
    For Each r In Range(Cells(1, 3), Cells(100, 3))
        r = WorksheetFunction.NormInv(Rnd, Mymean, Mysd)
    Next
End Sub

If you don't always want exactly the same sequence each time you run the program then you could try:

Sub Rand2()
    Dim Mymean      As Integer
    Dim Mysd        As Integer
    Dim Myseed      As Integer
    Dim Stupid_Step As Double
    Dim r           As Range
    
    Mymean = 90
    Mysd = 15
    Myseed = 0

    Stupid_Step = Rnd(-1)
    Randomize Myseed
    For Each r In Range(Cells(1, 1), Cells(100, 1))
        r = WorksheetFunction.NormInv(Rnd, Mymean, Mysd)
    Next
    
    Stupid_Step = Rnd(-1)
    Randomize Myseed
    For Each r In Range(Cells(1, 3), Cells(100, 3))
        r = WorksheetFunction.NormInv(Rnd, Mymean, Mysd)
    Next
End Sub

Changing the value of Myseed will result in different sequences.

Depending on what you are trying to do, you might want to make Myseed random.
Another idea would be to just copy column A to column C. That would mean you did not have to repeat the sequence by other means.


Regards,