Word Document Properties

Listing 9-3
Creating a Form to Interact with a Two-Dimensional Array
Private Sub btnOK_Click()
‘ Change the click type.
ArrayTypes.ClickType = vbOK
‘ Check the two input values.
txtInput1_Change
txtInput2_Change
‘ End the form.
Me.Hide
End Sub
Private Sub txtInput1_Change()
‘ Verify the user has input a number greater than 1.
If Val(txtInput1.Text) = 0 Then
‘ If not, display an error message box.
MsgBox “Type a numeric value greater than 1.”
‘ Return the text to an acceptable value.
txtInput1.Text = “5”
Else
‘ Otherwise, store the numeric value.
ArrayTypes.Input1Value = CInt(txtInput1.Text)
End If
End Sub
The 
txtInput1_Change
sub-procedure monitors any change to the
txtInput1
control. (The 
txtInput2
control has a similar event handler
210
Part III: Expanding Your VBA Horizons 
15_046500 ch09.qxp 12/5/06 5:36 PM Page 210

---

associated with it.) The 
If...Then
statement verifies that you have input a
number by using the 
Val
function to compare the number with 0. You can’t
use the 
CInt
function because it generates a type mismatch error when you
type a letter or special character. Typing an incorrect character generates an
error message and returns the value to something correct. A correct entry
places the integer value into the 
Input1Value
variable in the 
ArrayTypes
module (the one with the sub-procedure).
The 
btnOK_Click
sub-procedure sets the 
ClickType
variable in the
ArrayTypes
module to 
vbOK
. Likewise, clicking Cancel sets the 
ClickType
variable to 
vbCancel
. The code then calls 
Me.Hide
. The special keyword 
Me
refers to the current object. The 
Hide
method removes the form from sight
but not from memory.
When you want to remove an object from memory, you use the 
Unload
method and supply the object name. This method works only if VBA is done
using the object. Likewise, when you want to load an object into memory
(but not display it), use the 
Load
method. This method works only when VBA
has memory and other resources available to load the object.
When the 
GetArrayDimensions UserForm
completes (you click OK or
Cancel), control returns to the 
TwoDimension
sub-procedure. The code
checks the 
ClickType
value. Click Cancel, and the sub-procedure exits.
At this point, the code has the information needed to 
dimension
the array
(that is, make it a certain size), so it uses the 
ReDim
statement to change the
CalcResult
dimensions. Changing the dimensions erases the content of the
array unless you include the 
Preserve
keyword. A double loop serves to
address the two dimensions of the 
CalcResult
array. The calculation is
simple multiplication, but you can perform any task in the loop.
After the array is filled with data, it’s time to create an output string. The
code uses simple assignment to create a heading, generates the row heading
using a single loop, and then uses a double loop to create the output informa-
tion. The final statement displays an output message box containing a table
of the information.
Copying data from one array to another
You might need to copy data from one array to another. For example, you can
base a new array on the content of an existing array. It’s also safer to make
changes to a copy of an array rather than to change the original and poten-
tially damage the data. Listing 9-4 shows an example of code that you can use
to copy one array to another. (You can find the source code for this example
on the Dummies.com site at 
http://www.dummies.com/go/vbafd5e
.)
211