Try CountA
:
Dim myArray(1 to 10) as String
Dim arrayCount as String
arrayCount = Application.CountA(myArray)
Debug.Print arrayCount
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
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
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.
Source: Stackoverflow.com