Dynamic Arrays

What do you do if you're not sure how many subscripts you'll need in an array? You could guess at the correct number, but that will almost always leave you with one of the following problems:

■ If you guess too low and try to access a subscript higher than the array's upper bound, VBA will generate an error message.

■ If you guess too high, VBA will still allocate memory to the unused portions of the array, so you'll waste precious system resources.

To avoid both of these problems, you can declare a dynamic array by leaving the parentheses blank in the Dim statement: Dim myArray() As Double

Then, when you know the number of elements you need, you can use a ReDim statement to allocate the correct number of subscripts (notice that you don't specify a data type in the ReDim statement):

ReDim myArray(52)

The following is a partial listing of a procedure named PerformCalculations. The procedure declares calcValues as a dynamic array and totalValues as an integer. Later in the procedure, totalValues is set to the result of a function procedure named GetTotalValues. The ReDim statement then uses totalValues to allocate the appropriate number of subscripts to the calcValues array.

Sub PerformCalculations()

Dim calcValues() As Double, totalValues as Integer totalValues = GetTotalValues() ReDim calcValues(totalValues)

End Sub

0 0

Post a comment