配列の要素数を取得するには

Public Function ArrayCount(varArrray As Variant) As Integer
    If IsArray(varArrray) Then
        ArrayCount = UBound(varArrray) - LBound(varArrray) + 1

    Else
        ArrayCount = 0

    End If

End Function

Private Sub Test_ArrayCount()
    Debug.Assert ArrayCount(Array(1)) = 1
    Debug.Assert ArrayCount(Array(1, 2)) = 2
    Debug.Assert ArrayCount(Array(1, 2, 3)) = 3
    Debug.Assert ArrayCount(Array(1, 2, 3, 4, 5, 6, 7, 8, 9)) = 9

End Sub