VBA：24层嵌套IF语句的性能 [英] VBA : Performance on 24-deep nested IF statement
问题描述
原始问题
我有一个24深嵌套IF语句的子句
l = 2
虽然l <= lmax'lmax = 15000
如果condition1 then
如果condition2a或condition2b则
...
如果condition24然后
ReDim保留命题（UBound（命题）+ 1）
命题（UBound（命题））= l
由于此子被称为250次，IF语句被称为250 * 15000，因此性能是一个大问题。 （宏运行约23秒）
当我写如果condition2a或condition2b然后，VBA检查条件 2b 如果条件 2a 是否为真？ （即应该是 a 和 b ，以便 a 更常见于 b ？）
PS：当然，条件 1 vs 2 已经订购。
简短答案
如@iDevlop所述，简短的回答似乎是VBA不允许短路评估（看到这篇文章）
解决我的性能问题
我的问题是VBA 从表格读取/访问数据（而不是VBA计算IF语句）。
解决方案在中加载数据。这个单一的修改使我的Sub运行速度超过10倍（小于2s vs 23s）。
原始代码
这是我的陈述的一个较短的（17深）版本：
With Sheets（Sheet1）
lmax = .Cells（100000，1）.End（xlUp）.Row'通常14000
l = 2
虽然l <= lmax
如果boolean_ignore_param1或Left（.Cells（l， 1）.Formula，Len（param1））= param1 Then
如果boolean_ignore_param2或Left（.Cells（l，2）.Formula，Len（param2））= param2Then
If（param_boolean_A和.Range AF& l）.Formula =Yes）或（param_boolean_B和.Range（Ag& l）.Formula =Yes）然后
If（.Cells（1，6））。公式=或.Cells（1，6）.Value - marge< = param3或param3 = 0）然后
如果（.Cells（1，7）.Formula =或.Cells（l， 7）.Value + marge> = param3或param3 = 0）然后
If（ .Cells（1，8）.Formula =或.Cells（1，8）.Value - marge = = param4或param4 = 0）然后
如果（.Cells（l，9）.Formula = 或.Cells（l，9）.Value + marge> = param4或param4 = 0）然后
If（.Cells（l，10）.Formula =或.Cells（l，10）如果（.Cells（l，11）.Formula =或.Cells（l，11）.Value + marge> = param5或param5 = 0）然后
如果（.Cells（l，12）.Formula =或.Cells（l，12）.Value< = param6或param6 = 0）然后
If（.Cells （l，13）.Formula =或.Cells（l，13）.Value> = param6或param6 = 0）然后
如果（.Cells（l，16）.Formula =或。单元格（1，16）.Value - marge< = param7或param7 = 0）然后
If（.Cells（l，17）.Formula =或.Cells（l，17）.Value + marge > = param7或param7 = 0）Then
If（.Cells（l，18）.Formula =O r .Cells（l，18）.Value - marge< = param8或param8 = 0）然后
If（.Cells（l，19）.Formula =或.Cells（l，19）.Value + marge> = param8或param8 = 0）然后
If（.Cells（l，22）.Formula =或.Cells（l，22）.Value - marge< = param9或param9 = 0 ）然后
如果（.Cells（l，23）.Formula =或.Cells（l，23）.Value + marge> = param9或param9 = 0）然后
ReDim保留命题UBound（命题）+ 1）
命题（UBound（命题））= l
而不是或可以使用选择案例，其逗号分隔条件列表如下：
'如果condition2a或condition2b然后
选择案例True
案例condition2a，condition2b'这里逗号表示懒惰'OR'如vb.net中的OrElse）
's = s + 10
Case Else
's = s + 20
结束选择
另外，可能有很多点如果我们可以看到您的代码，可以提高宏观性能。立即，数组的redim再添加一个项目可能会在循环中耗费时间：
ReDim保留命题（UBound （命题）+ 1）
您可以考虑将其每次增加10或100个项目达到其长度（为下一个可能的用途预留一些空间），但将实际上限索引保留在变量中...
更新：
当您添加部分代码时，我可以建议您为每个代码使用一些帮助函数，如下所示：
替换 x< param 如果 ：
If（.Cells（1，6）.Formula =或.Cells（1，6）.Value- marge< = param3或param3 = 0）然后...
>
如果测试（.Cells（l，6）.Value，marge，param3）Then ...
'or without' .Value'：如果test（.Cells（l，6），marge，param3）Then ...
我们可以定义此函数：
函数testLesser （v As Variant，marge As Double，param As Double）As Boolean
'testLesser =（v =或v-marge <= param3或param3 = 0）
如果v =然后
ElseIf v - marge< = param然后
ElseIf param = 0然后
Else
testLesser = False：退出函数
结束如果
testLesser = True
'**另一个选项（使用选择案例）：
'选择案例True
'案例v =，v - marge< ; = param，param = 0
'testLesser = True
'Case Else
'testLesser = False
'End选择
结束函数
和其他类型（大于）类似，如果 s：
If（.Cells（l，7）.Formula =or .Cells（1，7）.Value我们有： 函数testGreater（v As Variant，marge As Double，param As Double）As Boolean
'testGreater =（v =Or v + marge> = param或param = 0）
如果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
'**另一个选项（使用选择案例）：
'选择案例True
'案例v =，v + marge> = param，param = 0
'testLesser = True
' Case Else
'testLesser = False
'End选择
结束功能
因此，代码将如下所示：
'If（.Cells（l，6））。公式=或.Cells（1，6）.Value - marge< = param3或param3 = 0）然后
'If（.Cells（1，7）.Formula =或.Cells ，7）.Value + marge> = param3或param3 = 0）然后
'If（.Cells（l，8）.Formula =或.Cells（l，8）.Value - marge< = param4或param4 = 0）然后
'If（.Cells（l，9）.Formula =或.Cells（l，9）.Value + marge> = param4或param4 = 0）然后
'...
如果testLesser（.Cells（l，6），marge，param3）Then
如果testGreater（.Cells（l，7），marge，param3）Then
如果testLesser（.Cells （l，8），marge，param4）然后
如果testGreater（.Cells（l，9），marge，param4）Then
'...
我的真实测试显示更快！ （显然，它也是更可读的代码）
注意：
非常重要的是安排如果条件让你尽快得到最终的条件！例如，如果单元格值通常为空，则将该条件设置在我们的测试函数中，但是如果param = 0通常为真，则将其作为第一个条件检查...
这是 x OR y 标准的规则。对于'x AND y'标准，这是相反的！最罕见的情况一定是要快速过滤结果。在你的代码中，我看到你安排从单元格（1，6）到的单元格（l，23）。我不知道这是否适合你的情况。这取决于你的数据和通常的情况，所以考虑修改那些嵌套的的顺序，如果你知道一些通常是错误的 c $ c>
另一个提示：
而不是使用 With Sheets（Sheet1），将其缓存在变量中，这可以提高性能！
Dim mySheet As Worksheet
设置mySheet = Sheets（Sheet1）
使用mySheet'Sheet（Sheet1）
我的测试显示，这个简单的参考变化比 10％更快。您可能会在使用工作表，范围，单元格时考虑其他类似更改。
注意：如果我们可以定义 marge 作为全局或工作表级别var，我们可以从函数params中删除它，但似乎它没有明智的效果...
最后更新：
由@Ioannis在评论中提出（参见本参考文献），当使用大范围的单元格时，更好地将值加载到2D数组中并使用它，而不是直接访问单元格：
myArray = Sheets（Sheet1）。Range（A1：AG15000）。value
/ pre>
然后使用该数组读/写为：
myArray（row，col）= myArray（row，col）+ 1
'row = 1 to UBound（myArray，1）'第一个数组维度用于行
'col = 1 to UBound（myArray ，2）'第二个数组维度用于列
最后，当你完成你可以反向更新整个范围：
Sheets（Sheet1）。Range（A1：AG15000）= myArray
Original question
I have a sub with a 24-deep nested IF statement
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
Since this sub is called 250 times, the IF statement get called 250 * 15000, thus performance is a big issue. (The macro run in about 23 seconds.)
When I write If condition2a or condition2b Then, does VBA check condition2b if condition2a is true ? (Ie, should ordrer a and b so that a is less often true that b ?)
PS : Of course, condition 1 vs 2 are already ordered.
Short answer
As stated by @iDevlop, the short answer seems to be that VBA doesn't allow "short-circuit evaluation" (See this post)
Solution to my performance problem
My problem was VBA reading/accessing data from the sheet (rather than VBA computing the IF statement.).
The solution was loading data in a 2D-array. This single modification make my Sub run more than 10 times quicker (less than 2s vs 23s).
Original code
Here is a shorter (17-deep) version of my statement :
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
解决方案 instead of or, you can use Select Case with comma seperated list of conditions as in following:
'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
Also, there may be many points to improve your macro performance if we can see your code. instantly, the redim of array to add one more item to it may be time consuming in a loop:
ReDim Preserve propositions(UBound(propositions) + 1)
you may consider to increase its ubound as 10 or 100 items each time you reach its length (to reserve some space for next probable uses), but keep the actual upper bound index in a variable...
Update:
as you add some part of your code, i can suggest you to use some helper function for each if as following:
to replace x<param if's:
If (.Cells(l, 6).Formula="" Or .Cells(l, 6).Value-marge<=param3 Or param3=0) Then ...
with something like as:
If test(.Cells(l, 6).Value, marge, param3) Then ...
'or without '.Value': If test(.Cells(l, 6), marge, param3) Then ...
we can define this function:
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
and similar for other type (greater than) of ifs:
If (.Cells(l, 7).Formula="" Or .Cells(l, 7).Value+marge>=param3 Or param3=0) Then ...
we have:
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
So, the code will look like as:
'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
'...
My real test shows its faster! (and obviously, its also more readable code)
Note:
its very important to arrange the if conditions such that you get final condition as soon as you can! for example if cell values are usually empty, put that condition at first in our test function, but if param = 0 is generally true, bring it as first condition check...
this is the rule for x OR y criteria. for 'x AND y' criteria, it is the reverse! the most rare case must be at first to quickly filter the results. in your code, i see you arrange the nested if's from Cells(l, 6) to Cells(l, 23). I don't know if this is best for your situation. it depends on your data and usual cases, so consider revising order of those nested if's if you know some are usually false...
Another Tip:
instead of using With Sheets("Sheet1"), cache it within a variable, this can improve the performance!
Dim mySheet As Worksheet
Set mySheet = Sheets("Sheet1")
With mySheet 'Sheets("Sheet1")
my test shows this simple reference change is faster about 10%. you may think of other similar changes when working with sheets, ranges, cells...
Note: if we can define marge as global or sheet level var, we could remove it from function params but it seems that it doesn't have sensible effect...
Last Update:
as suggested by @Ioannis in comments (see also this ref) when working with a large range of cells, its better to load values into a 2D Array and using it instead of direct access to the Cells:
myArray = Sheets("Sheet1").Range("A1:AG15000").Value
then use that Array for read/writes as:
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
finally when you finished you can update the entire range reversely:
Sheets("Sheet1").Range("A1:AG15000") = myArray
这篇关于VBA：24层嵌套IF语句的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
