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

How to declare Global Variables in Excel VBA to be visible across the Workbook

提问by user2978241

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

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

In an Excel Workbook: In Sheet1I have two(2) buttons.
The first is labeled SetMeand 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 ShowMeand is linked to a subroutine in ThisWorkbook's module:
ThisWorkbook code:

第二个标记为ShowMe并链接到ThisWorkbook 模块中的子例程：
ThisWorkbook 代码：

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

Clicking on SetMeproduces a compiler error: variable not defined.
When I create a separate module and move the declaration of Global1into it everything works.

单击SetMe会生成一个编译器error: variable not defined。
当我创建一个单独的模块并将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 Moduleis not correct.
The objects Sheet1, Sheet2, ThisWorkbook,... that come with a workbook: are these not modules capable of declaring variables at global scope?

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

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

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

回答by sancho.s ReinstateMonicaCellio

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).

唯一的一点是对 ThisWorkbook 或 Sheet 模块中的全局变量的引用必须是完全限定的（即，称为ThisWorkbook.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).

PS1：此答案主要基于此处的信息。非常值得一读（来自伟大的 Chip Pearson）。

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

PS2：你的线路Debug.Print ("Hello")会给你编译错误Invalid outside procedure。

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

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

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

PS4：检查是否也将 Excel-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%28v= office.15%29.aspx#sectionSection0；我没有测试这一点，所以这个问题还有待确认）。但是无论如何，您并不是要在您的示例中这样做。

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：在没有完全限定全局变量的情况下，有几种情况会导致歧义。你可以稍加修改才能找到它们。它们是编译错误。

回答by Alexander Bell

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

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

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

1. 打开新的 Excel 工作簿并在 Excel VBA 编辑器中右键单击模块->插入->模块

2. 在新添加的 Module1 中添加声明； Public Global1 As String

3. 在工作表 VBA 模块 Sheet1(Sheet1) 中放置代码片段：

Sub setMe()
      Global1 = "Hello"
End Sub

Sub setMe()
      Global1 = "Hello"
End Sub

1. in Worksheet VBA Module Sheet2(Sheet2) put the code snippet:

4. 在工作表 VBA 模块 Sheet2(Sheet2) 中放置代码片段：

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

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

1. Run in sequence Sub setMe()and then Sub showMe()to test the global visibility/accessibility of the var Global1

5. 依次运行 SubsetMe()和 SubshowMe()以测试 var 的全局可见性/可访问性Global1

Hope this will help.

希望这会有所帮助。