|
Dynamic Arrays
Rodney POWELL
Microsoft MVP - Excel |
|
Note: Some readers may find it useful to review the previous
article describing arrays.
Sometimes you won't know the specific size of an array and will need
the ability to change the number of elements contained at run time. A
dynamic array is a flexible type of array that you can resize at
run time. Besides that dynamic arrays are a convenient feature in VBA,
the importance of using dynamic arrays is memory management.
With dynamic arrays you can be sure that your operation conserves demands
on memory to no more than is actually required to meet the immediate need.
Since a dynamic array doesn't have a preset number of elements, you will
declare it with an empty set of parentheses.
Dim aMyArray()
Notice that the declaration of the array
aMyArray does not specify the
number of elements it contains. The statement above declares a
dynamic array, however it does not allocate any memory to the array. It
will not be until you use the ReDim statement to change the array
for the appropriate number of elements that memory is allocated for the
array. The ReDim statement can appear only in a procedure. It can change
the number of elements, as well as the lower and upper bounds.
Dim aMyArray()
ReDim aMyArray(7)
You can redimension an array programmatically as many times as necessary.
However, when an array is redimensioned, all it's elements are lost. VBA
resets the values to Empty for variant arrays, to zero for
numeric arrays, to a zero-length string for text arrays, and to
Nothing for arrays of objects.
If you want the array maintain the assigned values for all elements already
within your array, you can include the optional Preserve
keyword with the ReDim statement. If you redimension an array to a
size smaller than it was previously, values outside of the new upper bound
(and lower bound) of the array will be lost.
ReDim Preserve aMyArray(1 to 7)
With dynamic arrays you can manage memory efficiently. You can use a large
array for a short time and then free memory to the system when you're no
longer using the array. To accomplish this, you use the Erase
statement to reinitialize the elements of a dynamic array to wipe out the
data that it holds and recover all of the memory assigned to the array.
Here is an example of how to reclaim the memory allocated to the array:
Erase aMyArray
Let's wrap this up with a code example that may help to further demonstrate
how dynamic arrays work. Using the Forms toolbar, place three CheckBox
controls on a worksheet (Sheet1). Make the captions for those checkboxes:
"Florida", "Texas", and "Virginia".
Next insert a module and copy in the following VBA code.
Option Explicit
Option Base 1
Sub DynamicArrayDemo()
Dim cbx As CheckBox, aState$(), a&, b&
' Loop through all of the CheckBox Objects on Sheet1
For Each cbx In Worksheets("Sheet1").CheckBoxes
' If the Checkbox was selected then ...
If cbx.Value = xlOn Then
' Increment a counter
a& = a& + 1
' Expand the aState$ dynamic array to add
' another element
ReDim Preserve aState$(1 To a&)
' Initialize the new element of the array
' with the caption of the selected CheckBox
aState$(a&) = cbx.Caption
End If
Next cbx
' If there were any CheckBoxes selected, then ...
If a& > 0 Then
' Loop through the elements in the aState$
' dynamic array
For b& = 1 To a&
' Display the contents of each array
' element in a message box
MsgBox aState$(b&)
Next b&
End If
End Sub
You can run this code and experiment with checking different controls
on your worksheet to improve your grasp of how dynamic arrays are applied.
Know an even better way?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
