excel - Excel VBA 获取在“UserForm1”私有子脚本中找到的值（整数 K），转移到“Module1”子脚本

Question

我无法将我的值K（整数）从 my Private Subin 获取UserForm1到 my Subin Module1。我使用UserForm1来声明我想在哪个工作表上运行代码（通过从弹出的下拉列表中选择，然后将其声明为 2 到 9 之间的值/整数），所以我只需要传输K我找到的值在我UserForm1 Private Sub的。SubModule1

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

我当前的代码如下，从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

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

提前，谢谢。

标签： excelvbamodulecomboboxuserform

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