Variant Data Type

This method uses a Variant data type to contain the whole array.
This is different to an array of Variant data types.
A Variant that is not declared as an array can still contain an array.
The first line creates a single variable (not an array) that can contain any data type.
The second line declares an array containing 4 elements and assigns it to this single variable.

Dim arValues As Variant 
ReDim arValues(3)

Testing for Null

Using this method (without the parentheses) allows you to assign "Null" to the array when you want to represent no data.

Dim arValues As Variant 
arValues = Null
If (arValue is Null)

Returning Arrays from Functions

One reason you might use a Variant data type to contain your array is when you want to return an array from a function.
Passing Arrays Out


Dim arMyArray() As Variant

This can be used to create an array of Variant data types.

Dim arValues() As Variant 
ReDim arValues(3)
arValues(0) = 50
arValues(1) = "text"
arValues(2) = false
arValues(3) = -4.6

Can't test if the array is empty !!!

arValues = Empty   'this doesn't work !!! 

ARRAY Function

You can still use the ARRAY function to populate the array.
If you want to populate an array quickly using a series of comma-delimited values then you must declare your array as a Variant.
When using the ARRAY function the array always starts at zero. Any Option Base statement will be ignored.

Dim myArray As Variant 
myArray = Array(1, 2, 3, 4)
myArray = Array("one", "two", "three", "four")
myArray = Array("50, "text", false, -4.6)


© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext