VSTO in VBA: AddIn.Object returns Nothing (null) sometimes

Question

Given:

• A VSTO Add-In
• An override object RequestComAddInAutomationService() which returns an instance of a class which is called Facade in my scenario.
• A VBA macro in Excel 2007 which accesses the AddIn.Object to get the Facade and uses it.
• A plenty of times where this works perfectly fine.
• A couple of times where out of the blue, this doesn't seem to work.

Update: Turns out that it's a particular user that has the problem. She has it all the time, others never have it (? never say "never")

In this "couple of times" I get

Error: Object variable or With block variable not set

at the line of code which tries to access a property of Facade. In short I can tell you that the code in RequestComAddInAutomationService() doesn't have any error-prone magic in it, and the VBA code to access the add-in has been taken from the web and looks fine, too. The longer version is yet to come, for those who'll take the time to read it :-)

Question: Does anyone have a clue why this can happen? Is it an Excel issue?

---

Details as promised:

MyAddIn.cs:

public partial class MyAddIn
{
    public Facade Facade { get; private set; }

    protected override object RequestComAddInAutomationService()
    {
        if (this.Facade == null)
            this.Facade = new Facade(Controller.Instance);

        return this.Facade;
    }
}

Facade.cs:

[ComVisible(true)]
[Guid("1972781C-A71A-48cd-9675-AE47EACE95E8")]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IFacade
{
    // some methods
}

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class Facade : IFacade
{
    private Controller Controller { get; set; }

    public Facade(Controller controller)
    {
        this.Controller = controller;
    }
}

Facade has some methods but not a single field.

Controller.cs:

public class Controller
{
    private static Controller instance = null;
    public static Controller Instance
    {
        get
        {
            if (instance == null) instance = new Controller();
            return instance;
        }
    }

    private Controller() { }
}

Controller has some private fields. Since the fields assignments are executed on creation, I reviewed them. Most of them are not initialized at all, or they are set to null, so the constructor does virtually nothing.

The VBA code:

Dim addin As Office.COMAddIn
Dim automationObject As Object

Set addin = Application.COMAddIns("My AddIn")
Set automationObject = addin.Object

Dim oResult As Object
Set oResult = automationObject.SomeMethodThatReturnsAnObject()

The last line is where the error happens. Although the method called returns an object, I am pretty sure that it cannot be the source of the error: If the reference returned was null, then the statement would simply evaluate to Set oResult = Nothing which is still valid. VBA rather throws this type of error whenever a method is executed on an reference that is Nothing, which is automationObject in my case.

On the other hand, if the add-in wasn't there at all, the Application.COMAddIns(...) would raise an index out of bounds error, I've seen that before.

Wart

Answer 1

Turned out that Excel disabled the COM add-in. This is known to sometimes happen silently, without Excel complaining about anything.

So, since the add-in was registered with excel, the following line succeeded:

Set addin = Application.COMAddIns("My AddIn")

But since it was disabled, the object was not created and

Set automationObject = addin.Object

resulted in Nothing.

Wart

This "answer" says "excel disabled the COM add-in / known to sometimes happen silently", then explains that this explains the symptoms. But, it doesn't explain anything about how to prevent or work around this problem. – Porkpie

@TonyD: You're right there - but this is out of the scope of this question. The question was only how it could happen. The reason turned out to be that the add-in was disabled. Asking why Excel could conceivably disable an add-in opens a whole new space of possible answers... – Wart

Answer 2

Working most of the time and failing sometimes looks like a race-condition. Andrew Whitechapel has written about a race condition related to RequestComAddInAutomationService¹:

COMAddIns Race Condition

Although he says that race conditions should not be a problem with in-process VBA macros, it could be that the problem might happen in your specific scenario.

Try the suggested workaround and loop until your Addin.Object is valid (C# code, similar in VBA):

while (utils == null)
{
    utils = (ComServiceOleMarshal.IAddinUtilities)addin.Object;
    System.Threading.Thread.Sleep(100);
}    
utils.DoSomething();

_{¹There's lots of useful information on his blog for the things you are doing, so don't miss the related articles.}

Hayott

Thanks for your hint. I've read it. Unfortunately it seems not to apply to my case: I do call the add-in from VBA. I've now added the details to my question. If you could spot a race condition in that code, I'd be happy. Though it seems more like there isn't any... (of course, the retry-several-times trick might still do it) – Wart

Answer 3

I've had a similar problem, often but not always, so I can't say for certain but the thing that seemed to fix it was going to Project / Application / Assembly Information... and checking Make assembly COM-Visible, then creating the object (in Excel VBA) with:

Set automationObject = CreateObject("PlugInDllName.PlugInClass")

No problems since - fingers crossed.

Porkpie