我正在尝试将小数组打印到 Excel 电子表格中。大部分代码根据用户的判断循环 n 次。
输出 Sub 函数在第一次迭代时正确打印，但是当数组在下一次迭代中更改并且子函数移动到下一行进行输出时，它们还会修改电子表格中第一次迭代的第一个数组值。
示例：如果我经历了五次迭代，并且它们都在各自的数组中生成不同的值，则到第 5 次迭代时，所有已打印的五列都将修改为与上次迭代相同。
我正在尝试阻止代码替换以前的值。
我尝试了大 for 循环内部数组的擦除函数，这破坏了代码。
For loop for iterations
Dim Iter As Integer
For Iter = 1 To number_of_iterations
Randomize
reward_present = Int((1 - 0 + 1) * Rnd + 0)
reward_string = reward_present
reward_present = 1
'Randomize whether there is a reward present or not
If reward_present = 1 Then
Dim door_probabilities() As Variant
ReDim door_probabilities(1 To number_of_doors)
Dim remainder As Double
Dim reward_door As Integer
Dim reward_door_string As String
remainder = 1
For i = 1 To (number_of_doors - 1)
door_probabilities(i) = RndDbl(0, remainder)
remainder = remainder - door_probabilities(i)
Next i
door_probabilities(number_of_doors) = remainder
'randomizing probabilities of each door
Dim max As Variant
max = door_probabilities(1)
reward_door = 0
For i = 1 To number_of_doors
If max <= door_probabilities(i) Then
max = door_probabilities(i)
reward_door = i
End If
Next i
reward_door_string = reward_door
'choosing the reward door based on probability
If number_of_doors = 3 Then
random_player_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
game_doors(random_player_choice) = 1
ArrayFillPlayer1 game_doors, Iter
'choose first player door randomly
'output here
For i = LBound(game_doors) To UBound(game_doors)
msg = msg & game_doors(i) & vbNewLine
Next i
MsgBox "Game doors player choice 1: " + msg
random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
Do While random_host_choice = random_player_choice
random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
Loop
If random_host_choice = reward_door Then
Do While random_host_choice = reward_door
random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
Loop
End If
game_doors(random_host_choice) = 1
ArrayFillHost game_doors, Iter
'choose host door randomly
'output here
For i = LBound(game_doors) To UBound(game_doors)
msg = msg & game_doors(i) & vbNewLine
Next i
MsgBox "Game doors host choice: " + msg
random_player_choice2 = Int((number_of_doors - 1 + 1) * Rnd + 1)
Do While random_player_choice2 = random_host_choice
random_player_choice2 = Int((number_of_doors - 1 + 1) * Rnd + 1)
Loop
game_doors(random_player_choice2) = 1
'choose second player door
ArrayFillPlayer2 game_doors, Iter
For i = LBound(game_doors) To UBound(game_doors)
msg = msg & game_doors(i) & vbNewLine
Next i
ReDim game_doors(1 To number_of_doors)
End If
Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
'Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim TheRange As Range
CellsDown = 1
CellsAcross = 3
'Cells.Clear
'Set worksheet range
Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))
Transfer temporary array to worksheet
TheRange.Value = TempArray
End Sub
Sub ArrayFillHost(TempArray As Variant, RowToWrite As Integer)
'Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim TheRange As Range
CellsDown = 1
CellsAcross = 6
'Cells.Clear
'Set worksheet range
Set TheRange = Range(Cells(RowToWrite, 4), Cells(CellsDown, CellsAcross))
Transfer temporary array to worksheet
TheRange.Value = TempArray
End Sub
Sub ArrayFillPlayer2(TempArray As Variant, RowToWrite As Integer)
'Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim TheRange As Range
CellsDown = 1
CellsAcross = 9
'Cells.Clear
'Set worksheet range
Set TheRange = Range(Cells(RowToWrite, 7), Cells(CellsDown, CellsAcross))
Transfer temporary array to worksheet
TheRange.Value = TempArray
End Sub
我希望每个连续行的输出是不同的，但它们都是追溯修改的。
看起来你的意思是在你的潜艇中使用 Resize() 来填充工作表的数组？
例如：
Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
'Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim TheRange As Range
CellsDown = 1
CellsAcross = 3
'Cells.Clear
'Set worksheet range
Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))
Transfer temporary array to worksheet
TheRange.Value = TempArray
End Sub
这里是一行：
Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))
与以下相同：
Set TheRange = Range( Cells(RowToWrite, 1), Range("C1") )
并且"C1"随着RowToWrite的增加而保持不变，因此每次您都用TempArray填充更大的范围。
这更接近您想要的：
Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
Range(Cells(RowToWrite, 1).Resize(1, 3).Value = TempArray
End Sub