Tuesday, March 15, 2005

More on arrays in VBA

An array can be declared using either the Dim or Public statement. To show that the variable is an array () are placed at the end of the name. Eg MyArray()
If you don't know how big the array needs to be for example when working with a table of a variable size then there are some simple ways to determine the size and then the Redim command is used to redimension or resize the array. Eg Redim MyArray(3,5).

To determine the size of a variable array I would use something similar to the following code most times.
The reference "array_start" is a named range in the file at the upper left most cell of the range to be selected for the array. This also assumes that there are no empty cells in the data.

Dim MyArray()
Dim lowercol, uppercol, lowerrow, upperrow As Single
Sub test_array()
Application.Goto reference:="array_start"
lowercol = ActiveCell.Column
uppercol = ActiveCell.End(xlToRight).Column
lowerrow = ActiveCell.Row
upperrow = ActiveCell.End(xlDown).Row
Range(ActiveCell, ActiveCell.Offset(upperrow - lowerrow, uppercol - lowercol)).Select
ReDim MyArray(upperrow - lowerrow, uppercol - lowercol)
MyArray() = Selection
End Sub


Posted by: Jethro | 1:43 AM |