如何使 Excel VBA 变量可用于多个宏? [英] How to make Excel VBA variables available to multiple macros?

问题描述

我有一串宏，它们相互调用并引用工作簿 A 和 B.我希望第一个宏提示用户选择文档 A 和 B，这些选择成为我引用的工作簿 A 和 B 变量在各种宏中.

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.

如何使所选文档成为所有宏中引用的变量?

How do I make the selected documents the referred to variable throughout all the macros?

提前致谢！

推荐答案

在子程序之外声明它们，像这样:

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

或者使用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

在第二种方法中，在OtherSubRoutine 的范围内，您通过它们的参数名称wb1 和wb2 来引用它们.传递的变量不需要使用相同的名称，只需使用相同的变量类型.这允许您有一定的自由度，例如您可以在多个工作簿上循环，并且您可以将每个工作簿发送到一个子例程以对该工作簿执行某些操作，而无需创建所有(或任何)变量范围内公开.

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.

关于用户表单的说明

我个人建议在所有模块和表单中保持 Option Explicit(这可以防止您实例化名称中有拼写错误的变量，例如 lCoutn 当您的意思是 lCount 等，以及其他原因).

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

如果您使用 Option Explicit(您应该)，那么您应该限定模块范围的变量的样式并避免歧义，并且您必须 限定用户表单 Public 范围内的变量，因为它们不是同一个意义上的公共".例如，i 是未定义的，尽管它是 UserForm1 范围内的 Public:

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.i 以避免编译错误，或者由于表单是 New-able，您可以创建一个变量对象来包含对您的表单，并以这种方式引用它:

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:

注意:在上面的截图中，x在另一个标准代码模块中被声明为Public x as Long，不会引发编译错误.最好将其称为 Module2.x 以避免歧义和可能的阴影，以防您重用变量名...

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

这篇关于如何使 Excel VBA 变量可用于多个宏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！