VBA 全局变量，多个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3777446/
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
VBA global variables, multiple workbooks
提问by Sam Cogan
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.
我有一个 VB 应用程序，它使用一些全局变量来存储多个表单和模块所需的数据，这很好用。但是，如果用户打开另一个工作簿，运行相同的 VBA 应用程序，那么他们最终会访问（和更改）相同的公共变量。
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?
我怎样才能拥有工作簿级别的全局变量，或者如果这不可能，那么存储所有表单和模块都可以访问但只能在特定工作簿内访问的数据的最佳方法是什么？
This is a VBA addin, and the Global variables are declared in a standard module.
这是一个 VBA 插件，全局变量在标准模块中声明。
采纳答案by Marc Thibault
I was able to replicate the problem with a simple xla called George:
我能够用一个名为 George 的简单 xla 来复制这个问题：
Public harry As Variant
Public Sub setHarry(x)
harry = x
End Sub
Public Function getHarry()
getHarry = harry
End Function
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:
我安装了xla。然后我创建了 Alice.xls，其中包含一个在更改时调用 setHarry 的文本框和一个包含 =getHarry() 的单元格。我保持非常简单：
Private Sub TextBox1_Change()
Run "george.xla!setHarry", TextBox1
End Sub
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.
然后我将 Alice.xls 复制为 Bob.xls 并运行它们。正如预期的那样，如果任一工作簿更改 Harry，两个工作簿都会看到结果。
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.
我说是预期的，因为 xla 就像 dll，因为每个人共享的内存中只有一个副本；这显然包括全局变量，这是有道理的。我只是想测试一下这个理论。
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.
在我看来，解决这个问题的最好方法是使用类模块而不是普通模块。通过这种方式，您可以在 Workbook_Open 事件中为每个工作簿提供其自己的类实例及其变量。如果变量被声明为 public，则不需要属性 get 和 set，但如果这样做有价值，您可以使用它们。
回答by Dick Kusleika
Public myvar as Variant
Variables declared as Public are scoped to the workbook they're in. If you have another workbook with myvar, changing it's value in one won't change it in the other. Perhaps you have an event that's setting that variable from the activesheet that's firing in both projects and setting the variables to the same thing.
声明为 Public 的变量的作用域是它们所在的工作簿。如果你有另一个带有 myvar 的工作簿，在一个工作簿中更改它的值不会在另一个工作簿中更改它。也许您有一个事件，它从两个项目中触发的活动表中设置该变量并将变量设置为相同的内容。
回答by Charles Williams
Your global variables are globally scoped to your Addin. I would store them in hidden names in each workbook, and then reset your global variables each time a workbook is activated (and also re-store the values in the deactivated workbook).
您的全局变量全局作用于您的插件。我会将它们存储在每个工作簿中的隐藏名称中，然后在每次激活工作簿时重置全局变量（并将值重新存储在停用的工作簿中）。
回答by Oneide
I think you could get the workbook name in runtime.
我认为您可以在运行时获取工作簿名称。
So, a Collection probably will do the job.
因此，集合可能会完成这项工作。
'//-- module --
Public var As Collection
Public Function SetVar(Value)
If var Is Nothing Then
Set var = New Collection
End If
var.Add Value, ThisWorkbook.Name
End Function
Public Function GetVar() As Variant
If var Is Nothing Then
GetVar = Null
Else
GetVar = var.Item(ThisWorkbook.Name)
End If
End Function
Not sure if it'll work for your Addin though.
不确定它是否适用于您的插件。
