107

This question has been asked before, but each time the accepted answer is simply a resignation to provide function descriptions using Application.MacroOptions (VBA6) (VBA7), but this information does not actually appear as a tooltip, so it does not solve my problem.

The Goal

What we all desire is to be able to define custom functions by any means (VBA, VSTO, or COM add-in) and give the user the benefit of a pop-up/tool-tip description of the function and its parameters, as appears for every built-in Excel function, either inline or in the formula bar:

enter image description here

enter image description here

The widely accepted answer to this need is that it is not possible for custom functions, but I wish to challenge that belief.

The Problem

Currently, the best I've seen anyone do is to define functions (often using the above MacroOptions call) so that when bringing up the function dialog (the fx button in the formula bar) their function and parameter descriptions appear as below:

enter image description here

As you can see, this is a complicated function with many parameters. If the user is not aware of this "function arguments" dialogue and how to bring it up, and are instead familiar only with Excel's standard tooltip, they will only see the formula name and no additional help:

enter image description here

With which they have no chance of correctly providing the required parameters. (Without reading the documentation, which of course, no user ever does.)

Now, a power user might know that by typing Ctrl+Shift+A, they will be granted a auto-completed list of function parameters like so:

enter image description here

But of course, we have the same problem as above which is that standard excel users will only be used to the default behavior from the first image and will probably never have learned that feature.

It should be clear by this point why this isn't enough and we want what every built in function has - the in-line tool-tip that tells the user how to use the function.

The Tease

At first, I might have been convinced that this simply isn't possible except with the native Excel application functions. Add-ins and VBA are extensibility features, and this tool-tip may simply not be extensible. But that theory is challenged by the existence of the Analysis Toolpak add-in. Sure, it's built in Microsoft, but ANALYS32.xll is a standalone XLL add-in just like those that can be produced in VB, C, C++, and C#. Sure enough, when this XLL is loaded into the application the functions that it makes available have the same tool-tips of native excel functions:

enter image description here

Surely if this information is somehow encoded in this XLL file and passed on to Excel, there is a way to replicate it with our own Add-ins? I'm at the point now where I'm going to start teaching myself a little about decompiling and seeing if I can reverse-engineer whatever is going on in the analysis toolpak.

How You Can Help

I'm almost certain that I've researched all the publicly available information there is about this problem. If someone knows something I don't know that might help with this though, feel free to chime in. I am very unfamiliar with reverse-engineering compiled dlls / xlls, so if someone feels like popping open their local copy of Analysis32.xll and figuring out what's going on with it's custom function definitions, I would be much obliged. Otherwise, I'll just keep digging into this myself until I've hit all the dead ends and report back what I find.

Improve this question
13
  • 4
    It is not possible to create or show tooltips for UDF arguments in current Excel versions using any of the available technologies. Source by MVP: answers.microsoft.com/en-us/office/forum/office_2007-customize/… Commented Jun 3, 2013 at 19:56
  • .NET Reflector couldn't open ANALYS32.xll, concluding that it isn't a .NET Assembly. This surprises me. Looks like I'm going to have to try to decompile it to assembly language? Commented Jun 3, 2013 at 20:45
  • 5
    Sounds interesting, but I have in the past (office 97) made tooltips that using the winapi. And in that way you can do it. If thats interesting I can dig up the info and try to make an anwer out of it. Commented Jun 4, 2013 at 13:52
  • 1
    I will get it too you, it will take some time digging this up. But it should get up here for others to use. Its low down and evil combo of winapi/vba/ and I think I made it as vb.dll but its no prob lifting it up to .net Commented Jun 5, 2013 at 6:27
  • 1
    Apparently the Excel development team is taking votes for this feature here: excel.uservoice.com/forums/… Commented Nov 15, 2018 at 17:39

2 Answers 2

Reset to default
44
+75

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 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,

Function Description

and when selecting the function, we get argument help.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

Sadly, it does seem like Excel 2013 or Windows 8 or both are required for the UI Automation TextRange support that the argument help uses. I'd love to hear from anyone who has some Windows UI Automation experience ...
Did you try to reference older Excel assemblies before compiling to older machines?
References to the Excel object model are not really involved in this. But there are certainly different UI Automation versions which might affect things...
This is streets ahead of anything out there so far, so you get my vote for the bounty. I intend to see if I can contribute to this solution in the near future so keep an eye out for my pull request!
@M-- You might have a look here: github.com/Excel-DNA/IntelliSense/wiki/…
|
2

How about

  1. Capture inputting text on key press event of cell. like this
  2. Check if the text matches with custom functions.
  3. If matches, then show something like label or shape to pretend be a tooltip. like this

Is this acceptable?

It is a little ugly but easier.

Improve this answer

1 Comment

This is an interesting starting point that I considered, but ran into complications when more advanced forms of editing a formula are encountered (such as clicking somewhere in the middle and deleting/adding characters, or highlighting and overwriting a part of the formula, or using a custom formula in the middle of some other formula.) These require more than checking key-presses, we'd need to be looking at the current contents of the formula bar or cell in mid-edit. This too might be accomplished with some imported dll libraries and handle getting hackery. Certainly worth exploring.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.