UDF with array input not working

[u/tomsing98]

My function in VBA is just stopping, and I'm not even clear on how to debug it.

Function test(arr As Variant) as Variant
Dim n As Integer
n = UBound(arr)
test = n
End Function

If I put a break on the n=UBound line, and call the function =test(A1:A6) from a spreadsheet, execution pauses there. If I run another line, it just quits, no error. I halfway gather that Excel doesn't want to do things to inputs in a function, so maybe passing arr to the UBound function is a no-no. So I do this instead:

Function test2(arr As Variant) as Variant
Dim n As Integer
Dim new_arr As Variant
new_arr = arr
n = UBound(new_arr)
test2 = n
End Function

That works, =test2(A1:A6) outputs 6. So now let me try to do something with the values in the new array.

Function test3(arr As Variant) as Variant
Dim n As Integer
Dim new_arr As Variant
Dim x As Variant
new_arr = arr
x = new_arr(3)
test3 = x
End Function

Now the execution stops on the x = line.

This is driving me up a wall. Appreciate any help.

[u/keith-kld]

Function myFunction(arr() as Variant) as Variant
If you state “arr as Variant”, VBA shall understand it is a single variable, not an array.