You are currently offline, waiting for your internet to reconnect

How to Use Dynamic Arrays in a Macro

This article was previously published under Q142134
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel, an array can be declared to be dynamic so that thenumber of elements and dimensions can be changed later while the code isrunning.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. If the size of an array is not known during declaration, you can declarethe array to be dynamic. To do this, use a Static, Dim, Private, or Publicstatement to declare the array and leave the parentheses empty. Thefollowing examples are statements you can use to declare a dynamic array:
      ' 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				
After an array is declared in this fashion, you can use the ReDimstatement to change the number of elements and dimensions. If the array iscontained in a Variant variable, you can also change the type of the arrayelements using the As clause. In order to change the size of the arraycontained in a variant, the Variant variable must be explicitly declaredfirst. The following are examples of using the ReDim statement:
   ' 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				
Each time the ReDim statement is used, the values stored in the array arelost. To retain the existing data, you can use the Preserve keyword withthe ReDim statement, as in the following examples:
   ' 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)				
When the Preserve keyword is used, you can change only the upper bound ofthe last array dimension. If you make the size of an array smaller thanthe number of data elements currently stored in the array, the excess datawill be lost. The number of dimensions in the array cannot be changed.

The only exception to this situation is that the lower bound of the lastarray element can be changed if the array is contained in a Variantvariable, for example, when an array is declared as a variant variable.The ReDim statement is used to resize the array to one dimension with alower bound of 1 and an upper bound of 20. The array is then filled withdata.

Again, the ReDim statement is used with the Preserve keyword to resize thearray so that the lower bound is 5 and the upper bound is 34. Because thePreserve keyword was used, the data has been preserved and the subscriptsfor the elements of the array have been remapped to the original data. Thefollowing macro illustrates this operation (you will need a worksheetnamed 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				
When the macro is run, Sheet1 will contain the values before and afterthe ReDim is used. Note that the excess elements have already beeninitialized to zero.
For more information about declaring resizable arrays in Visual Basicfor Applications and the ReDim Statement, click Answer Wizard on theHelp menu and type:
tell me about the redim statement
5.00a 5.00c 8.00 XL97 XL98 XL7 XL5 XL

Article ID: 142134 - Last Review: 08/17/2005 18:15:34 - Revision: 2.1

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbdtacode kbhowto kbprogramming KB142134