公共变量在表单中的 VBA 中并不是真正公共的 [英] Public variables are not REALLY public in VBA in Forms

问题描述

下面是一个我会自己回答的问题，但它给我带来了很大的挫败感，而且我在网上搜索时遇到了很多麻烦，所以我在这里发帖，希望能节省一些时间 &为他人努力，如果我将来忘记这一点，也许为自己努力:

对于 VBA(在我的情况下，MS Excel)，Public 声明应该使该模块中的其他函数或子例程以及任何其他模块.

事实证明这不是真的，在 Forms 的情况下，我怀疑在 Sheets 中也是如此，但我还没有验证后者.

简而言之，当在 Form 中创建时，以下将不会创建一个公共的、可访问的变量，因此会崩溃，说 bYesNo 和 dRate 变量在 mModule1 中未定义:

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 Public declaration 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 variable when created in a Form, and will therefore crash, saying that the bYesNo and dRate variables are undefined in mModule1:

(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

mModule1 将工作得很好，假设 fMyForm 总是首先被调用，然后在 PrintVals 例程运行时，来自文本框和复选框的值在表单中将被正确捕获.

老实说，我无法理解 MS 对这种变化的想法，但缺乏一致性会极大地降低效率，学习像这样的特质，这些特质的记录非常糟糕，以至于 2013 年的谷歌搜索可能已经存在了十年或更长时间的搜索非常具有挑战性.

mModule1 will work perfectly fine and, assuming that the fMyForm is always called first, then by the time the PrintVals routine 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.

推荐答案

第一条评论:

用户表单和工作表模块是对象模块:它们的行为方式与常规模块不同.但是，您可以以与引用类属性的方式类似的方式引用用户表单中的变量.在您的示例中，引用 fMyForm.bYesNo 可以正常工作.如果您没有将 bYesNo 声明为 Public，则表单之外的代码将不可见，因此当您将其设为 Public 时，它确实与非 Public 不同.– 蒂姆·威廉姆斯 2013 年 4 月 11 日 21:39

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

实际上是一个正确的答案...

is actually a correct answer...

这篇关于公共变量在表单中的 VBA 中并不是真正公共的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！