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  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

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

#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 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 (2 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 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 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 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 2.00/5 (2 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 2.00/5 (2 votes) 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 Form Window randal.junior 3:34 23 Apr '07   Hi everyone, Is there any way to run a procedure (sub) instead of o function on Excel with this type of automation? I'm trying to pop-up a form or populate a spreadsheet, can't see how to load the sub. Thx for now. Randal Sign In·View Thread·PermaLink mscoree.dll not found - VB.NET randal.junior 4:43 2 Apr '07   Hi, I'm having the following problem. I wrote my add-in in VB.net and I've been able to see it as a automation in Excel, but even when I add it as a Automation it not recognizes my UDF and when I click it on the   Add-in listbox it says that mscoree.dll is missing and if I want to delete the Add-in. Did anyone have the same problem and knows how to solve it? Here my code: <ClassInterface(ClassInterfaceType.AutoDual)> Public Class Funcs       Public Function ADD2(ByVal a As Double, ByVal b As Double) As Double             Return a + b       End Function       <ComRegisterFunctionAttribute()> Public Shared Sub RegisterFunction(ByVal t As Type)             Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable")       End Sub       <ComUnregisterFunctionAttribute()> Public Shared Sub UnregisterFunction(ByVal t As Type)             Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable")       End Sub End Class Randal Junior Sign In·View Thread·PermaLink Re: mscoree.dll not found - VB.NET Stuart McConnell 1:15 11 Apr '07   I had the same problem, solved by creating a file Excel.exe.config file in C:\Program Files\Microsoft Office\OFFICE11 with the contents. <?xml version="1.0" ?> <configuration>    <startup>       <supportedRuntime version="v2.0.50727"/>    </startup> </configuration> Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: mscoree.dll not found - VB.NET randal.junior 2:40 11 Apr '07   It didn't solved my problem, this config file must be with the dll file or just in OFFICE11 folder? When I use the regasm soft to register it I get the following registry keys: [HKEY_CLASSES_ROOT\CLSID\{49D51C4B-4FE7-3563-8E7B-ED9875EE808F}\InprocServer32] @="mscoree.dll" "ThreadingModel"="Both" "Class"="ExcelAddinRandal.Funcoes" "Assembly"="ExcelAddinRandal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a67d17f8440c6d3c" "RuntimeVersion"="v2.0.50727" "CodeBase"="file:///d:/ExcelAddinRandal.DLL" [HKEY_CLASSES_ROOT\CLSID\{49D51C4B-4FE7-3563-8E7B-ED9875EE808F}\InprocServer32\1.0.0.0] "Class"="ExcelAddinRandal.Funcoes" "Assembly"="ExcelAddinRandal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a67d17f8440c6d3c" "RuntimeVersion"="v2.0.50727" "CodeBase"="file:///d:/ExcelAddinRandal.DLL" thx for the help Randal Junior Sign In·View Thread·PermaLink Re: mscoree.dll not found - VB.NET [modified] harafeh 7:48 20 Apr '07   I had the same problem and found a solution on the web that worked for me: To fix this we need to add the following to the end of the RegisterFunction method: Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey( "CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\InprocServer32"); key.SetValue("", @"C:\Windows\System32\mscoree.dll"); Oh, and the config file mentioned above is required for Excel 2002 to work with .Net 2.0 (it defaults to .Net 1.1) but is not required for Excel 2003. -- modified at 15:02 Friday 20th April, 2007 Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: mscoree.dll not found - VB.NET randal.junior 13:11 20 Apr '07   Thx a lot...   now it worked fine.... For other people: remember to look at the correct path of mscoree.dll. My VB.Net code became this: <ComRegisterFunctionAttribute()> _ Public Shared Sub RegisterFunction(ByVal t As Type)       Dim key As Microsoft.Win32.RegistryKey       Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\{" _             & t.GUID.ToString().ToUpper() & "}\Programmable")       key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\\{" _             & t.GUID.ToString().ToUpper() & "}\\InprocServer32")       key.SetValue("", "C:\WINNT\System32\mscoree.dll") End Sub Randal Sign In·View Thread·PermaLink Re: mscoree.dll not found - VB.NET lukejackson 2:31 30 May '08   Ideally, you should use Environment.GetFolderPath(Environment.SpecialFolder.System) to get the reference to the C:\Windows\System32 folder. Sign In·View Thread·PermaLink 5.00/5 (1 vote)

Last Visit: Tuesday, December 2, 2008 1:05 PM     Last Update:Tuesday, December 2, 2008 1:06 PM 1234 Next »