Wrapping a userform in a class module
Earl Takasaki
I was answering another question here, and a question I've been thinking
about for a while came up.
Has anyone written or found a nice class module and collection for wrapping
userform functionality? Specifically, I would like to be able to do the
following:
1) have access to an properties of a form via an index property of forms
collection before the forms are loaded (only loaded forms are part of the
built-in UserForms collection
2) have access to a form via an identifier -- it seems that the only way to
specific a form before it is loaded is to user its name, i.e., Userform1 (q1
and 2 are basically the same question).
3) have a property of a form that can carry a "return value" and other
properties that stick after the form is unloaded. Perhaps the Show method
could act like a function. or the return value could be stored in a property
of the form object accessible via a Get.even after the form is unloaded.
Currently, it seems we need to store "return" values either in a global or
on the worksheet to preserve them after the form is unloaded.
P.S. Tom's answer seems to work for 1 and 2, and I can use globals for 3,
but I am still curious is such a wrapper is possible and has been done.
--
Earl K. Takasaki
P.S. I accidently posted this as a reply to another posting where it really
is a new post. Sorry.
--
Please reply to the group.
Chip Pearson
A Userform module is essentially a class module. The only real difference is
that the object variable is implicitly declared by VBA, so you don't have do
declare it yourself. In other words, VBA essentially includes the following
code in your project.
Dim UserForm1 As New UserForm1
When you refer to UserForm1 in your code, you are really accessing the variable
UserForm1 (on the left side of the "As") rather than the class UserForm1 (on the
right side of the "As", and the class that shows up in the VBA Editor).
Because the variable UserForm1 is implicitly declared "As New" (called an
Auto-Instancing object), the VBA compile insert code similar to the following
prior to any use of that variable.
If UserForm1 Is Nothing Then
Set UserForm1 = New UserForm1
End If
Therefore, any reference to UserForm1 will cause the form to be loaded (except
when you use the Unload statement). For example, when you write code like
UserForm1.Show
the VBA compiler actually emits code like
If UserForm1 Is Nothing Then
Set UserForm1 = New UserForm1
End If
UserForm1.Show
Thus, UserForm1 will always be loaded when you use it in your code.
> 1) have access to an properties of a form via an index property of forms
> collection before the forms are loaded
This actually doesn't make much sense. It is meaningless to attempt to modify
properties of an object if the object hasn't been created (assuming we're not
talking about VBIDE Extensibility stuff here). That is the same as saying that
you want to store a value in a variable but not declare or allocate the
variable. If you want to modify a property of a form, you've code to create an
object based on the form's class, and that means loading (but not necessarily
showing) the form.
> 2) have access to a form via an identifier -- it seems that the only way to
> specific a form before it is loaded is to user its name, i.e., Userform1 (q1
> and 2 are basically the same question).
You can certainly declare a variable As the specific userform. For example,
Dim MyForm As UserForm1
(Note here you declare As the specific form/class, not as a generic UserForm
object). In this case, you will need to explicitly create a specific instance
of the form, using Set As New. E.g.,
Set MyForm = New UserForm1
Otherwise, you'll get an error 91 "object variable not set".
You can also create more than one instance of the same user form by declaring
two (or more) variable As that type. E.g.,
Dim UF1 As UserForm1
Dim UF2 As UserForm1
Set UF1 = New UserForm1
Set UF2 = New UserForm1
In this case, UF1 and UF2 are two completely independent objects, and changing a
property (e.g., Caption) of UF1 has no effect on UF2.
Once you've declared and created the MyForm variable(s), you can certainly store
them in a Collection object, and then use the Item propety access the particular
form in question, by its position (or key) in the Collection. Broadly speaking,
the code would look something like the following.
Dim Coll As Collection
Dim MyForm1 As UserForm1
Dim MyForm2 As UserForm2
Set MyForm1 = New UserForm1
Set MyForm2 = New UserForm2
Set Coll = New Collection
Coll.Add Item:=MyForm1
Coll.Add Item:=MyForm2
Coll(1).Caption = "One"
Coll(2).Caption = "Two"
Coll(1).Show
Coll(2).Show
> 3) have a property of a form that can carry a "return value" and other
> properties that stick after the form is unloaded. Perhaps the Show method
> could act like a function. or the return value could be stored in a property
> of the form object accessible via a Get.even after the form is unloaded.
You can't modify the Show method itself, but you can certainly write a custom
function in the form that will show the form and return a value.
Public Function ShowMe() As Long
Me.Show
ShowMe = 1234
End Function
You could then call this function directly, with code like
If UserForm1.ShowMe > 1000 Then
or you could use this method in combination with the code describe earlier, as
something like
If Coll(1).ShowMe > 1000 Then
I would not recommend using this approach with modeless forms.
Similarly, you can certainly create your own properties for the form. E.g.,
Property Get MyProp() As Long
MyProp = 1234
End Property
Property Let MyProp(V As Value)
' something
End Property
This property will then have the complete "look and feel" of a standard property
of a user form object, such as Caption. For example,
Debug.Print UserForm1.MyProp
' or
Debug.Print Coll(1).MyProp
> Currently, it seems we need to store "return" values either in a global or
> on the worksheet to preserve them after the form is unloaded.
That pretty much defines what "unloading" an object means. The object is gone,
and therefore all of its properties are destroyed. The reason that code like
UserForm1.Show
Unload UserForm1
Debug.Print UserForm1.Caption
works, and doesn't cause an error 91, is because the code that is actually
emitted by the compiler is
If UserForm1 Is Nothing Then '<
Set UserForm1 = New UserForm1 '<
End If '<
UserForm1.Show
Unload UserForm1
If UserForm1 Is Nothing Then '<
Set UserForm1 = New UserForm1 '<
End If '<
Debug.Print UserForm1.Caption
The lines of code tagged with '< are inserted by the compiler. You would not
write such code yourself.
So, I suppose the bottom line is that it makes no sense to read or write (Get or
Let) a property value of a form if the form is not loaded. The object must
have been created using the keyword (either explicitly by your code or
implicitly by VBA) in order to access its properties. That is what objects are
all about.
Of course, you don't have to unload the form. You can certainly leave it up in
memory as long as you may need to read or write its properties, even when the
form is not visible.
Remember, any reference in code to UserForm1 will cause the UserForm1 object to
be created. You don't have to explicitly load the form.
I hope this is somewhat helpful, and not even more confusing.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Earl Takasaki" <etak...@excelentconsulting.com> wrote in message
news:Op9JpthACHA.1840@tkmsftngp04...
Earl Takasaki
Thanks, Chip for taking the time to write that wonderful explanation. I
don't understand all of it at first reading (like I still don't understand
all of array formulas after 1,000,000 readings, but I can make them work
okay now) but I will play with what you gave me and tuck it in the library.
I think you have set the record for new information for me in one posting!
Mahalo nui,
--
Earl K. Takasaki
Please reply to the group.
"Chip Pearson" <ch...@cpearson.com> wrote in message
news:eT6975nACHA.2308@tkmsftngp02...
Earl Takasaki
I took a few minutes to digest your code and I think I got a handle.
Essentially, the code module of a userform is simply a class module, right?
What I didn't know (and never saw any one do) was that you can therefore
write methods and property gets and lets, right? Once that got into my head,
the returning a result from a userform became "possible." Let me know if I'm
the right track with this simple example:
I have a userform with 2 buttons and I want to know which is pushed. In the
userform code i have this:
Option Explicit
Dim mresult As Integer
Property Get result() As Integer
result = mresult
End Property
Private Sub CommandButton1_Click()
mresult = 1
Me.Hide
End Sub
Private Sub CommandButton2_Click()
mresult = 2
Me.Hide
End Sub
In a regular code module, i write this:
Option Explicit
Function ShowUserForm1() As Integer
Dim i As Integer
UserForm1.Show
i = UserForm1.result
ShowUserForm1 = i
Unload UserForm1
End Function
Now I have a function that returns a "result" from a userform, right?
I realize that you can also write a ShowMe method as your example shows. Q:
when is the method evaluated? I mean, is the method evaluated just before
the form either hides or unloads? Using the method approach, would the form
be destroyed before it had a change to evaluate the method? I.e. in the
ShowMe method, you have userform1.show followed by ShowMe = 1234; what if
this form was unloaded via the OK button and the last statment was ShowMe =
mresult? Would Showme and mresult still be in scope? It seems that using
this either approach, you must hide, not unload the form, then unload it
post getting the return value.
Thanks for all of the insights> I think I am getting a handle on this, but
please let me know it any of my conclusions are wrong.....
Cheers,
Earl K. Takasaki
Please reply to the group.
"Chip Pearson" <ch...@cpearson.com> wrote in message
news:eT6975nACHA.2308@tkmsftngp02...
Bob Phillips
As a slight extra in your discussion, in your particular example, you could
code the buttons as a collection that all invoke the same code, see John
Walkenbach's page http://j-walk.com/ss/excel/tips/tip49.htm for an example,
and test in the class which has been pressed.
--
Bob Phillips
Poole UK
"Earl Takasaki" <etak...@excelentconsulting.com> wrote in message
news:#WO6I9uACHA.1916@tkmsftngp04...
Chip Pearson
> Once that got into my head,
> the returning a result from a userform became "possible." Let me know if I'm
> the right track with this simple example:
Absolutely right. A "form class" can have properties and methods exactly as a
"normal class" does. There is no difference.
> I realize that you can also write a ShowMe method as your example shows. Q:
> when is the method evaluated? I mean, is the method evaluated just before
> the form either hides or unloads?
The code that I posted for the ShowMe method was...
Public Function ShowMe() As Long
Me.Show
ShowMe = 1234
End Function
When you call this code from a standard code module, e.g.,
Dim R As Long
R = UserForm1.ShowMe
The Show method in ShowMe is called, and does not return until the user form is
hidden (either with a Me.Hide method or the user clicks the "X" button on the
form), assuming that we are showing the form modally. Then, when the form is
hidden, Show returns and the value 1234 is assigned as the result of ShowMe, and
the function exits. The ShowMe method doesn't return, and therefore its result
isn't place in variable R, until after the form is hidden.
This is why I wrote that I would not recommend this method with modeless forms.
If you were to change the method to
Public Function ShowMe() As Long
Me.Show vbModeless
ShowMe = 1234
End Function
the form would display, and the ShowMe function would immediately return a
result of 1234, even while the form is still visible.
If you unload (and therefore hide) the form from within the form itself, the
ShowMe method will still work as expected.
Public Function ShowMe() As Long
Me.Show
ShowMe = 1234
End Function
Private Sub CommandButton1_Click()
Unload Me
End Sub
The form won't actually unload at the instant the Unload statement is
encountered. Instead, it will be deferred until the ShowMe method completes.
This will be true regardless of whether the ShowMe method is called directly
from the UserForm1 class or another variable declared As Userform1. E.g., The
following code snippets will both work.
Dim R As Long
Dim UF As UserForm1
Set UF = New UserForm1
R = UF.ShowMe
'and
R = UserForm1.ShowMe
However, if you use your Property-based approach, and Unload, rather than simply
Hide the form from with the form code, you will get the default value of the
Result property. This is because the value of mResult is destroyed when you
unload the form.
[in UserForm1]
Private mResult As Long
Property Get Result() As Long
Result = mResult
End Property
Private Sub CommandButton1_Click()
mResult = 1
Unload Me
End Sub
Private Sub CommandButton2_Click()
mResult = 2
Unload Me
End Sub
[in Module1]
UserForm1.Show
Debug.Print UserForm1.Result
When you call for the Result property, the reference to UserForm1 will cause VBA
to reload the form with all variable and property at default values. If you
want to actually Unload (destroy) the form from within the UserForm itself, you
should use something like the ShowMe method show earlier so that you can be sure
to get the correct result value.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Earl Takasaki" <etak...@excelentconsulting.com> wrote in message
news:#WO6I9uACHA.1916@tkmsftngp04...