How to refer to a Public variable in a Form?

scope, vba

Solution

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

Problem

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. This is not true, in the case of `Forms`, and I suspect also in `Sheets`, but I haven't verified the latter. 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 ``` If you make the alteration below, it all will work: ``` '(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 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 be captured.

Original source