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:

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.

By submitting your email address you agree that we can email you our Excel newsletter.

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!!

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?