Excel - VBA：将变量从子传递到用户窗体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17288997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - VBA : pass variable from Sub to Userform
提问by Phalanx
I have read and applied solution I found on similar topics but nothing seem to work in my case.
我已经阅读并应用了我在类似主题上找到的解决方案，但在我的情况下似乎没有任何效果。
So, I want to pass a variable from one sub of my Module1 to a userform. It's a string called "provinceSugg".
所以，我想将一个变量从我的 Module1 的一个子传递到一个用户表单。这是一个名为“provinceSugg”的字符串。
Here is the relevant part of my code :
这是我的代码的相关部分：
Public provinceSugg As String
Sub probaCity()
[...]
If province = "" And city <> "" Then
provinceSugg = sCurrent.Cells(p, db_column).Offset(0, 1).Value
UserForm2.Label1 = "Do you mean " & city & " in " & provinceSugg & " ?"
UserForm2.Label1.TextAlign = fmTextAlignCenter
UserForm2.Show
Else
End If
End Sub
And then in my userform code :
然后在我的用户表单代码中：
Private Sub userformBtn1_Click()
MsgBox provinceSugg
sMain.Range("J6").Value = provinceSugg
End Sub
When I run my program :
当我运行我的程序时：
1/I have the content of provinceSugg showing in the MsgBox called from my sub (so there is a provinceSugg, it's not an empty variable).
2/The MsgBox called from the userform is empty (so passing the value failed) and my program crashes when running " sMain.Range("J6").Value = provinceSugg" with something like "Error 424 Object Required" (so the variable failed to pass to the userform).
1/我在从我的 sub 调用的 MsgBox 中显示了 ProvinceSugg 的内容（所以有一个 ProvinceSugg，它不是一个空变量）。
2/从用户窗体调用的 MsgBox 是空的（因此传递值失败）并且我的程序在运行“sMain.Range("J6").Value = ProvinceSugg”时崩溃，并带有“Error 424 Object Required”之类的东西（因此变量未能传递给用户表单）。
I tried all the stuff I found on forum and here (different ways to indicate that provinceSugg is a public variable but still crashing...).
我尝试了我在论坛和这里找到的所有东西（表明 ProvinceSugg 是一个公共变量但仍然崩溃的不同方式......）。
Thanks in advance for your help !
在此先感谢您的帮助 ！
回答by Dan
You would be able to create public variables within the Userform that can be set by the Module.
您将能够在用户窗体中创建可由模块设置的公共变量。
These variables are only accessible within the Userform as it is loaded.
这些变量只能在加载时在用户窗体中访问。
Within the Userform, declare public variables for both objects.
在用户窗体中，为两个对象声明公共变量。
Public sMain As Worksheet
Public provinceSugg as string
Private Sub userformBtn1_Click()
MsgBox provinceSugg
sMain.Range("J6").Value = provinceSugg
End Sub
Within the Module, you can assess both of those variables.
在模块中，您可以评估这两个变量。
Sub probaCity()
[...]
If province = "" And city <> "" Then
provinceSugg = sCurrent.Cells(p, db_column).Offset(0, 1).Value
With UserForm2
.provinceSugg = provinceSugg
Set .sMain = sMain
.Label1 = "Do you mean " & city & " in " & provinceSugg & " ?"
.Label1.TextAlign = fmTextAlignCenter
.Show
End With
End If
End Sub
回答by Andrew Day
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim selectColumn
selectColumn= Split(Target.Address(1, 0), "$")(0)
Call UserFormStart(selectColumn)
End Sub
Inside Main Module
主模块内部
Public columnSelection As String
...
Public Sub UserFormStart(ByVal columnRef As String)
'MsgBox "Debug columnRef=" & columnRef
columnSelection = columnRef
UserForm1.Show
End Sub
Inside UserForm
内部用户表单
Private Sub UserForm_Initialize()
'MsgBox "Debug UserForm_Initialize =" & columnSelection
...
End Sub
Worksheet_SelectionChange calls a sub on the module where columnSelection is declared as public and visable from the UserForm. I used three different variables for the Column Reference to show that there is where the UserForm has access to the Module. The above all works and took ages to find and work out hence the submission. Happy hunting folks
Worksheet_SelectionChange 调用模块上的 sub，其中 columnSelection 被声明为 public 并且从 UserForm 可见。我为 Column Reference 使用了三个不同的变量来显示 UserForm 可以访问模块的位置。以上所有作品都需要很长时间才能找到并解决，因此提交。祝大家狩猎愉快
