Excel allows us to extend it’s programming/functional model by adding in new components known as Excel addin’s (or XLL files). There are several ways of creating these in .NET. For this post I’m going to concentrate on a commercial product known as ManagedXLL.

Let’s just straight into creating a skeleton project for an XLL add-in.

1. Let’s start by creating a new class library, I’m going to call mine (the nondescript name) MyExcelPlugin.
2. Now add a reference to ManagedXLL.dll
3. Next we need to copy and rename the Managed.XLL.config which comes with ManagedXLL. Copy it to your project’s folder (you can ofcourse place it where you like) and name it projectname.XLL.config (obviously replacing projectname with your project’s name) then add it to the project and set it’s “Copy to Output Directory” property in Visual Studio to “Copy if newer”
4. Next we need to copy the Managed.XLL file to our project. Copy it and rename it projectname.XLL, where project name is your project’s name. Add it to the project and set the “Copy to Output Directory” property in Visual Studio to “Copy if newer”.
   Note: Both the projectname.XLL and projectname.XLL.config files must be available in the same locations.
5. If you’ve not done so already, change the name of the Class1 file which was created with the project to MyExcelFunctions (or whatever you prefer)

Okay so to recap, we’ve created a class library, reference ManagedXLL.dll and copied (and renamed) Managed.XLL and Managed.XLL.config. Great but what have we really done ?

So the ManagedXLL.dll supplies us with attributes which will will use in a later section to mark out code for use in Excel. It also supplies functions for interacting with Excel. The Managed.XLL file is the add-in we use in Excel. We rename it so that obviously there’s (hopefully) no conflicts with other add-ins and the same goes with renaming the Managed.XLL.config. The Managed.XLL file is the runtime that Excel communicates with and it in turn will communicate with our DLL. So it acts as the proxy to our DLL. As is probably obvious at this point, the Managed.XLL.config will supply information to Managed.XLL about our DLL, such as whether we want menu’s displayed, the name of the add-in and so on.

Let’s make some changes to the Managed.XLL.config (now obviously renamed for our project).

Note: We can also use the XLL.config for any configuration required by our add-in, for example if there’s WCF config required or just plain old appSettings.

1. The first thing we’ll want to do is change the display name to reflect our add-in. So open the XLL.config file and scroll down to to the managedxll/settings. Here you will see the displayName so change the value attribute to the project name, for example MyExcelPlugin or whatever name you want to have Excel display
2. We now need to tell ManagedXLL the name of the assembly that it’s acting as a proxy to, so find the assemblies section in the config file and change the location to the name of your assembly. You’ll notice in the config comments for this section that there’s various ways of declaring your assembly’s location.
3. The last thing we’re going to add, for this section at least, is the license information. Locate the licenses section in the config file and supply the relevant information in the data and hash attributes

At this point we’re created the bare minimum of a DLL that we can load into Excel. Ofcourse it does nothing, but if you want to load up Excel and select Tools | Add-ins, then browse to the bin\debug or bin\release (whichever you’re building) of your project and load the .XLL you named after your project. Then press OK in the Add-ins dialog in Excel to accept it, this will add a menu entry named ManagedXLL (we can change this easily enough) and if all goes well no errors are displayed, i.e. the ManagedXLL debug console does not get displayed with any error messages.

Note: if you now make changes to the project and compile you will get “Unable to copy xxx because it is being used by another process” errors. This is because, you’ve probably got Excel running with the Add-in loaded. Either close Excel or go back to the Add-ins dialog in Excel and remove the Add-in by unchecking it.

Time to add something useful, some code

1. Open the class file, mine’s called MyExcelFunctions.cs and change the class to a static and add the ExcelCategory attribute to the class, as follows

   [ExcelCategory("My Excel Functions")]
   public static class MyExcelFunctions
   {
   }
   

   This will add a category title for display in the worksheet functions dialog etc.

2. Now let’s add an actual Excel callable function. This will not be anything special, but will simply return a string. However it also demonstrates how easy it is to now create functions for Excel.

   Add the following code to our class

   [WorksheetFunction("MyName", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
   [ExcelDescription("Returns a string with the name of the add-in.")]
   [return: ExcelDescription("A string representing the name of the add-in")]
   public static string Name()
   {
      return "My Excel Functions";
   }
   

   If you now build this and add the add-in to Excel (it may prompt you to say do you want to overwrite the existing plugin (or words to that effect), say yes to this.

   Once the add-in is loaded, select a cell then go to Insert | Function option in Excel and if you select the “Or select a category” combo box you can scroll down and find the text we supplied for the ExcelCategory, for example “My Excel Functions”. From here we can select our function. As there are no arguments for this function you’ll see very little of interest but if you click on “Help on this function” you’ll see the return description we set up in our class.

   Anyway, click OK on the function dialog and it’ll enter

   =MyName()

   into Excel and display the string our class returns. Ofcourse you do not need to go through the Functions dialog everytime, this was just to show the category and return description are available.

   Note: Notices that I wrote =MyName() and not =Name() as per our method name. As you might have spotted in the WorksheetFunction we declare the name of the function that Excel see’s/exposes. Thus allowing us to alter the name of the method for Excel.

So at this point we should have a basic, but working, Excel add-in.