The “Else-If” Statement in VBA

We need conditional instructions

When developing code, it is inevitable that a thread or logic will evolve in a different direction based on user input or details defined at runtime. This is where conditional operators are used.

Example scenario

When the user searches for an investment instrument with the Bank, the Bank will ask several questions such as the purpose of the investment (investment objective), age, gender, contact details of a guardian if the investor is a minor, investment capacity, etc. After obtaining all these data, the Bank will provide the investor with a list of suitable instruments.

For example:

1. If an investor wants to save monthly in order to pay the life insurance premium only once a year, he can opt for a periodic deposit.
2. If an investor is looking for a savings account that is open for credit and closed for withdrawals for 10 years or more.

These are the conditions under which the suggested tool differs from one client to another. If this scenario is programmed, the customer will certainly need conditional or branching operators working with different threads of the program to make suggestions. Assume that the offers made to the customer do not include options that are not suitable for the customer.

The statement “If

Like any other programming language, VBA provides the conditional statement “If” in four variants, listed below.

1. A simple “if.”
2. The integrated “if”
3. “If it’s Elsa.”
4. “El soap opera. “

Each of these variants provides the programmer with a certain advantage. Depending on the requirements, we can choose the appropriate variant. Here is a simple description and example of each.

Simple as.

It is a conditional expression that executes a block of code only if the condition is met (in programming language we say “if the condition is or will be met”). If the condition is not met, the entire “IF” box is ignored.

For example:

Sub If_statement_demo()
‘some accounting code

intprice = 600age
= InputBox( Enter customer’s age)

If the age and gross weight is 59, then

“offer a discounted price
intPrice = intPrice * (90 / 100)

Ends when

MsgBox “Please pay” & intprice “and pick up your goods”.

Last Subsection

Genesteaded If

A conditional “if” block in another conditional “if” block is called a nested “if”. There may be two or more conditions that must be met before the code block can be executed. In this case, a nested statement may be useful. There is no limit to the number of nested blocks.

For example:

Sub If_statement_demo()
‘some accounting code

intprice = 600age
= InputBox( Enter customer’s age)

If left(date, 5) = “25-12”, then

If age and lieutenant; 10 then

“offer a discounted price
intPrice = intPrice * (90 / 100)

Ends when

Ends when

MsgBox “Pay ” and pick up your items to take advantage of the Christmas discount for children.

Last Subsection

If it’s Elsa.

This variant of the “if” operator will have two blocks, only one of which will (obviously) be executed, depending on whether the condition is true or false.

For example

Sub si_calculation()

Explain theDim variables
p, n, age, r, si, amt_amt

Required end user input data fetchp
= InputBox(“Enter main sum”)
n = InputBox(“Enter number of years”
)age = InputBox(“Enter customer age”)

Set interest rate according to client’s age (varies for elderly)
If age and gt; 59 Then’
elderly person =
10Other
‘
non-elderly person =
8End if

“Calculate the simple amount of interest and principal.
if = (p * n * r) / 100
mat_amt = si + p

“Afficher la sortie
MsgBox “The interest amount is ” &amp ; si &amp ; vbCrLf &amp ; “The maturity amount is ” &amp ; mat_amt

Last Subsection

In the above example, the interest rate must be fixed regardless of the user’s age. If the entered age is over 59 years, the indicator in the “If” block is output. If the entered age is less than or equal to 59, the “If” block is ignored and the indicator in the “If” block is output.

Elsa. – Si.

This variant of the “If” operator is useful when multiple conditions need to be checked, so that checking is done in different directions in the program. If necessary, a default block (Else block ) can also be added. The Else block is executed if one of the conditions is not met.

Pictorial representation of the program sequence

The following figure uses the “If” clause. For each condition that is true, the corresponding code block is executed and the control immediately reaches the “End if” statement. If not, the controller skips the corresponding conditional declaration blocks in the sequence further and switches to the default code block, if any. After executing the logic, the controller continues executing the code after the “End If” line.

Ошибка! Имя файла не указано. “alt=”Else if Ladder Image Reference for Code” data-orig-width=”735″ data-orig-height=”575″ data-ezsrc=”http://server.digimetriq.com/wp-content/uploads/2021/02/The- “Else-If-Statement-in-VBA.jpg” data-ez=”” />

Error: The file name is not specified. “alt=”Pin it!” data-ezsrc=”/utilcave_com/social/pin_it.png” />

Error: The file name is not specified. “alt=”Share on Facebook” data-ezsrc=”/utilcave_com/social/fb_share.png” />.

General example of the Elsa-if trap.

To further explain this situation with a scenario, suppose the following program receives a complete input corresponding to a month and produces the corresponding word month.

