Click here to Skip to main content
15,868,292 members
Articles / Programming Languages / C#
Article

Create an Automation Add-In for Excel using .NET

Rate me:
Please Sign up or sign in to vote.
4.64/5 (36 votes)
3 Aug 20043 min read 594.5K   84   125
Describes how to create an automation add-in for Excel using .NET.

Introduction

Excel versions 2002 (XP) and 2003 introduce the concept of an Automation Add-In. Automation Add-Ins allow public functions in COM libraries to act as User-Defined Functions (UDFs) in Excel, thus to be referenced directly from cell formulae.

This article provides a detailed walk-through of how to create an Automation Add-In for Excel using Visual Studio .NET.

Background

Some relevant knowledge base articles that discuss Automation Add-Ins are:

  • Q291392 - INFO: Excel COM Add-ins and Automation Add-ins.
  • Q285337 - How To Create a Visual Basic Automation Add-in for Excel Worksheet Functions.
  • Q278328 - XL2002: How to Mark an Automation Add-In Function as Volatile.

A commercial library that presents equivalent (and more) functionality, and makes this all very easy is ManagedXLL. However, this library is quite expensive and requires run-time licenses to distribute user code. It uses the native XLL API for creating an add-in to Excel, and thus also supports older versions of Excel.

The exact requirements for creating a COM server that can be used as an Automation Add-In are poorly documented, complicated by the fact that the default options in Visual Basic 6.0 seem to work perfectly.

When creating a COM library using .NET, an attempt to add the library as an Automation Add-In in Excel causes the error: “The file you selected does not contain a new Automation Server, or you do not have sufficient privileges to register the Automation Server”.

Here, I describe how to create an Automation Add-In in .NET, using C#. The techniques should apply to any .NET language.

There seem to be three tricks for implementing an Automation Add-In for Excel using .NET:

  1. The library needs to be registered for use through COM. This can be done by marking the project to ‘Register for COM Interop’ or by manual registration using RegAsm.exe.
  2. The ‘Programmable’ registry key needs to be added in the registry, under HKCR\CLSID\{xxx}\. This can be automated by adding appropriate ComRegisterFunction methods to the class.
  3. The class needs to be marked with the ClassInterface attribute, with value ClassInterfaceType.AutoDual (explicit interface implementations can work too). The default class interface that is generated is a dispatch interface - Excel seems to ignore the dispatch interface for Automation Add-Ins.

Walk-through

  1. Create the library:
    1. Create a new C# Class Library Project, called NAddIn.
    2. Select the project's properties; under ‘Configuration Properties’, ‘Build’, set ‘Register for COM Interop’ to True.
    3. Rename the class, add a namespace declaration, set the ClassInterface attribute of the class, and add a function to be used from Excel:
      C#
      using System;
      using System.Runtime.InteropServices;
      
      namespace NAddIn
      {
          [ClassInterface(ClassInterfaceType.AutoDual)]
          public class Functions
          {
              public Functions()
              {
              }
      
              public double Add2(double v1, double v2)
              {
                  return v1 + v2;
              }
      
              [ComRegisterFunctionAttribute]
              public static void RegisterFunction(Type t)
              {
                  Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
                      "CLSID\\{" + t.GUID.ToString().ToUpper() + 
                         "}\\Programmable");
              }
      
              [ComUnregisterFunctionAttribute]
              public static void UnregisterFunction(Type t)
              {
                  Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
                      "CLSID\\{" + t.GUID.ToString().ToUpper() + 
                        "}\\Programmable");
              }
          }
      }
    4. Build the NAddIn project to create bin\debug\NAddIn.dll.
  2. Test the Add-In in Excel:
    1. Open a new workbook in Excel.
    2. Select Tools, Add-Ins, Automation.
    3. NAddIn.Functions should be listed - select it. OK.
    4. In a cell, type =Add2(3,4)
    5. The cell should display 7.
To register the .dll after moving it, run regasm with the
/codebase
flag (typically as
c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm 
        /codebase NAddIn.dll
). You will get a warning about the assembly being unsigned - you can ignore this (or sign the assembly as documented).

History

  1. Initial version - 19 July 2004.
  2. Removed type library embedding instructions - 19 July 2004.
  3. Added ComRegisterFunction bits to automate registry changes - 30 July 2004.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
