| 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 0.
The array returned by the Array function is a dynamic rather than static array. Once created, you can redimension the array using Redim, Redim Preserve, or another call to the Array function.
If you don't pass any arguments to the Array function, an empty array is created. Although this may appear to be the same as declaring an array in the conventional manner with the statement:
Dim myArray()
the difference is that you can then use the empty array with the Array function again later in your code.
Programming Tips and Gotchas
You can effectively use the Array function only in situations where you know in advance the number of elements you will need. It's not possible to write the function statement with a variable number of elements.
You can't assign the return value of Array to a variable previously declared as an array variable. Therefore, don't declare the variant variable as an array using the normal syntax:
Dim MyArray()
Instead, simply declare a variant variable, such as:
Dim MyArray as Variant
Array can also be invoked as a method within the VBA object model[1]
[1] If you use the Object Browser to locate the Array method within the VBA object, though, you won't be able to find it, since, as a member of the _HiddenModule module, it's hidden from view, although it remains accessible.
by using the syntax:
VBA.Array()
In this case, Option Base has no effect on the base element number of the array; the first element is always 0. Try this example:
Option Base 1 Private Sub CommandButton1_Click() Dim vaListOne As Variant vaListOne = VBA.Array("One", 2, "Three", 4) MsgBox vaListOne(1) Dim vaListTwo As Variant vaListTwo = Array("One", 2, "Three", 4) MsgBox vaListTwo(1) End Sub
The Array function is ideal for saving space and time and writing more efficient code when creating a fixed array of known elements, for example:
Dim Titles as Variant Title = Array("Mr", "Mrs", "Miss", "Ms")
You can use the Array function to create multidimensional arrays. However, accessing the elements of the array needs a little more thought. The following code fragment creates a simple two-dimensional array with three elements in the first dimension and four elements in the second:
Dim vaListOne As Variant vaListOne = Array(Array(1, 2, 3, 4), _ Array(5, 6, 7, 8), _ Array(9, 10, 11, 12))
Surprisingly, the code you'd expect to use to access the array returns a "Subscript out of range" error:
'This line generates a Subscript out of range error MsgBox vaListOne(1, 2)
You can overcome this limitation by declaring a second variant and assigning to it the element from the first dimension, then accessing the second dimension element in the normal way, like this:
Dim vaListTwo As Variant vaListTwo = vaListOne(1) MsgBox vaListTwo(2)
You can also use the Array function to populate the ActiveX (Microsoft Forms 2.0) ListBox or ComboBox controls, as the following example shows:
Private Sub CommandButton2_Click() ComboBox1.Clear ComboBox1.List = Array("Mr", "Mrs", "Miss", "Ms") ComboBox1.ListIndex = 0 End Sub
Note that this doesn't work with the standard Visual Basic ListBox or Combo-Box controls; it produces an "Argument not optional" compiler error. Our performance comparisons, however, indicate that the conventional technique of calling the control's AddItem method to add an item is between 5 and 25% faster than calling the Array function.
Here's another neat trick you can use with the Array function: you can even create your own "on-demand" control array (of existing controls) by simply listing a group of existing controls in the argument list to pass to the Array function. You can then use the array element the same way you'd use an object variable, as the following code demonstrates:
Dim vaTest as Variant vaTest = Array(CommandButton1, CommandButton2, _ CommandButton3) MsgBox vaTest(1).Caption
Because you declare the variant variable to hold the array as a simple variant, rather than an array, and can then make repeated calls to Array, the function can create dynamic arrays. For example, the following code fragment dimensions a variant to hold the array, calls Array to create a variant array, then calls Array again to replace the original variant array with a larger variant array:
Dim varArray As Variant varArray = Array(10,20,30,40,50) ... varArray = Array(10,20,30,40,50,60)
The major disadvantage of using this method is that, while it makes it easy to replace an array with a different array, it doesn't allow you to easily expand or contract an existing array.