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.

Answer 2

Function

Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

Usage

Dim arr(1 To 3) As String  ' Array starting at 1 instead of 0: nightmare fuel
Debug.Print ArrayLen(arr)  ' Prints 3.  Everything's going to be ok.

Answer 3

If the variant is empty then an error will be thrown. The bullet-proof code is the following:

Public Function GetLength(a As Variant) As Integer
   If IsEmpty(a) Then
      GetLength = 0
   Else
      GetLength = UBound(a) - LBound(a) + 1
   End If
End Function

Answer 4

Try CountA:

Dim myArray(1 to 10) as String
Dim arrayCount as String
arrayCount = Application.CountA(myArray)
Debug.Print arrayCount

Answer 5

Copy/Pasta Solution:
The most common answer is this:

UBound(myItems) - LBound(myItems) + 1

While it works +90% of the time, that other 10% fails with nasty unplanned errors when a client/user is running it. That is because there are a number of edge cases which this solution does not cover.

---

Generic Solution:
The solution below covers all the edge cases I have found thus far. And it eliminates all the run-time failures when a client/user is running it.

'Generic solution using Variant

Public Const SIZE_NOT_ARRAY As Long = -1
Public Const SIZE_EMPTY As Long = 0

'Return Value:
'   -1 - Not an Array
'    0 - Empty
'  > 0 - Defined
Public Function size( _
    ByVal values As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Long
  Dim result As Long: result = SIZE_NOT_ARRAY 'Default to not an Array

  Dim lowerBound As Long
  Dim upperBound As Long
  
  On Error GoTo NormalExit
  
  If (IsArray(values) = True) Then
    result = SIZE_EMPTY 'Move default to Empty
    lowerBound = LBound(values, dimensionOneBased) 'Possibly generates error
    upperBound = UBound(values, dimensionOneBased) 'Possibly generates error
    If (lowerBound < upperBound) Then
      result = upperBound - lowerBound + 1 'Size greater than 1
    Else
      If (lowerBound = upperBound) Then
        result = 1 'Size equal to 1
      End If
    End If
  End If
  
NormalExit:
  size = result
End Function

Public Function isEmpty( _
    ByVal values As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isEmpty = size(values, dimensionOneBased) = 0
End Function

Public Function isDefined( _
    ByVal values As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isDefined = size(values, dimensionOneBased) > 0
End Function

Caveat:
While the above "Generic" solution works and is robust, it is not the most performant. IOW, if one knows one is working with Dim strings() As String, then a more specific solution can be many times faster.

---

Much Faster Solution:
The Array of String solution below is many times faster than the "Generic Solution" above. Why? Because the extra instructions (defaulting to SIZE_NOT_ARRAY, IsArray, IsEmpty, etc.) and the conversions around from Variant to Array appear to carry considerable cost. In my testing, the solution below can be over 10 times faster.

'Specifically Typed solution for String

Public Const SIZE_EMPTY As Long = 0

'Return Value:
'   -1 - Not an Array
'    0 - Empty
'  > 0 - Defined
Public Function size( _
    ByRef r_values() As String _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Long
  Dim result As Long: result = SIZE_EMPTY 'Default to Empty

  Dim lowerBound As Long
  Dim upperBound As Long
  
  On Error GoTo NormalExit
  
  lowerBound = LBound(r_values, dimensionOneBased) 'Possibly generates error
  upperBound = UBound(r_values, dimensionOneBased) 'Possibly generates error
  If (lowerBound < upperBound) Then
    result = upperBound - lowerBound + 1 'Size greater than 1
  Else
    If (lowerBound = upperBound) Then
      result = 1 'Size equal to 1
    End If
  End If
  
NormalExit:
  size = result
End Function

Public Function isEmpty( _
    ByRef r_values() As String _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isEmpty = size(r_values, dimensionOneBased) = 0
End Function

Public Function isDefined( _
    ByRef r_values() As String _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isDefined = size(r_values, dimensionOneBased) > 0
End Function

Answer 6

Compilating answers here and there, here's a complete set of arr tools to get the work done:

Function getArraySize(arr As Variant)
' returns array size for a n dimention array
' usage result(k) = size of the k-th dimension

Dim ndims As Long
Dim arrsize() As Variant
ndims = getDimensions(arr)
ReDim arrsize(ndims - 1)
For i = 1 To ndims
    arrsize(i - 1) = getDimSize(arr, i)
Next i
getArraySize = arrsize
End Function

Function getDimSize(arr As Variant, dimension As Integer)
' returns size for the given dimension number
    getDimSize = UBound(arr, dimension) - LBound(arr, dimension) + 1
End Function

Function getDimensions(arr As Variant) As Long
' returns number of dimension in an array (ex. sheet range = 2 dimensions)
    On Error GoTo Err
    Dim i As Long
    Dim tmp As Long
    i = 0
    Do While True
        i = i + 1
        tmp = UBound(arr, i)
    Loop
Err:
    getDimensions = i - 1
End Function

Answer 7

UBound and LBound do not work when we have an uninitialized dynamic array.
I found no solutions for it, so, I handled the error. Now It works for all my script situations:

Public Function SizeOf(arr As Variant) As Integer
    On Error GoTo IsEmpty
    SizeOf = UBound(arr) - LBound(arr) + 1
    Exit Function
IsEmpty:
    SizeOf = 0
End Function