The quest for the Excel custom function tooltip
I've posted a proof-of-concept project to GitHub as the Excel-DNA IntelliSense project, implementing this.
Using the UI Automation classes to monitor the appropriate Excel user interface events, a form is displayed when appropriate.
The code is wrapped as an Excel-DNA add-in, and works on my Excel 2013 / Windows 8 machine. I've tested on one other configuration (64-bit Excel 2010 on Windows Server 2008) and had a serious problems.
For a C# function defined with the Excel-DNA attributes like this:
[ExcelFunction(Description =
"A useful test function that adds two numbers, and returns the sum.")]
public static double AddThem(
[ExcelArgument(Name = "Augend",
Description = "is the first number, to which will be added")]
double v1,
[ExcelArgument(Name = "Addend",
Description = "is the second number that will be added")]
double v2)
{
return v1 + v2;
}
we get both the function description
and when selecting the function, we get argument help
That looks nice, but it's all still very flaky, only works on my machine and sometimes crashes Excel. It might be a start, though...
Update 9 May 2014:
I've made some progress figuring out how to make the argument help work under older Excel and Windows versions. However, it still needs quite a lot of work to get everything reliable. Anyone who would like to help with this should please contact me directly.
Update 18 June 2016:
Excel UDF IntelliSense support for both Excel-DNA add-ins and VBA functions is now being tested. See the Getting Started page on GitHub for instructions.
How to put a tooltip on a user-defined function
Professional Excel Development by
Stephen Bullen describes how to
register UDFs, which allows a
description to appear in the Function
Arguments dialog:
Function IFERROR(ByRef ToEvaluate As Variant, ByRef Default As Variant) As Variant
If IsError(ToEvaluate) Then
IFERROR = Default
Else
IFERROR = ToEvaluate
End If
End Function
Sub RegisterUDF()
Dim s As String
s = "Provides a shortcut replacement for the common worksheet construct" & vbLf _
& "IF(ISERROR(<expression>), <default>, <expression>)"
Application.MacroOptions macro:="IFERROR", Description:=s, Category:=9
End Sub
Sub UnregisterUDF()
Application.MacroOptions Macro:="IFERROR", Description:=Empty, Category:=Empty
End Sub
From: http://www.ozgrid.com/forum/showthread.php?t=78123&page=1
To show the Function Arguments dialog, type the function name and press CtrlA. Alternatively, click the "fx" symbol in the formula bar:
Provide contextual help for users on custom functions in Excel formulas
You can press Ctl+Shift+A
after typing the function name and it will present the parameter names in the formula bar. If you want an actual tooltip to display, check out this post.
Excel custom function from Power Query
You can't use a Power Query function outside of the query editor, at least not directly in an Excel formula. You can set a table of inputs and run it through a query to get a table of outputs but that requires refreshing the query every time you change the inputs.
How to VBA show tooltip for UDF in runtime?
Unfortunately this isn't possible. Your best option is to use
Application.MacroOptions()
This method enables you to provide descriptions, help etc. when your UDF is added through the Function Wizard.
Related Topics
Sharing Violation Ioexception While Reading and Writing to File C#
Convert Transparent Png to Jpg with Non-Black Background Color
What's Wrong with These Parameters
Add a Package with a Local Package File in 'Dotnet'
Why Folderbrowserdialog Dialog Does Not Scroll to Selected Folder
Why Is There Not a 'Fieldof' or 'Methodof' Operator in C#
An Expression Tree May Not Contain a Call or Invocation That Uses Optional Arguments
Is [Callermembername] Slow Compared to Alternatives When Implementing Inotifypropertychanged
The Application Called an Interface That Was Marshalled for a Different Thread - Windows Store App
How to Run Something in the Sta Thread
Validating for Large Files Upon Upload
Adding or Subtracting Color from an Image in a Picturebox Using C#
Build Visual Studio Project Through the Command Line
Asynchronously Sending Emails in C#
Performance of "Direct" Virtual Call VS. Interface Call in C#