Skip to content

Usage Instructions

Jump to bottom Edit New page
Michael Voříšek edited this page Mar 28, 2019 · 10 revisions

Overview

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:

  1. The function description source, and
  2. the IntelliSense display server.

The following function description sources are supported:

  1. Method attributes in Excel-DNA add-ins (v0.32 and later). The [ExcelFunction] / [ExcelArgument] pair as well as [Description] attributes are supported.
  2. A special Worksheet (possibly hidden) added to an open Excel Workbook or .xla / .xlam add-in.
  3. A Custom XML part in an open Workbook.
  4. An external .xml file, matching the name of an open Workbook, .xla / .xlam or .xll add-in.

The IntelliSense display server can be:

  1. Hosted in a separate add-in (ExcelDna.IntelliSense.xll), or
  2. 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.

Function description sources

Every add-in or Workbook can provide function descriptions. The IntelliSense extension supports a variety of sources for the function descriptions, and will aggregate the descriptions from various sources for all the loaded add-ins and Workbooks.

Attributes in Excel-DNA based add-ins

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 Module

IntelliSense descriptions Worksheet in an open Workbook

Any open Workbook (including loaded .xla / .xlam add-ins) can provide function descriptions in a Worksheet (possibly hidden). The Woksheet should:

  • be called "_IntelliSense_" (note 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

Custom XML part in an open Workbook

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>

Here is a small helper VBA macro that will embed an .xml file as a CustomXML part:

' Only have to run this once
Sub EmbedIntelliSense()

    Dim strFilename As String
    strFilename = "C:\Path\To\VBAFunctions.IntelliSense.xml"
    Dim strFileContent As String
    Dim iFile As Integer
    iFile = FreeFile
    Open strFilename For Input As #iFile
    strFileContent = Input(LOF(iFile), iFile)
    Close #iFile
    
    Debug.Print strFileContent
    
    ThisWorkbook.CustomXMLParts.Add strFileContent

End Sub

External .intellisense.xml file

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.

Display servers

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.

Standalone display server: ExcelDna.IntelliSense.xll 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.

Integrated display server: ExcelDna.IntelliSense.dll library

The second approach to installing an IntelliSense display server is to add the IntelliSense server library to your Excel-DNA add-in. This simplifies distribution a bit (keeping the add-in a self-contained .xll that now 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 - always the one with the latest version.)

To incorporate the ExcelDna.IntelliSense.dll into your library:

  • Install the NuGet package ExcelDna.IntelliSense. (In the Package Manager Console: PM> Install-Package ExcelDna.IntelliSense.)
  • The package adds a reference to the following library:
    • ExcelDna.IntelliSense.dll
  • Register the IntelliSenseServer in your add-in's AutoOpen() implementation:
    using ExcelDna.Integration;
    using ExcelDna.IntelliSense;
    public class AddIn : IExcelAddIn
    {
        public void AutoOpen()
        {
            // Versions before v1.1.0 required only a call to Register() in the AutoOpen().
            // The name was changed (and made obsolete) to highlight the pair of function calls now required.
            IntelliSenseServer.Install();
        }

        public void AutoClose()
        {
            IntelliSenseServer.Uninstall();
        }
    }
  • Add the reference entries in your .dna file, used for packing the extra libraries into the -packed.xll:
<DnaLibrary Name="IntelliTest Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="IntelliTest.dll" LoadFromBytes="true" Pack="true" />
  
  <Reference Path="ExcelDna.IntelliSense.dll" Pack="true" />
</DnaLibrary>

Note about the 'LoaderLock' Managed Debugging Assistant

When debugging an add-in that includes the Integrated display server, you might see the following warning:

LoaderLock occured:

Managed Debugging Assistant 'LoaderLock' has detected a problem in 'C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE'.

Additional information: Attempting managed execution inside OS Loader lock. Do not attempt to run managed code inside a DllMain or image initialization function since doing so can cause the application to hang.

This warning relates to code in the IntelliSense server that monitors the Excel process for libraries loaded or unloaded. I believe the callback we run here is safe to execute under the OS Loader Lock, so this warning can be switched off in the debugger.

Add a custom footer
Add a custom sidebar
Clone this wiki locally