Structures Used in VB Code

In this section, we’ll briefly discuss some of the programming structures that you can use in Visual Basic code. These structures fall into three major categories:

• Sequential structures, in which one command follows another and all commands are executed.

• Conditional structures, in which a decision is made to execute one set of commands based on the value of a variable.

• Looping structures, in which one or more commands are repeated.

Sequential Structures

Sequential structures are the simplest structures. The procedure executes each command, in order, until it hits the End Sub command. The sample statements that have appeared in this chapter as well as the macros we created in Chapter 24 used a sequential structure.

Using Conditional Structures

Conditional structures are used to direct program flow based on one or more conditions. There are two major types of conditional structures: If (in a variety of flavors) and Select Case.

If Statements

VBA supports three types of If statements:

• If/Then

• If/Then/Else

• If/Then, ElseIf/Else

VBA also supports the IIF (immediate if) statement used in Microsoft Access.

The If/Then Statement

The If/Then statement is the simplest If statement, and is the same statement used in Excel, Access, and a variety of programming languages. Here’s the syntax of the If/Then statement:

If condition Then   (commands executed when condition = True) End If

The condition is a test constructed using operators and variables or constants. If the condition is true, the lines of code between the If and End If statements is executed once. If the condition is false, VB skips to the command line that follows the End If statement.

If/Then/Else Statements

With the If/Then/Else statement, you can execute one of two sections of code, depending on the condition. The syntax is as follows:

If condition Then   (commands to be executed when condition = True) Else   (commands to be executed when condition = False) End If

If/ThenÖElseIf/Else Statements

The third type of If statement is the If/Then...ElseIf/Else statement. This structure lets you test more than one condition. The syntax for the basic If/Then...ElseIf/Else statement is as follows:

If condition1 Then   (commands to be executed when condition1 = True) ElseIf condition2 Then   (commands to be executed when condition2 = True) Else   (commands to be executed when neither condition = True) End If 

With this version of If, VB executes the first group of commands if the first condition is true. If the first condition is false, it checks to see whether the second condition is true. If it is, it executes the second group of commands. You don’t have to stop here. You can use ElseIf again and test a third condition, a fourth condition, and so on. If none of the conditions is true, VB executes the commands following the Else statement and prior to the End If.

As soon VB tests a condition that is True, it executes the commands following the condition; then skips to the command following the End If statement. No other conditions are tested.

Select Case Statements

Select Case is much like If/Then...ElseIf/Else, and is used to execute one or more commands based on one value. Here’s the syntax for Select Case:

Select Case expression   Case expression1     [commands]   Case expression2     [commands]   Case Else     [commands] End Select

The Select Case structure begins with a Select Case statement, and ends with an End Select statement. Expression is a value or phrase used to determine which case gets executed. VB evaluates each Case statement in turn. As soon as it finds a matching case, it executes the commands for that case and then skips to the command line following the End Select statement.

Looping Structures

VBA supports a bevy of looping structures used to repeat a set of commands. The two families of looping structures are For loops and Do loops. For loops execute a series of commands a set number of times based on the value of a counter that’s incremented by VB. Do loops rely on a condition, and check to see whether it’s true or still true before the commands in the loop are executed. We’ll discuss four different looping structures commonly used in VBA:

• For/Next

• Do While

• Do/Loop While

• Do Until

For/Next Loops

A For/Next loop repeats for a specified number of times, controlled by a counter variable. The syntax for a For/Next loop is:

For counter = start To end [optional Step stepvalue]    (commands to be executed if counter < end) Next counter 

The counter is a numeric variable. When VB hits the For line, it checks the value of the counter and then compares it to the End value:

• If the current value of counter is less than or equal to the end value, VBA executes the statements in the loop. When it hits the Next line, it increments the counter and loops back to the For command.

• If the current value of counter is more than the end value, VBA skips to the command line following the Next command.

By default, the counter value is incremented by 1. To have VB increment by a different positive or negative value, include the optional Step keyword followed by the increment you want to use:

For intTaskCount = 10 to 50 Step 5

If the Step increment is a negative number, VB subtracts the increment from the counter, and continues executing the commands in the loop until the counter value is less than the end value:

For intTaskCount = 50 to 10 Step -5

In the following sections, we’ll look at each of the four types of Do loops:

Do While... Loop Do... Loop While Do Until... Loop Do... Loop Until

Do While Loops

The Do While loop tests a condition, and executes the commands in the loop as long as the condition is true:

Do While condition    (commands executed if the condition is true) Loop

The first time VB hits the Do While command, it evaluates the condition. If the condition is true, VB executes the commands in the loop. The Loop command returns execution to the Do While line, where VB evaluates the condition again.

Do/Loop While Loops

With Do While, if the condition is false the first time through, the commands between Do While and Loop are never executed. If you need the commands to execute at least once, use the Do Loop While command structure:

Do    (commands executed if condition is true) Loop While condition 

With this structure, the condition isn’t tested until the commands in the loop have been executed once.

Do Until Loops

The Do Until structure is the opposite of the Do While structure. With Do Until, commands in the loop are executed until the condition is met:

Do Until condition   (commands to be executed if condition is not true) Loop

Note 

There’s also a Do/Until loop structure that tests the condition after the first time through the loop.