I don’t want my Excel add-in to return an array (instead, I need UDF to change other cells)

Question

I created an Excel add-in, and one of the functions of this add-in allows New_Years to say that New_Years currently takes 2 years and displays each New Year between these 2 years as an array in Excel. Thus, New_Years(2000,2002) will return on January 1, 2000, January 1, 2001, and January 1, 2002 in the last cell.

The problem is that I should know that at this time there will be 3 dates, select 3 cells, enter my formula in the upper cell and press Ctrl + Shift + Enter to fill the array.

I use XLW version 5 to convert my C ++ code to a .xll file. I would really like it if I could just fill in one square with my formula, and Excel would fill in the squares below as needed with the corresponding dates. Does anyone know if this is possible? Or impossible?

Thank you very much!

Answer 1

In fact, this is possible, although difficult. I am retelling this piece of magic from Kevin Jones aka Zorvek , as he sits behind EE Paywall (link attached if anyone has access)

While Excel strictly prohibits UDF from modifying any cell, worksheet, or workbook, there is a way to make such changes when the UDF is called using the Windows timer and Application.OnTime timer in sequence. The Windows timer must be used in UDF because Excel ignores any Application.OnTime calls inside UDF. But because the Windows timer has limitations (Excel will stop working immediately if the Windows Timer tries to run VBA code, if the cell is edited or the dialog is open), it is used only to schedule the Application.OnTime timer, a safe timer that Excel only allows to fire if the cell cannot be edited, and dialog boxes are not open.

The sample code below illustrates how to start the Windows timer from within the UDF, how to use this timer procedure to start the Application.OnTime Timer, and how to transfer information known only to UDF for subsequent routines executed by the timer. The code below should be placed in a regular module.

 Private Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As Long _ ) As Long Private Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long _ ) As Long Private mCalculatedCells As Collection Private mWindowsTimerID As Long Private mApplicationTimerTime As Date Public Function AddTwoNumbers( _ ByVal Value1 As Double, _ ByVal Value2 As Double _ ) As Double ' This is a UDF that returns the sum of two numbers and starts a windows timer ' that starts a second Appliction.OnTime timer that performs activities not ' allowed in a UDF. Do not make this UDF volatile, pass any volatile functions ' to it, or pass any cells containing volatile formulas/functions or ' uncontrolled looping will start. AddTwoNumbers = Value1 + Value2 ' Cache the caller reference so it can be dealt with in a non-UDF routine If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection On Error Resume Next mCalculatedCells.Add Application.Caller, Application.Caller.Address On Error GoTo 0 ' Setting/resetting the timer should be the last action taken in the UDF If mWindowsTimerID <> 0 Then KillTimer 0&, mWindowsTimerID mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1) End Function Public Sub AfterUDFRoutine1() ' This is the first of two timer routines. This one is called by the Windows ' timer. Since a Windows timer cannot run code if a cell is being edited or a ' dialog is open this routine schedules a second safe timer using ' Application.OnTime which is ignored in a UDF. ' Stop the Windows timer On Error Resume Next KillTimer 0&, mWindowsTimerID On Error GoTo 0 mWindowsTimerID = 0 ' Cancel any previous OnTime timers If mApplicationTimerTime <> 0 Then On Error Resume Next Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2", , False On Error GoTo 0 End If ' Schedule timer mApplicationTimerTime = Now Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2" End Sub Public Sub AfterUDFRoutine2() ' This is the second of two timer routines. Because this timer routine is ' triggered by Application.OnTime it is safe, ie, Excel will not allow the ' timer to fire unless the environment is safe (no open model dialogs or cell ' being edited). Dim Cell As Range ' Do tasks not allowed in a UDF... Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Do While mCalculatedCells.Count > 0 Set Cell = mCalculatedCells(1) mCalculatedCells.Remove 1 Cell.Offset(0, 1).Value = Cell.Value Loop Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub