excel vba 中全局变量的生命周期是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7041138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the lifetime of a global variable in excel vba?
提问by chollida
I've got a workbook that declares a global variable that is intended to hold a COM object.
我有一个工作簿，它声明了一个用于保存 COM 对象的全局变量。
Global obj As Object
I initalize it in the Workbook_Open event like so:
我在 Workbook_Open 事件中初始化它，如下所示：
Set obj = CreateObject("ComObject.ComObject");
I can see it's created and at that time I can make some COM calls to it.
我可以看到它已经创建，那时我可以对它进行一些 COM 调用。
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.
我可以看到这些工作有点，但在刷新几张纸后，obj 对象不再被初始化，即它被设置为 Nothing。
Can someone explain what I should be looking for that can cause this?
有人可以解释我应该寻找什么会导致这种情况吗？
回答by Tim Williams
Any of these will reset global variables:
其中任何一个都将重置全局变量：
- Using "End"
- An unhandled runtime error
- Editing code
- Closing the workbook containing the VB project
- 使用“结束”
- 未处理的运行时错误
- 编辑代码
- 关闭包含 VB 项目的工作簿
That's not necessarily an exhaustive list though...
但这不一定是详尽的清单......
回答by Excel Developers
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.
除了上述 Tim 的 4 点之外，我还建议第 5 点：单步执行代码（调试）并在到达终点之前停止。可能这可以取代第 3 点，因为编辑代码似乎不会导致全局变量丢失其值。
