Array Function

Description

Returns a Variant containing an array.

Syntax

Array(arglist)

The arglist consists of a comma-delimited list of an arbitrary number 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

Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the way the array elements are accessed is the same. The notation used to refer to any element of an array consists of the variable name followed by parentheses containing an index number to the desired element. In the following example, the first statement creates a variable A as a Variant. The second statement assigns an array to the variable A. The final statement illustrates how to assign 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 determined by the lower bound specified with the Option Base statement.

See Also

Deftype Statements, Dim Statement, Let Statement, Option Base Statement.

Example

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

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

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 method 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 from drawing objects one, three, and five on Sheet1.

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