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

Solution 1:

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.}

Solution 2:

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.

Solution 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.