Excel VBA Conditional Operators

 

In the previous lessons on If Statements, you've already used one conditional operator - the equal sign. But there are others. Here's a list:

A table of Conditional Operators used in Excel VBA

By using the various Operators, you can set up more sophisticated conditions for your If Statements.

 

Add another Sub to your code from the previous lessons. Call it If_Test_3. As the code for your new Sub, add the following:

Dim MyNumber As Integer

MyNumber = 10

If MyNumber < 20 Then

MsgBox MyNumber & " is Less than 20"

End If

Your coding window should look like this:

Excel VBA code for the Less Than operator

Again, we've set up an Integer variable called MyNumber and stored a value of 10 in it. Look at the first line of the If Statement, though:

If MyNumber < 20 Then

If you consult the table above, you'll see the < symbol means Less Than. So the condition to test is, "If MyNumber is less than 20". If MyNumber is indeed less than 20 then the condition evaluates to TRUE. In which case, the code between If and End If gets executed. If it's FALSE then VBA will skip to any lines after End If.

One other thing to note about the code is the message box line:

MsgBox MyNumber & " is Less than 20"

After MsgBox we have the variable MyNumber then a space then an ampersand (&) symbol. The & symbol is used to concatenate (join together) things. After the & symbol we then have another space followed by some direct text in double quotes. So whatever is in the variable MyNumber will get joined with the direct text in double quotes.

Run your code and you should see the message box display the following:

A VBA message box

Return to your coding window and change MyNumber = 10 on the second line to MyNumber = 20.

Run your code again and you'll find that nothing happens.

The reason nothing happens is that 20 is not less than 20, so the If Statement is FALSE. We haven't got ElseIf or Else parts to check for a TRUE value, so the code just ends.

Now change the < symbol to <=. The <= symbols together mean "Less than or equal to". Change you message to this:

MsgBox MyNumber & " is Less than or equal to 20"

Your code should now be the same as ours below:

Dim MyNumber As Integer

MyNumber = 20

If MyNumber <= 20 Then

MsgBox MyNumber & " is Less than or equal to 20"

End If

When you run your code, here's what the message box will look like

Excel VBA Message box demonstrating Less than or equal to

Click OK and return to your code. Change the <= symbol to >=. The >= symbols together mean "Greater than or equal to".

Change your message text to this:

MsgBox MyNumber & " is Greater than or equal to 20"

When you run your code, you'll see the message box appear again:

Excel VBA Message box demonstrating Greater than or equal to

To test out the Greater Than sign by itself delete the = sign next to the > symbol. Change MyNumber to 25. Amend the text in your message. Your code will then look like this:

Dim MyNumber As Integer

MyNumber = 25

If MyNumber > 20 Then

MsgBox MyNumber & " is Greater than 20"

End If

Run your code again to see the new message:

Excel VBA Message box demonstrating Greater than

You can have ElseIf and Else parts with your Conditional Logic. Take the following as an example:

Dim MyNumber As Integer

MyNumber = 19

If MyNumber = 20 Then

MsgBox MyNumber & " is 20"

ElseIf MyNumber > 20 Then

MsgBox MyNumber & " is Greater Than 20"

Else

MsgBox MyNumber & " is below 20"

End If

In the code above, we have an If and ElseIf and Else. The If Statement tests for a value of exactly 20. The ElseIf tests if the variable MyNumber is Greater Than 20. The Else part catches any other value. Try it out and see how it works. Change the value of the variable MyNumber and run your code again.

 

In the next lesson, we'll take a look at the Logic Operators.