The “Else-If” Statement in VBA

by Lakshmi Ramakrishnan in IF

Contents

We need conditional statements

As a you develop code, there’s inevitably a need for the flow or logic to progress in a different direction based on the user’s inputs or on the details determined during runtime. This is where conditional statements are used.

Example scenario

If a user is looking for an investment instrument from a bank, the bank asks him several questions like the purpose of investment (investment goal), age, gender, guardian details if the investor is a minor, investment capacity, etc. After receiving all these details, the bank would provide the list of suitable instruments to the investor.

For example:

  1. If the investor wants to save money only on a monthly basis in order to pay his life insurance premium once in a year, he might opt for a recurring deposit.
  2. If the investor is looking for a savings account which is open to credits and locked for any withdrawal for 10 years or more, then the customer might opt for a provident fund account.

These are the conditions based on which the suggested instrument varies from one customer to another. If this scenario is programmed, there will definitely be a need for conditional  or branching statements that proceed with different flows in the program to make suggestions to the customer. Assume that suggestions given to a customer won’t include options that don’t suit them.

The If statement

Just like any other programming language, VBA provides an “If” conditional statement with its four variations as listed below.

  1. Simple “If”
  2. Nested “If”
  3. “If – Else”
  4. “ElseIf”

Each of these variations provides some benefit to the programmer. We can select the appropriate one to meet our requirements. Here is a simple description and an example for each.

Simple If

This is a conditional statement that runs a block of code only if the condition is met (in programming language, we say, “if the condition is satisfied or true”). If the condition is not met, the entire “IF” block is skipped.

For example:

Sub If_statement_demo()
'some billing code

intprice = 600
age = InputBox("Enter the age of customer")

If age > 59 Then

'provide a discounted price
intprice = intprice * (90 / 100)

End If

MsgBox "Please pay " & intprice & " and collect your goods."

End Sub

Nested If

One “if” conditional block inside another “if” conditional block is called a “Nested if” conditional block. There may be two or more conditions that need to be satisfied before executing the block of code. In such cases, a “nested if” statement can help. There is no limit on how many nesting If blocks you can make.

For example:

Sub If_statement_demo()
'some billing code

intprice = 600
age = InputBox("Enter the age of customer")

If Left(Date, 5) = "25-12" Then

    If age < 10 Then
    
        'provide a discounted price
        intprice = intprice * (90 / 100)
    
    End If

End If

MsgBox "Please pay " & intprice & " and collect your goods to avail the christmas discound for kids"

End Sub

If – Else

This variant of the “If” statement will have two blocks out of which only one will be executed (for sure) depending on whether the condition is true or false.

For example

Sub si_calculation()

' declare variables
Dim p, n, age, r, si, amt_amt

' Receive necessary inputs from the end user
p = InputBox("Enter the Principle amount")
n = InputBox("Enter the number of years")
age = InputBox("Enter the age of the customer")

' Set rate of interest depending on the age of the customer ( varies for senior citizens )
If age > 59 Then
    ' senior citizens
    r = 10
Else
    ' non- senior citizens
    r = 8
End If

'Calculate the simple interest and maturity amount
si = (p * n * r) / 100
mat_amt = si + p

'Display the output
MsgBox "The interest amount is " & si & vbCrLf & "The maturity amount is " & mat_amt

End Sub

In the above example, the rate of interest value has to be set regardless of the user’s age. If the age entered is greater than 59, the code in the “If” block would be executed. If the age entered is less than or equal to 59, then the “If” block would be skipped and the “Else” block of code would be executed.

Else – If

This variant of the If statement comes in handy when several conditions need to be tested, leading the control to flow in different directions in the program. If needed, a default block (Else block ) can also be added. The Else block would get executed if any of the conditions are not satisfied.

Pictorial representation of the program flow

In the image below, an “If” clause is used. For whichever condition is true, the respective block of code is executed and the control immediately reaches the “End if” statement. The control keeps skipping the blocks of the respective conditional statements one by one when they fail, then moves on to the default block of code if there is one. After executing the logic, the control proceeds with the execution of code post the “End If” line.

