Whenever you read something in English, you (probably) proceed as follows:
- From left to right; and
- From top to bottom.
In other words, you read everything in order. Word by word. Line by line. Page by page.
Therefore, perhaps you won’t be surprised by the fact that, whenever a macro that you’ve created is executed, the execution proceeds in a similar order. In fact, the basic rule regarding program flow in Visual Basic for Applications is relatively simple:
Execution of a VBA procedure, generally, goes from top to bottom.
In other words, the statements within basic VBA procedures are usually executed in the following order:
- The first statement at the beginning of the procedure is executed. Then…
- The second statement in the procedure is executed. Then…
- The third statement is executed…
- And so on. Until…
- The last statement within the procedure is executed. Then, the macro ends.
Any macro that you create using the macro recorder is executed like this.
Let’s go back to reading techniques:
As explained in Breakthrough Rapid Reading, you can use different reading techniques depending on your objectives. As explained by author Peter Kump:
(…) reading means meeting your purpose while viewing the print in a book or other source of reading.
In some cases, these different reading techniques may imply that you don’t read the book in the order that I mentioned above. This leads me back to the topic of this Excel tutorial:
Flow control through If… Then… Else statements.
As mentioned above, some macros are executed in order. From left to right and from top to bottom. Word by word. Line by line. This basic flow works fine for certain macros.
However, as explained by John Walkenbach in Excel VBA Programming for Dummies:
In many cases (…), you need to control the flow of your code by skipping some statements, executing some statements multiple times, and testing conditions to determine what the procedure does next.
So let’s start by understanding what flow control is:
As defined by Mr. Excel in Excel 2013 In Depth:
Flow control is the ability to make decisions within a macro.
You generally achieve this by using certain programming constructs such as GoTo, If… Then… Else or Select Case statements, and loops.
The reason why recorded macros are always executed from top to bottom and left to right, as explained by Excel experts Bill Jelen and Tracy Syrstad in Excel 2013 VBA and Macros, is that the concept of flow control “will never be recorded by the macro recorder”. In other words, when using the macro recorder, you can’t use the constructs I mention above.
Now, each of these constructs deserves its own separate Excel tutorial. In this particular blog post I focus on what is, perhaps, the most important flow control structure within Visual Basic for Applications:
If… Then… Else statements.
This isn’t “just” my opinion. For example:
- In Excel 2013 VBA and Macros, authors Bill Jelen and Tracy Syrstad describe If… Then… Else statements as the most common device for program flow control.
- In Excel 2013 Power Programming with VBA, John Walkenbach explains that If… Then… Else statements are “perhaps the most commonly used instruction grouping in VBA (…).”
In fact, in Excel VBA Programming for Dummies, Walkenbach explains that he uses If… Then… Else statements on a daily basis.
Considering the above, I believe that once you know the basics of this particular statement you’ll also start using If… Then… Else statements on an (almost) daily basis. So let’s take a look at the contents of this Excel tutorial and, afterwards, dive into this post’s topic:
- 1 Basic Purpose Of The If… Then… Else Statement In Excel VBA
- 2 Syntax Of The If… Then… Else Statement In Excel VBA
- 3 Parts Of The If… Then… Else Statement In Excel VBA
- 4 Examples Of If… Then… Else Statements
- 5 Conclusion
- 6 In which scenarios do you use If… Then… Else statements?
- 7 Books Referenced In This Excel Tutorial
Basic Purpose Of The If… Then… Else Statement In Excel VBA
From a very basic perspective (I explain the exact process in more detail below), the If… Then… Else statement proceeds as follows:
- Step #1: Carries out a logical test for purposes of checking whether a condition is met.
- Step #2: Performs one action if the condition is met. Many times, although not necessarily always, it performs other action(s) if the condition isn’t met.
The description above is only an introduction and doesn’t cover all the processes you can model using the If… Then… Else statement. I provide further explanation about what you can do with these statements below.
In other words, the If… Then… Else statement allows you to conditionally execute one or more statements. The key word in this description is “conditionally”:
This means that execution of the relevant statements depends on whether the condition tested for in the logical test is met or not. One of the most widely used worksheet function within Excel, the IF function, works in a similar (even if slightly different) way.
As a consequence of the above, If… Then… Else statements are particularly useful for purposes modeling decision-making processes. Knowing how to appropriately model decision-making processes within Visual Basic for Applications is an essential skill. In fact, John Walkenbach’s philosophy about a successful VBA application, as explained in Excel 2013 Power Programming with VBA, is that:
Good decision-making is the key to writing successful programs.
If… Then… Else statements can be quite powerful. As I explain below, their structure allows you to add different layers of complexity to help you model different decision-making models. Author Richard Mansfield confirms this idea in Mastering VBA for Microsoft Office 2013, where he states that:
(…) If blocks in VBA are among the most immediately useful and versatile commands for making decisions.
This doesn’t mean that you should always rely on the If… Then… Else statement for purposes of modeling decision-making.
After all, the If… Then… Else statement isn’t the only way to model decision-making processes within VBA. A particularly important alternative, that I may explain in future tutorials, is Select Case. If you want to receive an email whenever I publish new material in Power Spreadsheets, please make sure to register for our Newsletter by entering your email address below:
Syntax Of The If… Then… Else Statement In Excel VBA
You can structure your If… Then… Else statements using either of the following structures:
- One line.
- A block of VBA code.
Let’s take a look at each of these structures separately. I explain all of the individual elements after we’ve checked out each of these forms.
How To Write An If… Then… Else Statement In One Line
If you want to write an If… Then… Else statement in one line, the syntax you must follow is:
If condition Then [conditional_statements] [Else else_statements]
The elements that appear within square brackets ([ ]) are optional. I explain each element further below.
How To Write An If… Then… Else Statement Using The Block Form Syntax
In order to write an If… Then… Else statement using the block form syntax, you use the following syntax:
If condition Then
[ElseIf condition_n Then]
Even though it’s not mandatory, you may want to indent the rows within an If… Then… Else statement. This helps to keep your VBA code readable. The need to do this may not be so obvious from the relatively short block displayed above. However, as explained in Mastering VBA for Microsoft Office 2013:
With complex If statements, it can make all the difference between clarity and incomprehensibility (…).
The Basic Process Followed By If… Then… Else Statements
All of the items enclosed within square brackets ([ ]) above are optional. I provide a more thorough explanation of each of these elements individually, regardless of whether they’re optional or required, below.
However, before I do this, let’s take a closer look at the process followed by If… Then… Else statements, and the different variants you can implement. I provide a basic 2-step explanation above. However, now that you’ve seen the syntax and elements of these statements, we can go into the details
From a broad perspective, and following the classification suggested in Mastering VBA for Microsoft Office 2013, you can create 3 different statement varieties with the If… Then… Else statement:
- Variety #1: If… Then statements.
- Variety #2: If… Then… Else statements.
- Variety #3: If… Then… ElseIf… Then… Else statements.
Let’s take an introductory look at the process followed by the most complex variety of If… Then… Else statements: the If… Then… ElseIf… Then… Else statement (variety #3 above). This particular variety allows you test multiple conditions.
- Step #1: The first condition is tested.
- Step #2: If the condition returns True, the [conditional_statements] which appear after the Then keyword are executed.
- Step #3: If the condition returns False, any [condition_n] that appears after the ElseIf keyword is tested.
This only applies when using the block form syntax. If the statement uses the one line syntax, it skips to Step #6 below.
- Step #4: If there is more than 1 [condition_n], they’re tested in order (from top to bottom) 1 by 1 until:
One of those condition_n returns True; or
All of the conditions are tested, and all have returned False.
- Step #5: If a condition_n returns True, the else_if_statements associated to that condition_n are executed. The keyword associating a condition_n to an else_if_statement is Then.
- Step #6: If all of the conditions (condition_n) tested return False, the statements that follow the Else keyword are executed.
The structure of this statement is more complex than that of the other 2 varieties in the classification above. More precisely:
- If… Then statements (variety #1 above) only carry out steps #1 and #2 above. If the condition returns False, no statements are executed.
- If… Then… Else statements (variety #2 above) carry out steps #1, #2 and #6 above.
Using the terminology in Excel 2013 VBA and Macros, this particular variety can be seen as making an either/or decision. This structure is also the closest to the one followed by the IF worksheet function.
In any case, once a conditional statement within an If… Then… Else statement is executed, the Visual Basic Editor jumps to the statement following the end of the respective If… Then… Else statement.
In other words, once a condition returns True and the corresponding conditional statement is executed, the If… Then… Else statement is exited. The other conditions aren’t tested at all.
Which Syntax Is Better For If… Then… Else Statements
As with a lot of things in Excel and VBA, there’s no hard rule dictating that you should always use (or avoid) the block form or the one line form of the If… Then… Else statement.
However, as general guidance:
- The one line syntax is more adequate for purposes of carrying out short and relatively simple logical tests.
- Once the If… Then… Else statement starts becoming more complex, the block form syntax provides several advantages, such as the following:
Advantage #1: It’s structure is more flexible.
This includes, among others, the possibility of nesting If… Then… Else statements using the block form syntax. I provide an example of this below.
Advantage #2: It’s easier to read.
Advantage #3: It’s easier to maintain and debug.
Note, however, that creating extremely complex If… Then… Else statements isn’t necessarily advisable. Generally speaking, if the following 2 conditions are met, you may be better of using the Select Case statement. These 2 conditions are:
- You’re evaluating a single expression; and
- Depending on the value of that particular expression, there are several different possible actions.
For example, Excel authority John Walkenbach states in Excel VBA Programming for Dummies that he generally uses If… Then… Else statements for “simple binary decisions”. He also explains that:
When a decision involves three or more choices, the Select Case structure (…) offers a simpler and more efficient approach.
Another alternative to If… Then… Else statements that Walkenbach introduces in Excel 2013 Power Programming with VBA (and which may be useful in certain situations) is the IIf function. This function is very similar to the worksheet IF function. More precisely, (i) it evaluates an expression and, (ii) depending on its result, returns 1 of 2 parts.
I may explain the Select Case statement and IIf function in future blog posts. If you want to be notified whenever I publish new content in Power Spreadsheets, make sure to register for our Newsletter by entering your email address below:
Parts Of The If… Then… Else Statement In Excel VBA
Depending on which syntax (block form or one line) you use when working with If… Then… Else statements, the items that compose the statement may vary. However, there are some material similarities between both forms.
In the sections below, I introduce and explain all of the items you can, and the ones you must, include when structuring If… Then… Else statements.
Part #1: If condition Then [statements]
All If… Then… Else statements begin with these 4 items. The actual layout differs depending on whether you’re using the block form or the one line syntax.
More precisely, if you’re writing an If… Then… Else statement in one line, all of these 4 items are in the same row:
If condition Then [statements]
However, if you’re using the block form, the statements appear in a separate row, as follows:
If condition Then
In either case, the 4 items are always the same. Let’s take closer look at them:
Items #1 and #3: If And Then Keywords
If and Then are required keywords. In other words, your If… Then… Else statements should always include them without any change.
The Then keyword serves an additional function:
It is the main criteria to determine whether an If… Then… Else statement follows the block form or the one line syntax. In order to determine which of the 2 forms of syntax is used, you simply need to take a look at what is after the Then keyword (in the same line).
If there is anything (other than a comment) after the Then keyword, and in the same line of code, the If… Then… Else statement is treated as a one line statement.
Conversely, if there’s nothing after the Then keyword (in the same line) or if the only thing after this keyword is a comment, the If… Then… Else statement is treated as following the block form syntax.
Item #2: condition
Conditions are a key element of If… Then… Else statements. As I explain above, the basic idea behind If… Then… Else statement is to pursue (i) one course of action if a condition is met, or (ii) another course of action if the condition isn’t met.
Therefore, the condition is a required element of an If… Then… Else statement.
There are 2 expression structures that you can use to structure a condition. I explain each of them now:
Condition Structure #1: Expression Evaluating To True Or False.
Perhaps the most common way of structuring a condition is by using a numeric expression or a string expression. For these purposes:
- The key condition that an expression must meet to be considered numeric is to have the possibility of being evaluated as a number. As long as the result of the evaluation is a number, it can include elements such as keywords, variables, constants and operators.
- On the other hand, the result of evaluating a string expression is always a “sequence of contiguous characters“.
In addition to being a numeric or string expression, the condition must evaluate to the Boolean values True or False. If the condition contains no valid data and, as a consequence, evaluates to Null, Visual Basic for Applications treats the condition as evaluating to False.
Condition Structure #2: TypeOf Object Expression.
When creating conditions using this structure, you’re asking Visual Basic for Applications to determine whether a particular object is a of a determined object type. The syntax of this particular type of conditions is as follows:
TypeOf objectname Is objecttype
- “objectname” is an object reference.
- “objecttype” is a valid object type.
Some examples of object types are Application, File, and Range.
If objectname refers to an object that is of the object type specified as objecttype, the condition evaluates to True. If this isn’t the case, the expression returns False.
I introduce the Select Case statement above and explain why, in some scenarios, this may be more appropriate than using complex If… Then… Else statements. However, if you want to test conditions that use the TypeOf Object Expression structure (as explained in this section), you can’t rely on Select Case. The reason for this is that this type of clause can’t be used alongside Select Case.
In addition to the restriction regarding the Select Case statement, TypeOf can’t be used with hard data types that are different from Object.
Item #4: [statements]
These are the statements to be executed if the condition is met and returns True. Even though most of the examples below have 1 conditional statement, you can theoretically place “any number of statements between the If and End If statements” as explained in Excel 2013 Power Programming with VBA.
Even though I use the plural (statements) to refer to this item, you can also have a single conditional statement. If you choose to have several statements and are working with the one line syntax, separate them with colons (:).
When it comes to the statements, there is one important difference depending on whether you’re using the block form or the one line form:
- These statements are optional in the block form.
In such a case, there’s no code to be executed if the condition above evaluates to True.
- However, they’re required in the one line form.
Part #2: [ElseIf condition_n Then] [else_if_statements]
If the first condition above returns False, the first conditional statement within the If… Then… Else statement isn’t executed. If you want to test additional conditions in such cases, you can use the ElseIf clause.
As shown in the example below, you can use more than one ElseIf clause in an If… Then… Else statement. In fact, as explained at the Microsoft Developer Network:
You can have as many ElseIf clauses as you want in a block If (…).
In any case, any and all ElseIf clauses must always be before the Else clause.
The ElseIf keyword, condition_n and else_if_statements are all optional. Let’s take a look at what each of these individual items does:
Items #1 and #3: ElseIf and Then
ElseIf and Then are keywords. Both words are required if you choose to work with an optional ElseIf clause.
As a general rule, you should only use ElseIf clauses when working with the block form syntax. If you try to place an ElseIf clause within a one line If… Then… Else statement, the Visual Basic Editor usually displays a message box with a compile error.
In other words, you generally can’t create If… Then… ElseIf… Then… Else statements using one line only.
As explained by Microsoft here, the only 2 things that can be placed before the ElseIf keyword are:
- A line number, which you can use to identify lines of code. Line numbers must comply with 3 basic criteria:
Criteria #1: They must be a combination of digits.
Criteria #2: They must be unique within the relevant module.
Criteria #3: They must begin in the first column.
- A line label, which is an alternative to line numbers when identifying lines of code. Line labels must comply with the following basic criteria:
Criteria #1: Even though they can be a combination of different characters, the label must start with a letter and end with a colon (:). Line labels are not case sensitive.
Criteria #2: They must begin in the first column.
Item #2: condition_n
I explain the general rules surrounding the creation and structuring of conditions above. Those general rules also apply for any condition_n that you include in an If… Then… Else statement.
Item #4: else_if_statements
Whenever condition_n is met, the corresponding else_if_statements are executed.
Part #3: [Else [else_statements]]
If absolutely none of the previous conditions (including any condition_n) within the If… Then… Else statement returns True, the else_statements are executed. In the words of author Richard Mansfield (in Mastering VBA for Microsoft Office 2013):
(…) the Else statement can serve as a catchall for anything not caught by the If and ElseIf statements above the Else, so you need to make sure the If and ElseIf statements cover all the contingencies you want evaluated before the Else statement kicks in.
This part is optional. In other words, your If… Then… Else statements don’t need to have else_statements.
However, if you choose to include else_statements, use Else as the keyword that signals these them. Whenever you include else_statements in your macros, make sure to precede them with the Else keyword.
There is one important difference depending on which syntax (block form or one line) you’re using to structure your If… Then… Else statement:
- When using the block form syntax, the Else keyword can only be preceded by a line number or label.
This is similar to what happens with the ElseIf keyword.
- When using the one line syntax, the Else keyword goes in the same line after the relevant statements.
I provide an example of this syntax below.
Part #4: End If
End If signals the end of an If… Then… Else statement that uses the block-form. As explained by Excel expert Michael Alexander in Excel Macros for Dummies:
Every time you start an If-Then-Else check, you must close it with a corresponding End If.
These keywords aren’t necessary when you’re using the one line form.
Examples Of If… Then… Else Statements
By now, you may have a good idea about what the If… Then… Else statement can do. In the following sections, I show you 4 different practical examples of actual macros that use this statement in different ways:
- The first example, uses a relatively simple If… Then statement while saving an Excel workbook with a new name.
I also use the If… Then statement in 1 of the examples within this Excel tutorial, which covers the topic of saving Excel files as PDF using VBA.
- In the second example, you get to see a common If… Then… Else statement within a macro that saves a workbook when a particular range is modified.
- The third macro below uses a slightly longer If… Then… ElseIf… Then… Else form of statement for purposes of determining the bonus of a sales manager according to the number of units it has sold vs. its sales goal.
- The last example of VBA code that appears in this Excel tutorial, helps you delete blank rows. In order to achieve this, the macro uses nested If… Then… Else statements.
Example #1: If… Then Statement
The following screenshot shows the VBA code behind a macro called Save_Workbook_NewName.
The purpose of this macro is to save the current active workbook using a name provided by the user.
You can download the Excel workbook that accompanies this tutorial and includes the Save_Workbook_NewName macro, for free, by clicking here.
I explain the Save_Workbook_NewName macro and all of its elements in more detail here. For purposes of this blog post, let’s focus on the If… Then statement, as shown in the image below:
Let’s break the statement into 2 parts, as shown in the image below, and analyze each part separately. This allows us to see how this matches the general syntax rules that I explain above.
Part #1: If workbook_Name <> False Then
The first and last word of this statement (If and Then) are part of the basic syntax of the If… Then… Else statement. They’re both required.
More interesting, for purposes of this Excel tutorial is the logical test that appears between “If” and “Then”: workbook_Name <> False.
This expression is composed of the following 3 items:
- Item #1: workbook_Name is a Variant variable. It’s declared in the first statement of the macro.
The current value of workbook_Name is that returned by the Application.GetSaveAsFilename method used immediately above.
- Item #2: <> is a logical operator meaning “not equal to”.
- Item #3: False is simply the False keyword. False’s value is equal to 0.
In other words, this statement compares the value of the workbook_Name variable with the False keyword. Therefore, this expression evaluates to True if the variable workbook_Name is different from the Boolean value of False. In this case, the conditional statement within the If… Then statement are executed.
If workbook_Name’s value is equal to False, the condition above evaluates to False. If this is the case, the conditional statement within the If… Then statement isn’t executed.
The conditional statement that is or isn’t executed depending on the result of the logical test above is:
Part #2: ActiveWorkbook.SaveAs Filename:=workbook_Name
This is the statement that is executed if the condition evaluated in the first part of the If… Then statement is True. If the condition is False, the statement isn’t executed.
This particular statement uses the Workbook.SaveAs method to save the current active workbook. The file name under which the workbook is saved, as determined by the Filename argument, is the value stored by the workbook_Name variable.
Writing The If… Then Statement In One Line
The If… Then statement within the Save_Workbook_NewName macro, as it appears above, uses the block syntax. You can, however, easily write the statement in a single line.
The following image shows how the macro, and the If… Then statement, look like in such case:
This version of the Save_Workbook_NewName macro carries out exactly the same activities as the one displayed further above. The only difference between them is the syntax:
- The first version above uses the block form syntax.
- This last version uses the one line syntax.
Example #2: If… Then… Else Statement
The If… Then structure illustrated in the example above is probably the simplest way to create an If… Then… Else statement. As explained above, the Else clause is optional.
Even though it’s optional, the Else statement is quite helpful. Else clauses allow you to determine that certain statement(s) should be executed if the condition tested by the If… Then… Else statement isn’t met.
The following macro, suggested by Excel expert Michael Alexander in Excel Macros for Dummies, does precisely that.
This particular macro saves the Excel workbook whenever any of the cells between C5 and C16 is edited.
As shown by the screenshot below, the only statement within the Sub procedure shown above is the If… Then… Else statement:
Let’s break it apart in the following 5 pieces in order to see what’s going on:
Before jumping into the analysis of each of the statements above, note that this macro is designed to run automatically whenever there is a change on the worksheet. In more precise terms, the macro is executed as a response to the Worksheet.Change event.
The Worksheet.Change event has a single parameter: Target. This is the range that changes in the relevant worksheet.
Part #1: If Intersect(Target, Range(“C5:C16”)) Is Nothing Then
By now, you’re probably quite familiar with the first and last words of this statement (If and Then). They’re keywords that are always included in If… Then… Else statements.
Therefore, the condition being tested by this particular statement is: Intersect(Target, Range(“C5:C16”)) Is Nothing.
This condition is composed of several individual items. Let’s enumerate them:
Item #1: Application.Intersect Method.
The macro under analysis saves the workbook whenever any of the cells between C5 and C16 is modified. The Application.Intersect method helps the VBA code find out whether this is the case.
The basic purpose of the Application.Intersect method is to return a Range object representing the intersection of several ranges.
The ranges whose intersection is searched are the arguments of the Intersect method. In the case above, the arguments of Intersect are:
As explained above, Target is the only argument of the Worksheet.Change event. It represents the range that changes in the worksheet.
Application.Intersect returns one of the following:
- If the ranges provided as arguments intersect: The intersection between them.
In the case above, this is the intersection between cells C5 to C16, and the changed cells.
- If the ranges provided as arguments don’t intersect: Nothing.
In the example above, this happens whenever the changed cells are outside the range between cells C5 to C16.
Item #2: Is Nothing.
In this particular case, the Is keyword is used as a comparison operator. It, basically, compares:
- What the Application.Intersect method returns; and
- If the Application.Intersect method returns Nothing, the condition evaluates to True.
- If the Intersect methods returns a Range object, the condition evaluates to False.
As explained above, Application.Intersect returns Nothing if the cells that have changed in the worksheet are outside the specified range (C5 to C16 in the example above). In these cases, the condition is met and the relevant conditional statement is executed.
If the cells that have changed in the worksheet are within the specified range (C5 to C16), the Intersect method returns a Range object. In these cases, the condition isn’t met and the conditional statement isn’t executed.
Let’s take a look at this conditional statement:
Part #2: Exit Sub
In other words, if the cells that change in the worksheet are not within the specified range, the Sub procedure is immediately exited.
Part #3: Else ActiveWorkbook.Save
The Else keyword signals the statements that are executed if the previous condition isn’t met. In other words, “Else” indicates which statement is executed when the cells that change in the workbook are within the range you specify.
In the macro we’re looking at, the statement that is executed in these situations uses the Workbook.Save method to save the active workbook.
In other words, if the cells that change in the worksheet are within the specified range, the workbook is saved.
Part #4: End If
End If ends the If… Then… Else statement.
Writing The If… Then… Else Statement In One Line
This particular macro provides the opportunity to see, in practice, how you can structure an If… Then… Else statement in one line. The following screenshot shows the relevant VBA code:
Example #3: If… Then… ElseIf… Then… Else Statement
Assume that the size of a sales manager’s bonus depends on the sales manager performance vs. his sales goal. More precisely, the size of the bonus is determined by using the criteria that appear in the following table:
|If a sales manager sells a number of units which is at least the following percentage (%) of his yearly sales goal||but less than the following percentage (%) of his yearly sales goal||the bonus will be equal to the following percentage (%) of the yearly salary|
There are several ways to model this situation, some of them using Visual Basic for Applications. One of these ways is by creating a Sub procedure that:
- Prompts the user to enter the relevant (i) sales goal and (ii) actual sales (both in units); and
- Returns the bonus to be paid to the sales manager.
The following macro, called SalesManager_Bonus, achieves just that:
Notice how, in the SalesManager_Bonus macro, the If… Then… Else statement includes several times the ElseIf keyword.
This is a complex If… Then… ElseIf… Else statement. Therefore, as I explain above, it may be more appropriate to use a different structure. Please consider this macro merely as an illustrative example, not a suggestion of how you should structure your macros.
The other parts of the SalesManager_Bonus macro exceed the scope of this Excel tutorial. Therefore, I don’t explain them in detail. However, at a basic level, the macro proceeds as follows:
- Step #1: The variables sales_Goal (to store the sales goal for a sales manager), actual_Sales (to store the actual sales made by a manager) and manager_Bonus (to store the resulting bonus level) are declared.
You can read more about variable declaration and data types here and here.
- Step #2: The InputBox function is used to prompt the user to enter the values for sales goal and actual sales. The entered values are assigned to the corresponding variables.
- Step #3: The If… Then… Else statement (which I explain below) determines the bonus level that corresponds to the inputted sales goal and actual sales values.
- Step #4: The MsgBox function is used to display the bonus level.
In order to dissect the structure of this If… Then… Else statement, let’s break it up into the following parts:
Part #1: If actual_Sales < sales_Goal Then manager_Bonus = 0
These lines follow the general structure of the beginning of any If… Then… Else statement. In this particular case:
- The condition being tested is whether the value of the actual_Sales variable is lower than the value of the sales_Goal variable.
- The condition evaluates to True if the actual sales (the actual_Sales variable) are lower than the sales goal (sales_Goal variable).
- If the condition is met, the manager_Bonus variable is assigned a value of 0. In other words, the sales manager receives no bonus.
Part #2: ElseIf Clauses
The SalesManager_Bonus macro contains 5 ElseIf clauses. All of these ElseIf statements have roughly the same structure:
ElseIf actual_Sales >= (sales_Goal * 1.#) And actual_Sales < (sales_Goal * 1.#) Then
manager_Bonus = 0.#
In the statements above, “#” acts as a placeholder for a number that varies depending on the particular ElseIf statement.
The structure of ElseIf clauses is, in some ways, similar to that of the first row of an If… Then… Else statement. More precisely, ElseIf clauses also:
- Carry out a logical test for purposes of determining whether a condition is met; and
- If the result of the logical test is True (meaning the condition is met), execute the relevant statement(s).
In the macro we’re looking at, each ElseIf clause can be divided in the following 2 sections:
Item #1: ElseIf actual_Sales >= (sales_Goal * 1.#) And actual_Sales < (sales_Goal * 1.#) Then.
The first and last words of this line are keywords that are part of the If… Then… Else statement. “ElseIf” signals the beginning of the ElseIf statement.
These ElseIf clauses use the And operator for purposes of testing the following 2 conditions:
- Condition #1: Whether actual_Sales are at least equal to a certain proportion of the sales_Goal (sales_Goal * 1.#).
- Condition #2: Whether actual_Sales are less than a certain (higher) proportion of the sales_Goal.
The use of the And operator means that the logical test only returns True if both conditions are met.
In other words, the ElseIf statements are testing whether the actual sales made by a sales manager are between the lower and upper limit for a particular bonus level. If this is the case, the logical test returns True and the corresponding conditional statement is executed.
That conditional statement which is executed is:
Item #2: manager_Bonus = 0.#.
This statement assigns a value to the manager_Bonus variable.
Part #3: Else manager_Bonus = 1
The Else statement is executed if none of the conditions tested previously returns True.
In the SalesManager_Bonus macro, this is the case whenever the actual sales are more than 150% (1.50) of the sales target. In such situations, the value assigned to the manager_Bonus variable is equal to 1 (100%).
As an example of what happens when the macro is executed, the following screenshots show the input and message boxes displayed by Excel:
- The first input box requests the user to enter the number of units of the sales goal. In the example below, I enter 100 units.
- The second input box prompts the user to enter the number of units actually sold by the sales manager. In this screenshot, I enter 115.
- The sales goal and actual sales values entered above show that, in this case, the sales manager’s sales have exceeded the goal by 15%. Based on the bonus table displayed previously, the bonus of the sales manager is 20% of its yearly salary. And this is precisely the result returned by the sample SalesManager_Bonus macro:
Example #4: Nested If… Then… Else Statements
Just as you can nest IF functions when working directly with Excel worksheets, you can nest If… Then… Else statements when working with Visual Basic for Applications. The following macro, called Delete_Empty_Rows_2 and which I explain in more detail here, is an example of such a structure.
This particular macro takes the selected range and deletes all the rows within that range that are completely empty.
Even though this particular Sub procedure has several separate If… Then… Else statements, the following sections focuses only on the first one. This statement, which is the one containing nested If… Then… Else statements, is highlighted in the image below:
In very rough terms, Delete_Empty_Rows proceeds as follows:
- Step #1: The variables aRow and BlankRows are declared for purposes of, respectively, iterating through all the rows within the current selection and storing the empty rows within the selected range.
- Step #2: The macro goes through each and every single row within the selection and:
1. Checks whether the row is completely empty.
2. If the row is completely empty, that Row is “added” to the BlankRows variable. Every time a new blank row is found, it’s added to BlankRows.
- Step #3: Once the macro has looped through all of the rows within the selection, it takes all the empty rows (represented BlankRows) and deletes them.
This particular macro is slightly more complex than the other examples I provide above. If you need further information regarding its structure and statements, please refer to this blog post. Delete_Empty_Rows_2 is the fifth macro that I explain there.
As shown by the image above, nesting If… Then… Else statements, is simply placing one statement within another. In other words, you’re placing one If… Then… Else statement (#2 in the screenshot above) inside another If… Then… Else statement (#1 in the image above).
The nested If… Then… Else statement (#2 above) becomes, then, a conditional statement itself. It’s therefore executed subject to the result of the relevant logical test.
To understand this better, let’s break up the If… Then… Else statement within the Delete_Empty_Rows_2 macro above in the following parts:
However, before we dive into the analysis of each of these parts, notice that they are themselves nested within a For Each… Next statement. The purpose of these type of statements is to repeat a group of statements (in this case the nested If… Then… Else statements that we look at below) for each element within a collection or array.
In this particular case, the key statement to understand is “For Each aRow In Selection.Rows.EntireRow”. For purposes of this Excel tutorial, it’s enough for you to understand the following in connection with it:
- “Selection.Rows.EntireRow” uses the Application.Selection property, the Range.Rows property and the Range.EntireRow property to return all the complete rows within the current selection.
- aRow is a variable whose purpose is to aid in the process of iterating through each of the elements within Selection.Rows.EntireRow.
- The result of the above is that Visual Basic for Applications repeats the statements within the For Each… Next statement for all complete rows within the current selection.
As a consequence of the above, the If… Then… Else statements that I explain below are executed and repeated for each row within the range of data that you’ve selected before executing the Delete_Empty_Rows_2 macro.
Part #1: If WorksheetFunction.CountA(aRow) = 0 Then
This is the opening statement of the first (outer) If… Then… Else statement.
You’re already familiar with the first and last keywords that appear above (If and Then). You also know that the part of the statement (WorksheetFunction.CountA(aRow) = 0) is the condition that is evaluated to determine whether the conditional statements are executed or not.
You use the WorksheetFunction object to call Excel worksheet functions when using Visual Basic for Applications.
In the case under analysis, the called worksheet function is the COUNTA function. The WorksheetFunction.CountA method is used to count the number of cells within a range that aren’t empty.
The only argument of the CountA method is the variable aRow. In other words, CountA counts the number of cells that aren’t empty within aRow.
Since this statement is nested within the For Each… Next statement explained above, the WorksheetFunction.CountA method is executed separately for each row within the selected range of data. Every time this happens, the CountA method returns the number of cells that aren’t empty within that particular row.
For every row, the result returned by the CountA method is compared with the number 0. If CountA returns 0, the logical test returns True and the conditional statements are executed. Otherwise, it returns False and the conditional statements aren’t executed.
CountA returns a value of 0 only when all the cells within the relevant row are empty. Therefore, in the end, this logical tests checks whether all the cells within a particular row are empty. If they are, the conditional statement (which I explain below) are executed.
Part #2: If Not BlankRows Is Nothing Then Set BlankRows = Union(BlankRows, aRow)
This part is composed of (i) the opening statement of the second (nested) If… Then… Else statement and (ii) the conditional statement that is executed subject to the condition within the opening statement being true.
You’re already familiar with the overall structure of this type of statements. Therefore, I focus on explaining the 2 most important items within this part.
Item #1: The Condition (Not BlankRows Is Nothing).
As usual, the main part of the first line of an If… Then… Else statement is the logical test that is evaluated for purposes of determining whether the conditional statements are executed or not. In this case, the question asked by this test is whether “Not BlankRows Is Nothing”.
In this scenario, the Is operator is used as a comparison operator. Its purpose is to help compare the following 2 items:
- Item #1: Not BlankRows.
The Not operator is used to negate BlankRows. BlankRows is a variable whose purpose is to store and represent the blank rows within the selected range. Therefore, Not BlankRows is the opposite of the BlankRows variable.
- Item #2: Nothing.
As a consequence of the above, the tested condition is whether the opposite of BlankRows (Not BlankRows) makes reference to the same object as Nothing.
To understand when this is True, remember that this statement is within a For Each… Next loop which goes through each row within the current selection. Additionally, notice how the 2 conditional statements that are executed depending on the result of this logical test (whether Not BlankRows Is Nothing) assign a value to the BlankRows variable.
I explain both of these statements below. For the moment, however, let’s take a look at how the change in BlankRows happens:
- The first time(s) the For Each… Next loop runs, BlankRows hasn’t been assigned to anything. In other words, BlankRows is equal to Nothing and its negation (Not BlankRows) doesn’t make reference to the same object as Nothing.
Since Not BlankRows is not Nothing, the condition isn’t met and returns False. Therefore, the statement that is executed is “Set BlankRows = aRow”.
- After the first blank row is found, BlankRows is always assigned to something. This assignment is made by either “Set BlankRows = aRow” (the first time) or “Set BlankRows = Union(BlankRows, aRow)”. Therefore, BlankRows is different from Nothing and its negation (Not BlankRows) makes reference to the same as Nothing.
Since Not BlankRows is Nothing, the logical test returns True. In such a case, the statement that is executed is “Set BlankRows = Union(BlankRows, aRow)”.
This leads us to…
Item #2: The Conditional Statement (Set BlankRows = Union(BlankRows, aRow)).
This statement is executed whenever the condition above (Not BlankRows Is Nothing) evaluates to True. As explained above, this is always the case after the first empty row is found.
This conditional statement can be broken up in the following components:
- The Set statement, which is used to assign an object variable to the BlankRows property.
- The Application.Union method, which is used to return the union of the ranges stored in the BlankRows and aRow variables.
In other words, Application.Union returns all elements that belong to (i) both BlankRows and aRow, (ii) only BlankRows or (iii) only aRow.
In practical terms, the consequence of this assignment is as follows:
- The variable BlankRows stores all the empty rows that the For Each… Next loop found previously.
- Whenever the macro finds a new empty row, it adds it to the BlankRows variable by using the Union method.
- The process is repeated as many times as necessary, depending on how many blank rows are found.
Once the Sub procedure has looped through all the rows in the selection, the variable BlankRows represents absolutely all the empty rows. These rows are deleted using the Range.Delete method.
Part #3: Else Set BlankRows = aRow
This is the Else clause of the If… Then… Else Then statement.
In other words, this part specifies the statement that is executed whenever the evaluated condition (Not BlankRows Is Nothing) isn’t met and, therefore, returns False. As explained above, this is the case until the Delete_Empty_Rows_2 macro finds a blank row.
When the Sub procedure finds that first blank row, this part contains the statement that is executed:
BlankRows = aRow
This statement follows the same structure as the conditional statement that is executed when the logical test (Not BlankRows Is Nothing) returns True (Set BlankRows = Union(BlankRows, aRow)).
Therefore, this particular row sets the variable BlankRows to aRow.
Parts #4 and #5: End If
End If statements terminate an If… Then… Else statement.
In this particular case, there are 2 consecutive End If statements for purposes of closing the 2 If… Then… Else statements that we have just analyzed. More precisely, the first End If statement closes the nested If… Then… Else statement. The second End If statement finalizes the outer If… Then… Else statement.
In the image above, you can easily distinguish which If… Then… Else statement is terminated each time due to the indentation. Indentation is particularly important when nesting If… Then… Else statements precisely because of this reason: it allows you to easily identify each individual If… Then… Else statement.
I started this Excel tutorial with an analogy to reading.
Most of us are taught to read everything, from top to bottom and from left to right. Word by word. Sentence by sentence. Paragraph by paragraph.
Even if you don’t believe in speed reading methods, you may agree that (in some cases), this isn’t the most efficient way to read.
Something similar happens with Visual Basic for Applications and macros. At the beginning, you create macros whose execution follows the order of the code. Word by word. Line by line. This is due, partly, to the fact that any macro created using the macro recorder can only be executed in such a way.
However, this isn’t the most efficient way to proceed in certain cases.
And if you want to start creating more complex applications, you’ll definitely need to master flow control. One of the main ways in which you can control program flow is by modeling decision-making processes.
I quote Excel guru John Walkenbach once more. In Excel VBA Programming for Dummies, Walkenbach explains that his philosophy is that:
(…) a successful Excel application boils down to making decisions and acting on them.
If… Then… Else statements are a key construct for these purposes. This is the reason why I’ve created this detailed Excel tutorial, where you’ve read about the following aspects of If… Then… Else statements:
- The main purpose of If… Then… Else statements.
- The different syntax forms and parts of If… Then… Else statements.
- 4 practical examples illustrating different ways to structure If… Then… Else statements.
Each of these macros uses a slightly different structure of the If… Then… Else statement. Remember that, you can get immediate free access to the Excel workbook that accompanies this tutorial and has the VBA code behind all these macros by clicking here.
I hope that these macro examples provide you some ideas about the almost unlimited possibilities you have for implementing If… Then… Else statements in your code.
Once you’ve created a few macros using If… Then… Else statements, make sure to leave a comment below sharing…
In which scenarios do you use If… Then… Else statements?
If you’ve started using If… Then… Else statements in your VBA code, I would be very interested in learning what are the cases or scenarios in which you’re using it the most.
Also, if you have any tips regarding the use of If… Then… Else statements in VBA, or think that I left something important out of the tutorial above, please share with the rest of us by leaving a comment below.
Books Referenced In This Excel Tutorial
Click on any of the images below to purchase the book at Amazon.