How Can Non-Array Changing UDFs be Implemented in Excel Add-Ins?

Implementing Non-Array Changing UDFs in Excel Add-Ins

In Excel Add-Ins, UDFs that change cell values are generally prohibited. However, it is possible to bypass this restriction using a combination of Windows timers and Application.OnTime timers.

Windows Timer Limitation

Windows timers cannot execute VBA code while cells are being edited or dialogs are open. This limitation prevents them from directly modifying cell properties within UDFs.

Solution: Application.OnTime Timer

To circumvent this limitation, a Windows timer is used within the UDF to schedule an Application.OnTime timer. Application.OnTime timers are safe and are only fired when a cell is not being edited and no dialogs are open.

UDF Implementation

1. Create a regular module: Place the following code in a regular VBA module, not a UDF module.

2. UDF Implementation (AddTwoNumbers): Within the AddTwoNumbers UDF, calculate the sum as usual. After the calculation:

   • Cache the caller's cell reference in a Collection.
   • Set a Windows timer to start the timer routine AfterUDFRoutine1.

3. First Timer Routine (AfterUDFRoutine1):

   • Stop the Windows timer.
   • Schedule the safe Application.OnTime timer for AfterUDFRoutine2.

4. Second Timer Routine (AfterUDFRoutine2):

   • Perform tasks that are not allowed in UDFs, such as modifying cell values, disabling screen updating, or changing calculation mode.

Example Usage

Private Sub TestAddTwoNumbers()
    Dim Cell As Range

    Set Cell = Range("A1")

    ' Cache the reference in the UDF
    Cell.Formula = "=AddTwoNumbers(1, 2)"

    ' Trigger the timer routine
    Cell.Calculate
End Sub

Upon calculation, the Cell formulas will be changed to reflect the new value, by setting the cell next to the caller to their current value.

The above is the detailed content of How Can Non-Array Changing UDFs be Implemented in Excel Add-Ins?. For more information, please follow other related articles on the PHP Chinese website!