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
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
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.
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.
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
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
.
a As Variant
isn't an Array
? Here's what I created to deal with all the various edge cases: stackoverflow.com/a/68614881/501113
Aug 1 at 22:45
Try CountA
:
Dim myArray(1 to 10) as String
Dim arrayCount as String
arrayCount = Application.CountA(myArray)
Debug.Print arrayCount
myArray = Array()
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
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
arr
isn't an Array
. I created an answer that handles that situation as well: stackoverflow.com/a/68614881/501113
Aug 3 at 15:32
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