Computers do more than calculate among other things they also make decisions. VBA macros can select a course of action that depends on the outcome of some decision. We have already encountered one way in which this can be done by using the If Then statements. This chapter will look at VBA decision-making statements in a little more detail.
When we used a statement like If cell. Value > 0 Then in previous chapters,the expression cell. Value has to be greater than 0 before the result will succeed. In other words, the result of the comparison must be true. This is called a comparison operator. Clearly, the comparison operators produce Boolean outcomes, because the outcome of the comparison is either true or false. The mathematical operators, discussed in previous chapters, produce numeric values, whereas the comparison operators produce Boolean results. Table gives some illustrations of comparison operators.
Some comparison operators used in VBA
When you compare strings, VBA uses the American Standard Code for Information Interchange (ASCII) codes, to determine how to compare the characters. For example,the ASCII code for the uppercase letter A whose ASCII code is 65 is less than the uppercase letter B (ASCII code 66). The ASCII codes for the lower case characters are the same for the Uppercase characters plus 32. Hence,the ASCII code for lowercase a is 65 + 32 = 97, the ASCII code for lowercase b is 66 + 32 = 98, and so on. To use comparison operators in your programs, you must understand how they get their true or false results. The If statement, introduced in the next section,explains how you can use these results to make decisions in your program. Before you read the next section, make sure that you understand how these operators compare values.
Examples of comparison operators
Comparing different data types
The expressions on both sides of a comparison operator must conform to the same data type or at least compatible data types.You cannot compare a string to a numeric data type, e.g. one with 1.They are stored differently in the computer, and if you try, you will get a type mismatch error. You can compare any numeric data type against any other numeric data type most of the time. In other words, you can test whether a single-precision value is less than or greater than an integer value, such as 100 >99.99. Clearly, this comparison would return the result True.
The If statements
Many of the previous examples have shown that programs often have to make decisions. This means that a program will follow one course of action if one comparison is true or perhaps another course of action if it is false. There are two main structures for implementing decisions in VBA: If and Select Case.
If uses the comparison operators you learned earlier in this lesson to test data values, and can test whether to execute complete blocks of code.This means that If lets your program execute only parts of the program if the data warrants partial execution.In this section we will look at three variants of the If statement. These are:
One-way selection using If Then
Two-way selection using If Then Else
Multiway selection using If Then ElseIf Else
The one-way If structure: If Then
The one-way If structure is used in programs when some course of action is taken if the outcome of a decision is true; otherwise, no alternative action is taken when the decision is false.It has the following syntax:
If ComparisonTest Then one or more VBA statements End If
The End If statement lets VBA know where the body of the If structure ends.Suppose we wanted to write a macro that prompts the user with an InputBox to enter a range of cells, which it will then highlight. The program is implemented in Listing. It uses a one-way If Then statement, that checks to see if that the range variable Rng is not empty.If it is not, then the range is selected using the Rng.Select statement.
Figure Screenshot from Listing
The two-way If
The one-way If statement is applied when you want the program to execute one set of instructions if some condition is true, otherwise do nothing. However, you may want the program to execute one set of instructions if some condition is true, else execute another set of instructions. This type of logical construct is called a two way if statement. The general syntax is:
If ComparisonTest Then One or more VBA statements Else One or more VBA statements End If
We can see that whereas If executes code based on the comparison test s true result,the Else statement executes code based on the comparison test s false result. Else is an optional part of the If statement and specifies the code that executes if the comparison test is false.
If we look at the previous example, we can see that there is a problem. If a valid range is selected then the range is highlighted, if not, then nothing happens. However, in this event, we might want to prompt the user to enter a valid range, perhaps using a MsgBox. A two-way If structure has been used to implement this change in Listing. Note that the comparison test is the same, but an Else alternative displays the error message.
Listing and The error message from Listing
This next example shows how the two-way If can be used.This macro will input a person s sex and display the message You can retire at 60 if the sex of the person is female, otherwise display the message You can retire at the age of 65. It uses a string variable to store the sex of the person, which is input through an InputBox command.The two-way If then checks if the sex is female and outputs the corresponding message. Otherwise, it displays the alternative message.
Another two-way If example
Screenshot from Listing
What is wrong with this If statement?
If (z > 0) Then MsgBox "This" Else MsgBox "That" End Else
The multi-way If statement
The two-way If statement is applied when you want the program code to execute one set of instructions if some condition is true,else execute another set of instructions. However, there are times when you may want to execute one set of instructions if some condition is true, or else if some other condition is true, you may want to execute another set of instructions, and so on, until all alternatives have been completed. The general syntax is:
If ComparisonTest Then One or more VBA statements Else If One or more VBA statements Else If One or more VBA statements Else One or more VBA statements End If
Consider the previous example. This was written as a two-way If, where the first condition checked if the persons sex was female and displayed the appropriate message,else the alternative message for a male person would be displayed. The problem is that the program would take any alternative value other than female. We will extend this two-way If to a thre e-wa y If so that first condition will check if the person s sex is female and display the appropriate message, else if the person s sex is male, it will display the male message, else it will display an error message such as You have entered an invalid sex . In Listing,the If checks if the sex is female and display the corresponding message. Else If checks if the sex is male, will display the corresponding, and the final Else will display an error message.
Screenshot of Listing
VBA supports three logical operators: And, Or,and Not. They let you combine two or more comparison tests into a single compound comparison.For example,suppose we wanted to implement the following code:
If(myCell.Value =6) Then If (Activecell.Value>0) Then MsgBox "Done" End If End If
The two comparison tests can be replaced with one test as:
If (myCell.Value =6) And (ActiveCell.value >0) Then
The logical operators
Rewrite the following If to eliminate the Not and to clarify the code:
If Not(x < 20) Or Not(y >= 20) Then
Consider the If structure shown in Listing. Although the logic is simple, the coding is a little difficult to follow.
TicketPrice VBA Program
VBA supports the Select Case statement that can make it easier to understand, multiple-choice conditions than If ElseIf Else statements. The syntax of the Select Case statement is as follows:
Select Case Expression Case value one or more VBA statements Case value one or more VBA statements Case value one or more VBA statements Case Else one or more VBA statements End Select
Listing is a Select Case version of Listing. Select Case organises the multiple-choice selections into a more manageable format.
Using Select Case to simplify complex If Else statements
In this example, if ticketPrice holds 20, the message Standing at rear is displayed. If it holds 30, Front wing seats is displayed. The logic continues through the Case 60: statement. If ticketPrice holds a value that does not fall in the range 20 to 60, the final Case Else displays the message Royal Box.
Screenshot of Select Case example
The body of each Case can consist of more than one statement,just as the body of an If or If Else can consist of more than one. VBA executes all the statements for any given Case match until the next Case is reached.Once VBA executes a matching Case value,it skips the remaining Case statements and continues with the code that follows End Select.
Notice the colons after each Case value statement. These are optional, but do help to separate the case being tested from the code that it executes.
Alternative select case formats
You can use variations on the above Case Select format as the following syntax shows:
Select Case Expression Case expr1a To expr2a: One or more VBA statements Case expr1b To expr2b: One or more VBA statements Case expr1c To expr2c: One or more VBA statements Case Else: One or more VBA statements End Select
The Case lines require a range, such as 4 To 6. The To option enables you to match against a range instead of a relation or an exact match.
Notes on select case
The format of Select Case makes it look as difficult as a complex nested If Else, but Select Case structures are easier to code and to maintain than their If Else counterparts. Select Case is a good substitute for long, nested If Else conditions when several choices are possible.
The Case expression can be any VBA expression such as a calculation a string value,or a numeric value provided that it results in an integer or a string value, and this must match the expression s data type.
The Select Case structure is useful when you must make several choices based on data values. It can have two or more Case value sections, and the code that is executed depends on which value matches the expression.You can write an (optional)Case Else body of code which will be executed if none of the values match. Otherwise, nothing happens and control continues with the statement that follows End Select.
Here is an example of a macro based on month_bonus range in the weeklysales worksheet of the SALESMAN.XLS workbook The purpose of the macro is to classify cells in the range weekTotal by assigning different colours to the cells depending on whether the cell value is less than 60, equal to 60, 61 to 70 or greater than 70.The colours then assigned would be green, red, blue and yellow respectively. In this example, we can see that the Select Case statement is used for this four-way selection.
Implementing a multi-way If using Select Case
The operators in VBA
Table indicates the symbols that are used for operators in VBA. These have been classified into three groups: Arithmetic, Comparison and Logical operators.
Operators in VBA
Write a macro that will prompt the user to input a person s name using an InputBox and output the message TooLong if the name is longer than 30 characters, otherwise the macro should output the message Name OK .
Open a new workbook using the default sheet1. Using the cell range C3:C12 enter the following numeric data that represents student examination marks out of 100: 41, 55, 36, 59, 70, 67, 37, 69, 13, 62. Design a VBA macro using structured English pseudocode that will count the number of marks in each category as Distinctions (>=70),Credits (>=60 and < 70) Passes (>=40 and <60) and Failures (<40). From the pseudocode write the VBA macro using the Select Case to implement this program.Output the number in each of these categories using the cells E3: H3 under suitable headings.
Open a new workbook and enter the table of data shown below into the cell range A2:J3 into sheet1:
Orange Apple Apple Orange Pear Apple Lemon Orange Pear Lemon Lemon Orange Pears Orange Orange Lemon Orange Orange Apple Apple
Write two separate VBA programs,one that uses multi-way If and the other that uses Select Case statements so that the cell colour is orange if the cell value is Orange,yellow if the cell value is Lemon,green if the cell value is Apple and blue if the cell value is Pear.
Design a macro called categoriseMonthSales using structured English for the following task. It should check the value for:
below 0 and 200 then colour the cell green (colorIndex = 31), between 201 and 500 colour the cell yellow (colorIndex=31), 501 to 1000 colour the cell blue (colorIndex=5), above 1001, colour the cell another colour (colorIndex=3).