公共变量在表单中的 VBA 中并不是真正公共的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15959018/
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
Public variables are not REALLY public in VBA in Forms
提问by Mike Williamson
Below is a question that I will answer myself, however it caused a GREAT deal of frustration for me and I had a lot of trouble searching for it on the web, so I am posting here in hopes of saving some time & effort for others, and maybe for myself if I forget this in the future:
For VBA (in my case, MS Excel), the Publicdeclaration is supposed to make the variable (or function) globally accessible by other functions or subroutines in that module, as well as in any other module.
Turns out this is not true, in the case of Forms, and I suspect also in Sheets, but I haven't verified the latter.
In short, the following will NOT create a public, accessible variablewhen created in a Form, and will therefore crash, saying that the bYesNo and dRate variables are undefined in mModule1:
下面是一个我会自己回答的问题，但它给我带来了很大的挫败感，而且我在网上搜索时遇到了很多麻烦，所以我在这里发帖，希望为他人节省一些时间和精力，如果我将来忘记这一点，也许对于我自己：
对于 VBA（在我的情况下，MS Excel），Public声明应该使该模块中的其他函数或子例程以及在任何其他模块。
事实证明这不是真的，就 而言Forms，我怀疑也在 中Sheets，但我还没有验证后者。
简而言之，以下内容在创建时不会创建一个公共的、可访问的变量Form，因此会崩溃，说 mModule1 中的 bYesNo 和 dRate 变量未定义：
(inside fMyForm)
Public bYesNo As Boolean`
Public dRate As Double
Private Sub SetVals()
bYesNo = Me.cbShouldIHaveADrink.value
dRate = CDec(Me.tbHowManyPerHour.value)
End Sub
(Presume the textbox & checkbox are defined in the form)
(inside mModule1)
Private Sub PrintVals()
Debug.Print CStr(bYesNo)
Debug.Print CStr(dRate)
End Sub
However, if you make the slight alteration below, it all will work fine:
但是，如果您在下面稍作改动，一切都会正常工作：
(inside fMyForm)
Private Sub SetVals()
bYesNo = Me.cbShouldIHaveADrink.value
dRate = CDec(Me.tbHowManyPerHour.value)
End Sub
(Presume the textbox & checkbox are defined in the form)
(inside mModule1)
Public bYesNo As Boolean`
Public dRate As Double
Private Sub PrintVals()
Debug.Print CStr(bYesNo)
Debug.Print CStr(dRate)
End Sub
mModule1will work perfectly fine and, assuming that the fMyForm is always called first, then by the time the PrintValsroutine is run, the values from the textbox and checkbox in the form will properly be captured.
I honestly cannot possibly fathom what MS was thinking with this change, but the lack of consistency is a huge suck on efficiency, learning idiosyncracies like these, which are so poorly documented that a Google search in 2013 for something that has likely been around for a decade or more is so challenging to search.
mModule1将工作得很好，假设 fMyForm 总是首先被调用，然后在PrintVals例程运行时，表单中文本框和复选框的值将被正确捕获。
老实说，我无法理解 MS 对这种变化的想法，但缺乏一致性会极大地降低效率，学习这些特质，这些特质的记录非常糟糕，以至于 2013 年的谷歌搜索可能已经存在了一段时间十年或更长时间的搜索非常具有挑战性。
回答by Ota Milink
First comment:
第一条评论：
Userform and Sheet modules are Object modules: they don't behave the same way as a regular module. You can however refer to a variable in a userform in a similar way to how you'd refer to a class property. In your example referring to fMyForm.bYesNo would work fine. If you'd not declared bYesNo as Public it wouldn't be visible to code outside of the form, so when you make it Public it really is different from non-Public. – Tim Williams Apr 11 '13 at 21:39
Userform 和 Sheet 模块是 Object 模块：它们的行为方式与常规模块不同。但是，您可以以与引用类属性的方式类似的方式引用用户表单中的变量。在您的示例中，引用 fMyForm.bYesNo 可以正常工作。如果您没有将 bYesNo 声明为 Public，则表单外的代码将不可见，因此当您将其设为 Public 时，它确实与非 Public 不同。– 蒂姆·威廉姆斯 2013 年 4 月 11 日，21:39
is actually a correct answer...
其实是正确答案...
回答by Carl Colijn
As a quick add-on answer to the community answer, just for a heads-up:
作为社区答案的快速附加答案，仅供参考：
When you instantiate your forms, you can use the form object itself, or you can create a new instance of the form object by using New and putting it in a variable. The latter method is cleaner IMO, since this makes the usage less singleton-ish.
当您实例化表单时，您可以使用表单对象本身，或者您可以通过使用 New 并将其放入变量来创建表单对象的新实例。后一种方法是更干净的 IMO，因为这使得使用不那么单一。
However, when in your userform you Call Unload(Me), allpublic members will be wiped clean. So, if your code goes like this:
但是，当在您的用户表单中调用 Unload(Me) 时，所有公共成员都将被清除。所以，如果你的代码是这样的：
Dim oForm as frmWhatever
Set oForm = New frmWhatever
Call oForm.Show(vbModal)
If Not oForm.bCancelled Then ' <- poof - bCancelled is wiped clean at this point
The solution I use to prevent this, and it is a nice alternative solution for the OP as well, is to capture all IO with the form (i.e. all public members) into a separate class, and use an instance of that class to communicate with the form. So, e.g.
我用来防止这种情况的解决方案，它也是 OP 的一个很好的替代解决方案，是将所有带有表单的 IO（即所有公共成员）捕获到一个单独的类中，并使用该类的实例与表格。所以，例如
Dim oFormResult As CWhateverResult
Set oFormResult = New CWhateverResult
Dim oForm as frmWhatever
Set oForm = New frmWhatever
Call oForm.Initialize(oFormResult)
Call oForm.Show(vbModal)
If Not oFormResult.bCancelled Then ' <- safe
