Word Document Properties

Listing 9-2
Creating and Using a Two-Dimensional Array
‘ Define some data exchange values for the
‘ GetArrayDimensions form.
Public Input1Value As Integer
Public Input2Value As Integer
Public ClickType As VbMsgBoxResult
Public Sub TwoDimension()
‘ Create an array to hold the calculation results.
Dim CalcResult() As Integer
208
Part III: Expanding Your VBA Horizons 
15_046500 ch09.qxp 12/5/06 5:36 PM Page 208

---

‘ Create some loop variables for the calculation.
Dim Loop1 As Integer
Dim Loop2 As Integer
‘ Create an output string for the display.
Dim Output As String
‘ Display a form to obtain the array dimensions.
GetArrayDimensions.Show
‘ Determine which button the user clicked.
If ClickType = vbCancel Then
‘ If the user clicked Cancel, exit.
Exit Sub
End If
‘ Redimension the array.
ReDim CalcResult(Input1Value, Input2Value)
‘ Perform the calculation.
For Loop1 = 1 To Input1Value
For Loop2 = 1 To Input2Value
CalcResult(Loop1, Loop2) = Loop1 * Loop2
Next
Next
‘ Create a heading.
Output = “Calculation Results” + vbCrLf + _
“In Tabular Format” + vbCrLf + vbCrLf
‘ Define the column heading values.
For Loop1 = 1 To Input2Value
Output = Output + vbTab + CStr(Loop1)
Next
‘ Define the rows.
For Loop1 = 1 To Input1Value
Output = Output + vbCrLf + CStr(Loop1)
For Loop2 = 1 To Input2Value
Output = Output + vbTab + _
CStr(CalcResult(Loop1, Loop2))
Next
Next
‘ Create a message box to show the result.
MsgBox Output, vbInformation Or vbOKOnly, “Results”
End Sub
209
Chapter 9: Working with Arrays and Collections
15_046500 ch09.qxp 12/5/06 5:36 PM Page 209

---

The program declares three public variables. These variables hold the results
from the dialog box created to ask you how many values to compute. This
technique is regularly used to get information.
The 
TwoDimension
sub-procedure begins by declaring some variables.
Notice that it doesn’t define the number of elements in 
CalcResult
— the
code only tells VBA that it’s an array.
The code displays a dialog box containing one input text box for each dimen-
sion using the 
GetArrayDimensions.Show
method. The program uses this
dialog box to get the array dimensions.
This dialog box has several interesting features to consider. First, unlike the
standalone dialog boxes in Chapter 7, this dialog box interacts with a sub-
procedure. Consequently, you can’t simply end the dialog box — the code
within the dialog box has to report back to the calling sub-procedure in some
way. This is the reason for creating the global 
Input1Value
, 
Input2Value
,
and 
ClickType
variables. Listing 9-3 shows the essential code from the Two-
Dimensional Array Dimensions dialog box.