vba - 另一种方法而不是 do-until 循环使代码执行得更快

Question

我需要有关如何使我的代码执行得更快的帮助。我有大量数据和我的“Do Until loop ' 导致执行缓慢。我想知道是否有办法让我的代码运行得更快。我只使用 do until 循环，因为我对它感到满意。谢谢

Dim WB1 As Workbook
Dim ws1 As Worksheet
Dim last As Long
Dim i As Integer
Dim x As String, y As String

Set WB1 = ThisWorkbook
Set ws1 = WB1.Worksheets("Source")

last = ws1.Cells(Rows.Count, "X").End(xlUp).Row

i = 2
Do Until Sheets("Source").Cells(i, 1) = ""
    x = Left(Sheets("Source").Cells(i, 6), 3)
    y = Left(Sheets("Source").Cells(i, 23), 7)
    If x = "611" Or y = "INITIAL" Then

        Sheets("Source").Cells(i, 25) = "INITIAL"

    Else

        ws1.Range("Y2:Y" & last).Formula = "=VLOOKUP(A:A,'Assignment Reference'!$C:$E,3,FALSE)"

    End If

    i = i + 1
Loop

Answer 1

有很多方法可以提高 Excel 的 VBA 代码的性能。充其量您阅读一些有关此的文章:

Excel VBA performance coding best practices

Optimizing VBA

编辑:
正如 Bond I 所建议的那样，我在 VBA 代码中为提高执行性能所做的工作:

Option Explicit

Dim screenUpdating As Boolean
Dim calculation As XlCalculation
Dim enableEvents As Boolean
Dim displayPageBreaks As Boolean



' Freezes Excel into its current state to improve
' performance during executing macro code. Be sure
' to call DoEvents occasionally during execution to
' prevent completly freezing the Excel window.
' Call this routine before all other code but after
' setting up proper exception handling.
Public Sub freezeSystem()
    'Save Excel configuration to reset later
    screenUpdating = Application.screenUpdating
    calculation = Application.calculation
    enableEvents = Application.enableEvents
    displayPageBreaks = ActiveSheet.displayPageBreaks

    'Turn off some Excel functionality so your code runs faster
    Application.screenUpdating = False
    Application.calculation = xlCalculationManual
    Application.enableEvents = False
    ActiveSheet.displayPageBreaks = False 'Note this is a sheet-level setting, but only necessary for ActiveSheet if code do not change the ActiveSheet
End Sub




' Unfreezes Excel and resets it into the configuration it had
' before the freezeSystem() was executed.
' Call this routine at the end of the macro code and also during
' the cleanup of exception handling.
Public Sub defreezeSystem()
    ' Reset Excel configuration into previous state
    Application.screenUpdating = screenUpdating
    Application.calculation = calculation
    Application.enableEvents = enableEvents
    ActiveSheet.displayPageBreaks = displayPageBreaks 'Note this is a sheet-level setting, but only necessary for ActiveSheet if code do not change the ActiveSheet

    ' Perform recalculation of all formulas
    Application.CalculateFullRebuild
End Sub




' This routine is intended to update the Excel window
' while executing macro code in controlled manner if
' necessary. Be aware that calling this function too
' often will drastically reduce the execution performance.
Public Sub updateSystem()
    If Application.screenUpdating = False Then
        Application.screenUpdating = True
        Application.CalculateFullRebuild
        Application.screenUpdating = False
    Else
        Application.CalculateFullRebuild
    End If
End Sub

调用 freezeSystem()在开始或您的代码中以提高执行期间的性能。调用 defreezeSystem()在您的代码末尾，并在必要时在异常处理期间进行清理。

我这样做:

Sub entryPoint()
    On Error GoTo entryPointErrorHandler    ' set up exception handling
    freezeSystem
    [your regular code goes here]

entryPointCleanUp:                          ' clean up from exception and normal operation
    [your cleanup code goes here]
    defreezeSystem
    Exit Sub

entryPointErrorHandler:                     ' exception handling
    [your exception handling code goes here]
    GoTo entryPointCleanUp                  ' jump to clean up code

End Sub