Array Function

Description

Returns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.

Remarks

The notation used to refer to an element of an array consists of the variable name followed by parentheses containing an index number indicating the desired element. In the following example, the first statement creates a variable named A as a Variant. The second statement assigns an array to variable A. The last statement assigns the value contained in the second array element to another variable.

Dim A As Variant
A = Array(10,20,30)
B = A(2)

The lower bound of an array created using the Array function is always zero. Unlike other types of arrays, it is not affected by the lower bound specified with the Option Base statement.

Note A Variant that is not declared as an array can still contain an array. A Variant variable can contain an array of any type, except fixed-length strings and user-defined types. Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.

See Also

Deftype statements, Dim statement, Let statement, Option Base statement, Variant data type.

Example

This example uses the Array function to return a Variant containing an array.

Dim MyWeek, MyDay
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
' Return values assume lower bound set to 1 (using Option Base
' statement).
MyDay = MyWeek(2)                        ' MyDay contains "Tue".
MyDay = MyWeek(4)                        ' MyDay contains "Thu".

Example (Microsoft Excel)

This example fills the range A1:C5 on Sheet1, Sheet5, and Sheet7 with the contents of the same range on Sheet1.

x = Array("Sheet1", "Sheet5", "Sheet7")
Sheets(x).FillAcrossSheets _
    Worksheets("Sheet1").Range("A1:C5")

This example consolidates data from Sheet2 and Sheet3 onto Sheet1, using the SUM function.

Worksheets("Sheet1").Range("A1").Consolidate _
    sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _
    Function:=xlSum

This example adds an array of strings as a custom list.

Application.AddCustomList Array("cogs", "sprockets", _
    "widgets", "gizmos")

This example hides Chart1, Chart3, and Chart5. Note that in this example, the Charts property returns a Sheets object instead of a Charts object.

Charts(Array("Chart1", "Chart3", "Chart5")).Visible = False

This example sets the entries in list box one on Dialog1.

DialogSheets("Dialog1").ListBoxes(1).List = _
    Array("cogs", "widgets", "sprockets", "gizmos")

This example creates a group that includes drawing objects one, three, and five on Sheet1.

Set myGroup = Worksheets("Sheet1").DrawingObjects(Array(1, 3, 5)).Group
Worksheets("Sheet1").Activate
myGroup.Select