excel - Get length of array?

Question

I'm trying to get the length of an array, yet I keep getting this error:

Object required

Am I doing something wrong?

Dim columns As Variant
columns = Array( _
"A", "ID", _
"D", "Name")
Debug.Print columns.Length  ' Error: Object required

Answer 1

Length of an array:

UBound(columns)-LBound(columns)+1

UBound alone is not the best method for getting the length of every array as arrays in VBA can start at different indexes, e.g Dim arr(2 to 10)

UBound will return correct results only if the array is 1-based (starts indexing at 1 e.g. Dim arr(1 to 10). It will return wrong results in any other circumstance e.g. Dim arr(10)

More on the VBA Array in this VBA Array tutorial.