Description of arrays in Sub procedures in Visual Basic for Applications (843145)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
GSA Content Delivery Support:3485

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

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

MORE INFORMATION

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 <array name> (# of elements - 1) As <data type>

For example, if you have an array that must have 10 integer values, you create the array with the following statement: 
Dim MyArray(9) As Integer
Note You dimension the array with 9. The first index number in the array is assumed to be zero. Therefore, to access the first element in the array, you use the following syntax: 
MsgBox MyArray(0)

The location of the line where you declare your array affects the scope of the array and the lifetime of the array.

Try this exercise:
  1. Insert a new module into your project.
  2. Add the following code to the module: 
    Sub Array_Example()
'Create an array that has a total of three elements.
Dim MyArray(2) As Integer 
'Fill in the array with some values.
MyArray(0) = 100
MyArray(1) = 300
MyArray(2) = 500
MsgBox "First element= " & Myarray(0) & _
" Second element= "& MyArray(1) & _
" Third element= " & MyArray(2)
End Sub
  3. Run the Array_Example macro. You receive the following message:

    First element= 100 Second element= 300 Third element= 500
  4. Click OK to close the message.

Changing the first index number of the array

If you do not want the first index number for your arrays to be zero, either you can add an additional line that uses the Option Base statement to your module, or you can dimension your array by using a slightly different syntax.

The Option Base statement

You can add the Option Base statement at the top of your module to specify the default index for the first element of the arrays in the module. The value that you specify can be only zero or one.

Try this exercise:
  1. Modify the module that contains the Array_Example macro to the following: 
    Option Base 1
Sub Array_Example()
'Create an array that has a total of two elements.
Dim MyArray(2) As Integer 
'Fill in the array with some values.
MyArray(1) = 200
MyArray(2) = 400
MsgBox "First element= " & Myarray(1) & _
" Second element= "& MyArray(2) 
End Sub
  2. Run the Array_Example macro. You receive the following message: First element= 200 Second element= 400
  3. Click OK to close the message.
Note You must be careful with the default index for your arrays. It is a common mistake to assume that the size of the array matches the number that is specified in the line where you dimensioned the array.

Dimensioning the upper index and the lower index of an array

There is another way to dimension your array. You can use a second syntax that uses two numbers to specify the indexes for the array.

Try this exercise:
  1. Add the following code to the module that contains the Array_Example macro:

    Note Leave the Option Base statement from the previous example in the module. 
    Sub Array_Example_2()
'Create an array with two elements with indexes 3 and 4.
Dim NewArray(3 to 4) As Integer
'Fill in the array with some values.
NewArray(3) = 50
NewArray(4) = 60
MsgBox "First element= " & NewArray(3) & _
   " Second element= " & NewArray(4)
End Sub
  2. Run the Array_Example_2 macro. You receive the following the following message: First element= 50 Second element= 60
  3. Click OK to close the message.

Determining the bounds of an array

You can determine the upper bound and the lower bound of an array by using the UBound function and the LBound function, respectively. The default lower bound for any array is either zero or one, depending on how you are using the Option Base statement. However, as in an earlier example, you can set the lower bound for an array to something other than zero or one. If you do this, you must use the LBound function and the UBound function to determine the bounds of an array.

Try this exercise:
  1. Change the Array_Example_2 macro from the earlier example to the following: 
    Sub Array_Example_3()
'Create an array with three elements with indexes 2 through 4.
Dim NewArray(2 to 4) As Integer
'Fill in the array with some values.
NewArray(2) = 40
NewArray(3) = 50
NewArray(4) = 60
MsgBox "Lowest array index is " & LBound(NewArray) & _
     " and Highest array index is " & UBound(NewArray)
End Sub
  2. Run the Array_Example_3 macro. You receive the following message: Lowest array index is 2 and Highest array index is 4
Note The message displays the index numbers instead of the values that are stored in the array.

back to the top

Multidimensional arrays

In Visual Basic for Applications, arrays can have up to 60 dimensions. The simplest example of a multidimensional array is a two-dimensional array. A two-dimensional array can be treated just like a multi-columned table on a worksheet.

Declaring a multidimensional array

To declare a multidimensional array, use the default syntax: 
Dim <array name>(r, c) As <data type>

This syntax uses the value that you have set with the Option Base statement as the lower bound for each dimension. (This syntax uses zero if you are not using Option Base.) You can also use the following: 
Dim <array name>(1 to r , 1 to c) As <data type>

This syntax has a lower bound of one for each dimension of the array.

Either syntax will create a two-dimensional array that has r rows and c 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:
  1. Start Excel, and then open a new workbook.
  2. Start the Visual Basic Editor, and then insert a new module into your project.
  3. 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("A1").Value = MyArray(1,1)
Sheet1.Range("B1").Value = MyArray(1,2)
Sheet1.Range("A2").Value = MyArray(2,1)
Sheet1.Range("B2").Value = MyArray(2,2)
Sheet1.Range("A3").Value = MyArray(3,1)
Sheet1.Range("B3").Value = MyArray(3,2)
End Sub
  4. Run the Multi_Array macro.
  5. Return the focus to Excel by pressing ALT+F11.

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 <array name>() As <data type>

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

Try this exercise:
  1. Insert a new module into your project.
  2. 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 "First element= " & MyArray(1) & _
" Second element= " & MyArray(2)
'Redimension the array to three elements.
'The contents of the array are lost.
ReDim MyArray(3)
MyArray(3) = 3
MsgBox "First element= " & MyArray(1) & _
" Second element= " & MyArray(2) & _
" Third element= " & MyArray(3)
End Sub
  3. Run the Dynamic_Array macro. You receive the following message:

    First element= 1 Second element= 2
  4. Click OK to close the first message. You receive the following message:

    First element= 0 Second element= 0 Third element= 3
  5. Click OK to close the message.
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:
  1. 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 "First element= " & MyArray(1) & _
" Second element= " & 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 "First element= " & MyArray(1) & _
" Second element= " & MyArray(2) & _
" Third element= " & MyArray(3)
End Sub
  2. Run the Dynamic_Array macro. You receive the following message:

    First element= 1 Second element= 2
  3. Click OK to close the first message. You receive the following message:

    First element= 1 Second element= 2 Third element= 3
  4. Click OK to close the message.
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:
  1. Save and close any open workbooks, and then open a new workbook.
  2. Type the following in Sheet1:
    A1: 1
    A2: 2
    A3: 3
  3. Start the Visual Basic Editor, and then insert a module.
  4. 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("A1:A3").Value
MsgBox  "Cell A1 is: " & MyArray(1,1) & _
" Cell A2 is: " & MyArray(2,1) & _
" Cell A3 is: " & MyArray(3,1)
End Sub
  5. Run the Array_from_sheet_data macro. You receive the following message:

    Cell A1 is 1 Cell A2 is 2 Cell A3 is 3
  6. Click OK to close the message.
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

REFERENCES

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

843146 Excel for Windows Sub procedures in Visual Basic for Applications (control structures)

843144 How to use variables in Excel Sub procedures in Visual Basic for Applications

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming KbVBA kbinfo KB843145 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.