VBA全局变量，多个工作簿 [英] VBA global variables, multiple workbooks

问题描述

我有一个VB应用程序，它使用一些全局变量来存储多个表单和模块所需的数据，这很好。但是，如果用户打开另一个工作簿，运行相同的VBA应用程序，那么他们最终将访问（和更改）相同的公共变量。

I have a VB application, that uses some global variables to store data that is required by multiple forms and modules, this works fine. However if a user opens up another workbook, running the same VBA application, then they end up accessing (and changing) the same public variables.

如何获得工作簿级别全局变量，或如果这是不可能的，什么是最好的方式来存储数据，所有形式和模块可以访问，但只在特定的工作簿？

How can I have workbook level global variables, or if this is not possible, what is the best way to store data that is accessible by all forms and modules, but only inside the specific workbook?

这是一个VBA插件，全局变量是在一个标准模块中声明的。

This is a VBA addin, and the Global variables are declared in a standard module.

推荐答案

我能够复制问题简单的xla叫乔治：

I was able to replicate the problem with a simple xla called George:

Public harry As Variant

Public Sub setHarry(x)
    harry = x
End Sub

Public Function getHarry()
    getHarry = harry
End Function

我安装了xla。然后，我创建了一个名为setHarry的文本框，并在其中更改了一个带有= getHarry（）的单元格。我保持它很简单：

I installed the xla. Then I created Alice.xls with a text box that called setHarry when it changed and a cell with =getHarry() in it. I kept it really simple:

Private Sub TextBox1_Change()
    Run "george.xla!setHarry", TextBox1
End Sub

然后我制作了一份Alice.xls作为Bob.xls并运行它们都。正如预期的那样，如果两个工作簿都改变了Harry，那么两个工作簿都会看到结果。

I then made a copy of Alice.xls as Bob.xls and ran them both. As expected, if either workbook changes Harry, both workbooks see the result.

我说这是预期的，因为xla就像dll一样，因为每个人都有一个内存拷贝;这显然包括全局变量，这是有道理的。我只是想测试这个理论。

I say expected because xla's are like dll's in that there's only one copy in memory that everybody shares; that apparently includes global variables, which makes sense. I just wanted to test the theory.

在我看来，解决这个问题的最好方法是使用类模块而不是普通模块。这样，您可以在Workbook_Open事件中为每个工作簿分配其自己的类实例及其变量。如果变量是公开的，你不需要属性获取和设置，尽管如果这样做有价值的话你可以使用它们。

To my mind, the best way to fix this is to use a class module instead of an ordinary module. This way you can give each workbook its own instance of the class and its variables in the Workbook_Open event. If the variables are declared public, you don't need property gets and sets, though you can use them if there's value in doing it.

这篇关于VBA全局变量，多个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！