如何使 Excel VBA 变量可用于多个宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16374253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make Excel VBA variables available to multiple macros?
提问by Sean Connecticut
I have a string of macros that call upon each other and refer to workbooks A and B. I want the first macro to prompt the user to select document A and B and these Selections to become the workbook A and B variables I refer to in the various macros.
我有一串宏，它们相互调用并引用工作簿 A 和 B。我希望第一个宏提示用户选择文档 A 和 B，这些选择成为我在各种宏。
How do I make the selected documents the referred to variable throughout all the macros?
如何使所选文档成为所有宏中的引用变量？
Thanks in advance!
提前致谢！
回答by David Zemens
Declare them outside the subroutines, like this:
在子程序之外声明它们，如下所示：
Public wbA as Workbook
Public wbB as Workbook
Sub MySubRoutine()
Set wbA = Workbooks.Open("C:\file.xlsx")
Set wbB = Workbooks.Open("C:\file2.xlsx")
OtherSubRoutine
End Sub
Sub OtherSubRoutine()
MsgBox wbA.Name, vbInformation
End Sub
Alternately, you can pass variables between subroutines:
或者，您可以在子程序之间传递变量：
Sub MySubRoutine()
Dim wbA as Workbook
Dim wbB as Workbook
Set wbA = Workbooks.Open("C:\file.xlsx")
Set wbB = Workbooks.Open("C:\file2.xlsx")
OtherSubRoutine wbA, wbB
End Sub
Sub OtherSubRoutine(wb1 as Workbook, wb2 as Workbook)
MsgBox wb1.Name, vbInformation
MsgBox wb2.Name, vbInformation
End Sub
Or use Functionsto return values:
或者使用Functions以返回值：
Sub MySubroutine()
Dim i as Long
i = MyFunction()
MsgBox i
End Sub
Function MyFunction()
'Lots of code that does something
Dim x As Integer, y as Double
For x = 1 to 1000
'Lots of code that does something
Next
MyFunction = y
End Function
In the second method, within the scope of OtherSubRoutineyou refer to them by their parameter names wb1and wb2. Passed variables do not need to use the same names, just the same variable types. This allows you some freedom, for example you have a loop over several workbooks, and you can send eachworkbook to a subroutine to perform some action on that Workbook, without making all (or any) of the variables public in scope.
在第二种方法中，在OtherSubRoutine你的范围内通过它们的参数名称wb1和wb2. 传递的变量不需要使用相同的名称，只需使用相同的变量类型。这允许您有一定的自由度，例如您可以在多个工作簿上循环，并且您可以将每个工作簿发送到一个子例程以对该工作簿执行某些操作，而无需在范围内公开所有（或任何）变量。
A Note About User Forms
关于用户表单的说明
Personally I would recommend keepingOption Explicitin all of your modules and forms (this prevents you from instantiating variables with typos in their names, like lCoutnwhen you meant lCountetc., among other reasons).
我个人建议保留Option Explicit所有模块和表单（这可以防止您实例化名称中有拼写错误的变量，例如lCoutn您的意思lCount等，以及其他原因）。
If you're using Option Explicit(which you should), then you should qualify module-scoped variables for style and to avoid ambiguity, and you mustqualify user-form Publicscoped variables, as these are not "public" in the same sense. For instance, iis undefined, though it's Publicin the scope of UserForm1:
如果您正在使用Option Explicit（您应该使用），那么您应该限定模块范围的变量的样式并避免歧义，并且您必须限定用户表单Public范围的变量，因为它们不是相同意义上的“公共”。例如，i未定义，尽管它Public在以下范围内UserForm1：
You can refer to it as UserForm1.ito avoid the compile error, or since forms are New-able, you can create a variable object to contain reference to your form, and refer to it that way:
您可以引用它UserForm1.i以避免编译错误，或者由于表单是New-able，您可以创建一个变量对象来包含对表单的引用，并以这种方式引用它：
NB: In the above screenshots xis declared Public x as Longin another standardcode module, and will not raise the compilation error. It may be preferable to refer to this as Module2.xto avoid ambiguity and possible shadowing in case you re-use variable names...
注意：在上面的截图中x是Public x as Long在另一个标准代码模块中声明的，不会引发编译错误。最好参考这个Module2.x以避免歧义和可能的阴影，以防你重用变量名......
回答by Santosh
You may consider declaring the variables with moudule level scope. Module-level variable is available to all of the procedures in that module, but it is not available to procedures in other modules
您可以考虑使用模块级范围声明变量。模块级变量可用于该模块中的所有过程，但不可用于其他模块中的过程
For details on Scope of variablesrefer this link
有关详细信息，Scope of variables请参阅此链接
Please copy the below code into any module, save the workbook and then run the code.
请将以下代码复制到任何模块中，保存工作簿，然后运行代码。
Here is what code does
这是代码的作用
The sample subroutine sets the folder path & later the file path. Kindly set them accordingly before you run the code.
I have added a function IsWorkBookOpen to check if workbook is already then set the workbook variable the workbook name else open the workbook which will be assigned to workbook variable accordingly.
示例子例程设置文件夹路径，然后设置文件路径。在运行代码之前，请相应地设置它们。
我添加了一个函数 IsWorkBookOpen 来检查工作簿是否已经存在，然后将工作簿变量设置为工作簿名称，否则打开将相应地分配给工作簿变量的工作簿。
Dim wbA As WorkbookDim wbB As Workbook
Dim wbA As WorkbookDim wbB As Workbook
Sub MySubRoutine()
Dim folderPath As String, fileNm1 As String, fileNm2 As String, filePath1 As String, filePath2 As String
folderPath = ThisWorkbook.Path & "\"
fileNm1 = "file1.xlsx"
fileNm2 = "file2.xlsx"
filePath1 = folderPath & fileNm1
filePath2 = folderPath & fileNm2
If IsWorkBookOpen(filePath1) Then
Set wbA = Workbooks(fileNm1)
Else
Set wbA = Workbooks.Open(filePath1)
End If
If IsWorkBookOpen(filePath2) Then
Set wbB = Workbooks.Open(fileNm2)
Else
Set wbB = Workbooks.Open(filePath2)
End If
' your code here
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Using Prompt to selectthe file use below code.
使用提示选择文件使用下面的代码。
Dim wbA As Workbook
Dim wbB As Workbook
Sub MySubRoutine()
Dim folderPath As String, fileNm1 As String, fileNm2 As String, filePath1 As String, filePath2 As String
Dim filePath As String
cmdBrowse_Click filePath, 1
filePath1 = filePath
'reset the variable
filePath = vbNullString
cmdBrowse_Click filePath, 2
filePath2 = filePath
fileNm1 = GetFileName(filePath1, "\")
fileNm2 = GetFileName(filePath2, "\")
If IsWorkBookOpen(filePath1) Then
Set wbA = Workbooks(fileNm1)
Else
Set wbA = Workbooks.Open(filePath1)
End If
If IsWorkBookOpen(filePath2) Then
Set wbB = Workbooks.Open(fileNm2)
Else
Set wbB = Workbooks.Open(filePath2)
End If
' your code here
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Private Sub cmdBrowse_Click(ByRef filePath As String, num As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select workbook " & num
fd.InitialView = msoFileDialogViewSmallIcons
Dim FileChosen As Integer
FileChosen = fd.Show
fd.Filters.Clear
fd.Filters.Add "Excel macros", "*.xlsx"
fd.FilterIndex = 1
If FileChosen <> -1 Then
MsgBox "You chose cancel"
filePath = ""
Else
filePath = fd.SelectedItems(1)
End If
End Sub
Function GetFileName(fullName As String, pathSeparator As String) As String
Dim i As Integer
Dim iFNLenght As Integer
iFNLenght = Len(fullName)
For i = iFNLenght To 1 Step -1
If Mid(fullName, i, 1) = pathSeparator Then Exit For
Next
GetFileName = Right(fullName, iFNLenght - i)
End Function
回答by Steven
Create a "module" object and declare variables in there. Unlike class-objects that have to be instantiated each time, the module objects are always available. Therefore, a public variable, function, or property in a "module" will be available to all the other objects in the VBA project, macro, Excel formula, or even within a MS Access JET-SQL query def.
创建一个“模块”对象并在其中声明变量。与每次都必须实例化的类对象不同，模块对象始终可用。因此，“模块”中的公共变量、函数或属性将可用于 VBA 项目、宏、Excel 公式中的所有其他对象，甚至在 MS Access JET-SQL 查询定义中。
