Excel VBA 获取在"UserForm1"私有子脚本中找到的值(整数 K)，转到"模块 1"子脚本

Question

我无法将我的值K(整数)从UserForm1中的Private Sub转移到Module1中的Sub。 我使用UserForm1来声明我要在哪个工作表上运行代码(通过从弹出的下拉列表中选择，然后将其声明为 2 到 9 之间的值/整数)，所以我只需要将我在UserForm1Private Sub中找到的值K转移到我Module1中的Sub。

我希望这是可以理解的，即我希望能够读取KUserForm1中找到的值，在我的Module1脚本中。

我当前的代码如下，从Module部分开始：

Sub HenteMengderFraAutoCAD()
Dim K As Integer
Load UserForm1
UserForm1.Show
MsgBox (K)
Unload UserForm1
End Sub

接下来是我在UserForm中的代码，我在其中找到了要在代码中使用的值：

Private Sub UserForm_Activate()
ComboBox1.Clear
With ComboBox1
.AddItem "M350 og XT"
.AddItem "STB 300+450"
.AddItem "Alufix"
.AddItem "MevaDec og MevaFlex"
.AddItem "Alshor Plus"
.AddItem "Rapidshor"
.AddItem "KLK og Sjaktdragere"
End With
End Sub
Private Sub CommandButton1_Click()
If ComboBox1 = "M350 og XT" Then
K = 2
ElseIf ComboBox1 = "STB 300+450" Then
K = 3
ElseIf ComboBox1 = "Alufix" Then
K = 4
ElseIf ComboBox1 = "MevaDec og MevaFlex" Then
K = 5
ElseIf ComboBox1 = "Alshor Plus" Then
K = 6
ElseIf ComboBox1 = "Rapidshor" Then
K = 7
ElseIf ComboBox1 = "KLK og Sjaktdragere" Then
K = 9
End If
MsgBox (K)
UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

实际结果是Module1脚本中的MsgBox(K)将显示与MsgBox(K)UserForm1中显示的数字相同的数字。 现在我在MsgBox in the UserForm1中获得了正确的K值(2 到 9，具体取决于我在下拉列表中选择的内容)，但在Module1 MsgBox中我只得到0.

提前，谢谢。

Answer 1

UserForms是对象。从对象读取/写入值的推荐和可靠方法是使用属性。您可以创建一个属性，然后在模块中访问它

---

示例代码。有关详细信息，请阅读代码注释。

用户表格：

Option Explicit
'/ backing field for the custom property
Private m_MyProperty                As Long
'/ A public variable. Not recommended.
Public lAccessibleVariable          As Long
'/ Define property setters and getters
Public Property Let MyProperty(val As Long)
m_MyProperty = val
End Property
Public Property Get MyProperty() As Long
MyProperty = m_MyProperty
End Property
Private Sub CommandButton1_Click()
'/ Do something to the property
MyProperty = 10
lAccessibleVariable = 100
'/ Make sure you just hide the form and not close(destroy it)
Me.Hide
End Sub

模块

Sub test()
'/ Create an instance of the user form
Dim frm As New UserForm1
Dim lValFromUserForm  As Long
'/ launch the user form
frm.Show

'/ Read back the property value
lValFromUserForm = frm.MyProperty
'/ do something with the returned value
MsgBox lValFromUserForm
'/Just for example, access the public variable.
MsgBox frm.lAccessibleVariable
'/ Now that you are done, destroy the user form
Unload frm

End Sub

Answer 2

如果在用户表单代码中，您将UserForm1的内部引用更改为Me，即

UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1

自

Me.Hide
End Sub
Private Sub CommandButton2_Click()
Unload Me

并在用户窗体中声明一个公共变量，如下所示：

Public K As Integer

然后，您可以使用：

Sub HenteMengderFraAutoCAD()
Dim K As Integer
With New UserForm1
.Show
K = .K
End With
MsgBox (K)
End Sub

---

完整的用户表单代码

Option Explicit
Public K As Integer
Private Sub UserForm_Activate()
ComboBox1.Clear
With ComboBox1
.AddItem "M350 og XT"
.AddItem "STB 300+450"
.AddItem "Alufix"
.AddItem "MevaDec og MevaFlex"
.AddItem "Alshor Plus"
.AddItem "Rapidshor"
.AddItem "KLK og Sjaktdragere"
End With
End Sub
Private Sub CommandButton1_Click()
If ComboBox1 = "M350 og XT" Then
K = 2
ElseIf ComboBox1 = "STB 300+450" Then
K = 3
ElseIf ComboBox1 = "Alufix" Then
K = 4
ElseIf ComboBox1 = "MevaDec og MevaFlex" Then
K = 5
ElseIf ComboBox1 = "Alshor Plus" Then
K = 6
ElseIf ComboBox1 = "Rapidshor" Then
K = 7
ElseIf ComboBox1 = "KLK og Sjaktdragere" Then
K = 9
End If
MsgBox (K)
Me.Hide
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

Answer 3

我的方法同意Brian M Stafford的观点。

1st：在任何子例程之前将 K 声明为 UserForm1 下的公共变量，

public K as integer

第二：

Sub HenteMengderFraAutoCAD()
Load UserForm1
UserForm1.Show
MsgBox (UserForm1.K)
Unload UserForm1
End Sub