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

问题描述

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

在Excel工作簿中：
在 Sheet1 我有两个（2）按钮。

第一个标签为 SetMe ，并链接到 Sheet1模块中的子例程：

Sheet1代码

  Option Explicit 
 Sub setMe（）
 Global1 =Hello
 End Sub 
  

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

ThisWorkbook代码

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$ Global1）
End Sub

点击 SetMe 错误：变量未定义。

当我创建一个单独的模块并移动dec Global1 在一切中都有效。

所以我的问题是：
我读过的一切说，在任何代码之外的模块顶部声明的全局变量应该在项目中的所有模块都可见。显然不是这样。
除非我对模块的理解不正确。

对象 Sheet1 ， Sheet2 ， ThisWorkbook ，...与工作簿一起提供：这些不是能够在全局范围内声明变量的模块？

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

解决方案

您的问题是：
这些不是能够在全局范围内声明变量的模块？

答案：是的，他们有能力

唯一的一点是，对ThisWorkbook或Sheet模块中的全局变量的引用被完全限定（即，称为 ThisWorkbook.Global1 ，例如）
标准模块中对全局变量的引用只有在歧义（例如，如果有多个标准模块定义名称为Global1的变量，以及您的意思是在第三个模块中使用它。）

例如，放在Sheet1代码

  public glob_sh1 As String 
 
 Sub test_sh1（）
 Debug.Print（glob_mod）
 Debug.Print（ThisWorkbook.glob_this）
调试.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 
  

所有三分之一工作正常。

PS1：这个答案基本上是基于这里。这是非常值得阅读的（来自伟大的Chip Pearson）。

PS2：您的行 Debug.Print（Hello）将给你编译错误外部程序无效。

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

PS4：另请参见将Excel-VBA代码放在模块或工作表中？。

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

PS6：有些情况导致不全面限定全局变量的情况。你可以修补一下找到它们。他们是编译错误。

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:

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:

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.

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?

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

解决方案

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

For instance, place in Sheet1 code

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

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.

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

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

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: 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中声明全局变量在Workbook中可见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！