Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add Public Methods to a Userform Module in VBA

Tags:

excel

vba

Is it possible to call a public sub located in a UserForm from a Class Module? I want to put a callback in the Form Module but I can't seem to get it to expose.
Is this a fundamental limitation of UserForms in VBA? It is exposed inside the UserForm Code Module, I can see it in the intelisense for the Me object, but I can't seem to access it from outside the Form Module.

like image 224
Cool Blue Avatar asked Jan 04 '14 14:01

Cool Blue


People also ask

What's the difference between a module and a class module in VBA?

A class is more of a unit, and a module is essentially a loose collection of stuff like functions, variables, or even classes. In a public module, classes in the project have access to the functions and variables of the module. You don't have to specify the module name to address one.

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.


1 Answers

The real answer to my question is to have a better understanding of UserForms and since I could not find a good reference for that I thought I would answer my own question to share my learnings.

Thanks to @Dick Kusleika for the key insight!

First of all, this is not a UserForm:

enter image description here

It is no more a Form than a Class Module is a variable. UserForm1 is a Class Module with a GUI and with the following default, inherited properties enter image description here

These properties are like a standard Interface that is common to all Form Class Modules and therefore instances. The Name property is in parentheses because it is not the name of the object, it is the name of the the Type that is used to declare variables to instantiate the particular Form Class.

More properties and methods can be added by the user at design time and this is done in exactly the same way as a Class Module.

For example, in a Form Module...

Option Explicit
Dim mName As String
Property Let instName(n As String)
    mName = n
End Property
Property Get instName() As String
    If Len(mName) = 0 Then mName = Me.Name
    instName = mName
End Property

In this example, the Form Class Name is used as the default Instance Name.

When you add Controls to the form, its like graphically adding

Public WithEvents controlName As MSForms.ControlType

...in a Class Module.

The Methods inherited in the standard interface include one called Show.

You can create an instance of a form using UserForm1.Show and this is very confusing and misleading. To me it implies that you are showing the Object called UserForm1 but you are not. I don't know why you would want to use this method because, apart from being confusing, it does not deliver any direct reference to the object created. Its a bit like Dim v as New Type only worse, because there is no referencing variable.

You can instantiate a Form Class in exactly the same way you can a Custom Class object and then use the show method to deploy it...

Dim f As UserForm1
    Set f = New UserForm1
    f.Show

For me, this is the preferred method. You can add custom properties and controls to the UserForm1 Class and you can give it a meaningful name when creating it, but you can also reference it using the standard UserForm interface.

For example

'In a Class Module
Dim mForm as UserForm1
Property let Form(f as MSForms.UserForm)
    Set mForm = f
End Property

For me, after understanding the above, all of my confusion about UserForms and my frustration at not being able to find a decent reference disappears. I just treat them as Class Modules and its fine.

like image 64
Cool Blue Avatar answered Sep 20 '22 03:09

Cool Blue