Storing Arrays

You can store the values in an array variable in a name just as easily as you can store a number or a label. The following code creates an array of numbers in aiArray and stores the array values in MyName:

Sub ArrayToName()

Dim aiArray(1 To 200, 1 To 3) As Integer Dim iRow As Integer Dim iColumn As Integer

'Create array and store in name

'Create array

For iColumn = 1 To 3

aiArray(iRow, iColumn) = iRow + iColumn Next iColumn Next iRow 'Store in name

Names.Add Name:="MyName", RefersTo:=aiArray End Sub

There is a limit to the size of an array that can be assigned to a name in Excel 97 and Excel 2000. The maximum number of columns is 256 and the total number of elements in the array cannot exceed 5,461. In Excel 2002, 2003, and 2007, the size is only limited by memory.

The Evaluate method can be used to assign the values in a name that holds an array to a Variant variable. The following code assigns the contents of MyName, created in ArrayToName, to vArray and displays the last element in the array:

Sub NameToArray()

Dim vArray As Variant

'Assign contents of name to variant vArray = [MyName]

'Display element of array MsgBox vArray(200, 3)

End Sub

The array created by assigning a name containing an array to a variant is always one-based, even if you have an Option Base 0 statement in the declarations section of your module.

0 0

Post a comment