如何使Excel VBA变量可用于多个宏?

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!

提前谢谢!

3 个解决方案

#1

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 Functions to return values:

或使用函数返回值:

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 OtherSubRoutine you refer to them by their parameter names wb1 and 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 each workbook to a subroutine to perform some action on that Workbook, without making all (or any) of the variables public in scope.

在第二种方法中，在另一个子例程的范围内，通过它们的参数名称wb1和wb2来引用它们。传递的变量不需要使用相同的名称，只是相同的变量类型。这允许您有一些*，例如，您对几个工作簿有一个循环，并且您可以将每个工作簿发送到一个子例程来对该工作簿执行一些操作，而不会使范围内的所有(或任何)变量都是公共的。

A Note About User Forms

关于用户表单的说明

Personally I would recommend keeping Option Explicit in all of your modules and forms (this prevents you from instantiating variables with typos in their names, like lCoutn when you meant lCount etc., among other reasons).

就我个人而言，我建议您在所有的模块和表单中都明确地保留选项(这可以防止您在名称中使用拼写错误实例化变量，比如lCoutn，在其他原因中)。

If you're using Option Explicit (which you should), then you should qualify module-scoped variables for style and to avoid ambiguity, and you must qualify user-form Public scoped variables, as these are not "public" in the same sense. For instance, i is undefined, though it's Public in the scope of UserForm1:

如果您使用的是显式选项(您应该使用)，那么您应该将模块范围内的变量限定为样式，并避免歧义，并且您必须限定用户表单公共范围内的变量，因为这些变量在同一意义上不是“公共”的。例如，我没有定义，尽管它在UserForm1的范围内是公开的:

You can refer to it as UserForm1.i to 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。我要避免编译错误，或者由于表单是可更新的，您可以创建一个变量对象来包含对表单的引用，并以这种方式引用它:

NB: In the above screenshots x is declared Public x as Long in another standard code module, and will not raise the compilation error. It may be preferable to refer to this as Module2.x to avoid ambiguity and possible shadowing in case you re-use variable names...

NB:在上面的截图中，x在另一个标准代码模块中被声明为Public x，不会引起编译错误。最好将其称为Module2。为了避免歧义和可能的阴影，如果你重复使用变量名…

#2

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

您可以考虑使用moudule级别范围声明变量。模块级变量对该模块中的所有过程都可用，但对其他模块中的过程不可用

For details on Scope of variables refer this link

有关变量范围的详细信息，请参阅此链接

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来检查工作簿是否已经设置了工作簿变量，然后将工作簿名称设置为else，打开将相应地分配给工作簿变量的工作簿。

Dim wbA As Workbook
Dim wbB As Workbook

将wbA设置为工作簿，将wbB设置为工作簿

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 select the 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

#3

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查询def中也可用。