excel vba 中全局变量的生命周期是多少? [英] What is the lifetime of a global variable in excel vba?

问题描述

我有一个工作簿，它声明了一个用于保存 COM 对象的全局变量.

I've got a workbook that declares a global variable that is intended to hold a COM object.

Global obj As Object

我在 Workbook_Open 事件中初始化它，如下所示:

I initalize it in the Workbook_Open event like so:

Set obj = CreateObject("ComObject.ComObject");

我可以看到它已经创建，那时我可以对它进行一些 COM 调用.

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

我可以看到这些工作了一点，但是在刷新几张纸后，obj 对象不再被初始化，即它被设置为 Nothing.

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?

推荐答案

任何这些都会重置全局变量:

Any of these will reset global variables:

1. 使用结束"
2. 未处理的运行时错误
3. 编辑代码
4. 关闭包含 VB 项目的工作簿

这不一定是一个详尽的清单......

That's not necessarily an exhaustive list though...

这篇关于excel vba 中全局变量的生命周期是多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！