Excel 2007 UDF: how to add function description, argument help?

Question

The description

I am writing a couple of Excel UDFs in COM Servers. I'd like to get the standard help (Insert Function dialog) that you get when you press fx. Yes, I can see my COM Server listed in among the Category drop down, but

• I also see Equals, GetHashCode, GetType, and ToString (which are fairly undesirable to expose to the Excel user),
• selecting my COM Server brings up the *Function Arguments*[1] dialog with no argument information and no description of the function.

Here is the lameness that I get:

Insert Function dialog http://www.iwebthereforeiam.com/files/Insert%20function%20dialog.gif

Excel Function Arguments dialog http://www.iwebthereforeiam.com/files/Function%20Arguments%20dialog.gif

The question

Are there .NET attributes I could put on the methods to pass this through to Excel?

• Can I provide a description of the function?
• Can I provide a description of the parameters?
• Can I provide a category name for my functions, so that I get something better than just the ProgID?

(I see that it looks sadly easy to do in ExcelDNA, but I am not going that route. Emulating govert's code [custom attributes, a loader of some sort, etc.] looks like it would be pretty hard.)

---

Additional background

If you have not done work with Excel + COM Servers before, here are some useful resources to get up to speed:

Previous StackOverflow questions:
How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list?
How Add a COM-Exposed .NET Project to the VB6 (or VBA) References Dialog?

Other resources:
Writing user defined functions for Excel in .NET
Build and Deploy a .NET COM Assembly
Writing Custom Excel Worksheet Functions in C#

---

Edit 2009-10-20 14:10

I tried out calling Application.MacroOptions in a Sub New().

1. No Sub New()
   Semi-acceptable: Function is listed under category ProgID.
2. Shared Sub New()
   Not acceptable: build-time error.

   Cannot register assembly "...\Foo.dll".
   Exception has been thrown by the target of an invocation.

3. Sub New()
   Not acceptable: category is not listed in Insert Function dialog.

I suspect this is a problem both for MacroOptions and for the more involved route recommended by Charles.

---

Edit 2009-10-20 14:55

On the plus side, Mike's recommendation to create an interface to implement did kill off the annoying extra methods that were exposed.

---

Edit 2009-10-20 15:00

This Microsoft article from early 2007 (via Mike's link) seems a rather complete answer on the topic:

Automation Add-ins and the Function Wizard

Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.

---

1 Huh, a StackOverFlow bug. It looks like you cannot italicize a string inside an explicit HTML ul-list?

Answer 1

Some of this is easy to correct, other parts of it is rather hard. All of it is do-able, though, if you are willing to put the time in.

You wrote:

I also see Equals, GetHashCode, GetType, and ToString (which are fairly undesirable to expose to the Excel user)

Yes, agreed, this definitely undesirable, but it can be prevented. This is occurring because your class is inheriting from 'System.Object', as all .NET classes do, and your default interface that is exposed to COM is including these members. This occurs, for example, if you use the 'ClassInterfaceAttribute', using the setting 'ClassInterfaceType.AutoDual'.

E.g. in C#:

[ClassInterface(ClassInterfaceType.AutoDual)]

In VB.NET:

<ClassInterface(ClassInterfaceType.AutoDual)>

The use of 'ClassInterfaceType.AutoDual' should be avoided, however, in order to prevent the members inherited from 'System.Object' from being exposed (as well as to prevent potential versioning issues in the future). Instead, define your own interface, implement the interface in your class, and then mark your class with the 'ClassInterface' attribute with a value of 'ClassInterfaceType.None'.

E.g., using C#:

[ComVisible(true)]
[Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")]
public interface IClassName
{
    double AddTwo(double x, double y);
}

[ComVisible(true)]
[Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")]
[ProgId("ProjectName.ClassName")]
[ComDefaultInterface(typeof(IClassName))]
[ClassInterface(ClassInterfaceType.None)]
public class ClassName : IClassName
{
    public double AddTwo(double x, double y)
    {
        return x + y;
    }
}

Using VB.NET:

<ComVisible(True)> _
<Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")> _
Public Interface IClassName
    Function AddTwo(ByVal x As Double, ByVal y As Double) As Double
End Interface

<ComVisible(True)> _
<Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")> _
<ProgId("ProjectName.ClassName")> _
<ComDefaultInterface(GetType(IClassName))> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class ClassName
    Implements IClassName

    Public Function AddTwo(ByVal x As Double, ByVal y As Double) As Double _
        Implements IClassName.AddTwo
        Return x + y
    End Function
End Class

By making use of the 'ClassInterfaceAtribute' with a value of 'ClassInterfaceType.None', the inherited 'System.Object' memebers are excluded, because the class's interface is not made COM-visible. Instead, only the implemented interface ('IClassName' in this example) is exported to COM.

The above is also making use of the 'ComDefaultInterfaceAttribute'. This is not very important, and does nothing if you implement only one interface -- as in this example -- but it is a good idea in case you add an interface later, such as IDTExtensibility2.

For more detail on this, see:

(1) Managed Automation Add-ins by Andrew Whitechapel.

(2) Writing Custom Excel Worksheet Functions in C# by Gabhan Berry.

Ok, now to the hard part. You wrote:

Selecting my COM Server brings up the Function Arguments[1] dialog with no argument information and no description of the function.

Can I provide a description of the function?

Can I provide a description of the parameters?

Can I provide a category name for my functions, so that I get something better than just the ProgID?

The easiest approach here is to make use of the Application.MacroOptions method. This method enables you to provide a description of the function and specify which category under which you want it to be displayed. This approach does not allow you to specify any information for the functions parameters, unfortunately, but techniques that allow you to do so are very complicated, which I'll get to later. [Correction: The 'Application.MacroOptions' method only works for UDFs created via VBA and cannot be used for automation add-ins. Read on for more complex approaches to handle registration of UDFs containe in an automation add-ins -- Mike Rosenblum 2009.10.20]

Note that the help files for Excel 2003 and help files for Excel 2007 state that a string can be provided to the category argument in order to provide a custom category name of your choice. Beware, however, that the help files for Excel 2002 do not. I do not know if this is an omission in the Excel 2002 help files, or if this is a new capability as of Excel 2003. I'm guessing the latter, but you would have to test to be sure.

The only way to get your parameter information into the Function Wizard is to use a rather complex technique involving the 'Excel.Application.ExecuteExcel4Macro' method. Be warned though: many Excel MVPs have struggled with this approach and failed to produce a result that is reliable. More recently, though, it appears that Jan Karel Pieterse (JKP) has gotten it worked out and has published the details here: Registering a User Defined Function with Excel.

Skimming that article you'll see that it is not for the faint of heart. Part of the problem is that he wrote it for VBA / VB 6.0 and so all that code would have to be translated to VB.NET or C#. The key command, however, is the 'Excel.Application.ExecuteExcel4Macro' method, which is exposed to .NET, so everything should work fine.

As a practical matter, however, I vastly prefer using the 'Excel.Application.MacroOptions' approach because it is simple and reliable. It does not provide parameter information, but I have not yet had a strong need to motivate me to take on the 'ExecuteExcel4Macro' approach.

So, good luck with this, but my advice would be to utilize the 'MacroOptions', unless you are being paid by the hour. ;-)

-- Mike

Follow-up to Hugh's Replies

I tried out calling Application.MacroOptions in a Sub New().

No Sub New() Semi-acceptable: Function is listed under category ProgID.

Shared Sub New() Not acceptable: build-time error. Cannot register assembly "...\Foo.dll". Exception has been thrown by the target of an invocation.

Sub New() Not acceptable: category is not listed in Insert Function dialog. I suspect this is a problem both for MacroOptions and for the more involved route recommended by Charles.

You can't use shared (aka "static") classes or constructors when exposing your classes to COM because COM has no knowledge of this concept and so it cannot compile -- as you found out! You might be able to apply a 'COMVisibleAttribute' with a value of 'False' to the shared constructor, to at least allow it to compile. But this wouldn't help you in this case anyway...

Trying to register your automation add-in via the automation add-in itself might prove tricky. I realize that this is desirable in order to keep it as a single, stand-alone component, but it might not be possible. Or at least this won't be easy.

The issue is that automation add-ins are demand loaded. That is, they are not really there until Excel attempts to access the first worksheet function from your automation add-in. There are two issues related to this:

(1) If you put your registration code within the constructor for your class, then, by definition, your function wizard information cannot exist until the function has been called for the first time.

(2) Your constructor might be executing when Excel is not ready to accept automation commands. For example, an automation add-in is typically demand-loaded when the user begins to type in the name of one of the user-defined functions (UDFs) defined in the automation add-in. The result is that the cell is in edit-mode when your automation add-in first loads. If you have automation code within your constructor during edit mode, many commands will fail. I do not know if the 'Excel.Application.MacroOptions' or 'Excel.Application.Excel4Macro' methods have a problem with this, but many commands will choke when trying to execute while the cell is in edit mode. And if the automation add-in is being loaded for the first time because it is being called while the Function Wizard is open, I have no idea if these methods can work right.

There is no easy solution to this if you wish to have your automation add-in to be completely stand-alone with no other support. You can, however, create a managed COM add-in that will register your automation add-in for you via 'Excel.Application.MacroOptions' or the 'Excel.Application.Excel4Macro' approach when Excel starts up. The managed COM add-in class can be in the same assembly as that of your automation add-in, so you still only need one assembly.

By the way, you could even use a VBA workbook or .XLA add-in to do the same -- use the Workbook.Open event in VBA to call the registration code. You just need something to call your registration code when Excel starts up. The advantage to using VBA in this case is that you could utilize the code from the Jan Karel Pieterse's Registering a User Defined Function with Excel article as-is, without having to translate it to .NET.

On the plus side, Mike's recommendation to create an interface to implement did kill off the annoying extra methods that were exposed.

lol, I'm glad something worked!

This Microsoft article from early 2007 (via Mike's link) seems a rather complete answer on the topic:

Automation Add-ins and the Function Wizard

Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.

This is a limitation for the 'Excel.Application.MacroOptions' approach only. (My apologies, I had forgotten about this limitation of the 'Excel.Application.MacroOptions' method with respect to automation add-ins when I wrote my original answer, above.) The more-complex 'Excel.Application. ExecuteExcel4Macro ' approach, however, absolutely does work for automation add-ins. It should also work for .NET ("managed") automation add-ins as well, because Excel has no idea whether it is loading a COM automation add-in created via VB 6.0/C++ versus a managed COM automation add-in created using VB.NET/C#. The mechanics are exactly the same from the COM side of the fence because Excel has no idea what .NET is, or that .NET even exists.

That said, the 'Excel.Application.Excel4Macro' approach would definitely be a lot of work...

Answer 2

You could either use one of the .Net Excel systems such as ExcelDNA or ADDIN Express, or try to adapt one of the VBA/VB6 solutions: look at Laurent Longre's FunCustomise http://xcell05.free.fr/english/index.html or Jan Karel Pieterse's article at http://www.jkp-ads.com/Articles/RegisterUDF00.asp which uses a function overloading hack.