Using the If Conditional in VBA (If, ElseIf, Else, End If)

In this tutorial, we will explore the benefits of programming, one of which is the ability for the computer to “make decisions” using conditionals.

What is a Conditional?

A conditional is an evaluation made within the flow of information (one or more variables) that determines whether a specified condition is met. Based on the answer, different actions will be taken.

How to Write a Conditional in VBA?

To write a conditional (If in VBA), we need to evaluate a variable and give instructions on what to do if the condition is met. This evaluation is performed using the If command.

Example of If Conditional in VBA: Movie Tickets

Let’s imagine we are creating a system for selling movie tickets, where the ticket price depends on the viewer’s age. In this case, we define the following rules:

• Children aged 12 or younger will receive a “Preferential” ticket.
• Adults older than 12 will receive a “Regular” ticket.

To implement these rules in VBA, we will use an If conditional structure that evaluates the age entered in cell B2. If the age is less than or equal to 12, “Preferential” will be assigned to cell C2. Otherwise, “Regular” will be assigned. Here’s the corresponding code:

Sub AssignTicket()
    Dim age As Integer
    ' Read age from cell B2
    age = Range("B2").Value

    ' Evaluate the condition
    If age <= 12 Then
        Range("C2") = "Preferential" ' Ticket for children
    Else
        Range("C2") = "Regular" ' Ticket for adults
    End If
End Sub

In this example, if the user enters a value in cell B2 (e.g., 10), the system will evaluate the condition:

• If 10 <= 12 is true, cell C2 will show “Preferential”.
• If the user enters 15, the system will evaluate 15 <= 12, which is false, and cell C2 will show “Regular”.

Nested If Conditionals in VBA

Sometimes, it is necessary to evaluate a second condition after the first evaluation. This is called a “Nested Conditional”. The structure will be similar to the following:

Example of Nested If Conditional in VBA

Continuing with the movie ticket example, we could add an additional evaluation. Suppose we want to offer “Preferential” tickets to senior citizens (aged 60 or older) as well. The code might look like this:

Sub AssignCompleteTicket()
    Dim age As Integer
    age = Range("B2").Value

    If age <= 12 Then Range("C2") = "Preferential" ' Ticket for children ElseIf age > 59 Then
        Range("C2") = "Preferential" ' Ticket for seniors
    Else
        Range("C2") = "Regular" ' Ticket for adults
    End If
End Sub

Using Or and And (Logical Operators) in VBA Conditionals

It is also possible to evaluate multiple conditions in a single conditional using the logical operators And and Or.

Examples of Using Or and And in VBA Conditionals

If we want to consider an age range for “Regular” tickets, we can use the And operator:

Sub AssignTicketWithOperators()
    Dim age As Integer
    age = Range("B2").Value

    If age > 12 And age < 60 Then
        Range("C2") = "Regular"
    Else
        Range("C2") = "Preferential"
    End If
End Sub

Similarly, using the Or operator, we can evaluate if a ticket is “Preferential” for anyone under 13 or over 59:

Sub AssignTicketWithOr()
    Dim age As Integer
    age = Range("B2").Value

    If age < 13 Or age > 59 Then
        Range("C2") = "Preferential"
    Else
        Range("C2") = "Regular"
    End If
End Sub

Conclusions

As you have seen, there are various ways to program decision-making instructions in VBA, and each method has its advantages. With practice, you will improve your ability to write more compact and error-free code.

Want to know more? Contact us…