From Doco:
Chapter 10 of PED; “Userform Design and Best Practices”; page 303 – topic
“Use Classes, Not the Default Instance” et seq. I have read this a couple
times and wind up with the same confusion (which is a reflection on me not
the authors). It appears to me what is being stated is an instance of the
form class should be brought to bear from “within the form module” EG…
Dim frmOptions AS FOptions
Set frmOptions = New FOptionsfrmOptions.Show
…
This then would have two instances of FOptions class? I know I am missing
something – what is it?
Not from “within the form module”, but from within a standard module or another class module. You wouldn’t use the Show method from within the form’s module. This sub would exist somewhere else and serve to show the form. Do I understand your statement there?
You would only have one instance of FOptions class, the one you instantiated with the Set = New construct. VBA will not auto-instantiate a new FOptions variable unless you use FOptions somewhere else. This code, for instance, would produce two instances:
Set frmOptions = New FOptions
FOptions.Show
What VBA is really doing there is
Set frmOptions = New FOptions
If FOptions Is Nothing Then
Set FOptions = New FOptions
End If
FOptions.Show
Now you have 1) frmOptions which you instantiated with Set = New and 2) FOptions which you instantiated just by using it.
FOptions is the name of a class and it’s a built-in, auto-instantiating object variable. When you use it on the right side of a Dim or Set statement, you’re using the class name. When you use it on the left side of a property or method, you are using an object variable that has the same name as a class. In your example, you only used it on the right side of a Dim and Set, so you only have one instance.
That’s harder to explain than I thought it would be. Which goes to show I really don’t know what I’m talking about. Comments, corrections, and clarifications welcome.
“2) FOptions which you instantiated just by using it.”
Like using a constructor in JAVA or PHP?
Hi Doco
There are basically two ways in which we can use Userforms in Excel:
(a) Allow Excel to handle their lifetime
(b) Handle their lifetime ourselves
To illustrate the difference, let’s assume we have a form that we’ve called MyForm with a text box called TextBox1 (where I’m deliberately leaving off any prefix to avoid confusion).
(a) As soon as we add the form to the project, Excel effectively creates a global variable with the same name as the form and automatically makes it refer to an instance of the form when it’s used.
The form is ‘created’ (aka Loaded) in one of two ways:
(i) explicity loading the form using
Load MyForm
(ii) implicity loading the form when first refering to it:
MyForm.Show
Similarly, the form is ‘destroyed’ (aka Unloaded) in one of two ways:
(i) explicity unloading the form using
Unload MyForm
(ii) allowing Excel to unload it when the user clicks on the window [x] button to close it.
This can give us the problem in code like the following:
MyForm.Show
MsgBox “You entered ” & MyForm.TextBox1.Text
That runs without error, but doesn’t show what you entered. This is because the form is unloaded after the .Show (by the user clicking the [x]) and automatically recreated when we refer to it in the second line.
(b) Treating the form just like any other class module (and adding a little extra code to switch off Excel’s handling of its lifetime) helps avoid those types of error. Firstly, we have to handle the QueryClose event to ensure the form is only ever hidden:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Me.Hide
Cancel = True
End If
End Sub
Similarly, the code behind any OK or Cancel buttons would also just Hide the form rather than unload it.
The calling code to show the form (i.e. outside the form’s module) would then be:
Sub Test()
Dim SomeVar As MyForm
Set SomeVar = New MyForm
SomeVar.Show
MsgBox “You entered ” & SomeVar.TextBox1.Text
End Sub
In this situation, the form is loaded in the line ‘Set SomeVar = New MyForm’ and is unloaded at the end of the procedure when the SomeVar variable goes out of scope. As we now have total control over its lifetime, we don’t suffer from the previous problem.
Note, though, that if in the second example, we show the form modeless, it isn’t unloaded at the end of the procedure and we have to explicity unload it using ‘Unload SomeVar’
Hope that helps
Stephen Bullen
“we have to handle the QueryClose event to ensure the form is only ever hidden”
Oh no we don’t :) If you comment out your QueryClose code it works just the same.
Jamie.
Hi Jamie
Oh yes we do :-). Try showing the form again.
Regards
Stephen
Yes, quite helpfull – thanks.
“Try showing the form again”
You mean before SomeVar goes out of scope? Ooh, nice error message!
Apologies, I thought you were saying we needed to set Cancel = True to be able to access SomeVar.TextBox1.Text from the calling procedure.
Jamie.
“You mean before SomeVar goes out of scope? Ooh, nice error message!”
Exactly – and I think there are problems with accessing module-level variables in that situation too (rather than controls on the form).
Regards
Stephen
OK, like Doco, I had a shaky understanding of that section of PED (hey..I’m an economist) but it’s much clearer now. So thanks to Doco for asking, and Stephen for answering.
And I’m slowly coming to terms with class modules
So Stephen’s comment:
means ‘avoiding the As New’ declaration – or specifically that when we use an object/class, we should ALWAYS create a new instance of it?
thanks,
Christopher
(and thanks guys for writing the book)
Stephen,
This post has prompted me to read the chapter through.
It’s comforting to know that (through experience and pain) I had already settled on the methods this chapter describes – with an exception:
In UserForm_QueryClose I have not been checking the value of CloseMode.
Could you please advise the consequences of not checking this value?
Cheers,
Rob
Christopher:
By “treating the form just like any other class module”, I mean that we should think about its lifetime and at what point it should be created and destroyed. Generally, that means avoiding the ‘As New’ declaration – as that’s giving away control over when it’s created, but not necessarily ALWAYS creating a new instance. It’s generally good practice to only keep objects alive for as long as you need them, but if there’s something in the class that takes a long time to initialise (say retrieving some data over the web), you’ll probably want to make a conscious decision to only do that once and keep the class alive throughout the application. Similarly, some classes might provide application-wide services, such as event handling or an object model – in which case you’d want to keep those around too.
Rob:
The QueryClose event is called by lots of things to close the form, not just clicking the [x] button. If your code just does a Me.Hide, that’ll only give you issues if it’s a modeless form that uses scarce resources, as the form won’t be unloaded until the workbook is closed. If your QueryClose includes a “Do you want to save the changes?” prompt, you probably only want to show that if the close is called because of the [x] button.
Regards
Stephen
I’ve been trying to create a control array in VB Excel.
ie.
textbox(1)
textbox(2)
in standard VB 6.0 you just have to copy and paste the textbox and VB will ask if you want to create an array.
Is there a way to do that in VB Excel that you know of?
Calvin: VBA does not support control arrays like VB6. You have to create a custom class. See http://www.dailydoseofexcel.com/archives/2005/08/23/toggle-button-events-class/