Click here to Skip to main content
Click here to Skip to main content

Create an Automation Add-In for Excel using .NET

, 3 Aug 2004
Rate this:
Please Sign up or sign in to vote.
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:
      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

About the Author

Govert van Drimmelen
Web Developer
South Africa South Africa
No Biography provided

Comments and Discussions

 
QuestionNeed Help moving the dll/addin to another machine !!!!!!!!!!! Pinmemberanjana198118-Jul-07 11:53 
AnswerRe: Need Help moving the dll/addin to another machine !!!!!!!!!!! Pinmemberlukejackson30-May-08 1:25 
QuestionNot see the function Add2 in Excel's insert function Pinmembersimplexyz13-Jun-07 3:37 
AnswerRe: Not see the function Add2 in Excel's insert function PinmemberGovert van Drimmelen13-Jun-07 4:54 
Generali se it in excel automation list :( Pinmember_sardaukar_12-Jun-07 13:47 
GeneralRe: i se it in excel automation list :( [modified] Pinmemberzuraw22-Dec-08 22:53 
GeneralGetting a "REF!" error on using Add2 Pinmemberameysj@gmail.com29-May-07 4:40 
GeneralRe: Getting a "REF!" error on using Add2 Pinmemberameysj@gmail.com31-May-07 4:30 
GeneralForm Window Pinmemberrandal.junior23-Apr-07 2:34 
Questionmscoree.dll not found - VB.NET Pinmemberrandal.junior2-Apr-07 3:43 
AnswerRe: mscoree.dll not found - VB.NET PinmemberStuart McConnell11-Apr-07 0:15 
GeneralRe: mscoree.dll not found - VB.NET Pinmemberrandal.junior11-Apr-07 1:40 
AnswerRe: mscoree.dll not found - VB.NET [modified] Pinmemberharafeh20-Apr-07 6:48 
GeneralRe: mscoree.dll not found - VB.NET Pinmemberrandal.junior20-Apr-07 12:11 
GeneralRe: mscoree.dll not found - VB.NET Pinmemberlukejackson30-May-08 1:31 
GeneralRe: mscoree.dll not found - VB.NET Pinmembere800518-Aug-08 11:15 
GeneralTrap events linked with User Defined function Pinmemberparmanu1235-Feb-07 5:08 
GeneralRe: Trap events linked with User Defined function Pinmemberparmanu1237-Feb-07 3:12 
Questionhow to fill the multiple cell using UDF created using automation add-ins Pinmemberparmanu1235-Feb-07 5:01 
GeneralTo attach to a Com+ Application Pinmembertamasu14-Jan-07 2:56 
AnswerRe: To attach to a Com+ Application [modified] Pinmemberti-oh14-Jan-07 13:21 
GeneralRe: To attach to a Com+ Application Pinmembertamasu16-Jan-07 9:43 
GeneralRe: To attach to a Com+ Application Pinmemberti-oh17-Jan-07 0:35 
GeneralRe: To attach to a Com+ Application Pinmembertamasu17-Jan-07 8:49 
GeneralRe: To attach to a Com+ Application Pinmemberti-oh17-Jan-07 22:41 
QuestionAutomatic activation add-in PinmemberSerjoMainGod12-Nov-06 3:21 
AnswerRe: Automatic activation add-in Pinmemberrkapl18-Sep-07 5:20 
QuestionCan't shown on Insert Function Formular? Pinmemberjluo029-Nov-06 12:41 
GeneralFunction of an another PinmemberShurka20-Oct-06 3:52 
GeneralRe: Function of an another PinmemberGovert van Drimmelen20-Oct-06 4:36 
GeneralRe: Function of an another PinmemberShurka22-Oct-06 22:01 
GeneralRe: Function of an another Pinmemberdajianshi21-Aug-08 19:30 
QuestionType library embedding Pinmembertrias1028-Aug-06 7:44 
GeneralFunctions are not refreshed Pinmemberpilates_elates8-Aug-06 6:33 
GeneralRe: Functions are not refreshed Pinmemberpinkfloydfan26-Oct-07 1:52 
GeneralC++/CLI Pinmemberdavidward3024-Jun-06 1:28 
GeneralRe: C++/CLI Pinmemberarnab.m9-Oct-07 13:43 
GeneralRelocating plugin to another PC PinmemberManassypov26-May-06 10:17 
GeneralRe: Relocating plugin to another PC PinmemberJeltz126-May-06 20:42 
GeneralRe: Relocating plugin to another PC PinmemberManassypov27-May-06 3:34 
GeneralRe: Relocating plugin to another PC PinmemberJeltz127-May-06 4:20 
GeneralRe: Relocating plugin to another PC PinmemberGovert van Drimmelen27-May-06 7:29 
GeneralI can't see it in automation Please help Pinmemberuzidr190027-Jan-06 23:07 
GeneralRe: I can't see it in automation Please help Pinmemberm9628-Apr-06 9:53 
GeneralRe: I can't see it in automation Please help Pinmemberm968-May-06 13:48 
GeneralRe: I can't see it in automation Please help PinmemberManassypov26-May-06 10:18 
GeneralRe: I can't see it in automation Please help PinmemberJeltz126-May-06 14:38 
GeneralRe: I can't see it in automation Please help PinmemberZully Ramos29-Feb-08 8:59 
GeneralRe: I can't see it in automation Please help PinmemberJeltz126-May-06 20:41 
GeneralExcelDna - an alternative approach PinmemberGovert van Drimmelen24-Jan-06 0:13 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 4 Aug 2004
Article Copyright 2004 by Govert van Drimmelen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid