Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs

For several years people have been asking the Excel Dev team to add the capability to create Intellisense for UDFs to work the same way as native Excel functions. Well, now Govert van Drimmelen, author of the wonderful Excel DNA, has made a solution available for both VBA and XLL UDFs. It is now available in public Beta test.

Entering a Function

Lets walk through an example of using a function that has had Intellisense enabled by Govert’s method. As you start typing the name of the function you get a list of functions and an explanation of the function:

intellisense1

Double-clicking the selected function starts entering the function in the formula bar and gives you an additional explanation of the first parameter:

intellisense2

Selecting the name of the function in the Intellisense popup shows a blue link if Help has been enabled for the function:

intellisense3

Clicking the link shows you help:

intellisense4

Pressing Control-A invokes the Function Wizard:

intellisense6

Or pressing Control-Shift-A fills the function in the formula bar and you can double-click each parameter to get text describing the parameter.

intellisense5

Implementing IntelliSense using Govert’s Method

It is really simple to implement this: see Govert’s Excel-DNA Intellisense GitHub page.

For VBA workbooks or add-ins:

  • Download and load the latest ExcelDna.IntelliSense.xll or ExcelDna.IntelliSense64.xll from the Releases page.
  • Either add a sheet with the IntelliSense function descriptions, or a separate xml file

For my example I added a worksheet called _IntelliSense_ with the descriptions:

intellisense7

Note: DNA Intellisense does not itself enable the descriptions in the Function Wizard or build the Help text for you.

Support

At the moment ExcelDNA Intellisense works with Excel 2010 and later versions, Windows 7 and later versions.

You can log issues on the Github site and Govert is very responsive.

Conclusion:

I think Govert has done a fantastic job with this.

Please help by testing this solution so that it can progress beyond Beta test.

 

 

 

 

 

 

 

 

 

Advertisements
This entry was posted in .NET, UDF, VBA, XLL and tagged , , , , . Bookmark the permalink.

10 Responses to Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs

  1. kalx says:

    Govert’s work is nothing short of amazing. I’ve had the same experience as you with my add-in library. Everyone asks how to add Intellisense. Now we can..

  2. Pingback: Excel UDF IntelliSense for Excel-DNA and VBA | Excel-DNA

  3. Irina says:

    Hello there!
    While searching for a way to add tooltips to my VBA UDFs I came across your article and I guess this is exactly what I was looking for. Thank you so much!
    The only problem is that it does not appear that easy for me to implement the Govert’s method as I’m a newbie in VBA and even the detailed instructions provided in the User guide on the GitHub seem confusing rather than helpful =(
    I’ve downloaded the latest ExcelDna.IntelliSense.xll, but don’t get how to open it and where to add the worksheet or a separate xml file with function descriptions to make the tooltips work.
    My VBA UDFs are in an add-in that I share with my colleagues so it is really crucial for me to get Intellisense for my UDFs work the same way as it does for native Excel functions especially taking into consideration the number of arguments I have in them…
    At the moment I’m also experiencing a problem registering on the GitHub to address my issue directly to Govert and thought that maybe you could explain me how to make everything work as you’ve already succeded doign that with your own VBA UDFs?
    Thank you so much in advance!

  4. fastexcel says:

    For VBA-based functions

    Suppose you have VBA code like this:

    Function MyVbaFunction(FirstArg, SndArg)
    MyVbaFunction = FirstArg + SndArg
    End Function

    Function MyOtherFunc()
    MyOtherFunc = 7
    End Function

    Create a new Worksheet with the name “_IntelliSense_”. (It can be made hidden later.)
    Fill in the worksheet as follows (the first cell must be exactly “FunctionInfo” and the other rows are the names and descriptions):

    A B C D E F G H
    1 FunctionInfo
    2 MyVbaFunction Returns the best answers http://www.google.com FirstArg The first Argument SndArg The Second Argument
    3 MyOtherFunc Also returns a value

    Download and open the ExcelDna.IntelliSense.xll add-in from the Releases page.
    Optionally install the IntelliSense add-in – press Alt+t, i in Excel to open the add-ins dialog.

    • Irina says:

      Yeah, that’s exactly how it should be done according to the instruction. My question is how do I create this new Worksheet with function descriptions to make ExcelDna.IntelliSense.xll add-in work? Because, for example, when I create a new Workbook with a Worksheet called “_IntelliSense_”, fill it in as follows with descriptions of VBA-based functions I’ve created in my separate add-in, nothing actually happens even though the IntellSence add-in is installed. Maybe I should create an “_IntelliSense_” worksheet from VBA in my personal fubctions add-in or put a workbook with an “_IntelliSense_” worksheet to the same directory where I have my personal functions add-in and “_IntelliSense_” add-in, idk.
      It seems like IntelliSense add-in needs a source to make the function description pop-up but it does not actually get one if I just create a new Workbook with an “_IntelliSense_” worksheet filled with function descriptions. When I try to open the IntelliSense add-in it does not ask me for such a source (no dialog appears). In fact, when I open the IntelliSense add-in nothing happens even though IntelliSense add-in is loaded (according to Add-Ins available box).
      For this reason and as long as my colleagues need to use these functions in any Workbook they open how should I create and where should I save this “_IntelliSense_” worksheet to make it available to IntelliSense add-in and to other users?
      Thank you

  5. fastexcel says:

    try putting the _intellisense_ worksheet in your addin that contains the UDFs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s