CODEWITHSUNDEEP
vbascope
Mike Williamson
Mike Williamson

Reputation: 3198

How to refer to a Public variable in a Form?

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.

Upvotes: 12

Views: 42054

Answers (3)

mlr94549
mlr94549

Reputation: 21

There are other limitations to Public within Excel VBA. MSoft documentation in learn.microsoft.com states that public variables are global to the VBA project - it's not true. Public variables are only global to the workbook within which they are declared, and then only across standard modules. Public variables declared within workbook code are not visible in standard modules, even though standard module sub's are - which are defined to be public. Public variables declared in one workbook's standard modules are certainly not accessible from other workbooks in the same VBA project, contrary to the MSoft documentation.

Upvotes: 0

Carl Colijn
Carl Colijn

Reputation: 1617

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

Upvotes: 2

Ota Milink
Ota Milink

Reputation: 38

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

Upvotes: 7

Related Questions