Is there a difference between nested "if" and using "if x and y and z and ..." regarding speed?
I have a short question. Is there a VBA difference 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 of data and need to compare data row by row for duplicates in the database (things like SELECT DISTINCT won't work in this case).
I could guess 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 need to read all data from the recordset. Or will the number of ifs kill this speed advantage?
source to share
In a one-line space, all conditions are checked if any of them are not met.
Sub Main()
If Check And Check And Check Then
End If
End Sub
Function Check() As Boolean
Debug.Print "checked"
Check = False
End Function
Nested ifs are the best option, since once one condition is not met, code execution will jump to the next: else / end if the block is right away, rather than trying to evaluate all other conditions.
This is a form of short circuit in programming .
A nested If would be the best option from an optimization standpoint. Because Nested If ignores all others if condition fails, however one line. If using AND (or) OR will not be optimized as every single condition is met before reaching the verdict. Also, code readability and maintainability is much better in Nested If (provided they are indented properly).
So Nested If over Using multiple conditions any day for me!
source to share