Event Sub from run Function Event

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