Custom User Function (UDF) and Dynamic Ranges

Question

I have a Custom User Function (UDF) that returns an array. I use it in an Array Formula on a large range of cells.
The length of the returned array is dependent on the function parameters. This works great except one thing: When the length of the returned array is smaller than the range defined for the array-formula, the "out-of-range" entries are all set to #N/A.

Is there a way to either obtain the array-formula range inside the custom user function (so, if needed, I could prepare a larger array to return), or alternatively return some kind of an iterator (instead of the array) which is not limited in size and would return "" in case of out-of-range?

Use ReDim Preserve to build your array as you feed it values. For an example you could refer to my answer here: Erase empty cells in an array. — user4039065
– user4039065, Commented Apr 29, 2015 at 15:54

Gary's Student · Accepted Answer · 2015-04-29 16:49:21Z

1

Here is a pretty dumb example......a UDF to return the first 7 primes in column form:

Public Function Primes()
'
'   Array UDF to return the first 7 primes
'
    Dim rN As Long, ary(1 To 7) As Long
    Dim tdim As Long, i As Long
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    rN = Application.Caller.Rows.Count
    tdim = wf.Max(rN, 7)
    ReDim bry(1 To tdim, 1 To 1)

    ary(1) = 1
    ary(2) = 3
    ary(3) = 5
    ary(4) = 7
    ary(5) = 11
    ary(6) = 13
    ary(7) = 17

    For i = 1 To 7
        bry(i, 1) = ary(i)
    Next i

    If tdim > 7 Then
        For i = 8 To tdim
            bry(i, 1) = ""
        Next i
    End If
    Primes = bry
End Function

The UDF detects how many cells it has to fill and if that value exceeds 7, the balance is filled with blanks.

answered Apr 29, 2015 at 16:49

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amir Gonnen Over a year ago

Perfect. All I needed was Application.Caller.Rows.Count.

Collectives™ on Stack Overflow

Custom User Function (UDF) and Dynamic Ranges

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related