Lesson Objectives
Upon completion of this lesson, the participant will be able to:
Some Topics to be introduced in this lesson include:
Program Control Structures
Program control structures are used to shape the flow of code execution. There are three categories of control structures: branching (or decision), looping, and error. All structure types contain blocks of statements that perform tasks..
If...Then...Else
The If Then Else statement has two forms. The first is used when only a few statements are to execute based on whether the result is true or false. This type of If statement is commonly used when setting variables based on a condition.
Syntax 1:
If condition Then statements [Else elsestatements ]
Statements are separated by colons (:). The second form of the If statement is best used with larger statement blocks.
Syntax 2:
If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements]] . . .
[Else
[elsestatements]]
End If
The following example prompts the user for a number and if the number is less than zero, tells the user to enter a number greater than zero:
Sub IfThenElse()
Dim num as Integer
Num = Application.InputBox(prompt:="Enter a number, please", type:=1)
If num <= 0 Then
MsgBox "Please enter a number greater than zero"
Else
MsgBox num & " squared is " & num * num
End If
End Sub
The If Then Else branching structure is most effective in situations where there are conditions based on multiple variables. In situations where one variable has a large number of values, the Select Case statement is better suited.
Select Case
The Select Case statement executes a block of statements based on the value of an expression. VB examines the testexpression, and evaluates each of the expressionlists in turn, searching for a match. If it finds a match, it runs the code after the Case statement and stops when it reaches the next Case statement. If it finds no match, it will run the code after the Case Else statement. If more than one Case expression matches, VB will only evaluate the first one encountered and all others will be ignored.
Select Case testexpression
[Case expressionlist-n
[statements-n]] . . .
[Case Else
[elsestatements]]
End Select
The following example shows how the Select Case statement is used to display the name and telephone number of an employee in a message box based on the day of the week:
Sub AssignEmp()
Select Case Weekday(Now)
Case pjMonday , pjFriday
MsgBox "Bob Smith" & Chr(13) & "876-5432"
Case pjTuesday, pjThursday
MsgBox "Jane Johnson" & Chr(13) & "745-5844"
Case Else
MsgBox "Steve Simpson" & Chr(13) & "855-3846"
End Select
End Sub
For Next
The For Next loop executes a segment of code a specific number of times.
For counter = start To end [Step increment]
[statementblock]
Next [counter [,counter][,...]]
Do Loops
Do loops continue to execute a block of code until a certain condition is met. The condition can have one of two forms:
The Do loops have two different forms.
Syntax 1:
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop
The first form of the Do loop checks for the condition before executing any code. It is possible that the block of code does not execute at all if the condition is not met on the first pass.
Syntax 2:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until}condition]
The second form of the Do loop always executes the code at least once because it checks for the condition at the end of the loop.
This example shows how Do...Loop statements can be used. The inner Do...Loop statement loops 10 times, sets the value of the flag to False, and exits prematurely using the Exit Do statement (see next section). The outer loop exits immediately upon checking the value of the flag:
Check = True: Counter = 0 ' Initialize variables.
Do ' Outer Loop.
Do While Counter < 20 ' Inner Loop.
Counter = Counter + 1 ' Increment Counter.
If Counter = 10 Then ' If condition is true.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
While Wend
The While Wend statement is equivalent to the Do While statement in VB. (It is provided for backwards compatibility with earlier versions of Basic and should not be used. The Do...Loop statement provides a more structured and flexible way to perform looping.) It executes a series of statements as long as a given condition is True.
While condition
[statements]
Wend
Exit
The Exit statement is used to break out of a looping structure. When a Exit Do, Exit For, Exit While or Exit For Each is encountered within a loop, execution of the macro proceeds to the line following the Next, Wend or Loop statement for the current loop.
Unconditional Branching
Branching structures that unconditionally branch to other parts of the macro are less desirable than other structures because they make the macro difficult to read and debug. However, there are occasional needs for them (i.e., error checking) and they are listed below.
Goto
The Goto statement branches unconditionally to the specified line or label in the current procedure.
GoTo line
GoSub
The GoSub statement branches unconditionally to the specified line in the current procedure. When the program encounters a Return statement, it returns to the line following the GoSub statement.
GoSub line
line:
Return
On Goto, On GoSub
On Goto and On GoSub branch to a number of different labels or line numbers based on the value of the expression (a number between 1 and 255). The difference between the two is that On GoSub returns to the line following the statement when it encounters a Return statement, while On Goto does not.
On expression Goto destination
On expression GoSub destination
Nesting Control Structures
It is legal to nest control structures within other control structures. It is important to remember to close interior structures before closing external structures. It is possible to close more than one structure with the same Next statement (e.g., Next X, Y, Z). They will move from the inner most loop to the outer most.
Create a 2 by 10 array (ArrayName(1 to 2, 1 to 10)).
Using a Do Loop, If statements and For Next loops, place unique random numbers between 1 and 10 in the first column of the array (ArrayName(1, 1-10). (The array should contain all the numbers between 1 and 10, but in a random order).
Using
a For Next and a Select Case, assign one of these
three strings to the second column (ArrayName(2,
1-10)) based on the value in the first:
If less than 5 then "Smaller"
If equal to 5 then "Equal"
If greater than 5 then "Bigger"
Using a For Next loop, display in a message box the number and the corresponding string for each element where the random number is even.
Sub GenerateArrayValues()
Dim MyArray(1 To 2, 1 To 10)'Array declaration
Dim Counter As Integer 'Counts the number of times a value is assigned to the array
Dim RandNum As Integer 'Stores the random number
Dim Used As Boolean 'Indicates if number has already been assigned to the array
Dim i As Integer 'Used as a loop counter in various areas of the procedure
Counter = 0
Randomize 'Tells VB to use system timer for seed value when generating
' random numbers
' This part of the procedure generates the random numbers and assigns
' them to the array
Do While Counter < 10 'Loop until all values are assigned
Used = False
RandNum = Int((10 - 1 + 1) * Rnd + 1) 'Generates random number between 1-10 (see Help)
If Counter = 0 Then 'If first time, assigns number to first element
MyArray(1, 1) = RandNum ' and increments counter
Counter = Counter + 1
Else 'If it's not the first time through the loop
For i = 1 To Counter 'For every number in the array
If RandNum = MyArray(1, i) Then 'Compare new number to numbers already assigned
Used = True 'If found, then set the flag and exit the loop
Exit For
End If
Next i
If Used = False Then 'If the number does not exist in the array
MyArray(1, Counter + 1) = RandNum ' then assign it to the next element
Counter = Counter + 1 ' and increment the counter
End If
End If
Loop
' This part of the procedure assigns the strings to the array based
' on the value
For i = 1 To 10 'For each number in the array
Select Case MyArray(1, i) ' compare the value and assign the appropriate
Case Is < 5 ' string value
MyArray(2, i) = "Smaller"
Case Is = 5
MyArray(2, i) = "Equal"
Case Else
MyArray(2, i) = "Bigger"
End Select
Next i
' The part of the procedure displays the values that are even
For i = 1 To 10 'For each number array, see if the remainder
If MyArray(1, i) Mod 2 = 0 Then ' is zero when divided by two (= even number)
MsgBox "Number = " & MyArray(1, i) & " String = " & MyArray(2, i)
End If
Next i
End Sub