我可以优化和加速我的 VBA 代码吗?

Question

我不是 VBA 大师，但有能力将以下代码拼接在一起。 它本质上格式化发票以隐藏任何空行（填充了 vlookup，但没有值返回 "），设置为 1 页纵向并导出为 PDF。

我的问题是：

1. 代码从头到尾运行需要很长时间。

以下是我想要优化和加速的VBA代码。

Sub Save_Quote_As_PDF()
Application.ScreenUpdating = False

Dim a As Long
For a = 1 To ActiveSheet.Shapes.Count
   On Error Resume Next
   With ActiveSheet.Shapes.Item(a)
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
Next a
On Error GoTo 0

ActiveSheet.Range("DCANUMBER").SpecialCells(4).EntireRow.Hidden = True

Dim PdfFilename As Variant
PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ActiveWorkbook.Path & "" & ActiveSheet.Range("N2").Value, _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")

If PdfFilename <> False Then
    With ActiveSheet.PageSetup
        .Orientation = xlPortrait
        .PrintArea = "$A$1:$K$78"
        .PrintTitleRows = ActiveSheet.Rows(19).Address
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=PdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End If

 ActiveSheet.Range("DCANUMBER").SpecialCells(4).EntireRow.Hidden = False
 Application.ScreenUpdating = True
End Sub

Answer 1

隐藏空白行：

Dim ws As Worksheet
Set ws = ActiveSheet ' ActiveSheet is of type Object so using type Worksheet is a tiny bit faster
Dim row As Range ' again, specifying the type makes it a tiny bit faster
For Each row In ws.UsedRange.Rows    ' .UsedRange to limit the range to only the used range   
    If row.Find("*") Is Nothing Then
        row.EntireRow.Hidden = True
    End If
Next

我不认为隐藏行是代码中最慢的部分。您应该对代码进行计时，以查看哪些部分最慢：

Dim start As Single
start = Timer
' part of the code
Debug.Print CDbl(Timer - start), "part 1" ' CDbl to avoid scientific notation
start = Timer ' remember to reset the start time
' another part of the code
Debug.Print CDbl(Timer - start), "part 2"