Else if Ladder pictorial reference for code

An overall example of the Else-if ladder

To explain this situation further with a scenario, let us say that the program below receives an integer input equivalent to a month and prints the respective month in words.

The control first goes through the “If condition” and then the following “Elseif conditions” one by one as the conditions keep failing. Once a condition passes, the block of code inside it is executed and the control moves to the “End If” line, skipping all other code before it.

Sub find_month()

' declare variables.
Dim monthnum, month

' receive input
monthnum = InputBox(" Enter the month in numeric form ")

If monthnum = 1 Then
    Debug.Print "January"
ElseIf monthnum = 2 Then
    Debug.Print "February"
ElseIf monthnum = 3 Then
    Debug.Print "March"
ElseIf monthnum = 4 Then
    Debug.Print "April"
ElseIf monthnum = 5 Then
    Debug.Print "May"
ElseIf monthnum = 6 Then
    Debug.Print "June"
ElseIf monthnum = 7 Then
    Debug.Print "July"
ElseIf monthnum = 8 Then
    Debug.Print "August"
ElseIf monthnum = 9 Then
    Debug.Print "September"
ElseIf monthnum = 10 Then
    Debug.Print "October"
ElseIf monthnum = 11 Then
    Debug.Print "November"
ElseIf monthnum = 12 Then
    Debug.Print "December"
Else
    Debug.Print "Invalid Input"
End If

End Sub

A scenario without the default “Else” block of code.

This is a program that suggests gifts for parties. If no appropriate party_type is found, then nobody gets a gift!

Sub gift_selection()

' declare variables
Dim party_type, gift

' receive the input
party_type = InputBox(" Enter the type of party which you are planning to attend ")

' suggestions based on the party
If LCase(party_type) = "wedding" Then
    gift = "Couple watch"
ElseIf LCase(party_type) = "birthday" Then
    gift = "Flower bouquet"
ElseIf LCase(party_type) = "baby shower" Then
    gift = "Lactation kit"
ElseIf LCase(party_type) = "bouse warming" Then
    gift = "Wall clock"
ElseIf LCase(party_type) = "baby naming" Then
    gift = "Photos of baby's birth"
End If

' print the message finally
If gift = "" Then
    Debug.Print " The party type entered by you is not in our list "
Else
    Debug.Print " A " & gift & " would be a great gift for a " & party_type & " party"
End If

End Sub

Example of an Else-if ladder with multiple conditions

This code’s purpose is to suggest offers to a supermarket customer based on his shopping list and final bill amount. The customer can receive only one of the offers available in the supermarket.

The program checks for the offer’s conditions using the customer’s shopping list and bill starting from the top of the Else-if ladder. One offer satisfying the conditions is added to the product list and billed amount for the customer.

Sub supermarket_offers()

' declare variables
Dim prodlist, billamt

' receive values through inputboxes
prodlist = InputBox(" Enter the list of items purchased ")
billamt = InputBox(" Enter the total bill amount ")

' Offers based on multiple conditions using "and" and "or" keywords

'Flat 10 percent discount on the total bill if the bill amount is more than 10000
If billamt > 10000 Then
    billamt = billamt * (90 / 100)

' if soap is a part of the product list and if billing amount is more than 2500
ElseIf InStr(prodlist, "soap") <> 0 And billamt > 2500 Then
    ' offer flat 50
    billamt = billamt - 50
' if milk is a part of the shopping list and the customer has not got any other offer
ElseIf InStr(prodlist, "milk") And billamt <= 2500 Then
    'offer free curd
    prodlist = prodlist & ", curd - 200 gm"
End If

'Print final list and bill amount
Debug.Print "With all the offers included the bill amount is " & billamt
Debug.Print "The final list of products is " & prodlist

End Sub

Conclusion

All the four variants of the “If” statement are extremely useful to implement any required logic in a program. It helps the program’s runtime control and skips the execution of unnecessary blocks of code when relevant conditions are not satisfied.

The Else-if statement can also be used in combination with multiple conditions to replace nested-if blocks of code. This makes program flow more straightforward and saves on execution duration as well.

See also:

Tagged with: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *