Updated Instructions for Writing User Defined Functions for Excel in .NET


I updated a very popular blog post I made several years ago with some little additions and corrections that I think make the process of creating a user defined function (e.g. a custom function to use in Excel formulas) easier to create in Visual Studio and .NET


http://blogs.msdn.com/eric_carter/archive/2004/12/01/writing-user-defined-functions-for-excel-in-net.aspx


Here are the major changes to the article:


1) I’ve decided to recommend that you use the Guid attribute and generate a GUID using Generate GUID from Visual Studio Tools menu.  This makes debugging and troubleshooting any issues that come up easier as omitting this causes Visual Studio to generate new guids as you work with your project.  This makes it easier to search the registry for your automation add-in since it will always be listed under the GUID you specify.


2) I’ve changed the “RegisterFunction” code to write to the registry to the InProcServer32 key the full path to mscoree.dll.  This will fix the previous issue where Excel would complain about not being able to find mscoree.dll because regasm.exe (the tool that gets run when you check the Register for COM Interop box in Visual Studio) was only registering “mscoree.dll” rather than the full path such as “c:\windows\system32\mscoree.dll”.  The interesting thing about this issue is that the automation add-in would still work if you ignored the dialog where Excel complained, but it was annoying none the less.


3) I provide instructions on how to make Register for COM interop work on Vista.  Basically, you need to run Visual Studio as Administrator because the registration that is done of the add-in requires elevated privileges.


4) I provide the steps for finding the Automation Servers dialog in Excel 2007–it is pretty deeply buried with the new Ribbon UI.


5) I fixed a typo that an observant reader pointed out about area being height * width, not height + width (doh!)


Happy user defined function writing!


Comments (7)

  1. Johan says:

    Hi Eric,

    this is just what i needed. I have an old Vb6 com library and corresponding XLA file to add some special calculations to excel and i was already some time looking to update all this to .NET. Your method works fine, however, i am missing some functionality (lack of knowledge probable) to do a full upgrade:

    in the xla file (VBA) i could add some attributes to tell Excel a description of my function and where to find the help file is there something corresponding in .NET? (Attribute Funcxyz.VB_Description = "Funcxyz calculates Efficiency in [%]"

    Attribute Funcxyz.VB_HelpID = 110)

    How can i make errors appear in Excel like #N/A or #Value etc…, even a division by 0 generated by a function returning a double results in a very large number returned, but no error …

    Thanks in advance,

    Johan.  

  2. Sam says:

    Hi Eric, I am new to programming and .net. Your instructions were terrific. I created the addin and was able to test on my computer. How do i deploy this to another excel user?

  3. Eric Carter says:

    Johan–I haven’t completely looked into this but I know in general how it works so maybe you can do the legwork to figure it out once I give you a few pointers.

    These extra things like description and help id are store in the typelibrary generated by regasm.exe when your class is registered for COM interop.

    There are two avenues to pursue.  First you may be able to build a custom type library and plug in the description and help id yourself.  You can see the type library that regasm.exe generates by using the /tlb option and specifying a type library name.  You may have to decompile the typelibrary back to idl (use oleview) then modify the idl and recompile to a typelibrary and reregister the type library.

    Another possibility is that there is some .NET attribute you can put on your method that regasm will pick up (when it runs tlbexp).

  4. Eric Carter says:

    Sam–my books go into how to deploy these (see chapter 3) but to quickly sketch it out, you add a new setup and deployment project to your solution, then right click the newly added project and choose Project Output from the Add menu.  In the dialog box that occurs make sure that your AutomationAddin project is set as the Primary output then click OK.  Finally, make sure that when you click on the AutomationAddin node in the setup project and view the properties that Register is set to vsdrpCOM.

  5. Marc Enzler says:

    Dear Eric,

    Thanks a lot for this sample code. I have already wasted days on the topic. However, while this runs well on the development machine I have failed to deploy it. I tried your general instructions but to no avail. I am more than willing to buy your book to see how you do that, however it seems it has not yet been published.

    I am in desperate need to get this working. Any help is greatly appreciated!

    Best,

    Marc

  6. Eric Carter says:

    Marc–use the email link above and contact me directly and I’ll see if I can help.