arraysvbavariant

UBound function doesn't work when passing a Named Range as Variant

MyArray is a (100 row x 1 column) named range containing integers.

This doesn't work:

Function myFunc(MyArray As Variant)

myFunc = UBound(MyArray)

Nor does this:

Function myFunc(MyArray As Variant)

myFunc = UBound(MyArray, 1)

I'm sure this is a rather basic error, but I've done a fair amount of Googling and failed to work out the answer for myself. Help would be much appreciated.

Solution

  • Make sure you're passing a variant array, not a Range object. The function accepts a variant data type, which can be anything. In order to ensure it's an array, pass the .Value

    Sub Test()

Debug.Print myFunc(Range("A1:A50").Value)

End Sub

Function myFunc(MyArray As Variant)

myFunc = UBound(MyArray, 1)

End Function