VBA如何将用户窗体变量传递到子例程

Question

我正试图使用一个子程序从用户表单输入中调用一个变量。但是，即使我可以通过将userform输入打印到工作表来在userform中正确显示它，我似乎也不能将它作为公共变量传递给子例程。当我调用子程序中的变量时，它们都是空的。

我用这个作为指南：VBA 中从窗体到模块的变量传递

这是我的代码：

Public a As String
Public b As Double
Public c As String
Private Sub OKButton_Click()
'from userform
a = TextBox1.Value
b = TextBox2.Value
c = TextBox3.Value
Cells(1, 1).Value = a
Cells(2, 1).Value = b
Cells(3, 1).Value = c
'this displays the inputs properly
Unload UserForm1
End Sub

在模块中：

Public Sub Login()
'in module
UserForm1.Show
MsgBox (a)
MsgBox (b)
MsgBox (c)
End Sub

Answer 1

这样做。将您的公共变量放入模块代码中。

Public a As String
Public b As Double
Public c As String
Public Sub Login()
'in module
UserForm1.Show
MsgBox (a)
MsgBox (b)
MsgBox (c)
End Sub

Answer 2

我在开发一个用户应用程序时遇到了与您现在面临的问题相同的问题，下面是我得到的答案。

请查看上面的链接，因为Mat's Mugs的解释超出了我解释主题的能力。但这里有一个缩写。

基本上你要做的事情如下。您有三个类：模型、视图和演示者类。这听起来超级复杂，但一旦你掌握了窍门，就不会那么困难了

型号

是存储所有数据的类模块。因此，没有声明一堆公共变量包，而是有一个存储所有数据的大类。您也可以有多个模型类和类作为类成员，但为了简单起见，我们只接受上面提到的三个整数。

下面是一个model类的例子：(将所有类放在一个名为model的类模块中(

Option Explicit
' encapsulated data
Private Type TModel
a As Integer
b As Integer
c As Integer
End Type
Private this As TModel
' property get and property let define the way you can interact with data
Public Property Get a() As String
a = this.a
End Property
Public Property Let a(ByVal value As String)
this.a = value
End Property
Public Property Get b() As String
b = this.b
End Property
Public Property Let b(ByVal value As String)
this.b = value
End Property
Public Property Get c() As String
c = this.c
End Property
Public Property Let c(ByVal value As String)
this.c = value
End Property

视图

这是您的用户表单。但是你的UserForm又是一个类，所以除了所有其他代码之外，你还有这个代码：

Private Type TView
M As Model
IsCancelled As Boolean
IsBack As Boolean
End Type
Private this As TView
Public Property Get Model() As Model
Set Model = this.M
End Property
Public Property Set Model(ByVal value As UImodel)
Set this.M= value
'Validate
End Property
' This is responsible for not destroying all data you have when you x-out the userform
Public Property Get IsCancelled() As Boolean
IsCancelled = this.IsCancelled
End Property
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = VbQueryClose.vbFormControlMenu Then
this.IsCancelled=True
Cancel = True
OnCancel
End If
End Sub
Private Sub OKButton_Click()
Model.a = TextBox1.value
Model.b = TextBox2.value
Model.c = TextBox3.value
Cells(1, 1).value = Model.a
Cells(2, 1).value = Model.b
Cells(3, 1).value = Model.c
'this displays the inputs properly
Me.Hide
End Sub

演示者

这是一个正常的模块。你可以把你的代码放在你使用的地方

Public Sub Login()
'in module
Dim Ufrm As New UserForm1
Dim M As New Model
Set Ufrm.Model = M
Ufrm.Show
If Ufrm.IsCancelled Then Exit Sub
Set M = Ufrm.Model
MsgBox M.a
MsgBox M.b
MsgBox M.c
End Sub