Page 104 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

As stated earlier, a For…Next loop ensures that a specific number of repetitions are per-
formed. Suppose you had an array of 26 elements, and you wanted to set each one to its cor­
responding letter of the alphabet. A For…Next loop provides the perfect means to
accomplish this. The following code creates a 26-member array, assigns a letter of the alpha-
bet to each element, and then builds a message box to display those elements.

Sub AlphabetArray()
Dim strABC(1 To 26) as String
Dim intCounter as Integer
Dim strPrompt as String
For intCounter = 1 to 26
strABC(intCounter) = Chr$(intCounter + 64)
Next intCounter
strPrompt = "The strABC array contains the following values:" & vbCrLf
For intCounter = 1 to 26
strPrompt = strPrompt & strABC(intCounter)
Next intCounter
MsgBox strPrompt
End Sub
For…Next loops can be nested inside one another, so you can build even more complex iter­
ations. The following example modifies the previous example by building a two-dimensional
array and displaying the elements of the array backward:

Dim strABC(100 To 101, 1 To 26) As String
Dim intCounter1 As Integer, intCounter2 As Integer
Dim strPrompt As String
For intCounter1 = 100 To 101
For intCounter2 = 1 To 26
strABC(intCounter1, intCounter2) = Chr$(intCounter2 + 64)
Next intCounter2
Next intCounter1
Chapter 4
strPrompt = "The strABC array contains the following values:"
For intCounter1 = 100 To 101
strPrompt = strPrompt & vbCrLf & "Dimension" & Str$(intCounter1) & ": "
For intCounter2 = 26 To 1 Step -1
strPrompt = strPrompt & strABC(intCounter1, intCounter2)
Next
Next intCounter1
MsgBox strPrompt
The other iteration loop, the For Each…Next loop, is used with collections of objects or
arrays, ensuring that each member of the group is touched upon. It has syntax very similar to
the For…Next loop.

For Each element In group
[statements]
[Exit For]
[statements]
Next element

78
Part 2: Visual Basic for Applications