Given:
override object RequestComAddInAutomationService()
which returns an instance of a class which is called Facade
in my scenario.AddIn.Object
to get the Facade and uses it.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.
Working most of the time and failing sometimes looks like a race-condition. Andrew Whitechapel has written about a race condition related to RequestComAddInAutomationService
1:
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();
1There's lots of useful information on his blog for the things you are doing, so don't miss the related articles.
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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With