Microsoft KB Archive/843145

= Description of arrays in Sub procedures in Visual Basic for Applications =

Article ID: 843145

Article Last Modified on 2/13/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-





SUMMARY
''The article contains information about arrays in Sub procedures in Microsoft Visual Basic for Applications. The following three kinds of array are discussed in this article:''
 * General arrays. A general array is a single variable that is declared in such a way that it can store multiple values of the same data type.
 * Multidimensional arrays. Multidimensional arrays can have up to 60 dimensions in Visual Basic for Applications.
 * Dynamic arrays. The dimensions of dynamic arrays can be increased or decreased.

''The article uses samples to demonstrate each kind of array. Additionally, this article contains an example of how to populate an array with worksheet data.''



IN THIS TASK

 * INTRODUCTION
 * General information about Sub procedures
 * More Information
 * General arrays
 * Multidimensional arrays
 * Dynamic arrays
 * Populating an array with worksheet data
 * References



INTRODUCTION
This article describes arrays in Sub procedures in Visual Basic for Applications. This article describes these three types of arrays: general arrays, multidimensional arrays, and dynamic arrays. This article also provides some samples of each type of array and an example of how to populate an array with worksheet data.

General information about Sub procedures
A Sub procedure is a series of Visual Basic statements enclosed by the Sub statement and the End Sub statement. The Sub procedure performs actions, but the Sub procedure does not return a value. A Sub procedure can take arguments that are passed by a calling procedure. Examples of such arguments are constants, variables, and expressions. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses

Note Throughout this document, the term Sub procedure is used synonymously with the term macro.

Each macro has the following structure:

Sub MacroName lines of macro code End Sub

The following are the rules for names of Sub procedures:
 * You must use a letter as the first character.
 * You cannot use a name that is longer than 255 characters.
 * You cannot use a space, a period, an exclamation point, an at sign, an ampersand, a dollar sign, or a number sign in the name.
 * You cannot use restricted keywords.

Note To view a complete list of restricted keywords, click Contents and Index on the Help menu in the Visual Basic Editor, and then type keywords.

You can have two macros in the same project with the same name, but you must follow these rules:
 * The two macros with the same name must be in different modules.
 * If there are two macros in the same project that have the same name, you must put the module name in front of the macro name to call a macro.

For example, to call the Test macro in a module that is named Module2, use the following syntax:

Module2.Test



General arrays
An array is a single variable that is declared in such a way that it can store multiple values of the same data type. If you want to access any item in the array variable, you must provide the index number of the element and the variable name.

Declaring an array variable
Arrays are declared like non-array variables, by using the Dim statement, the Static statement, the Private statement, and the Public statement. Non-array variables are also known as scalar variables. The difference between array variables and scalar variables is that you must specify the size of the array variable when you declare it. The size of the array tells Visual Basic for Applications how many elements can be stored in the array. However, you can create an array that changes size during program execution. An array that changes size during program execution is referred to as a dynamic array.

Declaring a fixed array
When you declare an array, you must specify the name of the array, the size of the array, and the data type that is stored in the array. The following is the default syntax for declaring an array:

