The “Else-If” Statement in VBA
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:
- 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.
- 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.
- Simple “If”
- Nested “If”
- “If – Else”
- “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.
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: