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