VBA If, ElseIf, Else (Ultimate Guide to If Statements)
In this Article
VBA If Statement
If Then
If Range("a2").Value > 0 Then Range("b2").Value = "Positive"
If [test_expression] then [action]
If [test_expression] then _
[action]
If Range("a2").Value > 0 Then _
Range("b2").Value = "Positive"
End If
If Range("a2").Value > 0 Then
Range("b2").Value = "Positive"
End If
Here the syntax is:
If [test_expression] then
[action]
End If
The End If signifies the end of the if statement.
ElseIF – Multiple Conditions
If Range("a2").Value > 0 Then
Range("b2").Value = "Positive"
ElseIf Range("a2").Value < 0 Then
Range("b2").Value = "Negative"
End If
You can use multiple ElseIfs to test for multiple conditions:
Sub If_Multiple_Conditions()
If Range("a2").Value = "Cat" Then
Range("b2").Value = "Meow"
ElseIf Range("a2").Value = "Dog" Then
Range("b2").Value = "Woof"
ElseIf Range("a2").Value = "Duck" Then
Range("b2").Value = "Quack"
End If
End Sub
Now we will add an Else:
Else
The Else will run if no other previous conditions have been met.
We will finish our example by using an Else to indicate that if the cell value is not positive or negative, then it must be zero:
If Range("a2").Value > 0 Then
Range("b2").Value = "Positive"
ElseIf Range("a2").Value < 0 Then
Range("b2").Value = "Negative"
Else
Range("b2").Value = "Zero"
End If
If-Else
The most common type of If statement is a simple If-Else:
Sub If_Else()
If Range("a2").Value > 0 Then
Range("b2").Value = "Positive"
Else
Range("b2").Value = "Not Positive"
End If
End Sub
Nested IFs
You can also “nest” if statements inside of each other.
Sub Nested_Ifs()
If Range("a2").Value > 0 Then
Range("b2").Value = "Positive"
Else
If Range("a2").Value < 0 Then
Range("b2").Value = "Negative"
Else
Range("b2").Value = "Zero"
End If
End If
End Sub
IF – Or, And, Xor, Not
Next we will discuss the logical operators: Or, And, Xor, Not.
If Or
The Or operator tests if at least one condition is met.
The following code will test if the value in Range A2 is less than 5,000 or greater than 10,000:
If Range("a2").Value < 5000 Or Range("a2").Value > 10000 Then
Range("b2").Value = "Out of Range"
End If
You can include multiple Ors in one line:
If Range("a2").Value < 5000 Or Range("a2").Value > 10000 Or Range("a2").Value = 9999 Then
Range("b2").Value = "Out of Range"
End If
If you are going to use multiple Ors, it’s recommended to use a line continuation character to make your code easier to read:
If Range("a2").Value < 5000 Or _
Range("a2").Value > 10000 Or _
Range("a2").Value = 9999 Then
Range("b2").Value = "Out of Range"
End If
If And
The And operator allows you to test if ALL conditions are met.
If Range("a2").Value >= 5000 And Range("a2").Value <= 10000 Then
Range("b2").Value = "In Range"
End If
If Xor
The Xor operator allows you to test if exactly one condition is met. If zero conditions are met Xor will return FALSE, If two or more conditions are met, Xor will also return false.
I’ve rarely seen Xor used in VBA programming.
If Not
The Not operator is used to convert FALSE to TRUE or TRUE To FALSE:
Sub IF_Not()
MsgBox Not (True)
End Sub
Notice that the Not operator requires parenthesis surrounding the expression to switch.
The Not operator can also be applied to If statements:
If Not (Range("a2").Value >= 5000 And Range("a2").Value <= 10000) Then
Range("b2").Value = "Out of Range"
End If
If Comparisons
When making comparisons, you will usually use one of the comparison operators:
Comparison Operator | Explanation |
---|---|
= | Equal to |
<> | Not Equal to |
> | Greater than |
>= | Greater than or Equal to |
< | Less than |
<= | Less than or Equal to |
However, you can also use any expression or function that results in TRUE or FALSE
If – Boolean Function
When build expressions for If Statements, you can also use any function that generates TRUE or False. VBA has a few of these functions:
Function | Description |
---|---|
IsDate | Returns TRUE if expression is a valid date |
IsEmpty | Check for blank cells or undefined variables |
IsError | Check for error values |
IsNull | Check for NULL Value |
IsNumeric | Check for numeric value |
They can be called like this:
If IsEmpty(Range("A1").Value) Then MsgBox "Cell Empty"
Excel also has many additional functions that can be called using WorksheetFunction. Here’s an example of the Excel IsText Function:
If Application.WorksheetFunction.IsText(Range("a2").Value) Then _
MsgBox "Cell is Text"
You can also create your own User Defined Functions (UDFs). Below we will create a simple Boolean function that returns TRUE. Then we will call that function in our If statement:
Sub If_Function()
If TrueFunction Then
MsgBox "True"
End If
End Sub
Function TrueFunction() As Boolean
TrueFunction = True
End Function
Comparing Text
Msgbox "a" = "b"
Msgbox "a" = "a"
Option Compare Text
After making that declaration “A” = “a”:
Option Compare Text
Sub If_Text()
MsgBox "a" = "A"
End Sub
VBA If Like
The VBA Like Operator allows you to make inexact comparisons of text. Click the “Like Operator” link to learn more, but we will show a basic example below:
Dim strName as String
strName = "Mr. Charles"
If strName Like "Mr*" Then
MsgBox "True"
Else
MsgBox "False"
End If
Here we’re using an asterisk “*” wildcard. The * stands for any number of any characters. So the above If statement will return TRUE. The Like operator is an extremely powerful, but often under-used tool for dealing with text.
If Loops
VBA Loops allow you to repeat actions. Combining IF-ELSEs with Loops is a great way to quickly process many calculations.
Continuing with our Positive / Negative example, we will add a For Each Loop to loop through a range of cells:
Sub If_Loop()
Dim Cell as Range
For Each Cell In Range("A2:A6")
If Cell.Value > 0 Then
Cell.Offset(0, 1).Value = "Positive"
ElseIf Cell.Value < 0 Then
Cell.Offset(0, 1).Value = "Negative"
Else
Cell.Offset(0, 1).Value = "Zero"
End If
Next Cell
End Sub
If Else Examples
Now we will go over some more specific examples.
Check if Cell is Empty
Sub If_Cell_Empty()
If Range("a2").Value <> "" Then
Range("b2").Value = Range("a2").Value
End If
End Sub
Check if Cell Contains Specific Text
If Instr(Range("A2").value,"text") > 0 Then
Msgbox "Text Found"
End If
Check if cell contains text
Sub If_Cell_Is_Text()
If Application.WorksheetFunction.IsText(Range("a2").Value) Then
MsgBox "Cell is Text"
End If
End Sub
If Goto
Sub IfGoTo ()
If IsError(Cell.value) Then
Goto Skip
End If
'Some Code
Skip:
End Sub
Delete Row if Cell is Blank
Sub DeleteRowIfCellBlank()
Dim Cell As Range
For Each Cell In Range("A2:A10")
If Cell.Value = "" Then Cell.EntireRow.Delete
Next Cell
End Sub
If MessageBox Yes / No
Sub MsgBoxVariable()
Dim answer As Integer
answer = MsgBox("Do you want to Continue?", vbQuestion + vbYesNo)
If answer = vbYes Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub
VBA If, ElseIf, Else in Access VBA
The If, ElseIf and Else functions work exactly the same in Access VBA as in Excel VBA.
You can use an If statement to check if there are records in a Recordset.