Transform a range and return it from a UDF

Question

I am trying to write a very simple UDF which will take an input range and return another range. Each cell of the output range should be equal to the input range cell plus 1. Here is what I have

Public Function Addone(rng As Range) As Range

    Dim i As Integer, N As Integer
    N = rng.Count

    For i = 1 To N
        Addone.cells(1,i) = rng.cells(1,i) + 1
    Next i

End Function

Assume that the range "A1:C1" contains the numbers 1,2,3. I then go to the worksheet, select a range of cells (the output range, "A2:C2"), and then input Addone(A1:C1) and press Ctrl + Shift + Enter. The output cells, however, are filled with #VALUE.

I have checked other answers here and here, but could not make it work for my case. Any help is appreciated.

MrC MrC · Accepted Answer · 2017-05-26T12:27:57

You can not define the output as range and evaluate it in a cell have to change the output type to double array. Inside function define a temporary array, redefine its size basis N and then assign to the returning array. Following should do. Let me know if this solves.

Public Function Addone2(rng As Range) As Double()

    Dim temp() As Double
    Dim i As Integer, N As Integer
    N = rng.Count
    ReDim temp(1 To N) As Double
    For i = 1 To N
        temp(i) = rng.Cells(1, i) + 1
    Next i
    Addone2 = temp
End Function

Transform a range and return it from a UDF

1 Answers