The information in this article applies to:
SUMMARYIn 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 INFORMATIONMicrosoft 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 professionals 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 a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/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/overview/overview.asp If the size of an array is not known during declaration, you can declare the array to be dynamic. To do this, use a Static, Dim, Private, or Public statement to declare the array and leave the parentheses empty. The following examples are statements you can use to declare a dynamic array:
After an array is declared in this fashion, you can use the ReDim statement to change the number of elements and dimensions. If the array is contained in a Variant variable, you can also change the type of the array elements using the As clause. In order to change the size of the array contained in a variant, the Variant variable must be explicitly declared first. The following are examples of using the ReDim statement:
Each time the ReDim statement is used, the values stored in the array are lost. To retain the existing data, you can use the Preserve keyword with the ReDim statement, as in the following examples:
When the Preserve keyword is used, you can change only the upper bound of the last array dimension. If you make the size of an array smaller than the number of data elements currently stored in the array, the excess data will 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 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).
When the macro is run, Sheet1 will contain the values before and after ReDim is used. Note that the excess elements have already been initialized to zero. REFERENCESFor more information about the ReDim statement, in the Visual Basic Editor, click
Microsoft Visual Basic Help on the Help menu, type "ReDim Statement" in
the Office Assistant or the Answer Wizard, and then click Search to
view the topic. Additional query words: XL2000
Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA |
Last Reviewed: July 6, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |