Control Structures

Computer code only runs in a sequence. It first executes a statement and, if there is no error, moves on to the next statement. We call this a sequence structure. However, what happens if you want the next step in the sequence not to be the next line of code? You may need to transfer control of the program to a different block of code if a certain situation occurs. Or you may need a block of code to repeat until a situation occurs (or for a predefined number of times).

We call the mechanisms to accomplish this control structures. These structures can be divided into two very broad categories: decision structures and repetition structures. As you will see shortly, a decision structure allows the program to make decisions. The most common of them is the If…Then structure. However, VBA provides other possibilities: If…Then…Else, ElseIf, Select Case, and IIF.

A repetition structure goes through a block of code either a predefined number of times, or until something occurs to cause the program to break out of the loop.

VBA provides two broad repetition structures: For…Next and Do…Loop. Within those two structures, there are a number of variations.

Decision-Making Structures

Let’s consider the pseudocode for a morning routine. You may have one point where you write the following:

If it is raining,
Then I will take an umbrella.
Else I will just go to my car.

The words in boldface are the VBA keywords necessary to make a decision.

You start a decision-making structure by doing a comparison of two entities. Is entity A equal to entity B? Is entity A greater than entity B? Is entity B true? These are called conditional expressions. The symbols used for them are listed in the following table.

=	Is equal to
<>	Is not equal to
>	Is greater than
<	Is less than
>=	Is greater than or equal to
<=	Is less than or equal to

Let’s take a look at a simple decision-making structure in a subroutine:

Sub ifTest()   Dim intNum As Integer   Dim strMessage As String   intNum = 12   If intNum > 10 Then   strMessage = "The number is " & intNum   End If   Debug.Print strMessage End Sub 

There are a couple of things to notice in the code. The line that contains the conditional expression begins with If and ends with Then. Also, like other structures in VBA, the conditional If structure must end with an End statement, in this case End If.

If you run the preceding example in the Immediate window, you should see the results shown here:

There are a couple of problems here. The code works fine. But what happens if the number is not greater than 10? More importantly, since the number is “hard-coded” to the variable, it will always be greater than 10. Let’s take a closer look at these problems.

Within an If structure, you can have an alternative path by using an Else statement. Let’s modify the code we just used slightly:

Sub ifTest()   Dim intNum As Integer   Dim strMessage As String   intNum = 9  If intNum > 10 Then   strMessage = "The number is greater than 10"   Else  strMessage = "The number is less than 10"   End If   Debug.Print strMessage End Sub

Notice that in the preceding code, we assigned new values to the variable intNum and then added an Else statement. Since the Else statement is part of the If statement, it does not need a separate End.

When you run it now, since intNum is less than 10, the Else statement will be triggered, giving you the results shown here:

Of course, as we did earlier, you could have used the input box to enter the number and the message box to output it.

AND, OR, NOT

In some cases, you may want to test multiple conditions. Is intNum < 1 OR intNum >= 10? The keywords AND, OR, and NOT are called logical operators and will help you to evaluate multiple conditions.

In the case of an AND logical operator, both conditional expressions must be true in order for the If statement to be true.

The following table shows the possible logical outcomes using an AND logical operator.

First Conditional Statement	Second Conditional Statement	Result
True	True	True
True	False	False
False	True	False
False	False	False

With an OR connector, only one of the statements needs to be true for the If statement to be true. The following table shows the logical outcomes of an OR logical operator.

First Conditional Statement	Second Conditional Statement	Result
True	True	True
True	False	True
False	True	True
False	False	False

Finally, the NOT logical operator returns the opposite of what you expect. If the conditional statement is true (A is NOT equal to B), Not (A is NOT equal to B) returns false. However, if A does equal B, the statement is false and the result is true. (Don’t worry if you need to take a few moments to figure that out.)

We are going to get fancy with the ifTest subroutine we have been using. We are going to use multiple procedures using multiple If/Else statements. We also want to restrict the user to a range of possible entries between 1 and 15.

Change your code so that it looks as follows:

Option Compare Database Option Explicit Private intNum As Integer ––––––––––––––––––––––––––––- Sub ifTest()   Dim strMessage As String   intNum = InputBox("Enter a number between 1 and 15", _   "Testing the If structure")   If intNum >= 1 And intNum <= 15 Then   iftest2   Else   MsgBox "Sorry, the number must be between 1 and 15"   End If End Sub –––––––––––––––––––––––––––––––––- Sub iftest2()   If intNum > 10 Then  MsgBox intNum & " is greater than 10"   Else   MsgBox intNum & " is less than 10"   End If End Sub

