This question may be useful for many VBA programmers. It includes the implementation of two useful independent tasks and the simultaneous execution of their work.
The first task is to create a tooltip for Excel function for UDF. Although the final solution does not seem to have been found yet, at the moment I am satisfied with the decision to configure the Insert function wizard. You can find a topic regarding the implementation of the Insert Function Wizard configuration method here: How to put a tooltip in a user-defined function
When I say "Insert Function Wizard", I mean this window:
, , : Excel
- UDF , . : , Excel ( UDF )
: Excel . , , excel. UDF, , . , , , AddIn , ( ) , , ( . AddIn, ). , API- Bloomberg Excel, , BDH().
. displayParameters(), . , , Insert. - sumTwoNumbers, , , . (sumTwoNumbers()) Insert Function Wizard (ctr + A), , ( displayParameters()), Excel .
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 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
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 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
, Insert Function Wizard "=", . , , , . , . , UDF, , , , excel . , , , AfterUDFRoutine1_sumTwoNumbers(), AfterUDFRoutine2_sumTwoNumbers(). AfterUDFRoutine2_sumTwoNumbers(), , Excel , , excel breaks, Wizard. Insert Function , "".