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
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!