XL: How to Use Dynamic Arrays in a Macro

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel For Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0

SUMMARY

In Microsoft Excel, an array can be declared to be dynamic so that the number of elements and dimensions can be changed later while the code is running.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   ' Static must be used at the procedural level.
   Static MyArray1() As String

   ' Dim may be used at the module or procedural levels.
   Dim MyArray2() As Integer

   ' Private and Public must be used at the module level.
   Private MyArray3() As Single
   Public MyArray4() As Double

   ' Declare the array using Dim.
   Dim MyArray1() As String

   ' ReDim used to change the number of elements.
   ReDim MyArray1(10)

   ' ReDim used to change the number of elements and dimensions.
   ReDim MyArray1(15, 35)

   ' ReDim used to change the lower and upper bounds of the array
   ' dimensions.
   ReDim MyArray1(5 to 10, 10 to 30)

   ' Using variables to indicate the new sizes of an array's dimensions.
   ReDim MyArray1(X, Y)

   ' Using ReDim to change the type of the elements in an array
   ' contained in a variant.
   Dim MyArray as Variant
   ReDim MyArray(10, 20) as String

   ' Change the size of the array but maintain the current values that
   ' are stored in the array.
   Dim MyArray1(10, 20)

   ' Note only the last dimension can be changed when using Preserve.
   ReDim Preserve MyArray1(10, 30)

The only exception to this situation is that the lower bound of the last array element can be changed if the array is contained in a Variant variable, for example, when an array is declared as a variant variable. The ReDim statement is used to resize the array to one dimension with a lower bound of 1 and an upper bound of 20. The array is then filled with data. Again, the ReDim statement is used with the Preserve keyword to resize the array so that the lower bound is 5 and the upper bound is 34. Because the Preserve keyword was used, the data has been preserved and the subscripts for the elements of the array have been remapped to the original data. The following macro illustrates this operation (you will need a worksheet named Sheet1 that is blank and in the same workbook as the macro).

   Sub UsingReDim()

       ' Declare a Variant variable called MyArray.
       Dim MyArray As Variant

       ' Declare looping variable.
       Dim Counter As Integer

       ' Redimension MyArray for 20 elements as type Integer.
       ReDim MyArray(1 To 20) As Integer

       ' Loop 20 times.
       For Counter = 1 To 20

           ' Fill the elements of the array with the values 1 though 20.
           MyArray(Counter) = Counter
           ' Place values of MyArray in column A of worksheet
           ' on the row corresponding to the subscript.
           Worksheets("Sheet1").Cells(Counter, 1).Value = MyArray(Counter)
       Next Counter

       ' Redimension MyArray for 30 elements,
       ' change the lower and upper bounds, and
       ' retain the current values stored in the array
       ReDim Preserve MyArray(5 To 34) As Integer

       ' Loop 30 times.
       For Counter = 5 To 34

           ' Place values of MyArray in column B of worksheet
           ' on the row corresponding to the subscript.
           Worksheets("Sheet1").Cells(Counter, 2).Value = MyArray(Counter)
       Next Counter

   End Sub

REFERENCES

For more information about declaring resizable arrays in Visual Basic for Applications and the ReDim Statement, click Answer Wizard on the Help menu and type:

   tell me about the redim statement