MS-Excel / General Formatting

---

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

The ReDim statement reinitializes the array so that any values stored in the array are lost. If you want to preserve an array's existing values, use ReDim with the Preserve option, as follows:

ReDim Preserve myArray(52)

Listing below presents a more concrete example. (Note that this procedure uses lots of VBA code that you haven't seen yet, so don't be discouraged if you don't fully understand what's happening here.)

A Procedure That Stores the Names of the Worksheets in a Dynamic Array

Sub StoreWorksheetNames()
    Dim sheetNames() As String
    Dim totalSheets As Integer
    Dim sheet As Worksheet
    Dim i As Integer
    Dim strMessage As String
    '
    ' Store the total number of worksheets
    ' that are in the current workbook
    '
    totalSheets = ActiveWorkbook.Worksheets.Count
    '
    ' Now redimension the dynamic array
    '
    ReDim sheetNames(totalSheets)
    '
    ' Loop through the worksheets to store the names in the array
    For i = 1 To totalSheets
    sheetNames(i - 1) = ActiveWorkbook.Worksheets(i).Name
    Next 'i
    '
    ' Loop through the array to add the names to a string
    '
    strMessage = "Here are the worksheet names:" & vbCrLf
    For i = 0 To totalSheets - 1
    strMessage = strMessage & sheetNames(i) & vbCrLf
    Next 'i
    '
    ' Display the worksheet names
    '
    MsgBox strMessage
End Sub

This procedure begins by declaring sheetNames as a dynamic array. It then uses the totalSheets variable to store the total number of worksheets that are in the current workbook. The procedure then sets the size of the array based on the totalSheets value:

ReDim sheetNames(totalSheets)

The procedure then uses one loop to store the worksheet names in the array and a second loop to add the worksheet names to the strMessage variable, which is a String value. Finally, the procedure uses the MsgBox function to display the string.

If your program needs to know the lower bound and the upper bound of an array,VBA provides a couple of functions that can do the job:

LBound(arrayName)   Returns the lower bound of the array given by arrayName.
UBound(arrayName)   Returns the upper bound of the array given by arrayName.