Excel VBA IF Statement

Excel IF statement is a very powerful tool in VBA (and any programming language) and is used to check if a condition is TRUE or FALSE and perform a certain action depending on the result.

The basic structure of this statement is as follows:

Let’s look at this with a simple example:

We will check if the value of cell B2 in our sheet is equal to 2 or not and display the right message in cell C2.

Open your VBA Editor and let’s declare a variable myValue that will hold the value of cell B2 ( it will be an Integer since we are checking for a number):

3.png

Let’s also declare a String value that will hold the message we want to display in cell C3

Now let’s add the VBA If statement to the code and add the right message to cell C2:

Run the above code and you will see the following displayed In cell C2:

Now let’s try and change the value of cell B2 to any other number (let’s say 5) and rerun the macro. You will see that nothing will be displayed in cell C2.

This is because we have not told excel what to do if the value of cell B2 is different from 2.

Excel VBA If Else

To do this we need to add a VBA Else block to our If statement, please see below:

When you run the above code, first the macro will check the first condition:

Is the value of the cell 2

Since this condition is not true as our new value is 5, Excel will run the code under the Else statement. If you run this code, you will have the following message displayed in cell C2:

Excel VBA If - ElseIF

Continuing with our example, let’s add another condition and check if the value of cell B2 is:

Smaller than 2

Equals 2

Bigger than 2

To check these conditions we need to add the ElseIf block to our code as follows:

9.png

Let’s analyse this code, line by line:

The macro will first evaluate the first condition:

Is the value of the cell equal to 2

If this condition is true, it will run the code under this block and exit the VBA If statement, it will not check the rest of the conditions.

If the condition is false it will move on to the next condition and check:

Is the value of the cell smaller than 2

If true, it will display in cell C2:

“The value of cell B2 is smaller than 2”

And exit the If statement.

If this condition is false, it will go on to run the code under the Else block:

“The value of cell B2 is bigger than 2”

You can use as many ElseIf conditions as you need, it is not limited to two and as you noticed above the Else statement is optional, but always a good practice.

In our example, we are evaluating a cell value that happened to be a number, but the Excel If Then statement can do more than that such as:

Check that a string matches another string (be careful to declare any variables as String):

Check that a string contains a certain string (using the VBA InsStr function):

Check if a calculation equals a certain value:

Check if two conditions are both true (using the VBA AND function):

Check if at least one of the conditions is true (using the VBA OR function):

If you would like to read more about this topic, check out the Microsoft guide to using it here:

If...Then...Else Statement (Visual Basic) | Microsoft Docs












Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit