Passing Arguments to a Userform
The Initialize event of userforms doesn’t take an argument, so you can’t pass data directly to it. Here are three ways to pass a variable to a userform.
Global Variable
I avoid using global variables whenever practical, but depending on the complexity of the application, it may be the best choice. Create a global variable in a standard module and set it before showing the form.
Public gsMyVariable As String
Sub ShowForm()
gsMyVariable = “Pass to form”
UserForm1.Show
End Sub
Then you can use that variable in the userform’s code
Private Sub CommandButton1_Click()
MsgBox gsMyVariable
End Sub
The Tag Property
If you’re not using the Tag property for something else, you can store some data in it for later use. You just need to load the form before you set the property, then show it.
Sub ShowFormTag()
Load UserForm2
UserForm2.Tag = “Pass to form”
UserForm2.Show
End Sub
Private Sub CommandButton1_Click()
MsgBox Me.Tag
End Sub
Properties
Because a userform is just a class module with a built-in user interface, you can create properties in the form’s module. The form’s module might look like this
Private msMsg As String
Private Sub CommandButton1_Click()
MsgBox msMsg
End Sub
Property Let MyProp(sText As String)
msMsg = sText
End Property
and showing the form from a standard module
Sub ShowFormProp()
Dim frmMyForm As UserForm3
Set frmMyForm = New UserForm3
frmMyForm.MyProp = “Pass to form”
frmMyForm.Show
End Sub
“a userform is just a class module with a built-in user interface”
This is a good reason for not coding a userform to rely on a public variable i.e. makes it harder to re-use the class/form in another project.
Jamie (who has never used a public variable).
–
Waitaminute… I’m going to reveal how self-taught I am here:
What’s wrong with Public variables? (Perhaps subject of a future post, Dick?)
Matt: As Jamie mentioned it violates the good coding practice of encapsulation, which roughly means that your code should be self-contained. If you have a function, for instance, that uses a public variable, you can’t copy that function into a new project and use it because the new project won’t have that variable. Unlike Jaime, I can’t say that I’ve never used them, but for nontrivial programs, I avoid them like the plague.
I wish I was well versed in good programming practices enough to make a nice post about them, but I just pick up bits and pieces here and there. I don’t trust myself to get it right. Heck, maybe I’ll do it anyway.
I wonder if it isn’t helpful to note three different types of variables that John Walkenbach identifies, referring to the three levels of variable “scope”:
“You’re talking about a variable’s scope. There are three levels of scope: local, module, and public. Local variables have the narrowest scope and are declared within the procedure. A local variable is visible only to the procedure in which it was declared. Module-level variables are declared at the top of the module, prior to the first procedure. Module-level variables are visible to all procedures in the module. Public variables have the broadest scope, and they are declared using the Public keyword.” (John Wlakenbach, “Excel 2002 Power Programming with VBA”, p. 839)
This helps me keep straight the distinction between module-level and Public variables, noting that module-level may be as “high” up as I ever need to go.
An additional comment by Walkenbach explains the advantage of the local variable:
“In general, local variables are the most efficient because VBA frees up the memory they use when the procedure ends.” (”Excel 2002 Power Programming with VBA”, p. 188)
I like to work with C, but i work i must do it in vBasic. Passing arguments to forms is great, but how to make form to return value back to his creator? Thanks.
Passing arguments to forms is great, but how to make form to return value back to his creator?
This could be a post/thread in itself. The basic answer is that you need to add a Get statement and then access it as a property of the class like you would any other class/control/form (e.g. frmEmployees.Caption, frmEmployees.txtLastName.Text, or clsEmployee.LastName).
Here is a way I have used that is a bit unconventional. In fact, I have never seen anybody do this. I am wondering if I am missing some potential pit falls.
******************** Userform1 *******************
Option Explicit
Dim LocalVar as String
Private Sub UserForm_Initialize()
‘ Initialization code here
End Sub
Sub MyVariable (ByVal MyVal as String)
LocalVar = MyVal
Userform1.Show
End Sub
**************************************************
To pass the variable to the Userform I call it with:
Sub MainPgrm()
Dim MyVal as String
MyVal = “Some text”
Userform1.MyVariable MyVal
End Sub
The call first executes the “Private Sub UserForm_Initialize()”. It then goes to the “MyVariable” sub routine where the value is assigned to a local variable. If you don’t precede the call to MyVariable with Userform1 it doesn’t work. Is there a reason I should not pass variables to a userform this way?
Greg -
The “official” way to do this, which isn’t much different than what you’re doing, is to use a property procedure in the user form (or other class module). Then use a userform.show command in the calling procedure. There are property let (or set) procedures to assign values to a form’s property, and property get procedures to retrieve these properties from a form. I’ve written a short article on the topic:
http://peltiertech.com/Excel/PropertyProcedures.html
- Jon
——-
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
If you have to use multiple variables or if you want to save their values along with your workbook you might as well use a worksheet to store values. Simply use a cell as a variable. If you want you can name cells or ranges as well, whatever suites you best. This is used for example to create/store menu bars.
A small example:
*** workbook ***
Private Sub Workbook_Open()
Dim i As Integer, i2 As Integer
Dim bWksExists As Boolean
i2 = Worksheets.Count
For i = 1 To i2
If Worksheets(i).Name = “wksMyVars” Then
bWksExists = True
End If
Next i
If Not bWksExists Then
Worksheets.Add After:=Worksheets(i2)
With Worksheets(i2 + 1)
.Name = “wksMyVars”
.Visible = xlHidden
End With
End If
End Sub
*** In a module ***
Sub ShowForm()
With Worksheets(”wksMyVars”)
.Range(”A1″).Value = “Greeting”
.Range(”B1″).Value = “Hello World”
End With
‘ (A1 is set to Greeting for readability of the worksheet itself)
UserForm1.Show
End Sub
*** UserForm1.CommandButton1 ***
Private Sub CommandButton1_Click()
Dim sGreeting As String
sGreeting = Worksheets(”wksMyVars”).Range(”B1″).Value
MsgBox sGreeting
End Sub
Rembo
‘Create a new workbook. Add a module with the following code:
‘———————-
Option Explicit
Sub FormVariablePassingExample()
Dim F As frmValuePass ‘just creates a variable, doesn’t instantiate anything
Set F = New frmValuePass ‘instantiates the form, but because it’s hidden, code execution continues
F.Value1 = 8 ’set some values
F.Value2 = 6
‘You don’t have to have one property per value. You could use an array. Or a publicly defined
‘ type (like a C structure: Type TypeName // Variables // End Type.
‘ or an object. Or a delimited string. Whatever.
F.Display ‘This is a custom method on the form which gets it ready and does the .show
‘You could use .show directly, but I used this method to give the form a chance
‘ to know I was done passing values in so it could do some setup.
‘Code execution suspends here until the form is hidden (becomes invisible) again.
‘Clicking OK on the form makes the form hide, thereby resuming code execution.
Debug.Print F.Result ‘we can look at the form or get values out to our heart’s content
‘Note that the value printed out is whatever was in the textbox when you clicked close.
‘It’s good practice to use something like this Result property instead of examining the
‘ textbox directly. What if you change how your form functions or does its job? Better
‘ to observe the “Law of Demeter” and only use properly defined interfaces to the form’s
‘ data, instead of depending on knowing details about what’s inside the form.
Set F = Nothing ‘now actually unload the form, by removing all references to it.
End Sub
‘ Next, create a new form called frmValuePass
‘ give it any caption you like.
‘ Add a textbox and name it txbResult
‘ Add a button called btnOK and make it the Default button
‘ Add the following code:
‘———————-
Option Explicit
Private gValue1 As Long
Private gValue2 As Long
Public Property Let Value1(TheValue As Long)
gValue1 = TheValue
End Property
Public Property Let Value2(TheValue As Long)
gValue2 = TheValue
End Property
Public Sub Display()
txbResult = gValue1 * gValue2
Me.Show
End Sub
Public Property Get Result() As Long
Result = Val(txbResult)
End Property
Private Sub btnOK_Click()
Me.Hide
End Sub
I have a two userform system, I want to pass a name from one userform to another, the name is filled in a txt box and used in the userform1 to fill in details to a word document and then i want to pass it to userform2 to be used as part of an output string depending upon choices made in userform2. How would i do this?
I just need to know how i’d declare the variable and pass it over.
NVM I just declared it in the word document coding were i show the first form and its working fine when i set it to public, not exactly ideal coding but it works and its work computers so no memory problems