在VBA中初始化全局变量

问题描述

在Excel 2003中，如何声明全局变量并将其初始化一次，即打开工作簿？

In Excel 2003, how can I declare global variables and initialise them only once, ie when the workbook is opened?

我有一些参数被几个宏：基本上输入文件的路径。目前，我的代码如下所示：

I have some parameters that are used by a few macros: paths to input files, basically. At the moment, my code looks like this:

global path1, path2 as string

sub initPaths
    path1 = 'path\to\file1'
    path2 = 'path\to\file2'
end sub

然后，每当我需要在子例程或函数中使用file1或file2时，我会调用 initPaths 。但这似乎是不切实际的;我想只能设置一次路径，而不是重复路径。

Then, whenever I need to use file1 or file2 in a subroutine or function, I insert a call to initPaths. But this seems rather inelegant; I'd like to be able to set the paths only once rather than repeatedly.

推荐答案

从你的例子来看，它看起来像你想要的是常量，而不是全局变量。

From your example, it looks like what you want are constants, not global variables.

Public Const PATH1 = "path\to\file1"
Public Const PATH2 = "path\to\file2"

如果你真的需要使用代码确定值，但只想初始化一次，可以使用lazy-initialization ...

If you really do need to use code to determine the values, but only want to initialize them once, you can use lazy-initialization...

Private mstrPath1 As String
Private mstrPath2 As String

Public Function Path1() As String
    if mstrPath1 = vbNullString Then
        ' Initialize mstrPath1 value here.
    End If
    Path1 = mstrPath1
End Function

Public Function Path2() As String
    if mstrPath2 = vbNullString Then
        ' Initialize mstrPath2 value here.
    End If
    Path2 = mstrPath2
End Function

这里的东西是，如果你的代码被重新设置，那么下次再次通过它们各自的功能访问它们时，这些值就会被重新初始化。

The nice thing here is that if your code ever gets reset, the values simply get re-initialized again next time you access them via their respective functions.

注意，全局变量是尽可能避免，尽可能地总是倾向于公共的全局变量。必要时使用全局变量和公共全局变量，但仅在必要时使用。

Note that global variables are to be avoided as much as possible, and you should always prefer private global variables over public ones where possible. Use globals and public globals where necessary, but only where necessary.

这篇关于在VBA中初始化全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！