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

Question

Given:

• VSTO Add-in
• An override object RequestComAddInAutomationService() , which returns an instance of a class called Facade in my script.
• A VBA macro in Excel 2007 that accesses AddIn.Object to obtain a Facade and uses it.
• Many times when it works great.
• Several times, when it is unexpected, it does not work.

Update: It turns out this is a specific user who has a problem. She has it all this time, others never have it (never say never)

In this "a couple of times" I get

Error: object variable or with locked block variable

in the line of code that is trying to access the Facade property. In short, I can tell you that the code in RequestComAddInAutomationService() does not have error-prone magic in it, and the VBA code for accessing the add-in was taken from the Internet and looks great. A longer version is yet to come, for those who take the time to read it :-)

Question: Does anyone know why this might happen? Is this 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 several methods, but not one 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 several private fields. Since field assignments are done at creation, I looked through them. Most of them are not initialized at all, or they are set to null , so the constructor does practically nothing.

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 an error. Although the method called returns an object, I am sure that it cannot be the source of the error: if the returned link was null , then the operator would simply evaluate Set oResult = Nothing , which is still valid. VBA creates this type of error whenever the method is executed by the Nothing link, which in my case is automationObject .

On the other hand, if there was no add-in at all, Application.COMAddIns(...) will result in an index error outside the borders, I have seen this before.

Answer 1

It turned out that Excel disabled the COM add-in. This, as you know, sometimes happens 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 

led to Nothing .

Answer 2

Work most of the time and failure sometimes looks like a race condition. Andrew Whitechapel wrote about the race condition associated with RequestComAddInAutomationService ¹ :

COMAddIns Race Status

Although he says that race conditions should not be a problem with VBA macros in the process, perhaps the problem may arise in your specific scenario.

Try the proposed 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(); 

_{¹ The blog has a lot of useful information about what you are doing, so do not miss the relevant articles.}

Answer 3

I had a similar problem, often, but not always, so I can’t say for sure, but what seemed to fix it was in the Project / Application / Assembly Information ... and checking the Make assembly COM- Visible, then creating object (in Excel VBA) using:

 Set automationObject = CreateObject("PlugInDllName.PlugInClass") 

Since then, there have been no problems - fingers crossed.