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.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 116 (Total in Forum: 116) (Refresh) FirstPrevNext

C# Method visible in VBA but not reachable Asim Mahmood2 22:32 9 Apr '09   Hi, I have a library of C# functions which I have used successfully in Excel/VBA..so far! Lately, I have encountered a strange problem - I copied a pasted the solution folder on another machine and built the solution. It built fine and I could add the reference in VBA. However, when I debug it i am unable to enter the C# code (I have been able to do this successfully in the past!). The problem gets resolved when I create a new solution and copy and paste all my files in the new project (I therefore suspect it is something to do with the project settings..). It is very cumbersome for me to do this exercise often (between my home and office machines!). Can someone please advise a fix? (I have tried the update KB907417 as well..didn't work this time ). Thanks in advance. Asim Sign In·View Thread·PermaLink Thanks and promote more your ExcelDna! Alain VIZZINI 17:30 9 Mar '09   Thanks a lot, your article is clear even if I have to admit I never got it to work. But I found your ExcelDna project (http://groups.google.com/group/exceldna) in your comments, this is simply great ! Works very fine, very easy to use (only the emit at first is a little slow but hey), looks very powerful and code is very cleanly written. Bravo, and thanks a lot for sharing that! Alan. Sign In·View Thread·PermaLink how to make a value in an excel cell read only using c#? abhiram_nayan 2:43 22 Jan '09   hey ppl, how can i make a value in a cell or in a range of cells read only!? Sign In·View Thread·PermaLink a button on the excel active cell? abhiram_nayan 3:46 7 Jan '09   hey ppl, how to place a button on the active/ any excel cell programatically while building an excel automation add in?? I need the code dat would display a button on an excel cell. Sign In·View Thread·PermaLink how to call this " Add2(3,4)" from another c#.2005 programme vishal nikam83 21:44 16 Dec '08   hii i am using C#.NET 2005 my problem is that there is already an function developed some thing like " Add2(3,4)" and now i have to call this from another C# program the code which i have used is given below ,my problem is that the excell which is saved does not recognise the formula "Add2(3,4)" i am using excel 2007. Excel.Workbooks objBooks; Excel.Application objApp = (Excel.Application)new Microsoft.Office.Interop.Excel.ApplicationClass(); objBooks = objApp.Workbooks; Excel.Workbook objBook = objBooks.Add(System.Reflection.Missing.Value); Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1); objSheet.Activate(); Excel.Range range = ( Excel.Range)objSheet.Cells[1, 1]; range.Formula = "=Add2(3,4)"; string FName = ""; if (SaveF.ShowDialog() == DialogResult.OK) { FName = SaveF.FileName; object objMissing = System.Reflection.Missing.Value; objBook.SaveAs((object)(FName), objMissing, objMissing, objMissing, objMissing, objMissing, Excel.XlSaveAsAccessMode.xlNoChange, objMissing, objMissing, objMissing, objMissing, objMissing); objBook.Close(false, objMissing, objMissing); objApp.Quit(); thanks... Sign In·View Thread·PermaLink Re: how to call this " Add2(3,4)" from another c#.2005 programme Govert van Drimmelen 13:08 24 Dec '08   I think the add-ins are not automatically loaded in the Excel application you create via Automation. You might need to add your add-in that contains the UDF explicitly, using Excel.AddIns..... (not sure how the object model looks.) Also have a look at my newer project, ExcelDna (http://groups.google.com/group/exceldna) for making Excel Add-Ins and User-Defined Functions with .Net. Sign In·View Thread·PermaLink #REF! abi80 5:26 11 Aug '08   hi i am trying to make a Excel 2007 UDF using vb .net 2003 to build an automation add in. When i build the dll and add it to excel add in list through automation button, the dll gets added. the functions are available in the excel functions list. However on using the function name in the cell, i get a #REF! error. interestingly, if i use the fully qualified name fo the function, for example AutomationAddin.MyFunctions.MultiplyNTimes, the function executes and gives correct value. The function changes to 'MultiplyNTimes' immediately and gives error on editing again. Can you please advise how to solve this. Thanks Sign In·View Thread·PermaLink 1.50/5 (2 votes) Addin in Excel 2002 getting registered - Add2 function is not shown in function browser in Excel 2002. Member 3850645 4:20 9 Jul '08   I performed all the steps and build the solution. The excel.exe.config file was also added in office11 folder. The dll is getting registered. The automation add in gets displayed in automation server but then when the function is called in the excel cell it fails - #NAME? Please guide me ..Urgent. The same works well in excel 2003. Sign In·View Thread·PermaLink 2.00/5 (3 votes) cannot get .dll to work as addin Kl_Gl 0:16 22 Jun '08   Hi you all out there. I am trying to create the addin as described in the article, and also copied the .dll to the same location as the excel, and tried to register it by running the regasm on it. But I always get an error message saying RegAsm error: Failed to load 'NAddIn.dll' because it is not a valid .Net assembly I am using Windows Vista and Microsoft Visual C# 2008 Express Edition. Does anyone have a solution to the problem? Thanks Klaus G Sign In·View Thread·PermaLink 2.00/5 (1 vote) Using dll as an add-in for a different machine williamw 4:08 7 May '08   I have written a dll in c# .NET 2.0 and have added the dll as an automation add-in for excel 2003 on my local machine. And it works great, thank you for the aid. My problem is I want to use the dll as an add-in to excel 2003 on another machine. I have used regasm to register the dll on another machine and I have even been able to add the the dll as an automation add-in. Where it falls flat is that I cannot call any functions within excel on the other machine where I can call these functions on my local machine. If anyone can help please let me know where I am going wrong. Will Sign In·View Thread·PermaLink 4.00/5 (1 vote) Re: Using dll as an add-in for a different machine williamw 7:07 8 May '08   It is now working. The solution is as follows. Add the dll to the same location as Excel.exe. Run regasm.exe /codebase .dll This registers it correctly and the functions can now be used within excel. Will Sign In·View Thread·PermaLink 5.00/5 (1 vote) Is it possible to create functions that take a range/an array as input? JosefLagergren 1:48 5 Mar '08   Hi, I got the function to work, but not when I try to make a function that takes a range. I've tried (among other things): public double test(object input) { Excel.Range r = (Excel.Range)input; ... public double test(object[] input) { Excel.Range r = (Excel.Range)input; ... public double test(object[,] input) { Excel.Range r = (Excel.Range)input; ... public double test(object input) { double[] r = (double[])input; ... etc, etc, etc... When I debug the input, I see that I get an exception...that I was missing the PIA - Microsoft.Excel.Interop... Do I need this? Is there anyway to get this to work without it? Sign In·View Thread·PermaLink 2.00/5 (1 vote) Interesting Error pinkfloydfan 3:36 26 Oct '07   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 Sign In·View Thread·PermaLink Only works on my local machine AnthonyLondon 12:13 25 Oct '07   It's working fine on my machine with Office 2003 (SP2). I developed it using VS2005. I wanted to give the AddIn to another user for testing. On his machine, .net 2.0 framework and Office 2003 (SP2) are installed (visual studio is not installed on his machine). I copied the .dll file and ran Regasm. When I open Excel, I can see the Addin in the List of addins (Tools--> Addins), But when i try to enter the function in a cell it's not recognised and on checking in the "Insert Function Dialog box" , my addin is not appearing in the list. I even tried the steps mentioned in this article (kb908002). But still it's not working. Any help to solve this issue is greatly appreciated. Sign In·View Thread·PermaLink Re: Only works on my local machine AnthonyLondon 2:18 26 Oct '07   Found an answer to my own question. You just need to install this Office update: http://www.microsoft.com/downloads/details.aspx?FamilyId=1B0BFB35-C252-43CC-8A2A-6A64D6AC4670&displaylang=en This will only work if the AddIn was developed with VS2005 and you're using Excel 2003 which is my case. Sign In·View Thread·PermaLink 3.50/5 (3 votes) Great! But how do we add explanation to the functions? [modified] pinkfloydfan 0:33 24 Oct '07   Thanks very much for posting this. I have been looking for some time with no joy for a way to use C# Express to build Excel Add-Ins and you have solved my problem. What we are now missing is how to add the explanatory text to each function paramater that would come up if using the function wizard. Is it possible to post how to do that please? Many Thanks -- modified at 8:27 Friday 26th October, 2007 Having now read through the comments on this site I see that you have to write an XLL not a DLL to do this. Sign In·View Thread·PermaLink Need Help mousum_cp 3:04 3 Sep '07   Thanks G. V. Drimmelen for your nice document on creating UDF in C#. Based on your article i created a lot of UDF. Now I want to creat an other UDF like the following:- double MyAddition2(double arg1, double arg2) {       Excel.Application appObject = getAllicationObject();       Excel.Range cell = appObject.ActiveCell;       cell.Interior.ColorIndex = (object)20;       return arg1 + arg2; } Besically, I want to color the cell where my UDF will be called. But the line "cell.Interior.ColorIndex = (object)20;" returns an exception (Exception from HRESULT: 0x800A03EC). How to overcome this? I checked the cell contains proper excel cell and cell.Worksheet.Name is also correct as it should be. Mousum Dutta Sign In·View Thread·PermaLink Need Help moving the dll/addin to another machine !!!!!!!!!!! anjana1981 12:53 18 Jul '07   Hi I created the add in and it works like a charm in my machine but then I need to move the dll file to another machine and install it on the excel there,for which i tried the regasm cmd and it wudnt work, the cmd gives me errors on cmd prompt can someone pls pls help me out thanks a Sign In·View Thread·PermaLink 1.00/5 (3 votes) Re: Need Help moving the dll/addin to another machine !!!!!!!!!!! lukejackson 2:25 30 May '08   Set ComVisible property to "true" in AssemblyInfo.cs file. Sign In·View Thread·PermaLink Not see the function Add2 in Excel's insert function simplexyz 4:37 13 Jun '07   Hi, I need some direction here. I followed Govert van Drimmelen's original sample and got everything ok until I tried to insert the function ADD2. I do not see it. I got the add-in step correctly by using automation add-in. I also asked another friend try it on his PC, same problem. How does everyone else get around this prolem? I use Excel 2003 and VS 2005. Thank Larry Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: Not see the function Add2 in Excel's insert function Govert van Drimmelen 5:54 13 Jun '07   Hi Larry, I now use ExcelDna (http://groups.google.com/group/exceldna) to create Excel user-defined functions in .Net. But I can suggest one possible reason for your problem. If you compile with .Net 2.0 (thus VS 2005) your assembly might not load because version 1.1 of the runtime is loaded into the Excel process. To work around this, you need to delete a registry key, or create an Excel.exe.config file that ensures the correct runtime is loaded, or install a patch for Excel. You can check the 'Getting Started' instructions for ExcelDna (http://groups.google.com/group/exceldna/web/getting-started-with-exceldna) for the details. Please post back if this solves your problem. Regards, Govert Sign In·View Thread·PermaLink i se it in excel automation list :( _sardaukar_ 14:47 12 Jun '07   i dont know what i do wrong, i ticked it as ok for interop and i dont see it. if i manually add it in excel itsays its not a COM server or something. Sign In·View Thread·PermaLink 3.50/5 (3 votes) Re: i se it in excel automation list :( [modified] zuraw 23:53 22 Dec '08   I had the same problem using VS2008 and Excel 2003. I solved it by changing [assembly: ComVisible( false )] to [assembly: ComVisible( true )] in AssemblyInfo.cs modified on Tuesday, December 23, 2008 5:19 AM Sign In·View Thread·PermaLink Getting a "REF!" error on using Add2 ameysj@gmail.com 5:40 29 May '07   I have used the code from the article to create an Excel addin. The addin is visible in the list of available ones. But when I use it in a worksheet, I am getting "REF!" error. I am using home & student edition of Excel 2007. Amey Joshi Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: Getting a "REF!" error on using Add2 ameysj@gmail.com 5:30 31 May '07   Refer to details at the bottom of http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx[^] Amey Joshi Sign In·View Thread·PermaLink

Last Visit: 16:53 8 Nov '09     Last Update: 16:53 8 Nov '09 12345 Next »