VB & VBA in a Nutshell: The Language

Array Function

Syntax

Array([element1], [elementN],....)

element

Use: Required

Data Type: Any

The data to be assigned to the first array element

elementN

Use: Optional

Data Type: Any

Any number of data items you wish to add to the array.

Return Value

A variant array consisting of the arguments passed into the function.

Description

Returns a variant array containing the elements whose values are passed to the function as arguments.

The code fragment:

Dim vaMyArray
vaMyArray = Array("Mr", "Mrs", "Miss", "Ms")

is the equivalent to writing:

Dim vaMyArray(3)
vaMyArray(0) = "Mr"
vaMyArray(1) = "Mrs"
vaMyArray(2) = "Miss"
vaMyArray(3) = "Ms"

Because Array creates a variant array, you can pass any data type, including user-defined types and objects, to the Array function. You can also pass the values returned by calls to other Array functions to create multidimensional arrays (but see the comment on multidimensional arrays in Section 7.6.5).

Rules at a Glance

• You can assign the array returned by the Array function only to a Variant.

• Although the array you create with the Array function is a variant data type, the individual elements of the array can be a mixture of different data types.

• The initial size of the array you create is the number of arguments you place in the argument list and pass to the Array function.

• The lower bound of the array created by the Array function is determined by the Option Base directive; if there is no Option Base statement, the lower bound of the array is ...