Multiple Apps

Userforms: Passing Variables

Ease of Use

Intermediate

Version tested with

2000 (Win); X (Mac) 

Submitted by:

BlueCactus

Description:

Demonstration of how to pass variables to Userforms without going Public 

Discussion:

Discussion of how to pass variables to Userforms seems to come up fairly frequently among VBA users. The most common answer is to declare the variables using a Public statement at the module level of the calling code. This is easy, but overuse of Public statements is not recommended due to the risk of conflict between similarly named variables. Alternatives include: 1) Placing the variable values on the worksheet, and recalling them in Userform_Initialize(). 2) Loading the form, assigning the variable values to label captions within the form (e.g., Label1.Caption = testVar), then showing the form. Labels can be made invisible (Label1.Visible = False) to make this process transparent to the user. It occurred to me that there is a cleaner alternative. I am sure I'm not the first to think of this, but I have not yet seen it used elsewhere. The trick is simply to insert an extra Sub into the Userform code. This Sub must not be Private. Once the form is loaded in the calling code, variables can be passed to this extra Sub just like any other. See the example code and the instructions, to understand how this works. 

Code:

instructions for use 

			
 ' THIS CODE GOES INTO Module1
 
Sub TestForm() 
    Dim labelA As String, labelB As String 
     
    labelA = "TEST ONE" 
    labelB = "TEST TWO" 
     
     ' MUST load the form first!
    Load UserForm1 
     ' Send the variables over to the form
    Call UserForm1.FillVars(labelA, labelB) 
     ' Now show the form
    UserForm1.Show 
End Sub 
 
 '-------------------------------------------------------------
 ' THIS CODE REPLACES ALL CODE IN UserForm1
 
 ' Dim variables up here so that they are available to the entire form
Dim str1 As String, str2 As String 
 
Sub FillVars(ByRef s1 As String, ByRef s2 As String) 
     ' This is the sub that collects the variables from the calling module.
     ' Make sure it's not marked 'Private'.
     ' Any form initialization that relies on external variables should be done here.
    Label1.Caption = s1 
    Label2.Caption = s2 
     ' s1 and s1 are not visible to other Subs in the form,
     ' so we'll pass their contents to str1 and str2 before leaving.
    str1 = s1 
    str2 = s2 
     
End Sub 
 
Private Sub CommandButton1_Click() 
    Unload UserForm1 
End Sub 
 
Private Sub CommandButton2_Click() 
    MsgBox "You passed:" & Chr(13) & str1 & Chr(13) & str2 
End Sub 
 
Private Sub Userform_Initialize() 
     ' Any initialization that is dependent on passed variables MUST be done in
     ' FillVars() and not here. Initialize() is called before the variables are passed!
    CommandButton1.Caption = "Done" 
    CommandButton2.Caption = "Show Vars" 
End Sub

How to use:

  1. (For Excel) Open a blank workbook.
  2. Press Alt+F11 to open the Visual Basic Editor (VBE).
  3. From the menus, Insert -> Module
  4. Copy the code above destined for Module1, and paste it into the Module1 window in VBE.
  5. From the menus, Insert -> UserForm
  6. Using the toolbox (View -> Toolbox if it's not visible):
  7. a. Place two 'Labels' on the UserForm
  8. b. Place two CommandButtons on the UserForm
  9. Double-click on the UserForm1 (UserForm) window to go to the UserForm1 (Code) window.
  10. REPLACE UserForm code in the window with the above code marked for UserForm1.
  11. Close the VBE by clicking on the Excel icon in the toolbar.
 

Test the code:

  1. Test from the menus: Tools -> Macros... -> TestForm() -> Run.
  2. Click 'Show Vars' to verify that variables were passed.
  3. Click 'Done' to finish.
  4. Read the comments in the code for info on how it is done!
 

Sample File:

No Attachment 

Approved by sandam


This entry has been viewed 222 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express