Public variable defintion (newbie)

  • Thread starter Thread starter gregga
  • Start date Start date
G

gregga

I have two questions.

Is a public variable visible to all sub procedures in all modules bu
just under one workbook? In this case a public variable has bee
declared in a module under Personal.xls, but I also have anothe
workbook containing macros but am confused as to whether a public va
is visible to all workbooks - I only want it visible unde
Personal.xls.

Lastly, I've created a userform whereby the user has 3 options (usin
option buttons) and an OK and CANCEL button. I noticed th
"CommandButton1_Click()" userform is always preceded m
"private"...namely -

Private Sub CommandButton1_Click()

If OptButton_All Then
arSelect = Array("N3", "N2", "O-P", "SO4")
Else
If OptButton_SulphateOnly Then
arSelect = Array("SO4")
Else
If OptButton_ExcludeSulphate Then
arSelect = Array("N3", "N2", "O-P")
Else
MsgBox "You must select an option!"
Hide
Show

End If
End If
End If


Unload Me


End Sub


The array "arSelect" is declared as "Public arSelect As Variant" at th
very start of module 1. The module then opens the userform. Th
userform captures the selection and assigns a series of value(s) t
arSelect. I want arSelect to be available to module1.

I noticed that if I changed the "Private Sub CommandButton1_Click()" t
"*Public* Sub CommandButton1_Click()" then arSelect is then available t
module 1, but refuses to work if it is Private.

I guess I've answered my own question, but why does a userfor
automatically delcare procedures as private?

Thanks
 
Hi Gregga,

A Public variable is availble to all objects within the Parent project, ie:
not to another workbook, but to all Forms and Modules within that one
Project.

Second, Forms content are always Private to themselves, ie: the Forms
themselves, that way you can have many forms with objects with the same
name, ie: cmdOKButton, cmdCancelButton, etc...
You can however use the Friend and Public method to let some code within a
Form accessible by other objects than the form itself. If you need to do
that though, I suggest you do as I do, which is to create a Module named
after the Form so I remember later what it does, and use Public statements
so that the code within that Module can be used by all other objects in the
projects.
 
There is no reason that declaring a sub public or private would affect the
visibility of a publically declared variable.

If your arSelect variable is declared public at the top of a general module,
it should be visible throughout the project and should not be affected by
whether CommandButton1_Click is declared public or private.

You can simplify you code with

If OptButton_All Then
arSelect = Array("N3", "N2", "O-P", "SO4")
ElseIf OptButton_SulphateOnly Then
arSelect = Array("SO4")
ElseIf OptButton_ExcludeSulphate Then
arSelect = Array("N3", "N2", "O-P")
Else
MsgBox "You must select an option!"
Hide
Show
End If

I assume you want to trigger the activate event (is why you hide then show
the form?)
 
Absolutely, I erred on a different path suddenly and veered towards subs and
functions. Sorry about that! Indeed you can declare a variable Public or
Global, do a F1 on these 2.
Tom Ogilvy said:
There is no reason that declaring a sub public or private would affect the
visibility of a publically declared variable.

If your arSelect variable is declared public at the top of a general module,
it should be visible throughout the project and should not be affected by
whether CommandButton1_Click is declared public or private.

You can simplify you code with

If OptButton_All Then
arSelect = Array("N3", "N2", "O-P", "SO4")
ElseIf OptButton_SulphateOnly Then
arSelect = Array("SO4")
ElseIf OptButton_ExcludeSulphate Then
arSelect = Array("N3", "N2", "O-P")
Else
MsgBox "You must select an option!"
Hide
Show
End If

I assume you want to trigger the activate event (is why you hide then show
the form?)
Click to expand...
 
Back
Top