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

问题描述

我有一串宏相互调用并参考工作簿A和B.我想要第一个宏提示用户选择文档A和B，这些选择将成为我所参考的工作簿A和B变量在各种宏中。

如何使所选文档在所有宏中被引用为变量？

谢谢提前！

解决方案

将它们声明在子例程之外，如下所示：

 公共wbA作为工作簿
公共wbB作为工作簿
 Sub MySubRoutine（）
设置wbA = Workbooks.Open（C：\ file.xlsx）
设置wbB = Workbooks.Open（C：\file2.xlsx）
 OtherSubRoutine 
 End Sub 
 Sub OtherSubRoutine（）
 MsgBox wbA.Name，vbInformation 
 End Sub 
  

或者，您可以在子例程之间传递变量： / p>

  Sub MySubRoutine（）
 Dim wbA as Workbook 
 Dim w bB as Workbook 
设置wbA = Workbooks.Open（C：\file.xlsx）
设置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 
  

或使用函数返回值：

  Sub MySubroutine（）
 Dim i as Long 
i = MyFunction（） 
 MsgBox i 
 End Sub 
函数MyFunction（）
'执行某些操作的许多代码
 Dim x As Integer，y as Double 
对于x = 1到1000 
'许多代码做一些
下一个
 MyFunction = y 
结束函数
  

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

关于用户表单的注意事项

我个人建议在您的所有模块和表单中保留 Option Explicit （这样可以防止在名称中使用打字错误实例化变量，例如当您的意思是 lCount 等等，以及其他原因）lCoutn 。

如果您使用 Option Explicit （您应该），那么你应限定样式的模块范围变量并避免歧义，您必须限定用户形式公开范围变量，因为这些变量不是公开的 在同一个意义上。例如， i 未定义，虽然 public 在 UserForm1 ：

您可以参考作为 UserForm1.i 以避免编译错误，或者由于表单是新的可以，您可以创建一个可变对象包含对您表单的引用，并以此方式引用：

注意：在上述截图中 x 被声明为 Public x as Long in another 标准代码模块，不会引起编译错误。可以将它称为 Module2.x 以避免模糊和可能的阴影，以防您重复使用变量名称...

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?

Thanks in advance!

解决方案

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.

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

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:

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:

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屋！