VBA IF THEN ELSE

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

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