VBA If Else

Overview of VBA IF Else

VBA is not different when it comes to logical tests, it works the same way like how it works in regular worksheets. Of all logical functions “IF” function is mostly used from the lot. Using IF we can conduct a logical test and arrive the decisions if the logical test is satisfied and also arrive alternative decisions if the logical test is not satisfied. We will see what is “IF ELSE”  in VBA excel and how to use it in VBA coding.

Below is the syntax of IF Else condition in VBA.

IF <Logical Test> Then
    If the Logical Test is TRUE 
Else
    If the Logical Test is FALSE
End IF

If the condition requires only TRUE value then we can ignore “Else” part. For an example look at the below code.

In the above, I have tested the number 10 is greater than number 9.

If this logical test is TRUE then we need the result in a message box as “10 is greater”.

In this code, I have not supplied anything if the number 10 is not greater than the other number, so let’s run the code and see the result in a message box.

Since number 10 is greater than number 9 we got the result as “10 is greater”.

Now I will change the number 9 to 11.

If you run this code now we don’t get to see anything because the logical test 10 > 11 is not TRUE. Since we have not supplied anything to the FALSE logical test result, it has nothing to show as an alternative.

So, this is where “IF ELSE” comes into play a role. We will see what is “IF ELSE” and how to use it in excel VBA coding.

What is IF ELSE Statement in VBA?

Once the supplied logical test is FALSE we need something alternative task to execute as part of the code. So “IF ELSE” means if the logical test is FALSE then what else needs to do.

To better understand in the above example we have supplied the result as “10 is greater” only if the logical test is TRUE but in case of FALSE logical result we can supply the alternative result as “10 is lesser”.

So once the logical tests are supplied and TRUE part code written in the next line enter the word “ELSE”.

So, ELSE means if the logical test is not TRUE we need the result as “10 is lesser”.

Code:

Sub IF_Else_Example1()

    If 10 > 11 Then
       MsgBox "10 is greater"
    Else
       MsgBox "10 is lesser"
    End If

End Sub

Now our code gives at least any one of the above results. Execute the code and see the result.

Since we have supplied the alternative result if the logical test is FALSE, it has displayed the alternative result as “10 is lesser” because 10 is less than the other number 11.

Example of VBA If Else Statement in Excel

Now we will see how we can use this VBA IF ELSE statement practically with below example. For an example look at the below data.

With this data, we need to arrive the status based on the “Cost” of each product. To arrive status below are the criteria’s.

If the cost price is > 50 the status should be “Expensive” or else status should be “Not Expensive”.

Here we need to test the cost price i.e. logical test whether cost price is >50 or not. If the logical test is TRUE i.e. cost price is more than 50 we need the status as “Expensive” and if the logical test is FALSE i.e. cost price is less than 50 we need the alternative result as “Not Expensive”.

Ok, let’s write the code now. Before that copy and paste the above table to excel worksheet.

Step 1: Start the subprocedure.

Sub IF_ELSE_Example2()

End Sub

Step 2: Declare the variable as an Integer data type.

Dim k As Integer

Step 3: Since we need to test more than one cell values we need to employ FOR VBA LOOP to loop through the cells and apply the logic for all the cells.

Here we need to apply logical test from 2nd row to 8^th row, so start FOR LOOP from 2 to 8.

Code:

For k = 2 To 8

Next k

Step 4: Inside this loop, we need to execute the logical test. So open IF statement and select the first cell by using CELLS property.

Code:

If Cells(k, 2).Value > 50 Then

Here Cells(k,2) means row (value of k) and column 2.

Step 5: If this cell value is > 50 then we need the result as “Expensive” in the next column cell. So code will be –

Code:

Cells(k, 3).Value = "Expensive"

Step 6: If this logical test isn’t TRUE then we need ELSE statement result i.e. “Not Expensive”.

Code:

Sub IF_ELSE_Example2()

    Dim k As Integer

    For k = 2 To 8
        If Cells(k, 2).Value > 50 Then
            Cells(k, 3).Value = "Expensive"
        Else
            Cells(k, 3).Value = "Not Expensive"
        End If
    Next k

End Sub

Now, this code will loop through from 2^nd row to 8^th row tests the numbers and arrive the result based on the cost price.

 

Like this, we can use If  Else statement to arrive alternative results.

Things to Remember

• Else statement is for a FALSE logical test.
• If you want to apply more than two logical tests then we need to use ELSE IF statement.
• In case of task execution for more than one cell we need to use loops.
• With If and Else we can test only one logical test.

Recommended Articles

This has been a guide to VBA If Else. Here we discuss How to Use If Else Statement in Excel VBA along with a practical example and downloadable excel template. You may learn more about VBA from the following articles –

• Examples of Excel VBA String Array 
• VBA Declare Two Dimensional Array
• How to Use Counter in VBA Excel?
• VBA Asc | Examples
• VBA DateSerial | Examples
• Boolean in Excel VBA
• IIF Function in Excel VBA
• IF OR in Excel VBA
• COUNTIF in Excel VBA