-
Notifications
You must be signed in to change notification settings - Fork 52
Getting Started
The Excel-DNA IntelliSense extension provides in-sheet IntelliSense for user-defined functions created in Excel-DNA add-ins, VBA Workbooks and .xla add-ins, and other native code .xll add-ins.
To display IntelliSense descriptions for your functions, you need to consider two aspects:
- The function description source, and
- the IntelliSense display server.
The following function description sources are supported:
- Method attributes in Excel-DNA add-ins (v0.32 and later). Both
[ExcelFunction]/[ExcelArgument]and[Description]attributes are supported. - A special Worksheet (possibly hidden) added to an open Excel Workbook or .xla / .xlam add-in.
- A Custom XML part in an open Workbook.
- An external .xml file, matching the name of an open Workbook, .xla / .xlam or .xll add-in.
The IntelliSense display server can be:
- Hosted in a separate add-in (ExcelDna.IntelliSense.xll), or
- embedded in some Excel-DNA based add-in.
If multiple display servers are loaded (e.g. if different Excel-DNA based add-ins have the display server embedded), then only one display server will be active. Which display server is active will depend on the load sequence and versions, ensuring that the active display server is the newest version loaded.
Every add-in or Workbook can provide function descriptions. The IntelliSense extensions supports a variety of sources for the function descriptions.
Function descriptions from Excel-DNA add-ins (based on Excel-DNA v 0.32 or later) are automatically supported by the IntelliSense extensions. These descriptions were already displayed in the Function Arguments dialog (function wizard) and will automatically be displayed on-sheet when an IntelliSense display server is loaded (see below).
Attributes that are read by Excel-DNA are the [ExcelFunction / [ExcelArgument] attributes, as well as [Description] attributes on the function and arguments.
A small VB.NET example would be:
Imports ExcelDna.Integration
Public Module MyFunctions
<ExcelFunction(Description:="is a function that adds two numbers",
HelpTopic:="http://lmgtfy.com/?q=Addition")>
Function AddNumbers(
<ExcelArgument(Description:="First number")> number1 As Double,
<ExcelArgument(Description:="Second number")> number2 As Double)
AddNumbers = number1 + number2
End Function
End ModuleAny open Workbook (including loaded .xla / .xlam add-ins) can provide function descriptions in a Worksheet (possibly hidden). The Woksheet should:
- be called "IntelliSense" (not the underscore at the beginning and end of the name)
- Have the word "FunctionInfo" in cell A1, and the number 1 in cell B1.
- Every row (from row 2) provides the descriptions for a single function, with columns in the following order
- Function Name
- Function Descriptions
- Help Topic (either blank, or an http://... link, or a HelpFile and TopicId)
- Argument Name 1
- Argument Description 1
- ...
- Argument Name 2
- Argument Description 2
IntelliSense descriptions can be defined in an xml fragment that is added to the CustomXMLParts of a Workbook. The CustomXMLPart would have the following xml content:
<IntelliSense xmlns="http://schemas.excel-dna.net/intellisense/1.0">
<FunctionInfo>
<Function Name="MyVBAFunction" Description="A function described in XML"
HelpTopic="http://www.bing.com" >
<Argument Name="FirstArg" Description="Whatever you want to put in here" />
<Argument Name="AnotherArg" Description="Actually the second arg" />
</Function>
<Function Name="AnotherFunction" Description="A function described in XML"
HelpTopic="http://www.bing.com" >
<Argument Name="FirstArg" Description="Whatever you want to put in here" />
<Argument Name="AnotherArg" Description="Actually the second arg" />
</Function>
</FunctionInfo>
</IntelliSense>
Any Workbook, .xlam add-in or .xll add-in (native or managed) can provide an external file with the IntelliSense function descriptions. This file should have the same base name and path as the Workbook or add-in, but have the extension .intellisense.xml.
For example, the MyFuncs.xlam would have function descriptions in a file called MyFuncs.intellisense.xml in the same folder.
The content of the .xml file would be exactly the same as the Custom XML part described above.
The IntelliSense display server keeps track of available function descriptions, tracks the state of the Excel interface, and displays the IntelliSense tips when required. There can only be one active display server in a particular Excel instance. Different add-ins trying to each load a Display Server will coordinate the loading, so that only the newest version Display Server is active.
There are two options for loading a Display Server - either load the standalone ExcelDna.IntelliSense.xll add-in, or incorporate the Display Server as a library into your Excel-DNA add-in.
From the Releases page a pre-compiled ExcelDna.IntelliSense.xll add-in can be downloaded. (For the 64-bit version of Excel, use ExcelDna.IntelliSense64.xll.)
This add-in serves is a display server for all the description providers above. It will also load function descriptions from many loaded add-ins. In particular, Excel-DNA add-ins that provide descriptions in the function wizard will have IntelliSense information displayed by just loading this add-in, without changing or recompiling the add-in where the UDFs are defined.
Using the ExcelDna.IntelliSense.xll add-in is the preferred way of distributing an IntelliSense display server.
(This option is not yet supported.)
A future version of the IntelliSense extension will allow a library to be added to your Excel-DNA add-in, to serve as the IntelliSense display server. This simplifies distribution a bit (making for a self-contained .xll add-in that provides UDFs with IntelliSense.) Note, that there is no guarantee that the IntelliSense display server from a particular add-in will be the active display server. (Another add-in might also incorporate the IntelliSense library, and only one display server can be active.)