VBA Array Function

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)

The Excel VBA Array Function creates a Variant VBA Array from a list of comma-delimited values. In case no values are provided a zero-length VBA Array is created.

VBA Array Function Syntax

The syntax for the Array function in VBA is:

where Value can be any type of variable or constant i.e. Variant type (any type). Due to this nature you can initiate a VBA Array using above function even with variables of different types see below example of an array initiated with numbers, strings and dates:
VBA Array function example

You can find Microsoft documentation on the VBA Array Function here.

Tips and Tricks

Get length of your VBA Array

To see more tips and trick with VBA Arrays see my tutorial otherwise below a few basic tips for usage.

How to extend your VBA Array

Below an example of creating an array using the VBA Array Function and then extending it by item while still preserving its previous items.


Below are a few useful examples how to use the function:

VBA Array from string values

Above example can be simplified using the VBA Split function to omit the additional parentheses:

Split is even better for strings as you can use a different splitter:

VBA Array or numbers

Below a simple example of an array of numbers:

VBA Array or Arrays

Similarly you can use the function to create other types of arrays, even a VBA Array of Arrays i.e. 2 dimensional array:

Simply the best place to learn Excel VBA