The VBA IF THEN ELSE statement lets you test a condition, and then carry out other commands if the test result is true (or equivalent to true - more on that later).
Excel has an IF function that you use in the worksheet, but this is a different beast and does not work the same way as the VBA IF THEN ELSE, although the idea is the same.
An example:
If Profit > 100 Then Debug.Print "Good job everyone" End If
Or put more abstractly:
If Condition_1 is True Then Carry out these commands End If
There are several forms the IF statement can take, and we'll look at each of these, but at its most basic it can simply be one line:
Sub if_then() If 1 > 0 Then Debug.Print "1 is greater than 0" End Sub
You can use this form if you only have one line of code to carry out if the test is true.
If you have more then one line of code (a block), you need to use End If to indicate where the block of code finishes. You can also use this form with a single line of code in the block.
If Profit > 100 Then Debug.Print "Good" Debug.Print "job" Debug.Print "everyone" End If
If the test condition evaluates to false then you can carry out other commands by specifying an Else clause:
If Profit > 150 Then Debug.Print "Excellent, bonuses all round" Else Debug.Print "We need to increase sales" End If
If you have more than one test you want to carry out, you can test these one after the other using an ElseIf clause:
If Profit > 150 Then Debug.Print "Excellent, bonuses all round" ElseIf Profit > 100 Then Debug.Print "Good job everyone" Else Debug.Print "We need to increase sales" End If
Only one block of code is executed here. If Profit is 200, then the first test Profit > 150 is true and we see the message "Excellent, bonuses all round", and code execution then jumps to the End If and stops.
If Profit is 120, the second test is true Profit > 100 and we only see the message "Good job everyone".
If you have multiple Else If clauses then only one should be executed.
When you start doing this kind of thing, the order of the tests is critical to get the code to work correctly. For example if I wrote this:
If Profit > 100 Then Debug.Print "Good job everyone" ElseIf Profit > 150 Then Debug.Print "Excellent, bonuses all round" Else Debug.Print "We need to increase sales" End If
and Profit has a value of 200, then the first test is true Profit > 100 and I get the message, Good job everyone being printed. Really what I want is to see Excellent, bonuses all round.
You must make sure when writing these statements, that the tests will evaluate in the order that you want.
You can nest your IF statements in any way you want. You can have a straightforward IF, with another IF or an IF ELSE, IF ELSEIF ELSE etc inside each block.
Sub if_then_else_nested() Dim mynum As Long ' Change the value of mynum mynum = -16 If mynum > 0 Then Debug.Print "Positive" If mynum > 5 Then Debug.Print "Greater than 5" Else Debug.Print "Between 0 and 5" End If Else Debug.Print "Negative" If mynum < -10 Then Debug.Print "Less than -10" ElseIf mynum < -5 Then Debug.Print "Between -10 and -5" Else Debug.Print "Between -5 and 0" End If End If End Sub
Implicit Test
I keep writing that tests must evaluate to true, and by that I mean the result of the test must either be a numerical equivalent of true, or the actual Boolean value True.
In VBA, any number that is not zero is deemed to be the equivalent of True, 0 is deemed to be False.
Numbers
We may want to check if a variable has a specific value e.g.
If Total > 150 Then [ Do Something ] End If
But let's say we just care about whether Total is greater than 0, and we know that Total can't be a negative value, we could just write
If Total Then [ Do Something ] End If
The test is implied. We don't need to write
If Total > 0. Aas long as Total contains a number, the result of the test is deemed to be true.
Booleans
It's the same for Boolean variables. If we have a Boolean variable, Status, we can test if it is True like this:
If Status Then [ Do Something ] End If
Strings
You can't test a string in quite the same way because using an implicit test on an empty string generates an error.
However, you can still implicitly test if the string contains any characters by checking it's length. Essentially this is testing for a positive number, but is handy to know for when you are working with strings.
If Len(Answer) Then [ Do Something ] End If
As long as Answer contains at least one character, the test evaluates to true.
IF GOTO
Another way to use IF is with a GoTo statement. GoTo tells the code to go to the named Label and continue executing code from there.
In this example I have a label called Handler. If my test evaluates to true then I want the code to continue running from Handler:
If the test is false, then code execution just continues on the line after the IF statement.
Because code is run sequentially, one line at a time, if we use a GoTo, we have to use an Exit Sub statement to terminate the code. If we don't, VBA will just keep going down the lines of code until it gets to Handler: and then run the code in there.
We don't want that of course so don't forget to use Exit Sub in situations like this.
' Change this test to evaluate to false ' e.g. 1 < 0 If 1 > 0 Then GoTo Handler Debug.Print "Not in Handler code" ' Exit Sub is required to prevent ' VBA executing the Handler code ' Comment out the next line ' to see what happens Exit Sub Handler: Debug.Print ("In the handler")
Download Sample Workbook
Enter your email address below to download the sample workbook.
You can download a sample workbook with lots of examples of using IF THEN ELSE.
Joe Meyer
I thought that VBA interprets any non-zero value as true, whether it’s positive or negative, and that only zero is interpreted as false.
Philip Treacy
Hi Joe,
Looks like you are right, this is the way the IF function works in the sheet too. That’s bizarre – when I was testing my code yesterday I’m sure negative numbers were giving me false, but today they aren’t. I don’t know what’s going on there!!
Will amend accordingly.
Thanks
Phil
Martin Nolan
Hi Philip,
There appears to be a misprint In your paragraph about implicit tests. In VBA a positive number is actually the equivalent of False.
Philip Treacy
Hi Martin,
A positive number is the equivalent of True in VBA. Try this
Regards
Phil