Page 110 - Excel Progamming Weekend Crash Course

h540629 ch06.qxd 9/2/03 9:33 AM Page 85

Session 6 — Control Constructs 85

The following example uses For Each...Next to print all the worksheets in the active
workbook:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PrintOut
Next

To loop through an array, the syntax is similar:
For Each var in array
...
Next
In this case, n is a variable of the same data type as array, or type Variant. The follow-
ing code calculates the sum of all elements in the array (assuming MyArray is type Single):
Dim var As Single
Dim total As Single
total = 0
For Each var In MyArray
total = total + var
Next
Note that you could perform the same task using a For...Next loop and the LBound and
UBound functions, but For Each...Next permits somewhat easier and simpler programming.
As with For...Next, you can use the Exit For statement to exit a For Each...Next
loop before all of the elements of the collection or array are processed.

The Goto Statement
The Goto statement instructs program execution to go to the specified location in the code.
The syntax is:
Goto label

Label is a location identifier, consisting of a label followed by a colon on a line by itself
in the code. Goto is limited to branching within a procedure — that is, both the Goto state-
ment, and its target label must be within the same procedure. Here’s an example:
Dim val As Integer
val = InputBox(“Enter an even number:”)
If val mod 2 <> 0 Then Goto NotEven
MsgBox(“Thank You!”)
Goto Finished
NotEven:
MsgBox(“That’s not an even number, pal!”)
Finished:
‘ More code

A label itself has no effect on program operation — it serves only as the target of a Goto.