时间戳

首页 > 解决方案 > 如何让 Excel VBA 宏运行得更快

vba - 如何让 Excel VBA 宏运行得更快

问题描述

我对 VBA 比较陌生，并且正在尝试使用非常长的 VBA 代码创建仪表板。该代码用于从多个工作表中查找值，以及用空白单元格替换任何 #N/A 错误或 0，以及取消保护和保护工作表。代码本身可以工作，但运行速度非常慢并冻结了 Excel 工作表。任何有关如何使代码运行得更快的提示将不胜感激！

Sub RunDashboard()

Sheets("Dashboard").Select
ActiveSheet.Unprotect

Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C4:R10000C4"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,4,FALSE)"

Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C5:R10000C5"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,5,FALSE)"

Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C6:R10000C6"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,6,FALSE)"

Range("G2:G" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C7:R10000C7"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,7,FALSE)"

Range("H2:H" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C8:R10000C8"
Selection.FormulaR1C1 =         
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,8,FALSE)"

Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C9:R10000C9"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,9,FALSE)"

Range("K2:K" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C11:R10000C11"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,11,FALSE)"

Range("P2:P" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C16:R10000C16"
Selection.FormulaR1C1 =     
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,16,FALSE)"

Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C20:R10000C20"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,20,FALSE)"

Range("X2:X" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C24:R10000C24"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,24,FALSE)"

Range("Y2:Y" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C25:R10000C25"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,25,FALSE)"

Range("Z2:Z" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C26:R10000C26"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,26,FALSE)"

Range("AA2:AA" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C27:R10000C27"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,27,FALSE)"

Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C28:R10000C28"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,28,FALSE)"

Range("AH2:AH" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C34:R10000C34"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,34,FALSE)"

Range("AI2:AI" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C35:R10000C35"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,35,FALSE)"

Range("AJ2:AJ" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C36:R10000C36"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,36,FALSE)"

Range("AK2:AK" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C37:R10000C37"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,37,FALSE)"

Range("AL2:AL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C38:R10000C38"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,38,FALSE)"

Range("AM2:AM" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C39:R10000C39"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,39,FALSE)"

Range("AN2:AN" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C40:R10000C40"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,40,FALSE)"

Range("AO2:AO" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C41:R10000C41"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,41,FALSE)"

Range("AP2:AP" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C42:R10000C42"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,42,FALSE)"

Range("AQ2:AQ" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C43:R10000C43"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,43,FALSE)"


Range("L2:L" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C11:R10000C11"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,12,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,3, FALSE))"

Range("M2:M" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C13:R10000C13"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,13,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,21, FALSE))"

Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C14:R10000C14"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,14,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,22, FALSE))"


Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C15:R10000C15"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,15,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,4, FALSE))"

Range("Q2:Q" & Cells(Rows.Count, "A").End(xlUp).Row).Select
ActiveCell.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,17,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,7, FALSE))"


Range("R2:R" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C18:R10000C18"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,18,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,18, FALSE))"

Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C19:R10000C19"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,19,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,14, FALSE))"


Range("U2:U" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C21:R10000C21"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,21,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,5, FALSE))"


Range("V2:V" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C22:R10000C22"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,22,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,30, FALSE))"

Range("W2:W" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C23:R10000C23"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,23,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,31, FALSE))"


Range("AD2:AD" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C30:R10000C30"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,30,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,23, FALSE))"

Range("AE2:AE" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C31:R10000C31"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,31,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,24, FALSE))"

Range("AF2:AF" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C32:R10000C32"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,32,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,52, FALSE))"


Sheets("Dashboard").Select
On Error Resume Next
With Range("A2:A10000")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Sheets("Dashboard").Select
Dim c As Range, LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For Each c In Range("A1:AQ1" & LastRow)
If Application.IsNA(c.Value) Then
c.Value = ""
End If
Next

Dim rng As Range
For Each rng In Range("A1:AQ1" & LastRow)
    If rng.Value = 0 Then
       rng.Value = ""
    End If
Next

Sheets("Dashboard").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

MsgBox "Your dashboard has finished running."

End Sub

标签： vbaexcel

解决方案

除了@girlvsdata 和@jdoe 提出的建议之外，您还可以通过防止Ex​​cel 在每次从一个单元格切换到下一个单元格或更改数据时更新显示和计算所有公式来加快速度。

在 sub 的开头执行此操作：

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

然后在最后执行此操作以重新启用：

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

除了其他建议之外，这应该会有所帮助。

推荐阅读