Freigeben über

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

  • Artikel

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

https://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

  • Anonymous
    April 03, 2008
    PingBack from http://blogs.msdn.com/eric_carter/archive/2004/12/01/writing-user-defined-functions-for-excel-in-net.aspx

  • Anonymous
    April 09, 2008
    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.  

  • Anonymous
    May 03, 2008
    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?

  • Anonymous
    May 05, 2008
    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).

  • Anonymous
    May 05, 2008
    The comment has been removed

  • Anonymous
    May 19, 2008
    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

  • Anonymous
    May 19, 2008
    Marc--use the email link above and contact me directly and I'll see if I can help.