Excel: UDF not available in automation mode

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.

Advertisements

One Response to “Excel: UDF not available in automation mode”

  1. Don B Says:

    Thanks so much for this! I hope you don’t mind I’ve referenced this page in an MSDN forum discussion this very issue.

    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/637c90fa-c84c-428e-b411-800cbed0612f/

    Don

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: