Programming Fundamentals Using VBA - Control Structures

(Page 5 of 9 )

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:

 

Next: The ElseIf Structure >>
 

More Visual Basic.NET Articles
More By McGraw-Hill/Osborne

Comments On: Programming Fundamentals Using VBA

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.