I don't want my Excel Add-In to return an array (instead I need a UDF to change other cells)

Question

I've created an Excel Add-In, and one of the functions of this Add-In, lets say New_Years currently takes in 2 years and outputs every New Years day between those 2 years as an array in Excel. So New_Years(2000,2002) would return Jan 1st 2000, Jan 1st 2001, and Jan 1st 2002 in the last cell.

The problem is that I have to know there are going to be 3 dates in that time, select 3 cells, enter my formula in the top cell, and then hit Ctrl + Shift + Enter to fill out the array.

I use XLW version 5 to convert my C++ code to an .xll file. I would really like it if there was some way I could just fill in one square with my formula, and Excel would fill in the squares below as needed with the appropriate dates. Anyone know if this is possible? Or impossible?

Many thanks!

You may try this one example showing how to use UDF without limitations. — omegastripes
– omegastripes, Commented Apr 23, 2014 at 11:31

Greedo · Accepted Answer · 2018-04-10 12:09:11Z

19

It is actually possible albeit complex. I am reposting this piece of magic from Kevin Jones aka Zorvek as it sits behind the EE Paywall (link attached if anyone has access)

While Excel strictly forbids a UDF from changing any cell, worksheet, or workbook properties, there is a way to effect such changes when a UDF is called using a Windows timer and an Application.OnTime timer in sequence. The Windows timer has to be used within the UDF because Excel ignores any Application.OnTime calls inside a UDF. But, because the Windows timer has limitations (Excel will instantly quit if a Windows timer tries to run VBA code if a cell is being edited or a dialog is open), it is used only to schedule an Application.OnTime timer, a safe timer which Excel only allows to be fired if a cell is not being edited and no dialogs are open.

The example code below illustrates how to start a Windows timer from inside a UDF, how to use that timer routine to start an Application.OnTime timer, and how to pass information known only to the UDF to subsequent timer-executed routines. The code below must 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's 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, i.e., 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

edited Apr 10, 2018 at 12:09

Greedo

5,6354 gold badges42 silver badges100 bronze badges

answered Jan 3, 2012 at 11:27

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

6 Comments

brettdj Over a year ago

@pnuts Yes. While it is complex, the blanket "it cant be done" is too often trotted out with much investigation.

brettdj Over a year ago

@gserg - yes it's an easier approach to implement.

vpprof Over a year ago

Thanks for pasting this technique here! Remember this is legacy code though, so before you use it in a 64-bit version, make sure you familiarize yourself with 64-bit environment pointers and handles or you might crash Excel. PtrSafe and LongPtr are a must and other changes might be necessary.

z32a7ul Over a year ago

What happens if the user starts to edit a cell when AfterUDFRoutine1 is running?

mike01010 Over a year ago

does this still work today? i get a crash after it sets Application.OnTime the first time

Alan Elston Oct 11 at 18:52

Does my simplified solution help?

Alan Elston · Accepted Answer · 2025-10-11 18:34:35Z

Herllo Derek,

I am a bit late but, I was researching stuff like this, so it could be useful to someone passing as I did a while back

I have not quite figured exactly what you (Derek) want/ wanted to do.

But I can give a very simple alternative coding to get a UDF to change other cells, directly in terms of simplicity, ( possibly very indirectly in terms of what is happening behind the scenes.: What is going on here is sometimes considered as VBA having some redirection and ended up a bit lost, or rather does not know where it came from ).

No guarantees, but it may be something for you or others to consider

_ First put all these codings in a normal module

 Option Explicit
' This is the main UDF, used by writing in a cell something of this form   =UDF_Where(E3:E5)
Function UDF_Where(ByVal Cels As Range) As String      ' Looking at this conventionally, a string is likely to be returned  by this function in the cell you put the UDF into
 Let UDF_Where = "This is cell " & ActiveCell.Address & ", where the UDF is in" ' Conventional use of UDF to change value of the cell that it is in
