Here is the VBA function I wrote:

Function test(x)
    test = UBound(x)
End Function

In excel I pass it the range A1:A5 which is filled with some numbers, =test(A1:A5). It returns #VALUE

I’ve tested this in two different machines, running brand new excel instances.

It does work in a Sub instead of a Function though. After running the following and inspecting the variables, a is 5.

Sub test2()
    x = Range("A1:A5")
    a = UBound(x)
End Sub

Any ideas?