This question may be of use for many VBA programmers. It involves implementing two useful independent tasks and making they work concomitantly.
The first task is making an Excel function tooltip for an UDF. Though a definitive solution seems to not have yet been found, for now I am satisfied with the solution of customizing the Insert Function Wizard. You can find the topic regarding the implementation of a way to customize the Insert Function Wizard here: How to put a tooltip on a user-defined function When I say Insert Function Wizard, I mean this window:
If you are also interested in the topic regarding the quest for finding a definitive solution for the implementation of a function tooltip, you can go here: The quest for the Excel custom function tooltip
The second task is making a UDF write in a different cell from which it was called. I have found a great solution for this task here: I don't want my Excel Add-In to return an array (instead I need a UDF to change other cells)
Now, here comes the problem: Excel crashes when trying to implement these two tasks concomitantly. I want to make these two tasks work when calling a single function, preventing excel from crashing. I want to use a customized Insert Function Wizard for an UDF that can write in a different cell from which it was called. The reason for why I want to do this is that I am writing an AddIn with functions that take several input parameters (so the user need a tooltip for the input parameters) AND I need to write in cells different from the cell which they were called (because I do not want to stick to Macros. I want a function-driven AddIn and not a button-driven one). For those familiar with the Bloomberg Excel API, this is pretty much what the function BDH() does.
I wrote two modules as a guideline for the problem. The first one builds a displayParameters() function that needs to be run before running the main function. It executes through a function-driven way the task of customizing the Insert Function Wizard. The second function is the main function called sumTwoNumbers, which executes the sum of two numbers and displays the result in a cell different from the cell which the function was called. When you try to run the second function ( sumTwoNumbers() ) using the Insert Function Wizard ( ctr + A ), after it was customized ( after you have run displayParameters() ), Excel will crash.
Module 1:
Option Explicit
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 displayParameters() As Variant
' This is a UDF that returns true when the volscore file is created 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.
displayParameters = "Success"
'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_displayParameters)
End Function
Public Sub AfterUDFRoutine1_displayParameters()
' 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_displayParameters", , False
On Error GoTo 0
End If
' Schedule timer
mApplicationTimerTime = Now
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2_displayParameters"
End Sub
Public Sub AfterUDFRoutine2_displayParameters()
' 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 sumTwoNumbersArgumentsDescription(1 To 2) As String
sumTwoNumbersArgumentsDescription(1) = "Write the first number of a Sum"
sumTwoNumbersArgumentsDescription(2) = "Write the second number of a Sum"
Application.MacroOptions Macro:="sumTwoNumbers", _
ArgumentDescriptions:=sumTwoNumbersArgumentsDescription
'Description:="describre the ivol function"
MsgBox ("The formal parameters and instance of actual parameters are now successfully displayed at the Insert Function Dialog Box.")
End Sub
Module 2:
Option Explicit
'This global variable is the way I found of passing the output of sumTwoNumbers into the
'function "AfterUDFRoutine2"
Dim outputGlobal As Variant
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
'Non-Volatile Function2
Public Function sumTwoNumbers(Optional ByVal param1 As Integer _
, Optional ByVal param2 As Integer _
) As Variant
sumTwoNumbers = param1 + param2
outputGlobal = sumTwoNumbers
sumTwoNumbers = "Success"
'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_sumTwoNumbers)
End Function
Public Sub AfterUDFRoutine1_sumTwoNumbers()
' 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_sumTwoNumbers", , False
On Error GoTo 0
End If
' Schedule timer
mApplicationTimerTime = Now
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2_sumTwoNumbers"
End Sub
Public Sub AfterUDFRoutine2_sumTwoNumbers()
' 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).
'Write the output to the sheet
Dim dest
Set dest = ActiveCell.Offset(0, 1)
dest.Value = outputGlobal
Set outputGlobal = Nothing
End Sub
Progress so far
I have found that the Insert Function Wizard runs your function in the background after each time you fill an input parameter, and outputs the result after the '=' you see in the Wizard. Therefore, if there is an error that is triggered when you run your function with an insufficient number of parameters, that error will also be triggered after you provide the input in the Wizard. If a message box is displayed when you run your function with that number of inputs, a message box will be displayed in front of the Wizard. However, when you are using an UDF modified in order to write the output in a different cell from which it was called and no error is triggered when filling the inputs by the Wizard, excel will break. My guess is that this happens because your function runs in the background, triggering AfterUDFRoutine1_sumTwoNumbers(), then triggering AfterUDFRoutine2_sumTwoNumbers(). When AfterUDFRoutine2_sumTwoNumbers() finally tries to write in the excel Spreadsheet and the Wizard is open, excel breaks because you cannot write in a cell with the Wizard open. One obvious solution for this problem is finding a way to make the Insert Function Wizard stops running the function in the background after each input parameter is provided and make it wait till run the function after you click "OK".
