excel - VBA : Performance on 24-deep nested IF statement -
original question
i have sub 24-deep nested if statement
l=2 while l <= lmax 'lmax = 15000 if condition1 if condition2a or condition2b ... if condition24 redim preserve propositions(ubound(propositions) + 1) propositions(ubound(propositions)) = l since sub called 250 times, if statement called 250 * 15000, performance big issue. (the macro run in 23 seconds.)
when write if condition2a or condition2b then, vba check condition2b if condition2a true ? (ie, should ordrer a , b a less true b ?)
ps : of course, condition 1 vs 2 ordered.
short answer
as stated @idevlop, short answer seems vba doesn't allow "short-circuit evaluation" (see post)
solution performance problem
my problem vba reading/accessing data sheet (rather vba computing if statement.).
the solution loading data in 2d-array. single modification make sub run more 10 times quicker (less 2s vs 23s).
original code
here shorter (17-deep) version of 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 if boolean_ignore_param2 or left(.cells(l, 2).formula, len(param2)) = param2then if (param_boolean_a , .range("af" & l).formula = "yes") or (param_boolean_b , .range("ag" & l).formula = "yes") if (.cells(l, 6).formula = "" or .cells(l, 6).value - marge <= param3 or param3= 0) if (.cells(l, 7).formula = "" or .cells(l, 7).value + marge >= param3 or param3 = 0) if (.cells(l, 8).formula = "" or .cells(l, 8).value - marge <= param4 or param4 = 0) if (.cells(l, 9).formula = "" or .cells(l, 9).value + marge >= param4 or param4 = 0) if (.cells(l, 10).formula = "" or .cells(l, 10).value - marge <= param5 or param5 = 0) if (.cells(l, 11).formula = "" or .cells(l, 11).value + marge >= param5 or param5 = 0) if (.cells(l, 12).formula = "" or .cells(l, 12).value <= param6 or param6 = 0) if (.cells(l, 13).formula = "" or .cells(l, 13).value >= param6 or param6 = 0) if (.cells(l, 16).formula = "" or .cells(l, 16).value - marge <= param7 or param7 = 0) if (.cells(l, 17).formula = "" or .cells(l, 17).value + marge >= param7 or param7 = 0) if (.cells(l, 18).formula = "" or .cells(l, 18).value - marge <= param8 or param8 = 0) if (.cells(l, 19).formula = "" or .cells(l, 19).value + marge >= param8 or param8 = 0) if (.cells(l, 22).formula = "" or .cells(l, 22).value - marge <= param9 or param9 = 0) if (.cells(l, 23).formula = "" or .cells(l, 23).value + marge >= param9 or param9 = 0) redim preserve propositions(ubound(propositions) + 1) propositions(ubound(propositions)) = l
instead of or, can use select case comma seperated list of conditions in following:
'if condition2a or condition2b select case true case condition2a, condition2b 'here comma means lazy 'or' (like orelse in vb.net) 's = s + 10 case else 's = s + 20 end select also, there may many points improve macro performance if can see code. instantly, redim of array add 1 more item may time consuming in loop:
redim preserve propositions(ubound(propositions) + 1) you may consider increase ubound 10 or 100 items each time reach length (to reserve space next probable uses), keep actual upper bound index in variable...
update:
as add part of code, can suggest use helper function each if following:
to replace x<param if's:
if (.cells(l, 6).formula="" or .cells(l, 6).value-marge<=param3 or param3=0) ... with as:
if test(.cells(l, 6).value, marge, param3) ... 'or without '.value': if test(.cells(l, 6), marge, param3) ... we can define function:
function testlesser(v variant, marge double, param double) boolean 'testlesser = (v = "" or v - marge <= param3 or param3 = 0) if v = "" elseif v - marge <= param elseif param = 0 else testlesser = false: exit function end if testlesser = true '** 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 other type (greater than) of ifs:
if (.cells(l, 7).formula="" or .cells(l, 7).value+marge>=param3 or param3=0) ... we have:
function testgreater(v variant, marge double, param double) boolean 'testgreater = (v = "" or v + marge >= param or param = 0) if v = "" 'testlesser = true elseif v + marge >= param 'testlesser = true elseif param = 0 'testlesser = true else testlesser = false: exit function end if testlesser = true '** 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, code as:
'if (.cells(l, 6).formula = "" or .cells(l, 6).value - marge <= param3 or param3 = 0) 'if (.cells(l, 7).formula = "" or .cells(l, 7).value + marge >= param3 or param3 = 0) 'if (.cells(l, 8).formula = "" or .cells(l, 8).value - marge <= param4 or param4 = 0) 'if (.cells(l, 9).formula = "" or .cells(l, 9).value + marge >= param4 or param4 = 0) '... if testlesser(.cells(l, 6), marge, param3) if testgreater(.cells(l, 7), marge, param3) if testlesser(.cells(l, 8), marge, param4) if testgreater(.cells(l, 9), marge, param4) '... my real test shows faster! (and obviously, more readable code)
note:
its important arrange if conditions such final condition can! example if cell values empty, put condition @ first in our test function, if param = 0 true, bring first condition check...
this rule x or y criteria. 'x , y' criteria, reverse! rare case must @ first filter results. in code, see arrange nested if's cells(l, 6) cells(l, 23). don't know if best situation. depends on data , usual cases, consider revising order of nested if's if know false...
another tip:
instead of using with sheets("sheet1"), cache within variable, can improve performance!
dim mysheet worksheet set mysheet = sheets("sheet1") mysheet 'sheets("sheet1") my test shows simple reference change faster 10%. may think of other similar changes when working sheets, ranges, cells...
note: if can define marge global or sheet level var, remove function params seems doesn't have sensible effect...
last update:
as suggested @ioannis in comments (see ref) when working large range of cells, better load values 2d array , using instead of direct access cells:
myarray = sheets("sheet1").range("a1:ag15000").value then use array read/writes as:
myarray(row, col) = myarray(row, col) + 1 'row = 1 ubound(myarray, 1) 'first array dimension rows 'col = 1 ubound(myarray, 2) 'second array dimension columns finally when finished can update entire range reversely:
sheets("sheet1").range("a1:ag15000") = myarray 
Comments
Post a Comment