Now that I have my form margins macro working, I'd like to set the
parameters from a userform. I found a pretty good userform tutorial ("
http://www.fontstuff.com/vba/vbatut09a.htm) and have been working my
way through it. I have just one question (so far):
My plan is to put up a user form with 6 TextBoxes where the user can
enter the form dimensions (width, height, and the 4 margins). My plan
was to have the code behind each textbox set a global variable, which
my FormMargins macro can then use instead of the constants it uses
now.
My question is where I should put the global variable declarations?
Or, is there a better way to pass this data to the macro?
There are essentially three ways to do this (plus a couple of minor variations):
1. Put all the code that modifies the document into the Click event code of the OK button in the userform (as done in cmdOK_Click in the tutorial), and don't pass anything between macros.
2. Declare the global variables at the top of the module that contains the FormMargins macro, using the Public keyword in each declaration so the code in the userform can "see" the global variables.
3. Declare the global variables at the top of the userform's code, using the Public keyword in each declaration so the code in the FormMargins macro can "see" the global variables.
I'm guessing you don't want to use method 1 because you already have a lot of code in the FormMargins macro, and you don't want to have to move it and retest it. That's OK.
Method 2 is the one you're probably thinking of. It will work, but it has a theoretical drawback: Global variables in a module can be changed by any code in any module, typically by mistake when the
same variable name is used for several different macros. Debugging this situation can be difficult to nearly impossible. You can minimize the likelihood by using unique prefixes in each module or some
such scheme, but it's cumbersome and easy to forget.
Method 3 is better than method 2, because the "global" variables are really global only to modules that call that specific userform. Try this with a userform that has one text box and one button:
' In the code of the userform
Public globalText As String
Private Sub CommandButton1_Click()
globalText = TextBox1.Text
Me.Hide
End Sub
' In the regular module
Sub x()
UserForm1.Show
MsgBox UserForm1.globalText
Unload UserForm1
End Sub
A few things to notice:
(1) The end of the CommandButton1_Click in the userform calls "Me.Hide" instead of "Unload Me". This removes the userform from the screen and returns control to the macro that showed the userform, but
it doesn't remove the userform from memory. That's important because the calling macro needs to "read" the global variable that's stored in the userform.
(2) The macro refers to the global variable by prefixing it with "UserForm1." because the global variable is a member of the UserForm1 object.
(3) The "Unload UserForm1" statement does remove the userform from memory.
Finally, there's another way to handle a userform that's a little more complicated but more reliable, especially when you have several userforms in the same document or you call the same userform
multiple times. When you create a userform definition in VBA, it's really more like a template -- something that should be used to stamp out copies that all look the same. Although VBA will let you
work with that template as if it were an actual object, it's best to use it as it was intended:
Sub x()
Dim uf As UserForm1
Set uf = New UserForm1 ' make an object of type UserForm1
uf.Show
MsgBox uf.globalText
Set uf = Nothing ' equivalent to Unload uf
End Sub
With this kind of setup, you could make two or three copies of UserForm1, each with its own global variables, and they would behave independently.
