VBA-对于大范围的每个循环都很有效

Question

我对VBA还很陌生，正试图弄清楚如何在大范围(500k(内为每个循环使用。

我想根据概率在一个表中计算几个场景，该概率具有以下格式：

Group Type   | Group 1 | Group 2 | Group 3.
Probability  | 20%     | 30%     | 70%
Scenario 1   | 0       | 1       | 0 
Scenario 2   | 0       | 0       | 1   
....
Scenario 500k| 0       | 1       | 1 

不幸的是，我的For each循环只能在10k以下的小范围内工作——你们中有人知道我如何更好地使用它吗？

Sub ScenarioCalculation()

Dim propability As Double, random As Double, row As Long, col As Long
Application.ScreenUpdating = False
For col = 4 To 23
For row = 25 To 100
propability = Cells(12, col + 1).Value
random = 0# + Rnd * 1#
If random < propability Then
Cells(row + 1, col + 1).Value = 1
Else
Cells(row + 1, col + 1).Value = 0
End If
Next row
Next col

End Sub

Answer 1

逐单元操作相对较慢。您可以使用阵列来提高性能。

这应该更快：

Sub ScenarioCalculation()

Const NUM_SCENARIOS As Long = 10000 ' for example
Dim propability As Double, random As Double, row As Long, col As Long, arr
Dim rng As Range, ws As Worksheet

Set ws = ActiveSheet 'for example

Application.ScreenUpdating = False

For col = 4 To 23
propability = ws.Cells(12, col + 1).Value   'only need to read this once...
Set rng = ws.Cells(25, col + 1).Resize(NUM_SCENARIOS)
arr = rng.Value                             'create array from range
'populate the array
For row = 1 To UBound(arr, 1)
random = 0# + Rnd * 1#
arr(row, 1) = IIf(random < propability, 1, 0)
Next row

rng.Value = arr 'populate the range from the array
Next col

End Sub

Answer 2

创建一个长变量并使用范围：

Dim i As Long
Dim rng As Range
Dim probability as variant
Dim col as integer
Set rng = Range("B2")

For col = 0 to 20
probability=rng.offset(0,1).value
For i = 1 To 500000
random = 0# + Rnd * 1#
rng.offset(0,2 + col).value = iif(random<probability,1,0)
Set rng = rng.Offset(1, 0)
Next
Next

通过这种方式，您可以使用Excels的内部功能来使用大量单元格并单独处理每一行。正如@Tim Williams所展示的那样：如果你把它放在中

On Error Goto EndThisSub
Application.ScreenUpdating = False
....
EndThisSub:
Application.ScreenUpdating = True

它会快得多。