Automation add-in vs. COM add-in

Question

I am a newbie with add-in programming and need help with the following scenario:

I have an C# Excel automation add-in that calls a few UDFs. I want to do a user name and password check during when the automation add-in loads for which a screen where the user can enter his details must pop up on load of the add-in. How can it be done using an automation add-in?

How can event handling be done in general using automation addins ? I want some calculations to be done when the user presses F9 to calculate the UDF formula in some of the cells.

Are there any articles that explains event handling in automation add-ins?

Answer 1

Automation add-ins, in general, are not designed to handle this kind of functionality. You can have your add-in implement IDTExtensibility2 in order to get a reference to the 'Excel.Application' object in which your add-in is running. From there, you can access all the events of the 'Excel.Application' class. But an automation add-in is not generally designed to handle Excel object model events -- it is designed to support user defined functions (UDFs) only.

I want to do a user name and password check during when the automation addin loads for which a screen where the user can enter his details must pop up on load of the add in. How can it be done using an automation addin?

Beware of attempting to to take action when your automation add-in first loads. An automation add-in is demand-loaded, meaning that it is not loaded until it is needed by Excel. It will typically load when the user begins entering the first user-defined function (UDF) of your automation add-in into a cell. The problem is that the majority of programmatic commands will fail when attempting to execute while the user is still editing the cell. Therefore, if you attempt to take such actions when your add-in first loads, there is a pretty good chance that it is loading while Excel is in a mode that would not allow your code to execute safely. (For more on this, see: Excel fails when Automation add-In loads.)

To get around this issue, you could use a managed COM add-in instead, which is designed to handle Excel object model events. Your managed COM add-in could even load your automation add-in, if you wanted; or the managed COM add-in class and the automation add-in class could both reside within the same assembly, in which case they could communicate directly.

How can event handling be done in general using automation addins ? I want some calculations to be done when the user presses F9 to calculate the udf formula in some of the cells.

Detecting when the F9 key is hit could be done by subscribing to the 'Excel.Application.SheetCalculate' event, which will fire any time any worksheet has completed calculating. Calculation, in this case, could be triggered for any reason -- not just for hitting the F9 key. If you wish to trap the F9 key specifically, then you would need to make use of the 'Application.OnKey' callback, which is only available via VBA. You could, however, expose a class in your project to COM and have it called from a VBA add-in that is called back from the 'Application.OnKey' event.

For articles on automation add-ins, see:

• Writing user defined functions for Excel in .NET
• Writing Custom Excel Worksheet Functions in C#
• Implementing IDTExtensibility2 in an Automation Add-in
• Excel fails when Automation add-In loads
• How to Deploy a .NET Automation Add-in via a Visual Studio Setup Package

For articles on managed COM add-ins, see:

• How to build an Office COM add-in by using Visual C# .NET
• Building Microsoft Office Add-ins with Visual C# .NET and Visual Basic .NET

For articles regarding both COM add-ins and automation add-ins, see:

• Excel COM add-ins and Automation add-ins
• Build and Deploy a .NET COM Assembly

For articles discussing the use of a VBA add-in that calls your managed application, see:

• A Simple C# DLL - how do I call it from Excel, Access, VBA, VB6?
• How to create Excel UDFs in VSTO managed code
• Calling a .NET Component from a COM Component

I know that this is a lot to digest, but hopefully this can get you going. Overall, I would recommend a managed COM add-in to handle your user-interface functionality. The data saved by the managed COM add-in could be shared with the automation add-in in easily, either by having both add-ins reference a common assembly, or by having both the managed COM add-in class and the automation add-in class held within the same assembly.

I hope this helps, and please ask if you would like me to clarify anything further.

-- Mike

Follow-Up Reply:

Thanks a lot for the links and pointers. I went through the links and have a fair idea of what needs to be done.:)

Glad it helps. :) It's a lot of reading, I suggest you print them up and read them all. You are looking to do something that is fairly advanced, so the more you know about the topic, the better off you will be.

If I am using a COM based addin to handle my excel events, how do I incorporate my UDF functions?

A managed COM add-in cannot expose UDF functions. You would still need an automation add-in for that. However, your managed COM add-in class and your automation add-in class could both be within the same assembly, if you wanted. Therefore, your COM add-in could ask the user for the information that you need, and these values would be available to the automation add-in when the UDFs within it are run.

Will they be exposed as normal functions in the formula pane similar to the case of using an automation addin?

The UDFs exposed by your automation add-in will be included within the 'Insert Function' wizard automatically, under a category matching the name of your automation add-in. The description, however, will not automatically include as much information as is provided for Excel's built-in functions. The default functionality is usually fine, but if you wish to provide more complete information for the 'Insert Function' wizard, this is a complex topic unto itself. See: Excel 2007 UDF: how to add function description, argument help.

Say I need to call a formula getcube that returns the cube of a number public double getcube(double a) { return a * a * a; }

When my addin uses both the custom interface that defines my UDFs and IDTExtensibility2, how do I handle such a case? Can you please explain this case with an example?

So far, I don't see any need to implement IDTExtensibility2 based on what you show here, you only need a standard automation add-in. For a standard automation add-in, you should read Writing user defined functions for Excel in .NET and Writing Custom Excel Worksheet Functions in C#. For a discussion regarding how to implement IDTExtensibility2 for a managed COM add-in, see Implementing IDTExtensibility2 in an Automation Add-in.

Is there a way to just implement the IDTExtensibility2 on the automation addin to get access to the Excel.Application object or should I create a separate COM addin for it?

You absolutely can implement IDTExtensibility2 directly within your automation add-in, there is no need to create a managed COM add-in for this. Again, see, Implementing IDTExtensibility2 in an Automation Add-in. What you wish to achieve, however, is to use event handling against the Excel object model. While this could be done using an automation add-in, it is non-standard and is not the kind of task that an automation add-in is designed to do. In particular, you wish to have the user input information when the automation add-in first loads; this can be a particularly tricky situation, which is why I am recommending that you utilize a managed COM add-in for this task. For more on this, see Excel fails when Automation add-In loads.

To clarify, managed COM add-ins and automation add-ins are merely classes that have been made COM-visible and are registered correctly. There is no reason why these two classes cannot exist within the same assembly. And, since it sounds like you want your functionality to include both UDFs and Excel object model event handling, a single assembly that includes both a managed COM add-in and an automation add-in would enable you to handle all the functionality you seek in the manner that Excel expects.

I know that this is a lot to digest, but if you carefully read the articles that I suggested here, I think that it will make sense...

Mike