VBA代码优化

Question

我有一组VBA代码，可以很好地处理大约2000x16的单元格。但是，我需要使用最多80000 x 16个单元的代码。

我发现了两种运行速度非常慢的代码：

    c = 2 'this is the first row where your data will output
    d = 2 'this is the first row where you want to check for data

    Application.ScreenUpdating = False


    Do Until Range("A" & c) = "" 'This will loop until column U is empty, set the column to whatever you want
                            'but it cannot have blanks in it, or it will stop looping. Choose a column that is
                            'always going to have data in it.

     ws1.Range("U" & d).FormulaR1C1 = "=RC[-20] & RIGHT(""0000"" & RC[-14], 6)"

     c = c + 1 'Advances a and b when there is a matching case
     d = d + 1

    Loop

    Application.ScreenUpdating = True

End Sub 

Sub OpenValue()    
    Dim l As Integer
    Dim k As Integer
    Dim m As Integer

    m = Sheets("Input").Range("AC:AC").End(xlDown).Row

    For l = 2 To m

    If Range("AC" & l) = "Delievered" Then
       Range("AD" & l) = 0

    ElseIf Range("AC" & l) = "Cancelled" Then
       Range("AD" & l) = 0

    Else
      Range("AD" & l) = Val(Range("Z" & l)) * Val(Range("J" & l))

         End If

    Next

End Sub

我能做些什么来使它们流行起来……

Answer 1

@GSerg提供的链接是缩短脚本运行时间的绝佳方法。我发现自己在使用：

• Application.ScreenUpdating设置为False
• Application.Calculation设置为xlCalculationManual
• Application.EnableEvents设置为False
• Application.DisplayAlerts设置为False

我经常将它们组合成一个公开子例程。@Garys-Student提供了灵感：

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT         : True or False (i.e. fast or slow)
'DESCRIPTION   : this sub turns off screen updating and alarms then
'                sets calculations to manual
'
Public Sub GoFast(OnOrOff As Boolean)
    Dim CalcMode As XlCalculation
    CalcMode = Application.Calculation
    With Application
        .ScreenUpdating = Not OnOrOff
        .EnableEvents = Not OnOrOff
        .DisplayAlerts = Not OnOrOff
        If OnOrOff Then
            .Calculation = xlCalculationManual
        Else
            .Calculation = CalcMode
        End If
    End With
End Sub

实际上，您现在可以添加一行代码：

Call GoFast(True)

在脚本的开头作为设置的一部分，然后添加：

Call GoFast(False)

在你的脚本末尾作为拆卸的一部分。按您认为合适的方式进行修改！

Answer 2

可以将Do Until替换为one线条：

ws1.Range("A2", ws1.Range("A2").End(xlDown)).Offset(0,20).FormulaR1C1 = _
    "=RC[-20] & RIGHT(""0000"" & RC[-14], 6)"

请注意，如果A3为空，则此操作将失败。如果您在第一行中有标题，则可以将第二个A2更改为A1。

对于另一个Sub，我不确定你是否在用Val做一些特殊的事情，但如果不是，你可以把它改成类似的东西：

Sub OpenValue()

    Dim r As Range
    Set r = Sheets("Input").Range("AD2:AD" & Sheets("Input").Range("AC1").End(xlDown).Row)
    With r
        .FormulaR1C1 = "=IF(OR(RC[-1]=""Delivered"",RC[-1]=""Cancelled""),0,RC10*RC26"
        'If you want these as values uncomment the following lines
        '.Calculate
        '.Copy
        '.PasteSpecial xlPasteValues
    End With
End Sub

如果需要(计算、ScreenUpdating、DisplayAlerts、EnableEvents)，可以在周围撒一些计算的东西。

为什么这样更快：

简单地说，VBA和Excel之间必须打开一条相互通信的“通道”，这需要一定的时间。因此，对于大范围，循环遍历范围并逐个添加公式要比一次完成所有操作要慢得多，因为您只会打开一次“通道”。