Incorrect result in excel array formula from UDF if returned array has only one element

Question

This seems a simple issue, but i cannot get through on it.

In my UDF the result is a dynamic array which is spill on a range of cells. Excel is not 365, so i select an undefined number of cells and then check the result where the values are not #N/A, and "resize" the result on it.

The issue is that if the returned array's length is only one, than all the other cells in the selected cells get the same result as the one (first cell) with the returned value. Would need #N/A in all the other except the first cell. Definitely need error value in "over the range cells"

Function testoneresult(length As Integer)

ReDim a(length - 1)
For i = 0 To length - 1
  a(i) = Rnd()
Next i
testoneresult = a
End Function

Result

I tried some tricks but not works

Dim a(length-1,0) or

b=a(0) testoneresult=b or

dim a(length) (in this case the second value is 0.) or

a(length)=1/0 (all cells #VALUE (expected))

Is this a version dependent result, and is there a workaround to solve this?

Answer 1

You can use Application.Caller to determine what range your function is being applied to. Application.Caller is not necessarily going to be a range every time so it needs to be tested but other than that, it is very easy to manage your case:

Function testoneresult(ByVal length As Integer) As Variant
    Dim a As Variant
    If TypeName(Application.Caller) = "Range" Then
        ReDim a(1 To Application.Caller.Columns.Count)
        If length > Application.Caller.Columns.Count Then length = Application.Caller.Columns.Count
    Else
        ReDim a(1 To length)
    End If
    Dim i As Long
    For i = LBound(a) To length
      a(i) = Rnd()
    Next i
    For i = 1 + length To UBound(a)
        a(i) = CVErr(xlErrNA)
    Next i
    testoneresult = a
End Function

Note: If you were to type the formula =testoneresult(ROW()), you will see that it does not work.
The reason for that is that ROW() returns a 1-column array when used in an array formula. To have a proper integer, you need to type e.g. =testoneresult(MIN(ROW()).

Alternatively, you could change your parameter to be length as Variant, detect whether it comes as an integer or as an array and, in case of the latter, return a 2-dimensional array with 1 row per items inside length.