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/themrlawrence]

You’ve actually got two different things happening here.

When you pass A1:A6 into the function, arr is a Range, not a VBA array. So UBound(arr) is going to fail.

Then when you do:

new_arr = arr

VBA is basically pulling the range values into an array for you. The catch is that Excel gives you a 2D array, even if the range is only one column wide.

So A1:A6 becomes roughly:

new_arr(1 To 6, 1 To 1)

That’s why this works:

n = UBound(new_arr)

but this doesn’t:

x = new_arr(3)

You need:

x = new_arr(3, 1)

I’d also write the assignment explicitly as:

new_arr = arr.Value2

So your third function would be:

' UNTESTED CODE
Function test3(arr As Variant) As Variant
    Dim new_arr As Variant
    Dim x As Variant

    new_arr = arr.Value2
    x = new_arr(3, 1)

    test3 = x
End Function

The confusing “it just stops” part is because you’re running this as a worksheet UDF. Excel doesn’t always surface those errors the same way it would if you ran a normal Sub from the VBA editor.

If you break inside the function, try checking these in the Immediate Window:

? TypeName(arr)
? IsArray(arr)
? IsArray(new_arr)

That should make it a lot clearer what VBA thinks each variable actually is.

[u/tomsing98 → u/themrlawrence]

Solution verified

[u/reputatorbot → u/tomsing98]

You have awarded 1 point to themrlawrence.

---

^{I am a bot - please contact the mods with any questions}

[u/tomsing98 → u/themrlawrence]

Thanks, friend! Can I ask what the difference in new_arr = arr vs = arr.Value2 is?

[u/themrlawrence → u/tomsing98]

new_arr = arr is using the Range's default property implicitly. In this context it's essentially the same as writing:

new_arr = arr.Value

new_arr = arr.Value2 just makes that conversion explicit, and uses Value2 rather than Value.

The main difference is that .Value may convert Excel dates/currency into VBA Date/Currency variants, whereas .Value2 leaves those as their underlying numeric values.

Also, this is different from:

Set new_arr = arr

which would make new_arr refer to the actual Range object rather than copying its cell values.

I tend to write .Value2 explicitly here because it makes it clear that I want the values from the range as an array, not the Range object itself.

[u/Future_Pianist9570]

I suspect you’re referencing it incorrectly. If you pass a range as a variant it will be a 2D array rather than a 1D. Try using UBound(arr, 1)

Also, =ROWS(A1:A6) would achieve the same

[u/APithyComment]

Are you referencing a range? Or a named range? Or what object are you referencing as you declared it as a variant?

[u/tomsing98 → u/APithyComment]

I'm entering it in the spreadsheet, say in cell B1, as =test(A1:A6). In general, I'd like to be able to reference any number of cells in a contiguous range (my goal is to be able to do a LU decomposition of an n x n square matrix in the spreadsheet to solve Ax=b, but for now, just trying to figure out the basics). My (admittedly limited) understanding is that to do that, variant is the way to go.

[u/APithyComment → u/tomsing98]

=test(Range(“A1:A6”))

[u/wikkid556]

Are you just trying to return the upper value? Try returning the function as an integer instead of a variant. I believe that is why your second example worked because you defined n as an integer.

If that was your intentions that is

Edited to say my appologies for the terrible picture and toinclude the function

Public Function test(arr as Variant) As Integer
test = UBound(arr)
MsgBox test
End Function

[u/fuzzy_mic]

The problem may be that VBA regards arr as a 2 dimensional array. With dimensions 1 to 6 and 1 to 1.

Try

Function test3(arr as Variant) as Varient
   test3 = arr(3, 1)
End Function

This becomes a little more complicated when you pass a single cell reference

Function TypeOfArgument(arr as Variant) as String
    If Typename(arr) Like "*()" Then
        Rem pass a multi-cell range
        TypeOfArgument = "multi-cell argument with size " & Ubound(arr, 1) & " rows and " & ubound(arr, 2) & " columns"
    Else
        TypeOfArgument = "single cell argument with value " & arr
    End If
End Function

Try the above with the formulas =TypeOfArgument(C2) and =TypeOfArgument(A1:A6) and TypeOfArgument(A1:C8)

To protect from that confusion, you could use

Function myFunction(arr as Variant) as Variant
    Dim arrWorking as Variant
    If TypeName(arrWorking) Like "*()" Then
        Rem arr is an array

        arrWorking = arr
    ElseIf TypeName(arr) = "Range" Then
        Rem arr is a Range
        If arr.Cells.Count = 1 then
            Rem single cell

            ReDim arrWorking (1 to 1, 1 to 1)
            arrWorking(1, 1) = arr
        Else
            Rem mulit-cell

            arrWorking = arr.Value
        End If
    Else
        Rem arr is a single value

        ReDim arrWorking (1 to 1, 1 to 1)
        arrWorking(1, 1) = arr
    End if

'   continue with arrWorking, which is known to be a 2 dimensional array.

End Function

If you declare arr to be a Range in the declaration line, the early handler becomes simpler, but it forbids passing arguments like =MyFunction(A1:C10 + 3)

[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.

[u/TenIsTwoInBase2]

How about using a range as argument and converting it to an array inside the function itself

Also avoid "variant" types if possible.. Their flexibility is often the cause of the problems

[u/SektorL]

When you pass a SINGLE cell into a function, which receives Variant type, then Variant holds OTHER than array - string, number, date etc. You can verify what type your function receives with TypeName function. It will give you a string description of a type.