chiccodoro chiccodoro - 2 months ago 12
C# Question

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

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.

Answer

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.

Comments