Vba For Excel

Vba For Excel

This course contains the basics of Vba For Excel

Course introduction
Interview Questions
Pragnya Meter Exam

Vba For Excel

Decisions in VBA
Comparison operators

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

String comparisons

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.

Listing

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

Self-assessment exercise

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.

Listing

Screenshot of Listing

Logical operators

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

Self-assessment exercise

Rewrite the following If to eliminate the Not and to clarify the code:

If Not(x < 20) Or Not(y >= 20) Then

Select case

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

Exercises

  1. 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 .
  2. 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.
  3. Open a new workbook and enter the table of data shown below into the cell range A2:J3 into sheet1:
  4. 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.

  5. Design a macro called categoriseMonthSales using structured English for the following task. It should check the value for:
  6. 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).

Searches relevant to you
Top