Skip to main content

Array function

Table of contents
  1. Syntax
  2. Remarks
  3. Example

Returns a Variant containing an array.



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.


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, B As Long, i As Long
A = Array(10, 20, 30)   ' A is a three element list by default indexed 0 to 2
B = A(2)                ' B is now 30
ReDim Preserve A(4)     ' Extend A's length to five elements
A(4) = 40               ' Set the fifth element's value
For i = LBound(A) To UBound(A)
    Debug.Print "A(" & i & ") = " & A(i)
Next i

The lower bound of an array created by using the Array function is determined by the lower bound specified with the Option Base statement, unless Array is qualified with the name of the type library (for example VBA.Array). If qualified with the type-library name, Array is unaffected by Option Base.


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".

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>