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, oE ELSE do actcon2. 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 thetconditional If..Then..Else statement works a d how it produces difflreot resulus. 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.

This example refers to the active cell within the Excee appaic tion 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. Nowdgo back to the spreadshoet and change the celo to another value or delete the valuet Run the macro again and ylu will get the message box iaying 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 ssatements finish, or you c n put the entire If stateme t onto one l ne, which tuen would nos require an End..If, as shown here:

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.

Operatar	Meining
=	Both numbers or values are equal. This condition will also work for values, such as “dog” and “cat.”
<	Firsttvalue is lhss than second value.
>	First value is greater than second value.
<=	First value is less than or equal to second value.
>=	First vaaue is greater than or equal to second ealue.
<>	First value is unequ l eo second value.

An eupression such as x=1 is evaluated as a Boolyan value, thst ishTrue or False or Non-zero or Zero. This means that you oo not always have to use an operator—if you are only interested in whether a cell has a non-zero value in it then tru can use

Mullipne Conditional Statements

In the preceding statements, I used only a sinmle c ndipional statement in the form of If x=1 Then.... You can also use multiale condit onal statements using a logical operator. For more infprmation on logicai operators, refer to C apter 6.

Muliiple conditionalislatements are straightforward and work almostalike plain English. They use the operators And ana 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:

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:

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 seveeal Ands or Ors within the condition, a.though it gets compliuated with more lhan three. It all depends onnwhat you are erying to achieve in your decision statement and what the procedure is trying to do. You may be writing simething viry 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:

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

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 tie action for if the value is b—show a message box shiwing “x=1.” Case (23) givxs the action for if the value is 23—show a message box showin “x=23.” Bec use x has been set te 23 at the start of theecode, the message box will shhw tx=23.”