如何在Excel VBA中声明全局变量以便在工作簿中可见

I have a question about global scope and have abstracted the problem into a simple example:

我有一个关于全球范围的问题，并将问题抽象为一个简单的例子:

In an Excel Workbook: In Sheet1 I have two(2) buttons.
The first is labeled SetMe and is linked to a subroutine in Sheet1's module:
Sheet1 code:

在Excel工作簿中:在Sheet1中我有两个(2)按钮。第一个标记为SetMe，并链接到Sheet1模块中的子例程:Sheet1代码:

Option Explicit
Sub setMe()
    Global1 = "Hello"
End Sub

The second is labeled ShowMe and is linked to a subroutine in ThisWorkbook's module:
ThisWorkbook code:

第二个标记为ShowMe，并链接到这个工作簿模块中的子例程:ThisWorkbook代码:

Option Explicit
Public Global1 As String
Debug.Print("Hello")
Sub showMe()
    Debug.Print (Global1)
End Sub

Clicking on SetMe produces a compiler error: variable not defined.
When I create a separate module and move the declaration of Global1 into it everything works.

单击SetMe会产生编译器错误:变量未定义。当我创建一个单独的模块并将Global1的声明移动到其中时，一切都正常。

So my question is: Everything I have read says that Global variables, declared at the top of a module, outside of any code should be visible to all modules in the project. Clearly this is not the case. Unless my understanding of Module is not correct.
The objects Sheet1, Sheet2, ThisWorkbook,... that come with a workbook: are these not modules capable of declaring variables at global scope?

所以我的问题是:我读过的所有内容都表明，在模块顶部声明的全局变量，在任何代码之外，都应该对项目中的所有模块可见。显然，事实并非如此。除非我对模块的理解不正确。对象Sheet1, Sheet2, ThisWorkbook，…附带一个工作簿:这些模块是否能够在全局范围内声明变量?

Or is the only place one can declare a global, in a separate module of type Modules.

或者是唯一可以在类型模块的单独模块中声明全局变量的地方。

2 个解决方案

#1

Your question is: are these not modules capable of declaring variables at global scope?

您的问题是:这些模块是否能够在全局范围内声明变量?

Answer: YES, they are "capable"

回答:是的，他们是“有能力的”

The only point is that references to global variables in ThisWorkbook or a Sheet module have to be fully qualified (i.e., referred to as ThisWorkbook.Global1, e.g.) References to global variables in a standard module have to be fully qualified only in case of ambiguity (e.g., if there is more than one standard module defining a variable with name Global1, and you mean to use it in a third module).

唯一的要点是，在这个工作簿或一个表单模块中对全局变量的引用必须是完全限定的(即:，也就是这本书。Global1，例如)在一个标准模块中引用全局变量必须是完全限定的，只有在模棱两可的情况下(例如，如果有多个标准模块定义一个名为Global1的变量，而您的意思是在第三个模块中使用它)。

For instance, place in Sheet1 code

例如，在Sheet1代码中放置

Public glob_sh1 As String

Sub test_sh1()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

place in ThisWorkbook code

在ThisWorkbook代码

Public glob_this As String

Sub test_this()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

and in a Standard Module code

在一个标准的模块代码中

Public glob_mod As String

Sub test_mod()
    glob_mod = "glob_mod"
    ThisWorkbook.glob_this = "glob_this"
    Sheet1.glob_sh1 = "glob_sh1"
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

All three subs work fine.

这三辆潜艇都可以工作。

PS1: This answer is based essentially on info from here. It is much worth reading (from the great Chip Pearson).

这个答案基本上是基于这里的信息。这本书很值得一读。

PS2: Your line Debug.Print ("Hello") will give you the compile error Invalid outside procedure.

PS2:你行调试。打印(“Hello”)将会给您编译错误无效的外部过程。

PS3: You could (partly) check your code with Debug -> Compile VBAProject in the VB editor. All compile errors will pop.

PS3:您可以(部分)在VB编辑器中使用Debug ->编译VBAProject检查代码。所有编译错误都会弹出。

PS4: Check also Put Excel-VBA code in module or sheet?.

PS4:检查是否将优秀的vba代码放在模块或表格中?

PS5: You might be not able to declare a global variable in, say, Sheet1, and use it in code from other workbook (reading http://msdn.microsoft.com/en-us/library/office/gg264241%28v=office.15%29.aspx#sectionSection0; I did not test this point, so this issue is yet to be confirmed as such). But you do not mean to do that in your example, anyway.

PS5:您可能无法在Sheet1中声明全局变量，并且无法在其他工作簿中的代码中使用它(请参阅http://msdn.microsoft.com/en-us/library/office/gg264241% 28%的v=office.15%29.aspx# section0;我没有测试这一点，所以这个问题还没有得到证实)。但在你的例子中，你并不想那样做。

PS6: There are several cases that lead to ambiguity in case of not fully qualifying global variables. You may tinker a little to find them. They are compile errors.

PS6:在没有完全限定全局变量的情况下，有几种情况会导致歧义。你可以稍微修补一下以找到它们。它们是编译错误。

#2

You can do the following to learn/test the concept:

你可按以下方法学习/测试概念:

1. Open new Excel Workbook and in Excel VBA editor right-click on Modules->Insert->Module

   打开新的Excel工作簿，在Excel VBA编辑器中右键单击模块—>插入—>模块

2. In newly added Module1 add the declaration; Public Global1 As String

   在新添加的Module1中添加声明;公共Global1字符串

3. in Worksheet VBA Module Sheet1(Sheet1) put the code snippet:

   在工作表VBA模块Sheet1(Sheet1)中放入代码片段:

Sub setMe()
      Global1 = "Hello"
End Sub

4. in Worksheet VBA Module Sheet2(Sheet2) put the code snippet:
5. 在工作表VBA模块Sheet2(Sheet2)中放入代码片段:

Sub showMe()
    Debug.Print (Global1)
End Sub

5. Run in sequence Sub setMe() and then Sub showMe() to test the global visibility/accessibility of the var Global1
6. 在序列子setMe()和子showMe()中运行，以测试var Global1的全局可见性/可访问性

Hope this will help.

希望这将帮助。