Can You Modify Cells from a UDF Without Returning an Array in Excel?

Creating a UDF that Modifies Other Cells without Returning an Array

You have an Excel Add-In with a function called New_Years that currently returns an array of New Year's days between two specified years. However, this requires you to select multiple cells and use Ctrl Shift Enter to create the array.

Is it Possible to Avoid Array Creation?

Yes, it is possible to modify other cells directly from a UDF without returning an array. While Excel prohibits UDFs from making any changes to cell properties, there is a complex solution that involves using a combination of Windows timers and Application.OnTime timers.

Solution Overview

1. Create a Windows timer within the UDF.
2. The Windows timer schedules an Application.OnTime timer.
3. The Application.OnTime timer safely executes code outside the UDF.

Code Implementation

The following code 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

   AddTwoNumbers = Value1 + Value2

   ' Cache the caller's reference for later use 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

   ' Set the Windows timer
   If mWindowsTimerID <> 0 Then KillTimer 0&, mWindowsTimerID
   mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1)

End Function

Timer Routines

Two timer routines are defined: AfterUDFRoutine1 and AfterUDFRoutine2. They handle the scheduling and execution of code outside the UDF.

Example Usage

In your Excel worksheet, enter the AddTwoNumbers function in a cell: =AddTwoNumbers(A1, B1)

This will initiate the timer process.

Important Notes

• Do not make the UDF volatile or pass volatile functions/cells to it to avoid uncontrolled looping.
• The solution is complex and may not be suitable for all applications.

The above is the detailed content of Can You Modify Cells from a UDF Without Returning an Array in Excel?. For more information, please follow other related articles on the PHP Chinese website!