VBA : Performance on 24-deep nested IF statement

Question

原始问题

我有一个带有 24 层嵌套 IF 语句的子程序

l=2
While l <= lmax                'lmax = 15000
   If condition1 Then
      If condition2a or condition2b Then
         ...
            If condition24 then
               ReDim Preserve propositions(UBound(propositions) + 1)
               propositions(UBound(propositions)) = l

由于这个子程序被调用了250次，IF语句被调用了250 * 15000次，因此性能是一个大问题。 (宏运行大约需要 23 秒。)

当我编写Ifcondition2a或condition2bThen时，如果条件2a为真，VBA是否检查条件2b？ (即，是否应该对 a 和 b 进行排序，以便 a 的正确率低于 b？)

PS:当然，条件1与2已经订购。

简短回答

正如 @iDevlop 所说，简短的答案似乎是 VBA 不允许“短路评估”( See this post )

性能问题的解决方案

我的问题是 VBA 从工作表读取/访问数据(而不是 VBA 计算 IF 语句。)。

解决方案是在二维数组中加载数据。这个单一的修改使我的 Sub 运行速度快了 10 倍以上(不到 2 秒 vs 23 秒)。

原始代码

这是我的声明的较短(17 深)版本:

With Sheets("Sheet1")
        lmax = .Cells(100000, 1).End(xlUp).Row    'Usually 14000
        l = 2
        While l <= lmax
            If boolean_ignore_param1 Or Left(.Cells(l, 1).Formula, Len(param1)) = param1 Then
                If boolean_ignore_param2 Or Left(.Cells(l, 2).Formula, Len(param2)) = param2Then
                    If (param_boolean_A And .Range("AF" & l).Formula = "Yes") Or (param_boolean_B And .Range("Ag" & l).Formula = "Yes") Then
                        If (.Cells(l, 6).Formula = "" Or .Cells(l, 6).Value - marge <= param3 Or param3= 0) Then
                        If (.Cells(l, 7).Formula = "" Or .Cells(l, 7).Value + marge >= param3 Or param3 = 0) Then
                        If (.Cells(l, 8).Formula = "" Or .Cells(l, 8).Value - marge <= param4 Or param4 = 0) Then
                        If (.Cells(l, 9).Formula = "" Or .Cells(l, 9).Value + marge >= param4 Or param4 = 0) Then
                        If (.Cells(l, 10).Formula = "" Or .Cells(l, 10).Value - marge <= param5 Or param5 = 0) Then
                        If (.Cells(l, 11).Formula = "" Or .Cells(l, 11).Value + marge >= param5 Or param5 = 0) Then
                        If (.Cells(l, 12).Formula = "" Or .Cells(l, 12).Value <= param6 Or param6 = 0) Then
                        If (.Cells(l, 13).Formula = "" Or .Cells(l, 13).Value >= param6 Or param6 = 0) Then
                        If (.Cells(l, 16).Formula = "" Or .Cells(l, 16).Value - marge <= param7 Or param7 = 0) Then
                        If (.Cells(l, 17).Formula = "" Or .Cells(l, 17).Value + marge >= param7 Or param7 = 0) Then
                        If (.Cells(l, 18).Formula = "" Or .Cells(l, 18).Value - marge <= param8 Or param8 = 0) Then
                        If (.Cells(l, 19).Formula = "" Or .Cells(l, 19).Value + marge >= param8 Or param8 = 0) Then
                        If (.Cells(l, 22).Formula = "" Or .Cells(l, 22).Value - marge <= param9 Or param9 = 0) Then
                        If (.Cells(l, 23).Formula = "" Or .Cells(l, 23).Value + marge >= param9  Or param9 = 0) Then
                            ReDim Preserve propositions(UBound(propositions) + 1)
                            propositions(UBound(propositions)) = l

Answer 1

您可以使用Select Case代替or用逗号分隔的条件列表如下:

'If condition2a Or condition2b Then

Select Case True
Case condition2a, condition2b 'here comma means lazy 'OR' (like as OrElse in vb.net)
  's = s + 10
Case Else
  's = s + 20
End Select

此外，如果我们可以看到您的代码，则可能有很多地方可以提高您的宏性能。立即，重新调整数组以向其中添加一项在循环中可能会非常耗时:

ReDim Preserve propositions(UBound(propositions) + 1)

您可以考虑在每次达到其长度时将其 ubound 增加为 10 或 100 个项目(为下一次可能的用途保留一些空间)，但将实际的上限索引保留在变量中...

