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.

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:

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

if then elseif

You can insert If statement block faster by using the Code VBA add-in. The top of the menu shows fragments with commonly used conditions

Toolbar: Insert If

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

Dim str As Stringstr = ""
If Len(str) = 0 Then
    
End If

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
    
End If

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
    
End If