Using If Then ElseIf in VBA

An If-statement determines whether or not to execute a statement-block. Whether the block is executed is determined by the specified condition, a boolean expression which returns either True or False. Essentially, it looks like:


If Condition Then 
    DoSomething1
    DoSomething2
End If

For example:


Dim i As Integer, i4Count As Integer
If i = 4 Then
    i4Count = i4Count + 1
End If

This page gives examples on how to use If statements and its syntactic variants.

Tip: The Select...Case Statement might be more useful when you evaluate a single expression that has several possible values.

If Then Builder

If statement syntax variants

Anything you want to do with conditions can be done with the above syntax. However the syntax has been extended with additional constructs to make your code shorter and easier to understand:

If ... Then - single line

The code in the previous section can be made shorter in case the statement block only contains 1 line:


Dim i As Integer, i4Count As Integer
If i = 4 Then i4Count = i4Count + 1

If ... Then ... ElseIf ...

In case you want to handle different conditions using different blocks you can use ElseIf:


Dim i4Counter As Integer, i5Counter As Integer, i5CounterHappy As Integer, booIsHappy As Boolean
If i = 4 Then
    i4Counter = i4Counter + 1
ElseIf i = 5 And booIsHappy Then
    i5Counter = i5Counter + 1
    i5CounterHappy = i5CounterHappy + 1
ElseIf i = 5 And Not booIsHappy Then
    i5Counter = i5Counter + 1
End If

Improving the organization of the code using nested ifs

The above code, meaningless as it may be, could arguably be improved using nested ifs:


Dim i4Counter As Integer, i5Counter As Integer, i5CounterHappy As Integer, booIsHappy As Boolean
If i = 4 Then
    i4Counter = i4Counter + 1
ElseIf i = 5 Then
    i5Counter = i5Counter + 1
    If booIsHappy Then
        i5CounterHappy = i5CounterHappy + 1
    End If
End If

There are 2 reasons why the second version should be preferred:

  • It is clear that the main decision concerns the counter to be incremented, i4Counter or i5Counter, it is the only condition to test.
  • It is clear that booIsHappy only plays a role with i = 5

If ... Then ... Else ...

In many cases you will also want to specify what to do if none of the conditions for If and ElseIf are true. For this VBA introduces Else ('for all other conditions'):


Dim i4Counter As Integer, iOtherCounter As Integer
If i = 4 Then
    i4Counter = i4Counter + 1
Else
    iOtherCounter = iOtherCounter + 1
End If

Common code fragments

You can insert If statement block and conditions faster by using the Code VBA add-in. To insert an If Then structure, select the If submenu on Code VBA menu Code VBA » If or Alt-CI

Inside the If Then, start Code VBA IntelliSense Shift-Space. The menu that opens gives you a plethora of functions, object properties and fragments that can specify a condition.

As an example, when selecting VBA b a menu opens containing the FolderExists function. The tooltip shows the code that will be inserted when selected. As part of the process the fragment module 'modVBAFolderProcedures' that contains this function is inserted into your project.

conditions intellisense menu

Sample code fragments

Below are some typical fragments that implement a certain test that are also included in Code VBA.

Handle variable with empty string value - Len(str) = 0


Dim str As String: str = ""
If Len(str) = 0 Then

Uninitialized variant or empty field in database - IsNull()

Null is a term used in all databases; it is the value of a field when you have not entered. Think of it as meaning 'unknown.' Only the Variant can be Null. 


If Not IsNull(rst!Address2) Then

Uninitialized object variable - Is Nothing

Nothing indicates the uninitialized state of an object variable. You can only work with an object variabiale if it has been initialized, so you will regularly test this to prevent runtime errors.


Dim rng As Range
If Not rng Is Nothing Then