得到数组的长度？

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

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

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

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