为什么我的种子产生随机数不工作?

Question

我需要生成几组随机数，我的代码的最小工作示例如下:

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是唯一的，但我发现RA2, RA3和RA4中的随机数在每个i中都是完全相同的。换句话说，我的代码给出了RA1 <>Ra2 = ra3 = ra4。为什么呢?我认为改变RND的参数会改变随机数的种子?特别地，我需要RA1, RA2, RA3和RA4对每个I都有自己的随机数集，但是当我重新运行整个过程时，我每次都应该得到相同的随机数。我如何调整我的代码来实现这一点?

编辑

经过一些头脑风暴和猜测和检查的工作，下面的代码做到了!

子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

它也更短更甜。我很高兴:)

Answer 1

你可以给一个数组赋值，洗牌数组x次，保存到其他数组，哑巴到表。通过限制与表单的交互次数，这也应该更快:

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