如何在 Excel VBA 中声明全局变量在整个工作簿中可见 [英] How to declare Global Variables in Excel VBA to be visible across the Workbook

问题描述

我有一个关于全局作用域的问题，并将问题抽象为一个简单的例子:

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

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

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:

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

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

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

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

点击 SetMe 会产生编译器error: variable not defined.
当我创建一个单独的模块并将 Global1 的声明移动到其中时，一切正常.

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.

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

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?

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

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

推荐答案

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

答案:是的，他们有能力"

唯一的一点是对 ThisWorkbook 或 Sheet 模块中的全局变量的引用必须是完全限定的(即，称为 ThisWorkbook.Global1，例如)标准模块中对全局变量的引用只有在有歧义的情况下才必须是完全限定的(例如，如果有多个标准模块定义了一个名为 Global1 的变量，并且您的意思是在第三个模块).

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

比如放在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

放入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

并在标准模块代码中

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

所有三个 subs 都可以正常工作.

All three subs work fine.

PS1:此答案主要基于 此处 中的信息.非常值得一读(来自伟大的 Chip Pearson).

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

PS2:你的行 Debug.Print ("Hello") 会给你编译错误 Invalid external procedure.

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

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

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

PS4:同时检查 将 Excel-VBA 代码放入模块或工作表中?.

PS5:您可能无法在例如 Sheet1 中声明全局变量，也无法在其他工作簿的代码中使用它(阅读 http://msdn.microsoft.com/en-us/library/office/gg264241%28v=office.15%29.aspx#sectionSection0; 我没有测试这一点，所以这个问题还有待确认).但是无论如何，您的示例中并不是要这样做.

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.

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

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.

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