在VBA中声明全局变量的正确步骤?

I have a macro workbook with a number of worksheets that exist permanently, which are constantly cleared, updated, etc. Since they are referred to in various subroutines, I have made each corresponding worksheet object a pseudo-global variable in the following manner, for example for the "Main" sheet:

我有一个宏工作簿，其中有一些永久存在的工作表，这些工作表不断地被清除、更新等等。由于它们在不同的子例程中被引用，所以我以以下方式将每个相应的工作表对象变成一个伪全局变量，例如“Main”表:

    Function MAIN() As Worksheet
        Set MAIN = ThisWorkbook.Sheets("Main")
    End Function

By doing so, I can then refer to each sheet in the other subroutines, for example:

通过这样做，我就可以参考其他子例程中的每个表，例如:

    MAIN.Cells.ClearContents

I have also defined some pseudo-global constants which are located in a fixed place on the "Main" sheet in a similar way, for example:

我还以类似的方式定义了一些位于“Main”表固定位置的伪全局常数，例如:

    Function NumLines() As Integer
        NumLines = MAIN.Range("C3").Value
    End Function

In this way, I use "NumLines" just like any variable throughout the code. I expect that there is a more efficient way to manage globally accessed variables like these and was wondering, what would be a better way to accomplish this?

通过这种方式，我使用“NumLines”就像代码中的任何变量一样。我希望有一种更有效的方法来管理全局访问的变量，比如这些变量，我想知道，一个更好的方法是什么?

2 个解决方案

#1

For reliable sheet reference I would suggest to use Sheet.CodeName Property. Each sheet has its unique CodeName which you could find in the place marked yellow on the picture below.

对于可靠的表格参考，我建议使用表格。代号的财产。每一页都有自己独特的代号，你可以在下面的图片上找到标有黄色的地方。

For quick reference to cell value I would suggest to use Range name. After you select you C3 cell you need to put unique name in the box marked yellow below. All Range names are unique in the workbook.

对于单元格值的快速引用，我建议使用范围名称。在选择C3单元后，需要在标记为黄色的框中放入唯一的名称。所有范围名称在工作簿中都是惟一的。

As a result you can use sheet and cell reference as presented below in each of your subroutines in your project.

因此，您可以在项目中的每个子例程中使用下面所示的表和单元格引用。

Sub Test_Macro()

    Debug.Print MAIN.Name   '>> result: Sheet1
    Debug.Print Range("CellC3").Value   '>> result: 100

End Sub

#2

I expect that there is a more efficient way to manage globally accessed variables like these and was wondering, what would be a better way to accomplish this?

我希望有一种更有效的方法来管理全局访问的变量，比如这些变量，我想知道，一个更好的方法是什么?

When I use global variables in VBA, I do three things.

在VBA中使用全局变量时，我要做三件事。

1. I always preface global variables with a g_ prefix. It seems often that a global variable in VBA is useful. But I've also spent far too long trying to track down "what variables are global or not?" in other people's code. Keeping a very clear naming convention will save you and whoever looks at your code a TON of hassle in the future.

   我总是在全局变量前面加上g_前缀。VBA中的全局变量似乎常常是有用的。但我也花了太长时间试图在其他人的代码中追踪“什么变量是全局的还是非全局的?”保持一个非常清晰的命名约定将为您和任何查看您的代码的人节省大量的麻烦。

   This is even more important if you are less experienced as a developer. Avoiding globals is hard in VBA, and the less experience you have, the more likely it is you will use globals. For others to help or maintain the code this becomes so important.

   如果您作为开发人员经验不足，那么这一点就更为重要。在VBA中，避免全局是很困难的，而你的经验越少，你就越有可能使用全局变量。对于其他人来说，帮助或维护代码变得非常重要。

2. If you are going to be using even a small number of global variables, you must use Option Explicit unless you want to cause nightmares in maintaining code. It's hard enough to track down these errors when you wrote code let alone months or years later.

   如果您打算使用哪怕是少量的全局变量，您必须使用显式选项，除非您想在维护代码时引起噩梦。当您编写代码时，跟踪这些错误已经足够困难了，更不用说几个月或几年之后了。

3. I always create a module which is called "GlobalVariables" or something similar. That module contains all of the global declarations in one location. For larger code bases this can become longer but it has always paid off for me because I know exactly where all my globals are defined. None of the "which file is this variable actually being defined in?" game.

   我总是创建一个叫做“GlobalVariables”或类似的模块。该模块在一个位置包含所有全局声明。对于更大的代码基，这可能会变得更长，但对我来说总是有好处的，因为我知道所有全局变量的定义在哪里。“这个变量实际上是在哪个文件中定义的?”

---

Just an unrelated note, too, in your first example - I would use the code name rather than that function. Each VBA worksheet has a sheet name ("Main" in your case) as well as a codename, which you can set in VBA and remains the same. This prevents users from changing the name of "Main" and breaking code.

在您的第一个示例中，我还需要注意一点——我将使用代码名而不是那个函数。每个VBA工作表都有一个表名(在您的例子中是“Main”)和一个codename，您可以在VBA中设置它，并保持相同。这可以防止用户更改“Main”的名称并破坏代码。

You can also refer directly to them similar to how you are using MAIN.Cells. KazJaw has a good example of this.

您也可以直接引用它们，类似于您使用MAIN.Cells的方式。KazJaw就是一个很好的例子。