彭博数据历史记录的 VBA-Macro 效率

Question

我对 VBA 很陌生。我编写了这段代码并且它可以工作，但是它需要很长时间才能运行，我觉得它运行的时间越长，它变得越来越慢。

有没有办法让这样的事情更有效率？如果有人在这里给我一些建议，那就太棒了。

Sub Sheets()
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMinimized

Dim Data As String
Dim i As Long
Dim k As Long
Dim x As Long
Dim y As String


For i = 2 To 255

Sheetname = Worksheets("Input").Cells(i, 1).Value
Worksheets.Add.Name = Sheetname
ActiveSheet.Move After:=Worksheets(ActiveWorkbook.Sheets.Count)

x = 1

For k = 2 To 876
Data = Worksheets("Input").Cells(i, k).Value
y = Cells(1, x).Address(RowAbsolute:=False, ColumnAbsolute:=False)
BloomB = "=BDH(" & y & ",""TURNOVER"",""8/1/2011"",""4/30/2016"",""Dir=V"",""Dts=S"",""Sort=A"",""Quote=C"",""QtTyp=Y"",""Days=T"",""Per=cd"",""DtFmt=D"",""UseDPDF=Y"")"
Worksheets(Sheetname).Cells(1, x) = Data
Worksheets(Sheetname).Cells(2, x) = BloomB
x = x + 2
Next k

Application.Wait (Now + TimeValue("0:00:03"))
Next i

ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Answer 1

删除 Application.Wait (Now + TimeValue("0:00:03")) .或者把 Next i上面是这样的:

Next i
Application.Wait (Now + TimeValue("0:00:03"))

您将获得大约 43 分钟的时间。

如果你仍然喜欢等待的想法，那就更复杂一点，像这样:

If i Mod 50 = 0 Then Application.Wait (Now + TimeValue("0:00:02"))
Next i

因此，它将等待每 50 次迭代。