<小时/>

更新:

当您添加代码的某些部分时，我可以建议您为每个 if 使用一些辅助函数，如下所示:

替换x<param if的:

If (.Cells(l, 6).Formula="" Or .Cells(l, 6).Value-marge<=param3 Or param3=0) Then ...

类似于:

If test(.Cells(l, 6).Value, marge, param3) Then ...
'or without '.Value': If test(.Cells(l, 6), marge, param3) Then ...

我们可以定义这个函数:

Function testLesser(v As Variant, marge As Double, param As Double) As Boolean

    'testLesser = (v = "" Or v - marge <= param3 Or param3 = 0)

    If v = "" Then
    ElseIf v - marge <= param Then
    ElseIf param = 0 Then
    Else
                testLesser = False: Exit Function
    End If
    testLesser = True

    '** Another option (using Select Case):
    'Select Case True
    'Case v = "", v - marge <= param, param = 0
    '    testLesser = True
    'Case Else
    '    testLesser = False
    'End Select

End Function

对于 if 的其他类型(大于)类似s:

If (.Cells(l, 7).Formula="" Or .Cells(l, 7).Value+marge>=param3 Or param3=0) Then ...

我们有:

Function testGreater(v As Variant, marge As Double, param As Double) As Boolean

    'testGreater = (v = "" Or v + marge >= param Or param = 0)

    If v = "" Then 'testLesser = True
    ElseIf v + marge >= param Then 'testLesser = True
    ElseIf param = 0 Then 'testLesser = True
    Else
                testLesser = False: Exit Function
    End If
    testLesser = True

    '** Another option (using Select Case):
    'Select Case True
    'Case v = "", v + marge >= param, param = 0
    '    testLesser = True
    'Case Else
    '    testLesser = False
    'End Select

End Function

因此，代码将如下所示:

'If (.Cells(l, 6).Formula = "" Or .Cells(l, 6).Value - marge <= param3 Or param3 = 0) Then
'If (.Cells(l, 7).Formula = "" Or .Cells(l, 7).Value + marge >= param3 Or param3 = 0) Then
'If (.Cells(l, 8).Formula = "" Or .Cells(l, 8).Value - marge <= param4 Or param4 = 0) Then
'If (.Cells(l, 9).Formula = "" Or .Cells(l, 9).Value + marge >= param4 Or param4 = 0) Then
'...

If testLesser(.Cells(l, 6), marge, param3) Then
If testGreater(.Cells(l, 7), marge, param3) Then
If testLesser(.Cells(l, 8), marge, param4) Then
If testGreater(.Cells(l, 9), marge, param4) Then
'...

我的真实测试表明它更快! (显然，它的代码也更具可读性)

注意:

安排 if 条件非常重要，以便尽快获得最终条件!例如，如果单元格值通常为空，则首先将该条件放入我们的测试函数中，但如果 param = 0 通常为 true，则将其作为第一个条件检查...

这是 x OR y 的规则标准。对于“x AND y”条件，则相反!最罕见的情况必须首先快速过滤结果。在您的代码中，我看到您安排了来自 Cells(l, 6) 的嵌套 if至Cells(l, 23) 。我不知道这是否最适合您的情况。这取决于您的数据和通常情况，因此请考虑修改嵌套 if 的顺序如果您知道有些通常是错误的...

另一个提示:

而不是使用 With Sheets("Sheet1") ，将其缓存在变量中，这样可以提高性能!

Dim mySheet As Worksheet
Set mySheet = Sheets("Sheet1")
With mySheet 'Sheets("Sheet1")

我的测试显示这个简单的引用更改速度大约10%。在处理工作表、范围、单元格时，您可能会想到其他类似的更改...

注意:如果我们可以定义marge作为全局或工作表级别的变量，我们可以将其从函数参数中删除，但似乎它没有明显的效果......

最后更新:

按照 @Ioannis 在评论中的建议 (see also this ref)当处理大量单元格时，最好将值加载到二维数组中并使用它，而不是直接访问单元格:

myArray = Sheets("Sheet1").Range("A1:AG15000").Value

然后使用该数组进行读/写:

myArray(row, col) = myArray(row, col) + 1 
'row = 1 to UBound(myArray, 1) 'First array dimension is for rows
'col = 1 to UBound(myArray, 2) 'Second array dimension is for columns

最后，当你完成后，你可以反向更新整个范围:

Sheets("Sheet1").Range("A1:AG15000") = myArray