鉴于:
VSTO加载项 override object RequestComAddInAutomationService() ,它返回一个类的实例,在我的场景中称为Facade 。 Excel 2007中的VBA宏,它访问AddIn.Object以获取Facade并使用它。 很多时候这种方式非常好。 有几次突然出现,这似乎不起作用。更新:原来这是一个有问题的特定用户。 她总是拥有它,其他人从来没有它(?永远不会说“从不”)
在这“偶尔”中,我得到了
错误:未设置对象变量或With块变量
在尝试访问Facade属性的代码行。 简而言之,我可以告诉你, RequestComAddInAutomationService()中的代码中没有任何容易出错的魔术,并且访问加载项的VBA代码也是从Web获取的,看起来也很好。 对于那些花时间阅读它的人来说,更长的版本尚未到来:-)
问题:有没有人知道为什么会发生这种情况? 这是一个Excel问题吗?
承诺的细节:
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有一些方法,但没有一个字段。
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有一些私人领域。 由于字段分配是在创建时执行的,因此我对它们进行了审核。 它们中的大多数根本没有初始化,或者它们被设置为null ,因此构造函数几乎不做任何事情。
VBA代码:
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()最后一行是错误发生的地方。 虽然调用的方法返回一个对象,但我很确定它不能是错误的来源:如果返回的引用为null ,那么该语句将简单地计算为Set oResult = Nothing仍然有效。 每当在Nothing的引用上执行一个方法时,VBA就会抛出这种类型的错误,在我的情况下就是automationObject 。
另一方面,如果加载项根本不存在, Application.COMAddIns(...)会引发索引超出范围错误,我以前见过。
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.
最满意答案
工作大部分时间和失败有时看起来像一个竞争条件。 Andrew Whitechapel撰写了与RequestComAddInAutomationService 1相关的竞争条件:
COMAddIns比赛条件
虽然他说竞争条件不应该是进程内VBA宏的问题,但问题可能发生在您的特定场景中。
尝试建议的解决方法并循环,直到您的Addin.Object有效(C#代码,类似于VBA):
while (utils == null) { utils = (ComServiceOleMarshal.IAddinUtilities)addin.Object; System.Threading.Thread.Sleep(100); } utils.DoSomething();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.Objectresulted in Nothing.
更多推荐
发布评论