Worksheets("Derek").Evaluate Name:="OverProc(" & Cels.Address & ")"             ' Unconventional use of a UDF to change other cells    ' The  Evaluate(" ")  thing takes the syntax of  Excel spreadsheet   So I need this sort of thing   Cels.Address   to give me the  string bit like  $D$2
End Function


Sub OverProc(Cels As Range) ' This can be a  Sub  or  Function
Dim SteerCel As Range
    For Each SteerCel In Cels
     Let SteerCel = "This is cell " & SteerCel.Address & ", from the range I passed my UDF (" & Cels.Address & ")"
    Next SteerCel
 ActiveCell.Offset(10, 0) = "This cell is 10 rows down from where my UDF is"
End Sub

( You will need to name a worksheet "Derek"., (That is not a general requirement but just ties up with the demo coding above and in the uploaded workbook) )

_ Now, In the worksheet named "Derek", type in any cell, for example D2, the following

=UDF_Where(E3:E5)

, then hit Enter

You should see these results

Alan

‘StackOverflowUDFChangeOtherCells.xls’ https://app.box.com/s/knpm51iolgr1pu3ek2j96rju8aifu4ow

cdafun · Accepted Answer · 2020-09-12 08:20:42Z

Thanks to the earlier post on this page, I was able to create a sort of easy to use module.



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' POST UDF UPDATING MODULE                                   v.9.2020 cdf
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Since Excel won't allow UDFs to update any other cells,
'   an API timer is used to trigger a post UDF subroutine.
'
' This acts like a recalculate.  Your code should recalculate the entire
'   sheet.  Tried to get it to work with a specific range, but, with all
'   the different update variations ({Enter}, {Tab}, {Backspace}, {Delete},
'   mouse click in the middle of an update, etc.), no luck. Any ideas?
'
' Originally, before the slight tweak, the code was found here:
'   https://stackoverflow.com/questions/8520732/
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                             HOW TO USE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Create a module (in the Modules folder) and copy this entire document 
'   into it.
'
'
' Code this code into your UDF's subroutine in your module (in Modules
'   folder) (should be close to the end of the code):
'
'     ' Use Post UDF Timer to update other cells.
'     Call TimerModulesName.SetAPITimer("UDFModule.PostUDFRoutine")
'
'
' Edit the code names:
'
'     Change TimerModulesName to the module you copied this document into.
'            UDFModule        to the module your post UDF subroutine is in.
'            PostUDFRoutine   to your post UDF subroutine.
'
'     Example: Module4.SetAPITimer("Module1.UpdateMyCells")
'
'

#If VBA7 Then

Private Declare PtrSafe Function SetTimer Lib "user32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long, _
      ByVal uElapse As Long, _
      ByVal lpTimerFunc As LongLong _
   ) As Long

Private Declare PtrSafe Function KillTimer Lib "user32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long _
   ) As Long
   
#Else

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
   
#End If

Private mCalculatedCells As Collection
Private mWindowsTimerID As Long
Private mApplicationTimerTime As Date
Private mRoutine As String

Public Sub SetAPITimer(sRoutine As String)
    
' 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.

    ' Cache the caller's 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)

    ' Set Post UDF module and routine
    mRoutine = sRoutine
End Sub

Private 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

Private Sub AfterUDFRoutine2()

' This is the second of two timer routines. Because this timer routine is
' triggered by Application.OnTime it is safe, i.e., Excel will not allow the
' timer to fire unless the environment is safe (no open model dialogs or cell
' being edited).

    ' Do tasks not allowed in a UDF... (post UDF code)
    Application.Run mRoutine
End Sub

Application.OnTime fails in AfterUDFRoutine1 if its triggered while excel doesnt have focus. im not sure why and how to solve.

Collectives™ on Stack Overflow

I don't want my Excel Add-In to return an array (instead I need a UDF to change other cells)

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related