VBA Array Function

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 Reference – Array Functions

VBA Array Function Syntax

The syntax for the Array function in VBA is:

1
Array( [, Value] )

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.

1
2
3
4
5
Dim arr As Variant
arr = Array(10, 20, 30)
 
'Get array size
Debug.Print UBound(arr) - LBound(arr) + 1 'Result: 4

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.

1
2
3
4
5
6
7
8
9
10
Dim arr As Variant
arr = Array(10, 20, 30)
 
Debug.Print arr(2)' 30
 
'Change array size and preserve
ReDim Preserve arr(3)
Debug.Print arr(3) 'Result: Empty
arr(3) = 4 'Set to 4
Debug.Print arr(3) 'Result: 4

Examples

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

VBA Array from string values

1
2
3
4
5
6
7
8
9
10
Dim arr As Variant
arr = Array("Tom", "Michael", "John")
 
'Print all elements of the array
Debug.Print arr(0) 'Result: Tom
Debug.Print arr(1) 'Result: Michael
Debug.Print arr(2) 'Result: John
 
'Get array length
Debug.Print UBound(arr) - LBound(arr) + 1 'Result: 3

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

1
2
3
4
Dim arr As Variant
arr = Split("Tom,Michael,John")
 
Debug.Print arr(0) 'Result: Tom

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

1
2
3
4
Dim arr As Variant
arr = Split("Tom;Michael;John",";")
 
Debug.Print arr(0) 'Result: Tom

VBA Array or numbers

Below a simple example of an array of numbers:

1
2
3
4
Dim arr As Variant
arr = Array(10, 20, 30)
   
Debug.Print arr(0)'Result: 20

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:

1
2
3
4
5
Dim arr As Variant
arr = Array(Array(10, 20, 30), Array(40, 50, 60))
   
Debug.Print arr(0)(1) 'Result: 20
Debug.Print arr(1)(0) 'Result: 40