South Africa South Africa
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralVery nice, but on my machine needed ComVisible Pin
m_albert1372-Aug-10 13:24
m_albert1372-Aug-10 13:24 
GeneralMy vote of 5 Pin
m_albert1372-Aug-10 13:21
m_albert1372-Aug-10 13:21 
GeneralMy vote of 2 Pin
dhiraj.upadhyay22-Apr-10 21:02
dhiraj.upadhyay22-Apr-10 21:02 
GeneralUDF Listesd in "Inactive Application Add-Ins" Pin
Jean-Pierre Gervasoni12-Apr-10 0:32
Jean-Pierre Gervasoni12-Apr-10 0:32 
GeneralRe: UDF Listesd in "Inactive Application Add-Ins" Pin
Sonhospa11-May-10 0:44
Sonhospa11-May-10 0:44 
GeneralRe: UDF Listesd in "Inactive Application Add-Ins" Pin
Jean-Pierre Gervasoni11-May-10 3:38
Jean-Pierre Gervasoni11-May-10 3:38 
GeneralC# Method visible in VBA but not reachable Pin
Asim Mahmood29-Apr-09 21:32
Asim Mahmood29-Apr-09 21:32 
GeneralThanks and promote more your ExcelDna! Pin
Alain VIZZINI9-Mar-09 16:30
Alain VIZZINI9-Mar-09 16:30 
Questionhow to make a value in an excel cell read only using c#? Pin
abhiram_nayan22-Jan-09 1:43
abhiram_nayan22-Jan-09 1:43 
Questiona button on the excel active cell? Pin
abhiram_nayan7-Jan-09 2:46
abhiram_nayan7-Jan-09 2:46 
Questionhow to call this " Add2(3,4)" from another c#.2005 programme Pin
vishal nikam8316-Dec-08 20:44
vishal nikam8316-Dec-08 20:44 
AnswerRe: how to call this " Add2(3,4)" from another c#.2005 programme Pin
Govert van Drimmelen24-Dec-08 12:08
Govert van Drimmelen24-Dec-08 12:08 
AnswerRe: how to call this " Add2(3,4)" from another c#.2005 programme Pin
jibin.mn5-Aug-10 1:22
jibin.mn5-Aug-10 1:22 
General#REF! Pin
abi8011-Aug-08 4:26
abi8011-Aug-08 4:26 
GeneralAddin in Excel 2002 getting registered - Add2 function is not shown in function browser in Excel 2002. Pin
Member 38506459-Jul-08 3:20
Member 38506459-Jul-08 3:20 
Questioncannot get .dll to work as addin Pin
Kl_Gl21-Jun-08 23:16
Kl_Gl21-Jun-08 23:16 
AnswerRe: cannot get .dll to work as addin Pin
mike198924-Aug-10 11:28
mike198924-Aug-10 11:28 
QuestionUsing dll as an add-in for a different machine Pin
williamw7-May-08 3:08
williamw7-May-08 3:08 
AnswerRe: Using dll as an add-in for a different machine Pin
williamw8-May-08 6:07
williamw8-May-08 6:07 
QuestionIs it possible to create functions that take a range/an array as input? Pin
JosefLagergren5-Mar-08 0:48
JosefLagergren5-Mar-08 0:48 
QuestionInteresting Error Pin
pinkfloydfan26-Oct-07 2:36
pinkfloydfan26-Oct-07 2:36 
So I used this great method to create my Automation Add-In and it all works fine. Then with one function that uses an Enum as a paramater I got an interesting result:

1) If I input the number corresponding to the Enum parameter I want to use into the function parameter it works great
2) If I type the name/string of the Enum parameter I want to use again it works but it always assumes the Enum parameter is 0
3) If I reference a cell with either the name or number of the Enum parameter I get a #VALUE! error

So, does anybody know why this happens? And even better does anybody know how you can write the name of the Enum parameter or a reference to a cell with it directly into the function parameter and have the UDF work?

Thanks very much for your help
GeneralOnly works on my local machine Pin
AnthonyLondon25-Oct-07 11:13
AnthonyLondon25-Oct-07 11:13 
GeneralRe: Only works on my local machine Pin
AnthonyLondon26-Oct-07 1:18
AnthonyLondon26-Oct-07 1:18 
QuestionGreat! But how do we add explanation to the functions? [modified] Pin
pinkfloydfan23-Oct-07 23:33
pinkfloydfan23-Oct-07 23:33 
GeneralNeed Help Pin
mousum_cp3-Sep-07 2:04
mousum_cp3-Sep-07 2:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.