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:
Double-clicking the selected function starts entering the function in the formula bar and gives you an additional explanation of the first parameter:
Selecting the name of the function in the Intellisense popup shows a blue link if Help has been enabled for the function:
Clicking the link shows you help:
Pressing Control-A invokes the Function Wizard:
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.
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:
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.
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..
Awesome!
Pingback: Excel UDF IntelliSense for Excel-DNA and VBA | Excel-DNA
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!
Treat the intellisense XLL as an addin (which it is!) – you can load it using the Excel addin manager. Or you can load it from VBA using Application.RegisterXLL
I would post your questions on the Excel DNA Google group
https://groups.google.com/forum/#!forum/exceldna
Thats the main support place for EXcel DNA
Thank you. Now that I have it in my addins what should I do next? how do I make a source with function descriptions?
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.
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
try putting the _intellisense_ worksheet in your addin that contains the UDFs
Thank you very much! It’s finally working=)
Oh, OK. It’s finally working after I restarted the excel application.
Hello FastExcel. can you explain for me(beginner Vb) about dot between reverse.text (how you can type it?)
I always use the Application.MacroOptions method to describe my UDFs. It provides similar results but does not require a separate _Intellisense_ worksheet and related XLL. Toggle the IsAddin property False and apply the Application.MacroOptions method once for each UDF, then toggle the IsAddin property True and save the workbook as an Add-in. The information is retained in the Add-in.
If someone reading this has used both Govert’s method and the Application.MacroOptions method, please describe the Pros and Cons for comparison.
By the way, you can review my use of Application.MacroOptions at https://sites.google.com/view/MyExcelToolbox/
Govert’s method provides Intellisense but MacroOptions does not.( MacroOptions only supports the function wizard)
Govert’s method also supports XLL UDFs but MacroOptions only supports VBA UDFs (XLL functions natively support the Function Wizard)
Charles: Thanks for that explanation.
I’ve enjoyed reading many of your older blog posts. I tried twice to add a comment to https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/#comment-5081
But my attempts were not successful. Are comments limited to newer posts? If so, what is the cutoff date?
There are no cutoff dates for comments …
Charles: Since there are no cutoff dates for comments, there must be another problem. I’ve tried three times now over the past two days to add a comment to https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/#comment-5081
Twice I used the Reply link next to that earlier comment dated March 6, 2018. Once I tried adding a new comment to the end. No joy. I used my WordPress account each time, same as now. Sorry to trouble you. It’s not very important, but you might want to investigate.
It must have been a cache or cookie problem. I was finally able to enter the comment using a different computer.
This sounded very promising – as I was hoping to add Intellisense to my VBA UDF in Excel. I’ve followed all the steps – but no joy. It just doesn’t do anything. It doesn’t behave any differently than before following the steps. I’ve added the Add-in (under Insert – My Add-ins – Other Add-ins), I’ve created the _Intellisense_ tab, I’ve filled it in with the required info, restarted Excel – but – nothing. ???