Dim (# of elements - 1) As  rows and   columns. If you use the first syntax, the number of elements in each dimension will vary according to the Option Base setting.

If you use the following example declaration for an array, you create a three-row-by-two-column array that is similar to the range A1:B3 on a Microsoft Excel worksheet:

Dim MyArray(1 to 3, 1 to 2) As Integer

Try this exercise:  Start Excel, and then open a new workbook. Start the Visual Basic Editor, and then insert a new module into your project.  Type the following code into this module:

Option Base 1 Sub Multi_Array 'Create a 3-row-by-2-column array. Dim MyArray(3, 2) As Integer MyArray(1,1) = 11   'first row, first column MyArray(1,2) = 12   'first row, second column MyArray(2,1) = 21   'second row, first column MyArray(2,2) = 22   'second row, second column MyArray(3,1) = 31   'third row, first column MyArray(3,2) = 32   'third row, second column Sheet1.Range(&quot;A1&quot;).Value = MyArray(1,1) Sheet1.Range(&quot;B1&quot;).Value = MyArray(1,2) Sheet1.Range(&quot;A2&quot;).Value = MyArray(2,1) Sheet1.Range(&quot;B2&quot;).Value = MyArray(2,2) Sheet1.Range(&quot;A3&quot;).Value = MyArray(3,1) Sheet1.Range(&quot;B3&quot;).Value = MyArray(3,2) End Sub  Run the Multi_Array macro. Return the focus to Excel by pressing ALT+F11.</ol>

The range A1:B3 will be populated with the contents of the array. back to the top

Dynamic arrays
You can declare an array so that the dimensions of the array can be increased or decreased while your macro is running. However, the contents of the array will be lost when you redimension the array.

Declaring a dynamic array
To declare an array as a dynamic array, use the following syntax:

Dim As

Note No size is specified for the array when you declare a dynamic array.

Try this exercise:

<ol> Insert a new module into your project.</li>  Type the following code into this module:

Option Base 1 Sub Dynamic_Array 'Create a dynamic array. Dim MyArray As Integer 'Redimension the array to two elements. ReDim MyArray(2) 'Populate the array elements. MyArray(1) = 1 MyArray(2) = 2 MsgBox &quot;First element= &quot; & MyArray(1) & _ &quot; Second element= &quot; & MyArray(2) 'Redimension the array to three elements. 'The contents of the array are lost. ReDim MyArray(3) MyArray(3) = 3 MsgBox &quot;First element= &quot; & MyArray(1) & _ &quot; Second element= &quot; & MyArray(2) & _ &quot; Third element= &quot; & MyArray(3) End Sub </li> Run the Dynamic_Array macro. You receive the following message:

First element= 1 Second element= 2

</li> Click OK to close the first message. You receive the following message:

First element= 0 Second element= 0 Third element= 3

</li> Click OK to close the message.</li></ol>

The second message displays values of zero for the first element and for the second element because the second ReDim statement clears the contents of the array.

Preserving array contents with redimensioned array
When you use the ReDim statement to redimension an array, the contents of the array are lost. This is acceptable as long as you know about it when you are developing your macro. If you want to keep the contents of your array when you redimension it, use the Preserve keyword together with the ReDim statement.

Try this exercise: <ol>  Change the second ReDim statement in the Dynamic_Array macro so that the macro looks similar to the following:

Option Base 1 Sub Dynamic_Array 'Create a dynamic array. Dim MyArray As Integer 'Redimension the array to two elements. ReDim MyArray(2) 'Populate the array elements. MyArray(1) = 1 MyArray(2) = 2 MsgBox &quot;First element= &quot; & MyArray(1) & _ &quot; Second element= &quot; & MyArray(2) 'Redimension the array to three elements.

'The contents of the array are not lost when you use the Preserve keyword.

ReDim Preserve MyArray(3) MyArray(3) = 3 MsgBox &quot;First element= &quot; & MyArray(1) & _ &quot; Second element= &quot; & MyArray(2) & _ &quot; Third element= &quot; & MyArray(3) End Sub </li> Run the Dynamic_Array macro. You receive the following message:

First element= 1 Second element= 2

</li> Click OK to close the first message. You receive the following message:

First element= 1 Second element= 2 Third element= 3

</li> Click OK to close the message.</li></ol>

The second message displays the same values as the first message. This behavior occurs because the Preserve keyword was used with the ReDim statement.

Note When you use the Preserve keyword with a dynamic array, you can only change the upper bound of the last dimension in the array and you cannot change the number of dimensions in the array.

back to the top

Populating an array with worksheet data
Taking data from a worksheet and putting it in an array is a common use of arrays in Visual Basic for Applications. If you populate an array from worksheet data, you can do things like sort the data, perform a numeric analysis on the data, or export the data.

Variant variable that contains an array
If you want to transfer data from a range of cells on a worksheet to an array, you can either loop through the cells and populate each element in the array with the cell value every time through the loop, or you can directly assign the range to the array. The latter method is quicker. However, the array that you are passing the cell range to is really a Variant variable that contains an array.

For additional information about looping, click the following article number to view the article in the Microsoft Knowledge Base:

843146 Description of Excel for Windows sub-procedures in Visual Basic for Applications (control structures)

Note A Variant variable that contains an array is different from an array variable that contains elements of variant type. Be careful when you work with one-dimensional arrays.

Try this exercise: <ol> Save and close any open workbooks, and then open a new workbook.</li> Type the following in Sheet1:

</li> Start the Visual Basic Editor, and then insert a module.</li>  Type the following code in the module:

Option Base 1 Sub Array_from_sheet_data 'Create a Variant variable. Dim MyArray As Variant 'Assign the range A1:A3 to the Variant variable. MyArray = Sheet1.Range(&quot;A1:A3&quot;).Value MsgBox &quot;Cell A1 is: &quot; & MyArray(1,1) & _ &quot; Cell A2 is: &quot; & MyArray(2,1) & _ &quot; Cell A3 is: &quot; & MyArray(3,1) End Sub </li> Run the Array_from_sheet_data macro. You receive the following message:

Cell A1 is 1 Cell A2 is 2 Cell A3 is 3

</li> Click OK to close the message.</li></ol>

Note The references to the MyArray elements use both the row index and the column index, even though the data is a single column. The single-column data means that this array is a one-dimensional array. The MyArray variable is really not an array. Instead, the MyArray variable is a Variant that contains an array. Therefore, you must use both the row indexes and the column indexes when you reference this kind of array.

back to the top

<div class="references_section">