Why are my seeds for generating random numbers not working?-CodePudding

I need to generate a few sets of random numbers and a minimal working example of my code is as follows:

Sub Macro1()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim RA1 As Variant
Dim RA2 As Variant
Dim RA3 As Variant
Dim RA4 As Variant
ReDim RA1(1 To 63)
ReDim RA2(1 To 63)
ReDim RA3(1 To 63)
ReDim RA4(1 To 63)

For i = 1 To 1000

    Rnd (-1)
    Randomize i

    For j = 1 To 63
            
        RA1(j) = Rnd
                             
    Next j
    
    Rnd (-2)
    Randomize i

    For k = 1 To 63
            
        RA2(k) = Rnd
                             
    Next k
    
    Rnd (-3)
    Randomize i

    For l = 1 To 63
            
        RA3(l) = Rnd
                             
    Next l
    
    Rnd (-4)
    Randomize i

    For m = 1 To 63
            
        RA4(m) = Rnd
                             
    Next m

    With Sheets("Economic Assumptions")
    
        .Range("B10:BL10").Value = RA1
        .Range("B11:BL11").Value = RA2
        .Range("B12:BL12").Value = RA3
        .Range("B13:BL13").Value = RA4
        .Calculate
        
    End With

Next i

End Sub

However, while RA1 is unique, I find that my random numbers in RA2, RA3 and RA4 are all exactly the same within each i. In other words, my code gives me RA1 <> RA2 = RA3 = RA4. Why is that? I thought that changing the argument in RND will change the seed of the random numbers? In particular, I need RA1, RA2, RA3 and RA4 each have their own set of random numbers for each i, but when I re-run the entire thing, I should be getting the same random numbers each time. How can I tweak my code to achieve this?

Edit

After some brainstorming and guess-and-check work, the following code does the trick!

Sub Macro1()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim RA1 As Variant
Dim RA2 As Variant
Dim RA3 As Variant
Dim RA4 As Variant
ReDim RA1(1 To 63)
ReDim RA2(1 To 63)
ReDim RA3(1 To 63)
ReDim RA4(1 To 63)

For i = 1 To 1000

    Rnd (-1)
    Randomize i

    For j = 1 To 63
            
        RA1(j) = Rnd
        RA2(j) = Rnd
        RA3(j) = Rnd
        RA4(j) = Rnd
                             
    Next j
    
    With Sheets("Economic Assumptions")
    
        .Range("B10:BL10").Value = RA1
        .Range("B11:BL11").Value = RA2
        .Range("B12:BL12").Value = RA3
        .Range("B13:BL13").Value = RA4
        .Calculate
        
    End With

Next i

End Sub

It is much shorter and sweeter too. I am so happy :)

CodePudding user response：

You can assign to an array, shuffle the array x times, save to other array, dumb to sheet. By limiting the nr of interactions with the sheet this should be faster too:

Option Explicit
Sub xx()
    Dim arr, nrOfsets As Long
    arr = WorksheetFunction.RandArray(1, 63)
    nrOfsets = 4
    
    Dim arr2, j As Long, i As Long
    ReDim arr2(1 To nrOfsets, 1 To UBound(arr))
    For j = 1 To nrOfsets
        For i = 1 To UBound(arr)
            arr2(j, i) = arr(i)
        Next i
        arr = Resample(arr):
    Next j
    
    Dim startrow As Long, startcol As Long
    startrow = 10: startcol = 2
    With Sheet3
        .Range(.Cells(startrow, startcol), .Cells(startrow - 1   nrOfsets, startcol - 1   UBound(arr2, 2))) = arr2
    End With
End Sub
   
Function Resample(data_vector As Variant) As Variant()
'source: https://stackoverflow.com/questions/61020724/shuffle-an-array-in-vba
   Dim shuffled_vector() As Variant
   shuffled_vector = data_vector
   Dim i As Long
   For i = UBound(shuffled_vector) To LBound(shuffled_vector) Step -1
       Dim t As Variant
       t = shuffled_vector(i)
       Dim j As Long
       j = Application.RandBetween(LBound(shuffled_vector), UBound(shuffled_vector))
       shuffled_vector(i) = shuffled_vector(j)
       shuffled_vector(j) = t
   Next i
   Resample = shuffled_vector
End Function