Public variables are not REALLY public in VBA in Forms

Question

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 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.

Answer 1

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

is actually a correct answer...

Answer 2

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.

However, when in your userform you Call Unload(Me), all public members will be wiped clean. So, if your code goes like this:

  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.

  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