3

I got a short question. Is there any difference in VBA between

if x1 and x2 and x3 and ... and x10 then
    foo
end if

and

if x1 then
  if x2 then
    if x3 then
      ...
      foo
    end if
  end if
end if

regarding speed?

More specifically: I have 10 columns with data and need to compare the data row by row for duplicates in the database (stuff like SELECT DISTINCT won't work in this case).

I could imagine, that using

x1 = recordset.fields("Field1").value
if x1 then
  x2 = recordset.fields("Field2").value
  if x2 then
    x3 = recordset.fields("Field3").value
    if x3 then
      ...
      foo
    end if
  end if
end if

will be faster than

x1 = recordset.fields("Field1").value
x2 = recordset.fields("Field2").value
...
if x1 and x2 and x3 and ... and x10 then
    foo
end if

since I won't have to read all the data from the recordset. Or will the amount of ifs kill this advantage regarding speed?

frrrt
  • 89
  • 8

2 Answers2

9

The one-liner is checking all the conditions disregard if any of them have failed already. 

Sub Main()
    If Check And Check And Check Then
    End If
End Sub

Function Check() As Boolean
    Debug.Print "checked"
    Check = False
End Function

The nested ifs are the better option because as soon as one condition fails the code execution will jump to the either: else/end if block right away and not try to evaluate all the other conditions.

It's a form of short-circuiting in programming.

Community
  • 1
  • 1
3

Nested If would be a better option in terms of optimization. As Nested If will ignore all other following if the condition fails, however the one line If using AND (or) OR would not be optimized as every single condition is executed before reaching a verdict. Also the code readability and maintainability is far better in Nested If's (provided they are indented properly).

So Nested If over If using multiple conditions any day for me !

PaulFrancis
  • 5,748
  • 1
  • 19
  • 36