The control goes through the “If” condition first and then through the following “Other Conditions” in order, as the conditions continue to fail. Once the condition is met, the code block containing it is executed and the control goes to the “End If” line, skipping all other code preceding it.

Sub find_month()

Explain the variables.
Dim month, month

Input
monthnum = InputBox (“Enter month in numeric form”)

If month = 1, then
Troubleshooting. Press “January.”
If the number of months = 2, then
Debug. Print “February.”
Otherwise, the number of months = 3
Debug. Print “March.
Otherwise, the number of months = 4
Troubleshooting. Print “April.
Otherwise, the number of months = 5 after
Troubleshooting. Print “May.”
Otherwise, when the number of months = 6 after
Troubleshooting. Print “June.”
Else, when the number of months = 7
Troubleshooting. Print “July.”
Otherwise, when the number of months = 8 after
Troubleshooting. Press “August.”
Otherwise, when the number of months = 9, then
Troubleshoot. Print “September
If the number of months = 10, then
Troubleshoot. Print “October
Otherwise, the number of months = 11 after
debugging. Print “November
Otherwise, the number of months = 12 after
Debug.
Otherwise, print “December.
Debug. The printout of “Invalid entry”
is completed if

Last Subsection

Scenario without the default code block “Elsa”.

This is a program that offers gifts for the holidays. If a suitable party_type is not found, no one gets a gift!

Sub gift_selection()

declareDim party_type, gift variables

Obtaining contributions
party_type = InputBox(“Enter the type of party you want to attend”)

Sätze basierend auf partyIf LCase(party_type) = “mariage” Thengift
= “horloge de couple”.
ElseIf LCase(party_type) = “Geburtstag” Thengift
= “Blumenstrauß”.
ElseIf LCase(party_type) = “baby shower” Thengift = ”
kit d’allaitement”.
Sinon, LCase(party_type) = “house warming”.
Gift = “Wanduhr
ElseIf LCase(party_type) = “nom du bébé” Thengift
= “photos de naissance du bébé”
Fin Si

Print the message at the end. If
gift = “” Then.
Debug.Print ” The type of party you specified is not in our list ”
Other.
Debug.Print ” A ” & gift & ” would be a great gift for ” & party_type & ” party “End If

Last Subsection

Example of an “Else-if” scale with multiple conditions

The purpose of this code is to suggest offers to the supermarket customer based on his shopping list and the final bill amount. The buyer can only get one of the offers available in the supermarket.

The program checks the offer conditions against the customer’s purchase list and invoice, starting at the top of the Else-if scale. An offer that meets the conditions is added to the product list and the customer is invoiced.

Sub-supermarket offers()

Report variablesDim prodlist, billamt

Obtaining values via input fields
prodlist = InputBox ( Enter list of goods purchased )
billamt = InputBox (“Enter total invoice amount”)

Sentences based on multiple terms and using the key words “and” and “or”.

“Flat rate discount of 10% on total bill amount if bill amount exceeds 10000 if bill amount and gross amount exceed 10000”.
billamt = billamt * (90 / 100)

if the soap is listed in the product list and the billamte is greater than 2500.
Otherwise, InStr(prodlist, “soap”) & lt;& gt; 0 And billamt & gt; 2500 Then” bid a package of 50
billamt = billamt – 50
” if the milk is on the purchase list and the buyer has not received another bid.
If InStr(prodlist, “milk”) and billamt & lt;= 2500, then” offer
free cottage cheese
prodlist = prodlist and “, the cottage cheese weighs 200 g” Fin Si

” Print the final list and bill amount
Debug.print “Include all offers, invoice amount” & billamt
Debug, print “The final product list is” & extend

Last Subsection

Conclusion

The four variants of the term “If” are extremely useful to implement any desired logic in a program. It allows controlling the execution time of the program and skipping the execution of unnecessary code blocks if the corresponding conditions are not met.

The Else-if statement can also be used in conjunction with various conditions to replace nested code blocks. This simplifies program flow and saves execution time.

See also :

Frequently asked questions

How do you write when you’re still in VBA?

It executes one set of codes if the specified condition is TRUE, or another set of codes if it is FALSE. The IF-THEN-ELSE declaration is a function embedded in Excel that is classified as a logical function. It can be used as a VBA function in Excel.

How do I write multiple “If” statements in VBA?

VBA If-Else explanation – How to create formulas step by step.

What does “Else” mean in VBA?

Execute some statements if the condition is true, and execute others if the condition is false. Use “If”… And then… Otherwise, to define two blocks of executable statements: one block is executed if the condition is true, and another block is executed if the condition is false.

Related Tags:

excel vba if then multiple statements,nested if vba,excel vba if statement multiple conditions,else without if vba,excel vba if statement based on cell value,if range( ).value = then,access vba if statement multiple conditions,vba nested if then else in a for next loop