Accepting a range as an array parameter

Question

I have a function that takes an array and outputs another array. Its internals are more complicated than the toy example below.

Public Function divide_by_2_5(ByRef coeffs() As Double) As Double()
    Dim Columns As Integer
    Columns = UBound(coeffs, 2) - LBound(coeffs, 2) + 1
    Dim output() As Double
    ReDim output(1 To 1, 1 To Columns)
    Dim i As Integer
    For i = 1 To Columns
        output(1, i) = coeffs(1, i) / 2.5
    Next i
    divide_by_2_5 = output
End Function

Here's what I see:

I would like the second row to instead contain the function's output. In this case, that would be 0.4, 0.4, 0.4, 0.4.

Unfortunately, I get a #VALUE! error and I don't know how to debug this.

Some clarification: clearly it is possible to have the same function return an array or have it write to the spreadsheet (with Ctrl-Shift-Enter). In an analogous fashion, is it possible for the input to come from either a range or an array?

Answer 1

Public Function divide_by_2_5(coeffs As Variant) As Double()
    Dim v() As Variant
    If TypeName(coeffs) = "Range" Then
        v = coeffs.Value
    Else
        v = coeffs
    End If
    Dim output() As Double
    ReDim output(LBound(v, 1) To UBound(v, 1), LBound(v, 2) To UBound(v, 2))
    Dim r As Long
    Dim c As Long
    For r = LBound(v, 1) To UBound(v, 1)
        For c = LBound(v, 2) To UBound(v, 2)
            output(r, c) = v(r, c) / 2.5
        Next
    Next
    divide_by_2_5 = output
End Function

An example of calling this as a UDF would be:

{=divide_by_2_5(C2:F2)}

An example of calling this from VBA using a Range might be:

Dim v As Variant
v = divide_by_2_5(Worksheets("Sheet1").Range("C2:F2"))

An example of calling this from VBA using an array might be:

Sub test()
   Dim x(1, 4) As Variant
   Dim v As Variant
   x(1, 1) = 6
   x(1, 2) = 7
   x(1, 3) = 8
   x(1, 4) = 9
   v = divide_by_2_5(x)
   MsgBox v(1, 3)
End Sub

Answer 2

if you would like D2, E2, F2, G2 to be equal to 0.4, you need to pass on one singe value to your function, like:

Public Function divide_by_2_5 (ByRef coeff As Range) As Double
         divide_by_2_5 = coeff.Value / 2.5
End Function

make the following call: =divide_by_2_5(D1) on D2 and then drag it until G2.

I think a UDF can only add a value to its calling cell only

Answer 3

Change the passed parameter to a Range variable.

Public Function divide_by_2_5(ByRef inputRange As Range) As Double()
    Dim output() As Double
    ReDim output(1 To inputRange.Rows.Count, 1 To inputRange.Columns.Count) As Double
    Dim r As Long
    Dim c As Long
    For r = 1 To inputRange.Rows.Count
        For c = 1 To inputRange.Columns.Count
            output(r, c) = inputRange.Cells(r, c).Value / 2.5
        Next
    Next
    divide_by_2_5 = output
End Function

Note: I was originally thinking I could just have a Variant array passed into the function, but was confused because I tested using a header of

Public Function divide_by_2_5(ByRef x As Variant) As Double()

instead of

Public Function divide_by_2_5(ByRef x() As Variant) As Double()

So the version I was testing wasn't accepting a Variant array, just a Variant containing a Range object. And then in my subsequent testing code, I was successfully accessing things like x(i), but that wasn't returning the i-th element of the Variant array - it was just returning the i-th cell of the Range.