This project has moved and is read-only. For the latest updates, please go here.

Add-in overview

The Excel-DNA runtime contains a small loader (the .xll) that loads the .NET runtime, then checks the configuration (.dna) file and accordingly loads the managed assemblies to be registered with Excel. These assemblies are inspected using the .NET reflection mechanism, and the appropriate methods are registered with Excel (with custom information as set by the attributes). Ribbon or command bar interfaces are loaded, and RTD servers registered. Once the reflection-based inspection process and registration is complete, the resulting function exports are directly accessible from Excel, leaving very low function-call overhead at runtime.

A basic add-in will consist of three files:

  • The managed assembly containing the functions to be exported, called MyLibrary.dll.
  • The Excel-DNA runtime library (distributed as exceldna.xll), renamed to FirstAddIn.xll.
  • The configuration file (a text-based xml file), renamed to FirstAddIn.dna, containing the configuration:

A packing feature allows the Excel-DNA runtime library, configuration files, user add-in assemblies and other dependent libraries to be packed into a single .xll file. The only external requirement for using such an add-in would be that the appropriate .NET runtime version should be installed.

Getting and testing Excel-DNA

In this section we download Excel-DNA and check that the simplest sample add-in works. This ensures that the .NET framework is correctly registered, that the Excel security settings will not prevent the add-ins from loading.

  1. Download the latest version of Excel-DNA from http://exceldna.codeplex.com and extract the .zip file to a convenient location, say C:\Test\ExcelDna\.
  2. Extracting the distribution will create folders called Distribution and Source.
  3. In the Distribution folder, double-click the ExcelDna.xll file – Excel should open and possibly display a security warning:
     clip_image001
  4. Click to enable the add-in in Excel.
  5. The add-in registers a function called “AddThem”, which can be entered into a cell :
    clip_image002  and enter, giving  clip_image003
  6. The code creating defining this add-in can be found in the ExcelDna.dna file:
    clip_image004

Troubleshooting

The following are some common problems which may arise when loading the simplest samples add-in:

  • Excel claims that the file is in an incorrect format:

clip_image006

This may occur if the 64-bit version of Excel 2010 in installed. Load the corresponding ExcelDna64.xll file instead.

  • The .NET runtime is not present.
  • Security restrictions prevent Excel from loading the file. The following setting would cause Excel to silently disable all add-ins:

clip_image008

  • Some evaluation versions of Excel do not support add-ins; installing Excel without support for the VBA environment also prevents .xll add-ins from loading.

Creating a new add-in library in C#

In this section we create a simple C# add-in library, and expose it to Excel with the Excel-DNA runtime.

  1. In Visual Studio, create a new Class Library project
    clip_image010
  2. Add a reference to the ExcelDna.Integration.dll assembly (which is in the Excel-DNA Distribution directory. Mark this reference as Copy Local – False:
    clip_image011
  3. In the Class1.cs file, add a namespace declaration and a public static function to the Class1 class:
using System;
using ExcelDna.Integration;

namespace MyLibrary
{
    public class Class1
    {
        [ExcelFunction(Description="My first Excel-DNA function")]
        public static string MyFirstFunction(string name)
        {
            return "Hello " + name;
        }
    }
}
  4. Add a file to the project called “FirstAddIn.dna” with the following content: 
<DnaLibrary Name="First Add-In" RuntimeVersion="v4.0">
    <ExternalLibrary Path="MyLibrary.dll" />
</DnaLibrary>
2. Rebuild the project as a release build.

3. Open a command prompt in the output directory of the project, in this case C:\Test\MyLibrary\bin\Release\. The following files should be present:

clip_image023

4. From the command prompt, run “C:\Test\ExcelDna\Distribution\ExcelDnaPack.exe FirstAddIn.dna”. The following output results:

clip_image025

5. The result is a file called FirstAddIn-packed.xll which is a single file add-in which can be renamed, distributed and run with no other files required. (Both FirstAddIn.dna and MyLibrary.dll are packed inside the .xll as resources, and will be loaded at runtime.)

Further development

Some directions for further development of the add-in:

  • Accessing the C API and the COM object model from Excel-DNA.
  • Multithreading functions, volatile functions, reference parameters.
  • Macros, menus, ribbons and custom task panes.
  • Registration-free RTD servers.

Last edited Apr 26, 2012 at 11:32 PM by govert, version 5