2

I am writing a macro for MS Excel in VBA. Inside a sub-routine I have a set of conditions to be satisfied for a particular task to be executed. Which of the following method will be optimum from running time and memory point of view?

Method 1:

If condition_1 then
  If condition_2 then
     If condition_3 then
       ....
     End If
  End If
End If

OR

Method 2:

If condition_1 And condition_2 And ..... then
  ....
End If

If I have an Else statement for any of the conditions, I have to go for method 1 to implement it. But I don't have an Else statement for any of the conditions, so both methods stand on equal levels for accomplishing the task. I just want to know which one would be better in case of run-time and memory?

Community
  • 1
  • 1
Sathish
  • 181
  • 3
  • 11
  • 1st is better. VBA doesn't support short-circuiting. ([*although a `Select Case` may sometimes speed things up*](http://stackoverflow.com/questions/3242560/andalso-orelse-in-vba)) –  Oct 13 '14 at 09:03
  • @vba4all Thanks for the help. Wont it short-circuit even if I explicitly define conditions as `boolean` variables in the code? – Sathish Oct 13 '14 at 09:10
  • 1
    there is not short-circuiting in VBA :) –  Oct 13 '14 at 09:12
  • @Sathish doesn't matter - it will always check each expression. so method 1 is definitely the better solution, also regarding null check etc. – therak Oct 13 '14 at 09:14
  • with the and method, vba evaluates EVERY condition, so its slower, but usually you won't even see any difference (depending on the conditions). Also, consider the method Select Case... – Patrick Lepelletier Oct 13 '14 at 17:05

1 Answers1

7

Even if you do not have an Else statement still your first method is a better one.

The latter one will evaluate each condition_X while the first one will skip further checking as soon as one of the conditions fails.

You can test it yourself

Sub Main()
    If Check1 And Check2 Then
    End If
End Sub

Function Check1()
    Debug.Print "exec Check1"
    Check1 = False
End Function

Function Check2()
    Debug.Print "exec Check2"
    Check2 = True
End Function

You should see that even though Check1 returns false, Check2 is being evaluated.... which proves the point.

  • Thanks. This solves my question. But wont the second method also quit as soon as one of the conditions is `False`? I assume that in the second method, the program internally follow the method 1. Is it true? – Sathish Oct 13 '14 at 09:15
  • 1
    that's false. It works like an `IIF()` evaluating each of the conditions. –  Oct 13 '14 at 09:15
  • 4
    Further to this, in Method1 you should use the highest rate of failure first to minimize the following conditions being evaluated. In an abstract situation comparing a random day in three years of dates by `Year()`, `Month()` and `Day()` you should check the `Day()` first as there is only a 1 in ~30 chance of evaluating the secondary condition. Then for `Month()` there is only a 1 in 12 chance of evaluating the final condition (`Year()` 1 in 3) when all three may match. –  Oct 13 '14 at 09:24