Notice that you have to redefine intNum as a global variable. We have not discussed this yet. However, recall from the beginning of the chapter that anything placed in the general declarations area will affect the whole module.

Subroutine ifTest tests intNum to see if it is between 1 and 15. If it is, it calls ifTest2, which contains the code we used earlier. However, if it does not, it returns a message to the user. The call to another procedure is known as a procedure call.

Go ahead and run the code a few times. If the number is between 1 and 15, you should get back the less-than or greater-than message, depending on the value you choose. If the number is not between 1 and 15, you should get the following message:

The ElseIf Structure

You can combine several If structures using ElseIf. As an example:

Sub ifTest()   Dim intNum as Integer   intNum = 12   If intNum = 1 Then  Debug.Print “This is the lowest number”   ElseIf intNum = 15 Then  Debug.Print “This is the highest number”   Else   Debug.Print “The number is between 1 and 15”   End If End Sub

You can use as many ElseIf statements as necessary to perform as many conditional tests as necessary.

Select Case Structure

If you find yourself using a lot of ElseIf structures, you may want to consider the Select Case structure. It will result in easier-to-read code. Using the ElseIf example in the previous section, you would use Select Case as follows:

Sub ifTest()   Dim intNum as Integer   intNum = 2   Select Case intNum   Case 1   Debug.Print "This is the lowest number"   Case 15   Debug.Print "This is the highest number"   Case Else   Debug.Print "The number is between 1 and 15"   End Select End Sub 

Of course you could add a case to match any situation. VBA will keep going through the structure until it finds a match with the value of intNum and then carry out the instructions. If it can’t make a match, it defaults to Case Else. The Case Else statement is optional, but I strongly recommend that you always use it to have all your bases covered.

If you have multiple cases in which you want to carry out the same set of instructions, you can use the syntax:

Case 1, 2, 3…

Or you could use

Case 1 To 4

IIF

There is one other structure that we will take a quick look at: IIF. This is referred to as the Immediate If. This is handy if you want to assign the final value to a variable because its syntax is self-contained. The correct syntax is

IIF(conditional test, value for True, value for False)

So, working code might look something like this:

strMessage = IIF(intNum > 10, “Number is greater than 10”, _ “Number is less than 10”)

The performance of the IIF structure is somewhat slow and rarely used by programmers in a larger programming project.

Now let’s turn our attention to the second type of control structure—looping.

Loops

You use loops when you need to have a block of code repeated a certain number of times or until an event of some sort happens. The number of times the code repeats can be controlled by a counter. This is called counter-controlled repetition. The second type is called sentinel-controlled repetition. We will look at both types.

For…Next Loop

The For…Next loop is an example of a counter-controlled repetition. Using either actual numbers, or variables, you can set the following components for the loop:

• Counter This is the heart of the loop. It tracks the number of times the loop has repeated.

• Start This is the starting number for the counter. It is rare, if ever, to set this to anything else but 1. (Occasionally you may use a different start number for mathematical calculations.)

• End This number marks the end of the loop, where you want the counter to stop.

• Step You can specify a number for the counter to increment with each loop. This part is optional.

Here is an example of the syntax for a For…Next loop:

Dim intCounter As Integer For intCounter = 1 To 25   ……. Next

To repeat, either the start or end numbers can be variables. Notice that the final line of the loop, unlike previous structures we have worked with, is not End, but Next. This instructs the counter to advance to the next number.

We also could have used the following syntax to declare the loop:

For intCounter = 1 To 25 Step 5

This forces the counter, in this case intCounter, to increment by five on every loop. As a result, the For loop will run five times.

It is not unusual to include If…Then structures within a loop. Let’s try the one that follows:

Sub forTest() Dim intCounter As Integer For intCounter = 1 To 10   If (intCounter Mod 2) = 0 Then   Debug.Print intCounter & " is an even number"   Else   Debug.Print intCounter & " is an odd number"   End If Next End Sub 

All right! I can hear my editor yelling at me that I snuck a couple of extra things in here. Let’s take a closer look at a few things in this code.

Here we see a For…Next loop that is instructed to make 10 passes. Within that, I wanted to test to see if a particular iteration was odd or even. I used an If…Then…Else structure to do that. Think about this: we have an If…Then…Else structure within a For…Next structure. When you have one structure within another, it is called a nested structure, which is very common in programming.

