VBA生成随机数时种子设置无效问题及代码调整方案

问题背景 #

我需要生成多组随机数，最初的最小可复现代码如下：

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

异常现象 #

运行上述代码后发现，仅RA1的数值是唯一的，每个i对应的RA2、RA3、RA4生成的随机数完全一致，结果满足RA1≠RA2=RA3=RA4。预期实现效果为：每个i对应的RA1、RA2、RA3、RA4均为不同的随机数序列，且每次重新运行整个代码时，生成的随机数序列保持一致。

问题原因 #

VBA的Rnd和Randomize有固定的运行逻辑：

• 调用Randomize [数值]时，会将传入的数值作为种子重置伪随机数生成器的状态，相同的种子会生成完全一致的随机数序列
• 原代码中每生成一组随机数前，都会调用Randomize i用相同的i重置种子，相当于每次都从同一个起点开始生成63个随机数，自然得到完全相同的结果
• 给Rnd传递负数参数仅会用该负数生成一个固定随机数，并不会改变后续Randomize的重置逻辑，因此无法达到修改序列的效果

最终实现方案 #

经过测试，优化后的代码如下，完全满足需求：

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

    For k = 1 To 63
            
        RA2(k) = Rnd
                             
    Next k

    For l = 1 To 63
            
        RA3(l) = Rnd
                             
    Next l

    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

方案说明 #

优化后代码仅在每个i循环开始时执行一次种子初始化，之后连续生成4组共252个随机数，伪随机数序列的线性特性保证了4组序列互不相同，固定的初始种子i保证了每次运行整体生成的结果完全可复现，同时代码更简洁，运行效率更高。

内容的提问来源于stack exchange，提问作者Ethan Mark