Excel VBA IF Statement: Know Your Coding

Microsoft Excel is a powerful piece of software as it is, but with the proper skills and a bit of patience, you can make it even more powerful. Thanks to Microsoft’s Visual Basic for Application, or VBA for short, you can write snippets of code to be executed within Excel, thus boosting its functionality to new limits. This article will cover one of the most basic VBA functions in Excel: the IF function.

IF vs. VBA IF

With Excel being so complex, it is not uncommon for people to mistake functions that have similar names or syntaxes, and the IF function is the perfect example for this. When used as a formula applied from the formula bar in Excel, the IF function has the role of returning a True or False result if a certain condition is met. The VBA version of IF, often referred to as the IF-THEN or the IF-THEN-ELSE statement, has a similar functionality, with two noticeable differences:

1. you get to define the return values or set certain actions to be performed when the condition is met;
2.  you can add multiple conditions.

As you can see, the VBA version is a lot more powerful, allowing you to create really complex functions. This blog post on Excel VBA will allow you to get a better understanding of formulas, macros and VBA programming.

Basic VBA IF Syntax

The syntax of the basic IF statement is very simple:

If condition Then result

This means that, if the condition is met, the defined result will be displayed or the defined action will be taken. If the condition is not met, VBA will do nothing. However, you can define an action to be performed even if the condition is not met by adding the else argument to the formula:

If condition Then result Else result2

In this case, the formula contains instructions on how to proceed if the condition is met (the Then argument), as well as how to proceed if it isn’t (the Else argument). Let’s see a practical example of the VBA IF statement:

Sub IF_ELSE_FUNCTION()
If 1 > 4 Then
MsgBox "1 is greater than 4"
Else:
MsgBox "1 is less than 4"
End If
End Sub

This code snippet simply checks if the condition is met, in this case whether 1 is greater than 4, and displays the appropriate message. While in this scenario the condition is a simple relationship between numbers, you can replace the numbers with cells, rows or even functions, and the VBA IF statement will work just the same. This two-part course on VBA and Macros for Excel will teach you how to create complex pieces of code and adapt them to your needs.

ElseIf

The role of the ElseIf argument is to allow you to specify additional conditions to be checked if the first condition is not met. The syntax of a VBA IF statement that includes an ElseIf argument looks like this:

If condition Then result ElseIf condition2 Then result2 Else result3

What’s good about the ElseIf argument is that it can be used as many times as you need it, meaning that your statement can have a single ElseIf argument, ten ElseIf arguments or a thousand ElseIf arguments – it’s up to you.

An example of an IF statement with multiple ElseIf arguments looks something like this:

Sub IF_ELSEIF_ELSE_FUNCTION()
If 1 > 4 Then
MsgBox "1 is greater than 4"
ElseIf 2 > 4 Then
MsgBox "2 is greater than 4"
ElseIf 3 > 4 Then
MsgBox "3 is greater than 4"
Else:
MsgBox "1, 2 or 3 are lesser than 4"
End If
End Sub

In this example, Excel will process the first statement, notice that it is false, then move to the next ones; since all ElseIf arguments are false, the result of the Else argument will be displayed as a final result of the IF statement.

There are a two things to keep in mind about ElseIf arguments:

• ElseIf arguments are parsed in the order they are written, so you should always write the ElseIf arguments with the highest probability of being true first, to avoid unnecessary parsing and thus result in a quicker execution of your code;
• ElseIf arguments can only be placed before the final arguments can only be placed before the Else argument; if you need to create more complex functions, see the next section about nested arguments or check out this ultimate Excel VBA online course.

Nested Statements

If you are using complex formulas in your work, you might come across a scenario where you will need to use the result of an IF statement as a result for another statement as well; since Excel jumps to the end of the IF statement once the Else or ElseIf arguments are satisfied, you will need to create a nested statement to get the job done. Nested statements are basically simple statement contained within other statements.

Nested statements can be contained in the Then or the Else arguments of the main statement. It is very important to make sure your nested argument is fully contained in that section, otherwise your entire statement might not work at all.

With that being said, let’s look at an example of a nested VBA IF statement:

Sub IF_NESTED_FUNCTION()
If salary > 15000 And salary < 35000 Then
*This is a nested statement*
If HasKids = "Yes" Then
getTaxRate = 0.10
Else
getTaxRate = 0.20
End If
ElseIf salary >= 35000 And salary < 75000 Then
*This is another nested statement*
If HasKids = "Yes" Then
getTaxRate = 0.22
Else
getTaxRate = 0.34
End If
ElseIf salary >= 75000 Then
*This is yet another nested statement*
If HasKids = "Yes" Then
getTaxRate = 0.44
Else
getTaxRate = 0.48
End If
Else
getTaxRate = 0
End If

This nested statement calculates the tax rate for employees depending on their salary and whether they have children or not. As you can see, nested functions allow you to have a lot more control over your formulas, at a cost of being a bit more complex.

There are two main things to keep in mind when creating nested statements:

• ALWAYS close the nested functions, using the End If argument;
• Create the nested statements first, then work your way up to the main statement.

Check out this Microsoft Visual Basic for Excel online course to get a better understanding of nested statements and other interesting things you can do with VBA for Excel.