excel - 我的 VBA 代码每次迭代都会变慢

Question

我正在使用代码添加一列，将值存储在左侧(通过 vlookup)，然后自动填充整个列。我遇到的问题是每次我使用宏时，代码都会花费越来越长的时间。将不胜感激任何帮助:)

这是代码:

 Sub insert_col()
 '
 ' insert_col Macro
 '
 ' Keyboard Shortcut: Ctrl+w
 '
 Dim x As Variant
 Dim a As Long
 Dim b As Long
 Dim y As Variant
 Dim t As Single

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

t = Timer

ActiveSheet.Columns(ActiveCell.Column).EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
x = ActiveCell.Column
Cells(22, x).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R13C1:R193C2,1)"
Cells(22, x).Select
a = ActiveCell.Column
x = ActiveCell.Row
y = ActiveCell.End(xlDown).Row
Selection.AutoFill Destination:=Range(Cells(x, a), Cells(36600, a)), Type:=xlFillDefault
ActiveCell.Offset(0, 2).Select

MsgBox Timer - t

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

谢谢!

Answer 1

为了更清楚地了解上面的评论，您的代码每次运行时都会添加额外的公式，这会增加计算时间。

您可以更简单地使用以下代码:

Sub insert_col()

' Keyboard Shortcut: Ctrl+w

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

t = Timer

ActiveCell.EntireColumn.Insert
Range(Cells(22, ActiveCell.Column), Cells(36600, ActiveCell.Column)).FormulaR1C1 = "=VLOOKUP(RC[-1],R13C1:R193C2,1)"
ActiveCell.Offset(0, 2).Select

MsgBox Timer - t

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub