VBA IF Statements: Else, ElseIf, And, Or

Associated Files Download Links

VBA If Statement

vba else if statement

If Then

VBA If Statements allow you to test if expressions are TRUE or FALSE, running different code based on the results.
Let’s look at a simple example:
This tests if the value in Range A2 is greater than 0. If so, setting Range B2 equal to “Positive”
vba if then
Note: When testing conditions we will use the =, >, <, <>, <=, >= comparison operators. We will discuss them in more detail later in the article.
Here is the syntax for a simple one-line If statement:
To make it easier to read, you can use a Line Continuation character (underscore) to expand the If Statements to two lines (as we did in the above picture):

End If

The above “single-line” if statement works well when you are testing one condition. But as your IF Statements become more complicated with multiple conditions, you will need to add an “End If” to the end of the if statement:

vba end if

Here the syntax is:

The End If signifies the end of the if statement.

Now let’s add in an ElseIF:

ElseIF – Multiple Conditions

The ElseIf is added to an existing If statement. ElseIf tests if a condition is met ONLY if the previous conditions have not been met.
In the previous example we tested if a cell value is positive. Now we will also test if the cell value is negative with an ElseIf:

vba elseif

 

You can use multiple ElseIfs to test for multiple conditions:

 

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:


vba else

If-Else

The most common type of If statement is a simple If-Else:

vba if else

 

Nested IFs

You can also “nest” if statements inside of each other.

nested ifs

 

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 or

 

You can include multiple Ors in one line:

If you are going to use multiple Ors, it’s recommended to use a line continuation character to make your code easier to read:

vba multiple ors

If And

The And operator allows you to test if ALL conditions are met.

vba if and

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:


vba if not

Notice that the Not operator requires parenthesis surrounding the expression to switch.

The Not operator can also be applied to If statements:

if not

 

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:

Excel also has many additional functions that can be called using WorksheetFunction. Here’s an example of the Excel IsText Function:

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:

vba if boolean function

Comparing Text

You can also compare text similar to comparing numbers:
When comparing text, you must be mindful of the “Case” (upper or lower).  By default, VBA considers letters with different cases as non-matching.  In other words, “A” <> “a”.
If you’d like VBA to ignore case, you must add the Option Compare Text declaration to the top of your module:

After making that declaration “A” = “a”:

 

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:


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:

vba else if statement

If Else Examples

Now we will go over some more specific examples.

Check if Cell is Empty

This code will check if a cell is empty. If it’s empty it will ignore the cell. If it’s not empty it will output the cell value to the cell to the right:
vba if cell empty do nothing

Check if Cell Contains Specific Text

The Instr Function tests if a string of text is found in another string. Use it with an If statement to check if a cell contains specific text:

Check if cell contains text

This code will test if a cell is text:

 

If Goto

You can use the result of an If statement to “Go to” another section of code.

 

Delete Row if Cell is Blank

Using Ifs and loops you can test if a cell is blank and if so delete the entire row.

 

If MessageBox Yes / No

With VBA Message Boxes you’re able to ask the user to select from several options. The Yes/No Message Box asks the user to select Yes or No.  You can add a Yes / No Message Box to a procedure to ask the user if they would like to continue running the procedure or not. You handle the user’s input using an If statement.
Here is the Yes/No Message Box in practice:
vba yes no msgbox

 

You may also like some of this related content...

Ads