Note:
Dynamic named ranges can only be referenced in an open workbook, because they are dependent upon a workbook's ability to calculate.
Luôn tiện các bạn đọc luôn bài sau
Dynamic Arrays
Rodney Powell
Microsoft MVP - Excel need a developer for
your excel project ?
go here
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.
