Home and Learn - Free Excel VBA Course
3.3 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:
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:
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:
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
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:
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:
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.
Next Lesson: 3.4 Logic Operators >