To clarify points:

In that example xyz is a Variant, by default when you don't specify a type
it is of type Variant

Second, UBound only work for arrays so if you don't use type checking like
TypeOf or IsArray you will have a problem using it on an Excel Range...


"Alan Beban" wrote in message
...
Two clarifying points.
First, it is not necessary to keep the array in a Variant variable; the
following works fine:

Sub abtest3()
Dim arr() As Single, rng As Range
Set rng = Range("C1:C14")
ReDim arr(1 To rng.Count, 1 To 1)
For i = 1 To rng.Count
arr(i, 1) = rng(i)
Next
Size = getSize(arr)
Debug.Print Size, TypeName(arr) '<----Returns 14 Single()
End Sub
Function getSize(xyz)
If IsArray(xyz) Then
If TypeOf xyz Is Range Then
getSize = xyz.Count
Else
getSize = UBound(xyz)
End If
Else
Msg = "Input must be an array or multi-celled range"
MsgBox Msg, 16
End If
End Function

Second, Alain's first response in this thread included the statement "The
declaration of xrange must be an array for UBound to work, now it is a
Variant type containing an array." In fact, UBound works fine on either
an array contained within a Variant variable or a Variant() type array (or
for that matter, any other type of VBA array, e.g., Integer(), Single(),
etc.).

Sub abtest4()
Dim xrange1, xrange2()
xrange1 = Range("C1:C14")
xrange2 = Range("C1:C14")
'xrange1 is an array contained within a Variant type variable;
'xrange2 is a Variant() type array
Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14
End Sub

Alan Beban

Alain Vaillancourt wrote:
Sorry I think I answered to quickly, if you need to pass an array or
Excel range you keep it in a Variant and use the IsArray and TypeOf to
handle the difference between them. The next message from Alan in this
thread give you a good example of how to do it...