VBA – IIF() vs IF()

Posted on March 8, 2021 by

What is the difference between IIF() and IF() in #VBA?

Saw that cat, had to write a #VBA article!

The idea of IIF() is that it evaluates both the True and the False part of the condition, before evaluating the condition itself. Thus, if the False part throws an error, you will suffer! Anyway, IIF() is a 1-liner, while the If() is a 5-liner. And some people tend to think that “Less is more”… But it is not always true. Run the two subs below and see for yourself:

Sub TestingIIf()
    
    Dim i As Long: i = 0
    Debug.Print IIf(1 = 1, "TRUE!", 5 / i)
    
End Sub

Sub TestingIf()
    
    Dim i As Long: i = 0
    
    If 1 = 1 Then
        Debug.Print "TRUE!"
    Else
        Debug.Print 5 / i
    End If
    
End Sub

Sub TestingIIf()

Dim i As Long: i = 0

Debug.Print IIf(1 = 1, "TRUE!", 5 / i)

End Sub

Sub TestingIf()

Dim i As Long: i = 0

If 1 = 1 Then

Debug.Print "TRUE!"

Else

Debug.Print 5 / i

End If

End Sub

TestingIIf() from above throws the error, alhough the 5/0 should not be evaluated as far as 1 = 1 is True.
TestingIf() works exactly as you would expect it, without a problem. A few more lines indeed, but you stay on the safe side.
Thus, make sure that you really have a good reason to use IIF(), being a 1-liner fancy is not worth it about 99.(9)% of the time!

Enjoy!

Tagged with: condition, Excel, if, iif, VBA

VBA – IIF() vs IF()

Related posts: