vba 在用户窗体中定义要在模块中使用的变量

Defining Variables in Userform to be used in module

提问by Doug

I have seen similar questions raised both on this forum and others, and I tried all combinations I could think of before posting this. I also referenced a few textbooks and more technical sites concerning userforms and cannot seem to get it right.

我在这个论坛和其他论坛上都看到过类似的问题，我在发布之前尝试了所有我能想到的组合。我还参考了一些有关用户表单的教科书和更多技术站点，但似乎无法正确理解。

I create variables in a user form that I want to then reference in my module. I have tried labeling them as global, defining them on both the userform and module, on just the userform, on just the module, and a few other combinations but have not gotten it correct.

我在用户表单中创建变量，然后我想在我的模块中引用这些变量。我尝试将它们标记为全局，在用户窗体和模块上、仅在用户窗体上、仅在模块上以及其他一些组合上定义它们，但没有得到正确的。

This is what I have as of now and by everything I have read should be correct:

这就是我现在所拥有的，并且我所阅读的一切都应该是正确的：

*In declaration section of Userform:

*在用户表单的声明部分：

 Dim ABC as Workbook
 Dim Primary as Workbook

---

---

*In a command button section of Userform

*在用户窗体的命令按钮部分

For j = 0 To (ListBox1.ListCount - 1)
    If ListBox1.List(j) Like "ABC" Then
        Set ABC = Workbooks(ListBox1.List(j))
        ABC.Activate
        Exit For
    End If
Next

For i = 0 To (ListBox1.ListCount - 1)
    If ListBox1.List(i) Like "Aggregate" Then
        Set Primary = Workbooks(ListBox1.List(i))
        Primary.Activate
        Exit For
    End If
Next 

*This continues for a few more variables, each indexed on a different letter.

*这对于更多的变量继续进行，每个变量都以不同的字母索引。

In module, I then have something like this:

在模块中，我有这样的事情：

        Primary.Sheets("Summary").Range("A5:H40").CopyPicture Appearance:=xlScreen, Format:=xlPicture

My question, as also stated above, is how do I define these workbooks (Primary, ABC, etc.) so that I can use them in my module, like the example above.

如上所述，我的问题是如何定义这些工作簿（主要、ABC 等），以便我可以在我的模块中使用它们，就像上面的示例一样。

And when using in my module, can I just write

在我的模块中使用时，我可以写

Primary.Sheets(.......)....

Primary.Sheets(.......)....

Or do I need to do Workbooks("Primary").Sheets(....)....

或者我需要做 Workbooks("Primary").Sheets(....)....

Maybe I should Dim Primary as String and go about it that way?

也许我应该把 Primary 作为 String 调暗并那样做？

Thanks!

谢谢！

回答by

In your normal module on the top (below Option Explicitand above the first Sub) stick these few lines

在顶部的普通模块中（在Option Explicit下方和第一个Sub上方）粘贴这几行

Public Primary As Workbook
Public ABC As Workbook

Sub Main()

    UserForm1.Show

    If Not ABC is Nothing Then
        ABC.Sheets(1).Range("A1") = "activated ABC workbooks sheet 1"
    End If

    If Not Primary Is Nothing Then
        Primary.Sheets(1).Range("B1") = "activated primary workbook sheet 1"
    End If
    Unload UserForm1

End Sub

Declaring both Primary and ABCas public workbook objectsallows you to access them anywhere in your VBA Project. This means they are accessible in Modules, Classes, and Userforms.

将两者都声明Primary and ABC为public workbook objects允许您在 VBA 项目中的任何位置访问它们。这意味着它们可以在Modules, Classes, and Userforms.

If you want to declare Public/Global variables you always have to use the regular Module objects not the Userform ones. They can only have private fields.

如果你想声明公共/全局变量，你总是必须使用常规的 Module 对象而不是 Userform 的对象。它们只能有私有字段。

Now, your Userform1

现在，你的 Userform1

Private Sub CommandButton1_Click()

    Dim j As Long

    For j = 0 To (ListBox1.ListCount - 1)
        If ListBox1.List(j) Like "ABC" Then
            Set ABC = Workbooks(ListBox1.List(j))
            ABC.Activate
            Exit For
        End If
    Next

    For j = 0 To (ListBox1.ListCount - 1)
        If ListBox1.List(j) Like "Aggregate" Then
            Set Primary = Workbooks(ListBox1.List(j))
            Primary.Activate
            Exit For
        End If
    Next

End Sub

To prove the point you, once you've Setyour ABCand Primaryworkbooks add a Debug.Print ABC.Nameline to print out the Names. If nothing goes wrong then open Immediate Windowwith CTRL+Gand you will see the workbooks names printed out.

为了证明这一点，你，一旦你Set的ABC和Primary工作簿添加Debug.Print ABC.Name行打印出来的名称。如果没有任何问题，请Immediate Window使用CTRL+打开，G您将看到打印出的工作簿名称。

---

---

The other way around would be to send workbooks names through string parameters to a code module sub. You have to create a Sub in the code module taking workbooks names as String parameters. So, still your *UserForm1*

另一种方法是通过字符串参数将工作簿名称发送到代码模块子。您必须在代码模块中创建一个将工作簿名称作为字符串参数的 Sub。所以，还是你的*UserForm1*

For j = 0 To (ListBox1.ListCount - 1)
    If ListBox1.List(j) Like "ABC" Then
        MySubInCodeModule Workbooks(ListBox1.List(j))
        Exit For
    End If
Next

then in your code module

然后在你的代码模块中

Sub MySubInCodeModule(wbName As String)

    Dim wb As Workbook
    Set wb = Workbooks(wbName)

    wb.Sheets(1).Range("A1") = "activate workbook: " & wb.Name
    wb.Sheets(1).Range("B1") = "active sheet: " & wb.ActiveSheet.Name

    'closing the active workbook
    wb.Close
    Set wb = Nothing
End Sub

I hope this makes sense :) I can't even think of another way of explaining it.

我希望这是有道理的:) 我什至想不出另一种解释方式。