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 Empty

Using this method (without the parentheses) allows you to check for Empty and assign Empty to an array.
The Empty data type is the default value when you declare a variable as a Variant.

Dim arValues As Variant 
If (arValue = Empty)
   Debug.Print "Empty"
End If

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 = Null)
   Debug.Print "Null"
End If

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

ARRAY Function

When you declare an array as a Variant you can initialise it using the ARRAY function.
This lets you quickly populate it using a series of comma-delimited values.
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)

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