Excel 2002 UDF screen tips

[Guest]

Greetings and TIA for your time.
When typing a standard excel function directly into a cell a screen tip is
displayed showing the function arguments. This is an excellent time saver
when using unfamiliar functions. The current argument to be typed is shown in
bold text. Is there any way to add this feature to User Defined Functions?

 

[Guest]

if you type =function then SHIFT+Ctrl+A the variables will be typed in
eg function abc(x a slong, y as long)....end function

=abc [SHIFT]+[Ctrl]+[A]
results in
=abc(x,y)

 

[Bob Phillips]

There are a couple of ways, one not too robust, one that requires a dll.


The not too robust way, suggested by Laurent Longre and working for two
parameters is :-
Const Lib = """c:\windows\system\user32.dl­­l"""
Option Base 1


Private Function Multiply(N1 As Double, N2 As Double) As Double
Multiply = N1 * N2
End Function


'=============================­­=============


Private Function Divide(N1 As Double, N2 As Double) As Double
Divide = N1 / N2
End Function


'=============================­­=============


Sub Auto_open()


Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
"Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
"Multiplies two numbers", """First number"",""Second number """, _
"CharNextA"


End Sub


'=============================­­=============


Sub Register(FunctionName As String, NbArgs As Integer, _
Args As String, MacroType As Integer, Category As String, _
Descr As String, DescrArgs As String, FLib As String)


Application.ExecuteExcel4Macro _
"REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
& """,""" & FunctionName & """,""" & Args & """," & MacroType _
& ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"


End Sub


'=============================­­=============


Sub Auto_close()


Dim FName, FLib
Dim I As Integer
FName = Array("DIVIDE", "MULTIPLY")
FLib = Array("CharPrevA", "CharNextA")
For I = 1 To 2
With Application
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & _
",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
End With
Next


End Sub


The dll solution:
Also from Laurent Longre, you use the FUNCUSTOMIZE utility .Get it at
http://longre.free.fr/english/