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

问题描述

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

 全局对象为对象
  

我在Workbook_Open事件中如此活跃：

  Set obj = CreateObject（ComObject.ComObject）; 
  

我可以看到它被创建，当时我可以对它进行一些COM调用。 >

在我的工作表上，我有一堆细胞，如下所示：

  = Module.CallToComObject（....）
  

模块内部我有一个函数

 函数CallToComObject（...）
如果obj是Nothing然后
 CallToComObject = 0 
 Else 
 Dim result As Double 
 result = obj.GetCalculatedValue（...）
 CallToComObject = result 
 End If 
 End Function 
  

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

有人可以解释我应该寻找哪些可以导致这种情况？

解决方案

任何这些都将重置全局变量：

1. 使用结束


2. 未处理的运行时错误


3. 编辑代码


4. 关闭包含VB项目的工作簿

这不一定是详尽的列表，但...

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?

解决方案

Any of these will reset global variables:

1. Using "End"
2. An unhandled runtime error
3. Editing code
4. Closing the workbook containing the VB project

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

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