However, I asked the conditional statement to perform a calculation—specifically, division. I used the keyword Mod, which is short for modulus. Mod returns the remainder of the division (what is to the right of the decimal place). If you divide an even number by 2, the remainder is 0. So, if the counter is divided by 2 and there is a 0 remainder, it triggers the If condition. If the remainder is other than 0, the Else condition is triggered.

The Next just increments the counter.

If you run this subroutine in the Immediate window, you should see the result shown here:

Let’s now turn our attention to the other type of loop.

Do Loop

You use the Do loop as the sentinel-controlled loop. In other words, it will continue until a specific condition occurs (something equals something, or is greater than something, for example).

There are two variations of the Do loop—the Do While and the Do Until. We will look at each one.

Do While The Do While loop tests to see if a condition is true. If it is true, the statements in the loop execute. For instance, let’s look at the following subroutine:

Sub doTest()   Dim intCounter As Integer   Dim intTest As Integer   intTest = 1  intCounter = 1   Do While intTest = 1   Debug.Print "This is loop number " & intCounter   If intCounter >= 5 Then   intTest = 0   End If   intCounter = intCounter + 1   Loop End Sub

You will notice that the Do While loop is not as self-contained as the For…Next loop discussed earlier. In this case, you have to set up two variables, one for the counter and one for the conditional test. In this particular subroutine, I wanted the loop to end after the fifth pass.

Unlike the For loop from before, we are not running this based on a counter but, instead, until intTest changes to a value other than 1. I then nested an If…Then structure within the loop and tested the counter value. As soon as that value is equal to 5, the If structure will change the intTest value to 0 and will prevent the loop from running again.

Since it is testing for a condition rather than the number of iterations, you would not normally use a counter in a Do loop. In this case, I forced one with a second variable and incremented it as the last line of the loop. There are a variety of ways to do this.

Notice that this structure ends with the word Loop. Remember, we are not required to work with a counter here.

If you try running the Do While code, your Immediate window gives you these results:

However, here is a question: what happens if, for some reason, intTest never gets a value of 1? Will this Do loop ever run? (All right! Two questions!) The answer is no, it won’t. However, what happens if you need the loop to run at least once?

Sub ifTest()   Dim strMessage As String   intNum = InputBox("Enter a number between 1 and 15", _ "Testing the If structure")   If intNum >= 1 And intNum <= 15 Then  iftest2   Else    MsgBox "Sorry, the number must be between 1 and 15"   End If End Sub –––––––––––––––––––––––––––- Sub iftest2()   If intNum > 10 Then   MsgBox intNum & " is greater than 10"   Else   MsgBox intNum & " is less than 10"   End If End Sub 

Notice, in the ifTest subroutine, there is a test to see if the user has entered a number between 1 and 15. However, if the user enters a number out of bounds, the program just stops, and you need to start it up again. We want to keep prompting the user until he or she enters a number in the correct range.

Change ifTest to look as follows:

Sub ifTest()   Dim strMessage As String   Dim intTest As Integer   intTest = 1  Do   intNum = InputBox("Enter a number between 1 and 15",_ "Testing the If structure")   If intNum >= 1 And intNum <= 15 Then   intTest = 0   iftest2   Else   MsgBox "Sorry, the number must be between 1 and 15"   End If   Loop While intTest = 1 End Sub

Notice that we are not using a counter of any sort here. Instead, what we are doing is testing for intTest. However, what is interesting here is where the test occurs. Unlike the previous example, we are testing at the end of the loop rather than the beginning. This has the effect of forcing the loop to run at least once, which can be very handy in situations such as this.

Do Until This is a subtle variation of what we just looked at. In a Do While loop, you run the loop while a condition is true. However, in a Do Until loop, you run the loop until a condition becomes true.

Using the first example of the Do While loop above, with a few changes, you can make it into a Do Until loop:

Sub doTest()   Dim intCounter As Integer   Dim intTest As Integer   intTest = 1   intCounter = 1   Do Until intTest <> 1   Debug.Print "This is loop number " & intCounter   If intCounter >= 5 Then   intTest = 0   End If   intCounter = intCounter + 1   Loop End Sub 

Notice that here we are saying to run the loop until intTest does not equal 1. From that point on, everything else in this example is identical. You should see the same result as with the Do While loop.

Like the Do While loop, you can place the Until condition at the end of the structure after the word Loop. This forces the loop to run at least once.

We will be revisiting loop structures frequently throughout the book. Like variables, they are an integral part of most programming situations today.