Archive for the ‘Excel’ Category

C# Excel plugin getting disabled

November 15, 2010

Excel disables a plugin (I’m referring to COM and Automation addins here) in two ways:

1. Hard disable – when the plugin causes the host (Excel) to crash

In this case, the COM addin will be in the disabled list and the user will get a message next time it starts Excel asking whether the user still wants that addin or not.
The way Excel does this is by putting the plugin on a black list before calling the OnConnection method (IDTExtensibility2). If the method crashes the host, next time Excel starts up, it finds the offending plugin in the black list.
If the method returns fine, Excel removes it from the black list.
It’s difficult to crash the host from .net, but not impossible. Most of the disables in .net are however…

2. Soft disable – when the plugin returns a failure HRESULT from OnConnection

In .net that means that an exception escapes from OnConnection. The .net framework converts this into an HRESULT when the method returns.
In order to fix that, you need to look into the following areas:
– catch every exception in OnConnection (duh!)
– look at any members part of your class implementing IDTExtensibility2 (any constructor there which throws will generate this condition)
– also look at any static members that can potentially throw
– look at any problems loading dependent assemblies needed by your assembly (this can also cause this)
– if you haven’t written a wrapper for your plugin, and your plugin runs as the mscoree.dll plugin, then check if other similar plugin haven’t been disabled (Excel disables mscoree which disables all plugins running under that – check the MS website on how to write a wrapper and avoid this problem)

Advertisements

Excel: UDF not available in automation mode

October 19, 2010

One problem I was having lately is that some UDF in an Automation addin was not available when Excel was started in automation mode, via a C# application. Checking the Automation addin in Tools/Addins showed it was enabled, ticked and should have been working.
But when trying to call the UDF, I was getting #NAME?

This can be easily replicated by starting Excel in automation mode:

Excel /automation spreadsheet_name.xls

Using the UDF in the automation addin has the same problem, it shows #NAME?

After a bit of googling, it turns out that in automation mode, you have to re-enable the plugin as it’s not loaded by default. So the solution is to un-tick the addin in the Tools/Addins dialog and tick it back on.

Or programatically you need to do this:

set oAddin = Application.Addins(“YourAddin.ProgId”)
oAddin.Installed = False
oAddin.Installed = True

If it’s not in the list, you will have to add it first with:

Application.Addins.Add(“YourAddin.ProgId”)

Once you do that, the UDF starts working fine.

If your UDF is in an XLL rather than an Automation Addin, then you can also use the RegisterXLL API.

Excel

October 9, 2010

I know it’s been a long time since my last post and I apologize.

In the mean time, I was doing quite a bit of Excel programming and that’s the experience I want to talk about. I didn’t know much about Excel before starting. I have done VB, VBScript before so that came as no surprise. I also knew Excel is used quite a lot in the financial community, so I was keen to get some exposure to it.
After almost a year of development, ranging from VBA, plugin development (COM addin + Automation addin – DDE) and RTD, I can say, I have mixed feelings.

The overall experience I’m taking from this is that Excel is not a mature environment for development and the experience can be quite frustrating. It is almost decent at VBA, but when you start with addins, RTD, it all leaves a slightly bitter taste in your mouth. And here’s why, all the reasons listed one after the other:

1. Excel is single threaded. This has several implications:
a. You cannot call into Excel when it’s busy, RPC calls fail, for this you have to retry, or sometimes you get the dreaded dialog telling you that the STA is busy
b. It’s slower than it should be. Cannot take advantage of multi-processors
c. Can get easily into a “frozen” state, when long operations have to be done on the UI thread
d. Operations need to be marshalled into the STA, which is extremely slow and I do mean extremely

In Excel 2007 they have introduced multi-threading. However, they have also changed the interface, introducing the ribbon interface and alienating the entire financial community. No one is moving to Excel 2007 for that reason.

2. Error handling is extremely poor

When something happens, you don’t get a clear error, but instead a general error of what happens. Many APIs return the same errors and the errors are not documented.
This is truly a killer, and it’s true with the whole Excel object model. A very very big issue.

3. The addin frameworks are all a bit of mess

There are 3 main types of addins:
a. COM addin, you can implement UI elements, but no UDFs
b. Automation addins, you can implement UDFs, but no UI elements
c. RTD servers, you can implement a very limited interface to update data, a pull-push hybrid, but no access to the Excel object model at all

So, let’s say you want to implement an addin which has a UI menu with some functionality, provides a UDF, which operates asynchronously. Then you need a combination of potentially all three addins in one, maybe add VBA xla on top of that.

4. Some operations don’t work from addins, only from VBA, or only from some context, or only if the VBA_E_IGNORE is not set, or if other black box conditions are set

Again, lack of documentation makes it really difficult to develop. It’s a trial & error approach which works most of the time.
You always face the problem where your addin works most of the time, unless the user does something which you didn’t expect and tested.

5. Pushing data into Excel is very difficult

Either Excel is busy, or sets itself into VBA_E_IGNORE when a dialog is present, or operations fail because you’re in edit mode.
This problem was addressed by the introduction of the RTD framework

6. Excel is still buggy and it can crash

If you do certain operations, you might hit a bug in Excel and crash it. It crashes because bugs in Excel, but your addin/VBA are triggering, so you look like a culprit.

7. Excel doesn’t perform very well when you have big workbooks with a lot of data

8. Very difficult to debug problems in Excel, or weirdness like above, because Excel is a black box

Still, Excel is loved in the financial community, because it makes non-programmers more productive by its operations. It’s an easy way to present, format data which can be refreshed and kept up to date.
Yet, for developers, it can be a nightmare.