Decisions

Programs, unless they are extremely simple, usually have to make decisions according to data retrieved or input by the user . Decision making is one of the most important areas of programming, because it specifies what will happen when different events occur.

A good example of a common programming decision is IF something is true, THEN do action1 , or ELSE do action2 . In everyday life, this might be the statement ‚“IF it is raining, THEN carry an umbrella, or ELSE (if the condition is not met and it is not raining) carry sunglasses. ‚½

Here is some sample code to show how the conditional If..Then..Else statement works and how it produces different results. Enter this in the module you created in Chapter 2. See Figure 4-1 for an example of what your code window should look like.

 Sub test_if() 
 If Application.ActiveCell = 5 Then 
 
 MsgBox "Cell is 5" 
 Else 
 
 MsgBox "Cell is not 5" 
 End If 
 End Sub 

Figure 4-1: Code for conditional If statement

This example refers to the active cell within the Excel application object with the line Application.ActiveCell , where the cursor is currently positioned. Click the worksheet and enter 5 in the current cell. Make sure that the cursor remains on that cell. Go back to your code window and press F5 . You will get a message box saying that the cell is 5. Now go back to the spreadsheet and change the cell to another value or delete the value. Run the macro again and you will get the message box saying the cell is not 5.

Notice in Figure 4-1 that I have indented the code to separate the main parts of the conditional statement ‚ this makes it easier to read and easier to debug because you can instantly see the groups of statements and where they start and finish. It is possible for If statements to be nested inside each other, so you can have an If statement within an If statement; this frequently happens in more complicated programs. (See the section ‚“Looping, ‚½ later in this chapter, for more on how this works.) It is convenient to be able to see at a glance where one If statement starts and ends. If there are others in between your start and stop point, you can frequently get lost when debugging code.

The End..If statement shows where the conditional statements finish, or you can put the entire If statement onto one line, which then would not require an End..If, as shown here:

 If Application.ActiveCell = 5 Then MsgBox "Cell is 5" 

If you have multiple instructions to be executed, you can place the statements on a single line if you separate each statement with a colon . However, this can become very difficult to read and debug and there are often several instructions to be carried out that preclude putting everything on one line.

Conditional operators that can be used are as follows :

Operator	Meaning
=	Both numbers or values are equal. This condition will also work for values, such as ‚“dog ‚½ and ‚“cat. ‚½
<	First value is less than second value.
>	First value is greater than second value.
<=	First value is less than or equal to second value.
>=	First value is greater than or equal to second value.
<>	First value is unequal to second value.

An expression such as x=1 is evaluated as a Boolean value, that is True or False or Non-zero or Zero. This means that you do not always have to use an operator ‚ if you are only interested in whether a cell has a non-zero value in it then you can use

 If Application.ActiveCell Then MsgBox "Cell has a value" 

Multiple Conditional Statements

In the preceding statements, I used only a single conditional statement in the form of If x=1 Then... . You can also use multiple conditional statements using a logical operator. For more information on logical operators, refer to Chapter 6.

Multiple conditional statements are straightforward and work almost like plain English. They use the operators And and Or and, for the purposes of this example, mean exactly what they mean in English.

If you have two conditions that you want to test, you write the If statement in the following form:

 If x = 1 And y > 5 Then 
 MsgBox "x=1 and y>5" 
 Endif 

The message box will be displayed only if both conditions (x = 1 and y < 5) are met. If, for instance, x > 1 but y has a value of 4, the message box will not be displayed. Similarly, you could use the following statement:

 If x = 1 Or y > 5 Then 
 MsgBox "x=1 or y>5" 
 End If 

In the case of the preceding Or , the message box will be displayed if either one of the conditions is met. For example, if x = 1 or y > 5, the message box will be displayed. Therefore, x could be 0 and y could be 6, or x could be 1 and y could be 4, and the message box would still be displayed in either case.

You can put in several And s or Or s within the condition, although it gets complicated with more than three. It all depends on what you are trying to achieve in your decision statement and what the procedure is trying to do. You may be writing something very simple such as If x=1 Then , or you may be working on a more complicated conditional statement.

Select Case Statements

Another statement available in VBA for conditional processing is the Select Case statement. If you have a variable and you want different actions to occur based on the value of that variable, you can use a series of If statements as follows:

 If x=1 then MsgBox "x=1" 
 If x=2 then MsgBox "x=2" 
 If x=3 then MsgBox "x=3" 

However, this is a good example of where a Select Case statement makes the code much cleaner:

 x = 23 
 Select Case (x) 
 
 Case (1) 
 MsgBox "x=1" 
 Case (23) 
 MsgBox "x=23" 
 
 End Select 

The Select Case statement provides a simple means to interrogate a specified variable and take action accordingly . The statement Select Case (x) defines the variable to be interrogated as x and is the start of the block of code for this procedure. Case (1) gives the action for if the value is 1 ‚ show a message box showing ‚“x=1. ‚½ Case (23) gives the action for if the value is 23 ‚ show a message box showing ‚“x=23. ‚½ Because x has been set to 23 at the start of the code, the message box will show ‚“x=23. ‚½

You can also include the statements To and Is in the Case statement:

 Function Test_Case (Grade) 
 Select Case Grade 
 Case 1 
 Msgbox "Grade 1" 
 Case 2, 3 
 Msgbox "Grade 2 or 3" 
 Case 4 To 6 
 Msgbox "Grade 4, 5 or 6" 
 Case Is > 8 
 MsgBox "Grade is above 8" 
 Case Else 
 Msgbox "Grade not in conditional statements" 
 End Select 
 End Function