Excel VBA中全局变量的生命周期是多少？
我有一个工作簿，它声明了一个用于保存 COM 对象的全局变量。
Global obj As Object
我在 Workbook_Open 事件中初始化它，如下所示：
Set obj = CreateObject("ComObject.ComObject");
我可以看到它已创建，此时我可以对其进行一些 COM 调用。
在我的工作表上，我有一堆单元格，如下所示：
=Module.CallToComObject(....)
在模块内部，我有一个函数，
Function CallToComObject(...)
If obj Is Nothing Then
CallToComObject= 0
Else
Dim result As Double
result = obj.GetCalculatedValue(...)
CallToComObject= result
End If
End Function
我可以看到这些功能工作了一段时间，但是在刷新几张工作表后，obj 对象不再初始化，即它设置为 Nothing。
有人可以解释一下我应该寻找什么会导致这种情况吗？
I've got a workbook that declares a global variable that is intended to hold a COM object.
Global obj As Object
I initalize it in the Workbook_Open event like so:
Set obj = CreateObject("ComObject.ComObject");
I can see it's created and at that time I can make some COM calls to it.
On my sheet I have a bunch of cells that look like:
=Module.CallToComObject(....)
Inside the Module I have a function
Function CallToComObject(...)
If obj Is Nothing Then
CallToComObject= 0
Else
Dim result As Double
result = obj.GetCalculatedValue(...)
CallToComObject= result
End If
End Function
I can see these work for a bit, but after a few sheet refreshes the obj object is no longer initialized, ie it is set to Nothing.
Can someone explain what I should be looking for that can cause this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
其中任何一个都会重置全局变量：
但这不一定是详尽的列表...
Any of these will reset global variables:
That's not necessarily an exhaustive list though...
除了上面蒂姆的第四点之外，我还建议第五点：单步执行代码（调试）并在到达终点之前停止。这可能会取代第 3 点，因为编辑代码似乎不会导致全局变量丢失其值。
I would suggest a 5th point in addition to Tim's 4 above: Stepping through code (debugging) and stopping before the end is reached. Possibly this could replace point number 3, as editing code don't seem to cause global variable to lose their values.