Event Sub from run Function Event

I'm not sure what you are after.
Do you want to run some event code as part of a UDF (is findnum a UDF)?

Or do you want this UDF to modify some event code.

A UDF is a User Defined function.

You would normally use one on a worksheet, but unfortunately there are no events directly associated with one.

What would you want the event 'sub' do?

Like I said there's no event associated with a UDF and a UDF will not directly trigger any other event.

The usual way to return multiple values to multiple cells is to use an array (CSE) function.

Did the code for that you were given in the other thread not work?

Looks like the same issue being discussed here: http://www.excelforum.com/excel-prog...-function.html Mikerickson has provided an array UDF that, I think, should be what the OP is asking for, once he/she understands how to enter and use an array UDF.

Where exactly are you using the function?

PS What's wrong with an elegant solution?

Another work-around to let a UDF modify cells is to have it pass information to a Collection. The Calculate event will then change the cells as indicated.

Here's an example. Put =ColorACell(A1:A3,D4) in a cell and watch A1:A3 change color as you put different numbers in D4.

' in a normal module

Function ColorACell(RangeToColor As Range, ColorForRange As Long) As Boolean
    ColorACell = True
    On Error Resume Next
    ThisWorkbook.CellsToColor.Add Item:=RangeToColor, Key:=RangeToColor.Address(, , , True)
    ThisWorkbook.ColorsForCells.Add Item:=ColorForRange, Key:=RangeToColor.Address(, , , True)
    On Error Goto 0
End Function

'in ThisWorkbook Code module

Public CellsToColor As New Collection
Public ColorsForCells As New Collection

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim oneRange As Range
    On Error Resume Next
    For Each oneRange In CellsToColor
        With oneRange
            .Interior.ColorIndex = ColorsForCells(oneRange.Address(, , , True))
        End With
    Next oneRange
    On Error Goto 0 

    Set CellsToColor = Nothing
    Set ColorsForCells = Nothing
End Sub

Is this mysterious function pulling data from a database, or some other data source?

The way that I would do it is

'in the UDF
test Application.Caller against the result array,
if it (the range Application.Caller) is the right size, return the array: DONE
if it is not
pass the current range (to be blanked)
pass the range of the correct size
pass the formula
End UDF

' in the Calculate event
Blank the current (wrong sized range)
put the formula in the correct sized range. This triggers the UDF again, which will go out the OK branch.

Here is an example. Note that this heavily assumes that the result is wanted in a column.
put =myArrayFtn(A1) in a cell (not in column A) and change the value of A1.
Then put =myArrayFtn(B1) in a different cell.

' in a normal module

Function myArrayFtn(N As Long) As Variant
    Dim i As Long
    Dim arrResult As Variant
    Dim keyAddress As String
    Dim resultSize As Long
    
    Rem get values for array
    ReDim arrResult(1 To N)
    For i = 1 To N
        arrResult(i) = i
    Next i
    
    Rem handle displaying the results
    resultSize = UBound(arrResult)

    If TypeName(Application.Caller) = "Range" Then

        If Application.Caller.Rows.Count <> resultSize Then
            Rem if array formula is wrong size, send info to collections for adjustment

            keyAddress = Application.Caller.Resize(resultSize, 1).Address(, , , True)
            
            With ThisWorkbook
                .CellsForFormula.Add Item:=Application.Caller.Resize(resultSize, 1), Key:=keyAddress
                .FormulaForCells.Add Item:=Application.Caller.FormulaArray, Key:=keyAddress
                .CellsToBlank.Add Item:=Application.Caller, Key:=keyAddress
            End With
            
            myArrayFtn = False
        Else
            Rem if called by array formula (of the correct size), transpose for column output
            myArrayFtn = Application.Transpose(arrResult)
        End If
    Else
        Rem if not called by worksheet formula
        myArrayFtn = arrResult
    End If

End Function

' in ThisWorkbook code module

Public CellsForFormula As New Collection
Public FormulaForCells As New Collection
Public CellsToBlank As New Collection

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim keyAddress As String
    Dim oneRange As Range
    For Each oneRange In Me.CellsForFormula
        keyAddress = oneRange.Address(, , , True)
        Me.CellsToBlank(keyAddress).ClearContents
        Me.CellsForFormula(keyAddress).FormulaArray = Me.FormulaForCells(keyAddress)
    Next oneRange
    With Me
        Set .CellsForFormula = Nothing
        Set .CellsToBlank = Nothing
        Set .FormulaForCells = Nothing
    End With
End Sub