Excel VBA If, Else-If, and Case Conditions

Excel VBA If Banner
Choices, choices….

Conditional statements are one of the most fundamental building blocks in any coding language, and Excel VBA conditionals are no different. VBA supports If, Else-If, and Case conditions so you can achieve whatever your application needs elegantly. Excel VBA If statements are a great place to start learning about coding principles, and this post will help you get started.

VBA If Condition

To implement an If condition, create a macro and paste in the following code:

Sub Conditionals()
    If Worksheets("Sheet1").Cells(1, 1).Value = "" Then
        Worksheets("Sheet1").Cells(1, 1).Value = "Not Null!"
    End If
End Sub
Excel VBA If 1
Before running the macro
Excel VBA If 2
After running the macro

Pretty simple! The syntax for an If statement in Excel VBA is

If [condition] Then
    [run the code if the condition is true]
End If

To check if a cell is blank, use two double quotes, to represent an empty string. If the cell value was a number, you would simply use the number value, without quotes, in your condition:

    If Worksheets("Sheet1").Cells(1, 1).Value = 1 Then
        Worksheets("Sheet1").Cells(1, 1).Value = "Not Null!"
    End If

VBA Else-If Condition

Usually you are not checking only one condition. This case requires the use of the Else-If condition:

Sub Conditionals()
    If Worksheets("Sheet1").Cells(1, 1).Value = 1 Then
        Worksheets("Sheet1").Cells(1, 1).Value = "This was a 1"
    ElseIf Worksheets("Sheet1").Cells(1, 1).Value = 2 Then
        Worksheets("Sheet1").Cells(1, 1).Value = "This was a 2"
        Worksheets("Sheet1").Cells(1, 1).Value = "This was something else!"
    End If
End Sub

When you have multiple conditions, and ‘ElseIf’ conditions need to be written as:

ElseIf [condition] Then
    [code to run if condition is true]

Anything that is left as an ‘Else,’ the outcome when no other conditions are met, are written by:

    [code to run if other outcomes are not true]

If an ‘ElseIf’ was your last statement in an ‘If’ condition, you would need to write the ‘End If’ after that line. ‘End If’ is used to denote the end of the ‘If.’

VBA Case Statements

A case statement is a slightly more elegant, robust way to do an Else-If statement. For a case statement, you can specify a number of conditions and the code will evaluate what case you current condition falls and under. It will then run the associated code with that condition:

Dim Number 
Number = Worksheets("Sheet1").Cells(1,1).Value
Select Case Number
Case 1 To 2
    Worksheets("Sheet1").Cells(1,1).Value = "This is between 1 and 2"
Case 3, 4
    Worksheets("Sheet1").Cells(1,1).Value = "This is between 3 and 4"
Case > 4
    Worksheets("Sheet1").Cells(1,1).Value = "This is greater than 4"
Case Else
    Worksheets("Sheet1").Cells(1,1).Value = "N/A"
End Select

You can use Cases to mix and match different conditions. Cases are a bit easier to read then a large block of Else-If statements, so use a Case if you have a complicated case of conditional logic.

With conditionals, you can begin creating complex and sophisticated macros and applications.

Leave a Reply

%d bloggers like this: