Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA: how to pass a form as a paramter

I have a simple user form with a button on it. I want to pass this form to a function in another module - but even inside the same module, it does not work as expected:

Private Sub Test(ByRef oForm As MSForms.UserForm)
    Debug.Print "caption: >" & oForm.Caption & "<"
End Sub

Private Sub CommandButton3_Click()
    Debug.Print "caption: >" & Me.Caption & "<"
    Test Me
    Debug.Print "caption: >" & Me.Caption & "<"
End Sub

when I click the button it prints this to the debug console:

caption: >UserForm1<
caption: ><
caption: >UserForm1<

so inside of the Test() sub the Caption is blank.

any ideas why this does not work?

Note: if I use Variant as parameter-type it works

like image 994
TmTron Avatar asked Mar 10 '14 14:03

TmTron


People also ask

How do you pass a parameter in VBA?

To pass an argument by reference, use the ByRef keyword before the argument to its left. Passing arguments by reference is also the default in vba, unless you explicity specify to pass an argument by value. Example 4a - Passing an argument by value in a procedure using the ByVal keyword.

What does .show do in VBA?

When the . Show method is called, VBA will direct the program flow to the UserForm_Initialize event. Here, you can make numerous customizations. You can edit the userform controls, like the caption on the command button.

What are Userforms in VBA?

A User Form is a custom-built dialog box that makes a user data entry more controllable and easier to use for the user. In this chapter, you will learn to design a simple form and add data into excel. Step 1 − Navigate to VBA Window by pressing Alt+F11 and Navigate to "Insert" Menu and select "User Form".


1 Answers

Type UserForm is not directly equivalent to an instance of a specific User Form and presents a slightly different interface.

Pass the form as Object:

Private Sub Test(Form As Object)

or for a type safe approach a UserForm may implement an Interface:

Private Sub Test(Form As IMyForm)
like image 120
Alex K. Avatar answered Oct 13 '22 09:10

Alex K.