Excel VBA 代码优化：生成两组模拟数据

Private Sub CommandButton1_Click() Dim i As Double, j As Double, k As Double, l As Integer, m As Double, n As Double Dim lastRow As Long Dim o As Double, p As Double, q As Double, r As Integer, s As Double, t As Double

'第一组数据
i = 0.031
k = 2331.66 / 31.6
j = i * k
l = 0
m = 0.005 + WorksheetFunction.RandBetween(1, 9) / 10000 + WorksheetFunction.RandBetween(1, 9) / 100000 + WorksheetFunction.RandBetween(1, 9) / 100000
n = m

lastRow = Range('A' & Rows.Count).End(xlUp).Row

While i <= 31.6 And j <= 2331.66
    l = l + 1
    Range('A' & lastRow + l).Value = l
    Range('B' & lastRow + l).Value = 1008
    Range('C' & lastRow + l).Value = i
    Range('D' & lastRow + l).Value = j
    Range('E' & lastRow + l).Value = n
    i = i + 0.031 + WorksheetFunction.RandBetween(1, 4) / 1000
    j = i * k
    n = m + n
Wend

Range('A' & lastRow + l).Value = l
Range('B' & lastRow + l).Value = 1008
Range('C' & lastRow + l).Value = 31.57
Range('D' & lastRow + l).Value = 2331.66
Range('E' & lastRow + l).Value = n + m

MsgBox '第一组模拟数据完成'

'第二组数据
o = 0.031
q = 2891.85 / 31.6
p = o * q
r = l + 1
s = 0.005 + WorksheetFunction.RandBetween(1, 9) / 10000 + WorksheetFunction.RandBetween(1, 9) / 100000 + WorksheetFunction.RandBetween(1, 9) / 100000
t = s

While o <= 31.6 And p <= 2331.66
    Range('A' & lastRow + r).Value = r
    Range('B' & lastRow + r).Value = 1009
    Range('C' & lastRow + r).Value = o
    Range('D' & lastRow + r).Value = p
    Range('E' & lastRow + r).Value = t
    o = o + 0.031 + WorksheetFunction.RandBetween(1, 4) / 1000
    p = o * q
    t = s + t
    r = r + 1
Wend

Range('A' & lastRow + r - 1).Value = r - 1
Range('B' & lastRow + r - 1).Value = 1009
Range('C' & lastRow + r - 1).Value = 31.57
Range('D' & lastRow + r - 1).Value = 2891.85
Range('E' & lastRow + r - 1).Value = s + t

MsgBox '第二组模拟数据完成'

End Sub

说明：

将变量的声明放在最前面，将第二组数据的声明放在第一组数据的下面，避免了多次声明变量。
在第二组数据的循环中，将r的初始化放在了p的计算前面，否则第一次循环r会重复计算一次。
将最后一行数据的行数计算放在循环外面，避免了多次计算。