UserForms - A Few Questions on Variable Scope

[Randall00]

My knowledge of VBA is fairly specialized so a lot of the fundamentals aren't quite clear, but this is what I understand about the lifetime of variables and their scope:

A Public variable declared at the top of a module is available to all modules and procedures within that workbook.

Sooooo..:

You cannot declare an "application level" variable that is available to all open workbooks and the closest you can get would be explicity declaring and setting the same public variables in the two workbooks in question...right?

Also, even though you declare those variables publicly, you still have to set those variables to their appropriate values in each procedure that uses them...right?​

A Module-Level variable is declared at the top of any module and is available to all procedures in that module. But assuming I'm on the right track above, you still need to set the variables explicitly within each procedure...(right?).


A Procedure-Level variable is declared within the procedure itself and is only available to commands within that procedure. Which should mean that if, within Macro1, you call Macro2, which does not declare those variables, then those variables will not be available to Macro2....err...right?


I hope I'm at least on the right track there, but I've found that UserForms are causing me some grief on this concept. What is a UserForm in the context of the scope of variables?

Are Public variables declared at the top of a module available to UserForms? If so, does that make them available to all UserForms?

Can you declare a Public variable at the top of a UserForm's code? If so, does that make it available to just UserForms, all modules or both?

If you declare a "UserForm"-level variable at the top of a UserForm's code, is it available to all procedures within that code?​

----

So I guess I'm just trying to understand the relationship between variables declared from a UserForm's code and those declared in other modules. If anyone would be able to provide some insight, that would be excellent--otherwise, I'll be driving myself crazy with trial and error trying to figure it all out.

 

[JackBean]

Randall00:

You have several questions and I can not answer all of these but I can direct you to a recent question about variables in another worksheet.

Re: dynamic array as public variable
http://www.mrexcel.com/forum/showthread.php?t=453114

Sooooo..:

You cannot declare an "application level" variable that is available to all open workbooks and the closest you can get would be explicity declaring and setting the same public variables in the two workbooks in question...right?
​

 

[mikerickson]

A variable that is declared Public at the top of a Userform's code module is avaliable to every proceedure whenever that userform is loaded.

So if you have

Public myUFVar as Variant

at the top of UserForm1's code module any proceedure can access it by UserForm1.myUFVar.
In fact, if Userform1 isn't loaded before that line is run, running that line will create an instance of Userform1 and myUFVar will take the value set by the Userform_Initialize event.

Public variables set in Userform code are directly analogous to Public variables in Class modules.

 

[Norie]

How about not using Public/Global variables in the first place?

You really shouldn't need them as long as you organise/structure the code in the right way.

 

[xenou]

Useful reading:

http://www.dailydoseofexcel.com/archives/2004/07/22/passing-arguments-to-a-userform/
http://peltiertech.com/Excel/PropertyProcedures.html

 

[Randall00]

Thanks, everyone--that at least sheds some light on what I was wondering.

How about not using Public/Global variables in the first place?

You really shouldn't need them as long as you organise/structure the code in the right way.

Haha, sure maybe--I definitely believe you, but if I were taking helicopter lessons and the instructor said "whatever you do, don't pull this switch", I'd really want to know what it does before taking flight.

A variable that is declared Public at the top of a Userform's code module is avaliable to every proceedure whenever that userform is loaded.

I see, I see--so a procedure that is outside of the UserForm's code module could still use a Public variable from that module, so long as the UserForm is running...right? So you could have code in the UserForm that calls procedures outside of the UserForm, which utilize variables that were declared public in the UserForm in the first place, so long as the form is running...?

I'm getting dizzy.

 

[mikerickson]

Try this in a normal module

Sub test()
    MsgBox UserForm1.TextEntered
End Sub

Sub makeUF()
    UserForm1.Show
End Sub

and this in a userform's code module (uf has 1 text box, 1 command button)

Public TextEntered

Private Sub CommandButton1_Click()
    Call test
End Sub

Private Sub TextBox1_Change()
    TextEntered = TextBox1.Text
End Sub

Private Sub UserForm_Initialize()
    TextEntered = "Initial Value"
End Sub

Then run Test, without loading the UF.
Then run makeUF(), press the button, enter some text and press the button again.

 

[Randall00]

Nice--something that works like I thought it would! Thanks, Mike!

 

[mikerickson]

Norie is right about Public variables not being needed.
Try this variation of the above
in a normal module

Sub test()
    MsgBox UserForm1.BothTxtBoxes
End Sub

Sub makeUF()
    UserForm1.Show
End Sub

in a Userform's code module (with two textboxes and a command button)

Private Sub CommandButton1_Click()
    Call test
End Sub

Private Sub UserForm_Initialize()
    TextBox1.Text = "Initial Value one"
    TextBox2.Text = "Initial Value two"
End Sub

Public Function BothTxtBoxes() As String
    BothTxtBoxes = TextBox1.Text & "-" & TextBox2.Text
End Sub

Note that there is no Public variable declared and that the behavior is similar to the example before.