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

Gregarine

Does this answer your question? How to return the number of dimensions of a (Variant) variable passed to it in VBA – Fated

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.

Callboard

For real? There is no native function for something as fundamental as length of an array? Wow. This seems very cumbersome to have to use an alternative to say Len(columns). – Pacesetter

@Pacesetter And what would a length property return? VBA arrays can have up to 60 dimensions... with the most common scenario being 2D arrays. So, Length would return what? – Menchaca

@excel hero - I think you would have it work just like the UBound( ArrayName, [Dimension] ), no? – Pacesetter

@ExcelHero Most languages just return the first dimensions and then if you call the first-dimensional subindex, you can return the length of the second dimension, and so on. – Glaikit

The Answer code is correct ONLY IN THE CASE THE ARRAY IS DEFINED AND NON-EMPTY. If you really want to generically get the size of an Array, then here is a robust solution that handles all the edge cases: https://mcmap.net/q/237464/-get-length-of-array – Neace

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.

Effendi

The Answer code is correct ONLY IN THE CASE THE ARRAY IS DEFINED AND NON-EMPTY. If you really want to generically get the size of an Array, then here is a robust solution that handles all the edge cases: https://mcmap.net/q/237464/-get-length-of-array – Neace

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

Suffragist

A good addition +1:-) - Just a side note: in case of assigning an empty array via columns = Array() the referenced array isn't regarded as empty (actually it isn't), the function GetLength returns a zero length correctly as it calculates GetLength = UBound(a) - LBound(a) + 1 = -1 -0 +1 = 0. – Blodget

This is a more robust solution than the one in the accepted Answer. However, as an edge case, what if the a As Variant isn't an Array? Here's what I created to deal with all the various edge cases: https://mcmap.net/q/237464/-get-length-of-array – Neace

Answer 4

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

Neace

Answer 5

Try CountA:

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

Zeringue

Nice idea; caveats: a) returns 1 if only declaration without any actual assignment, b) fails with Type Error 13 if empty as in myArray = Array() – Blodget

Shouldn't this be WorksheetFunction.CountA? – Serafina

@hohny, In my POC, Application.CountA worked. – Zeringue

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

Henbit

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

Heaton

Your solution will return 0 even when arr isn't an Array. I created an answer that handles that situation as well: https://mcmap.net/q/237464/-get-length-of-array – Neace