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
Essentially, it looks like:
If Condition Then DoSomething1 DoSomething2 End If
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
- If ... Then ... ElseIf ...
- If ... Then ... Else ...
- Improving the organization of the code using nested ifs
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
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
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 faster by using the Code VBA add-in. The top of the menu shows fragments with commonly used conditions
|Toolbar: Insert If|
|Alt-CI | Menu: Code VBA » If|
Handle variable with empty string value - Len(str) = 0
Dim str As String: str = "" 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