3.1 If Statements in Excel VBA

Conditional Logic is all about the IF word. It's about saying what should happen IF a certain condition is met, or what should happen if it's not met.

If Statements

You use Conditional Logic all the time in your daily life. You say things like this:

IF I buy these shoes I will be happier

IF I eat this ice cream I will ruin my diet

IF I go online I might have some emails

Programming in any language is heavily reliant on Conditional Logic like the IF Statement. It allows you to go down different paths, depending on an initial condition.

The structure of a VBA IF Statement looks like this:

If Condition_To_Test Then

'CODE HERE

End If

You start with the word If (uppercase "I" lowercase "f"). After a space, you have a condition that you want to test. This conditional is something that can either be TRUE or FALSE. After your condition, you type a space followed by the word Then (uppercase "T"). An If Statement ends with the words End If.

In Between If and End If is where you type your code. But this code will only be executed IF your condition is TRUE. If it's FALSE then VBA skips past the End If and continues on its way.
Let's clear things up with a few coding examples. You can start a new spreadsheet for this. When you save the new file, don't forget to save it as an Excel Macro- Enable Workbook. The file ending will then be XLSM.

Click the Developer tab in Excel, then the View Code item on the Controls panel:

Clicking View Code will open the VBA Editor. If the coding window for Sheet1 is not already open, double click Sheet1 in the Project Explorer on the left. (If you can't see the Project Explorer, click View > Project Explorer from the menu at the top.)

Create a new Sub in your coding window, and call it If_Test_1. Add the following code for your Sub:

Dim MyNumber As Integer

MyNumber = 10

If MyNumber = 10 Then

MsgBox "Number = 10"

End If

Your coding window will then look like this:

The first two lines set up an Integer variable called MyNumber. We're storing the number 10 in MyNumber. Notice the first equal sign:

MyNumber = 10

The equal sign here means "Assign a value of". So we're assign a value of 10 to the variable called MyNumber.

The first line of the If statement is this:

If MyNumber = 10 Then

We have the word "If" and the word "Then". Between the two we have the condition we wish to test:

MyNumber = 10

You might think that this is the same as line two from our code. But it's not. It means something entirely different. When you use an equal sign in an If statement it doesn't mean "Assign a value of" anymore, it means "has a value of". What you're saying now is "If MyNumber has a value of 10". The equal sign in an If Statement is known as a Conditional Operator. You'll meet more of these later.

But by saying "If MyNumber has a value of 10" you're creating a statement that can either be TRUE or FALSE. That's what VBA will be checking for: "Can this statement be evaluated as either TRUE or FALSE?"

If MyNumber does indeed have a value of 10 then the If Statement is TRUE. In which case the code between If an End If will get executed. For us, this is just a simple Message Box:

MsgBox "Number = 10"

We have surrounded Number = 10 with double quotes. This will turn it into text.

With your cursor flashing between the Sub and End Sub of your code, press F5 on your keyboard to run it. You should see the Message Box display:

Click OK on the message box to return to your coding window. Now change the second line of your code from MyNumber = 10 to MyNumber = 11. Leave the IF Statement alone, though. Now press F5 again to run the code. You should find that nothing happens.

The reason nothing happens is that the message box is enclosed between an If and an End If. The message box line will only get executed if the If Statement evaluates to TRUE. Since MyNumber now equals 11 the If statement will now evaluate to FALSE. A value of FALSE means that VBA can skip past the If statement without executing any of the code between If and End If.

In the next lesson, you'll see how to extend your If Statements by using Else and ElseIf.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney