I am Having a challenge with ThisWorkbook.Close

[SamT]

Yeah, I saw them.


Then I took two aspirins.

[GTO]

LOL!

I saw your comment about 'mental funk'. You have no idea how much empathy I have...

Well, I am glad I found Rory's answer. I imagine in a good 95% + the form is referred to by codename, and I am certainly still guilty of doing so in my own code. A lot of testing (leastwise for a slow typist), but nice to see such a clear demonstration to his points.

Cheers Sam ,

Mark

[SamT]

I don't believe in using One-Size-Fits-All solutions. So:

The rules are

• An MS Forms UserForm is a special Class Module that has a hidden Variable assigned which Name is the same as the Class Name, (AKA, CodeName,) (Default = "UserForm1".)
• Every time VBA code refers to the Hidden Variable, the Class is instantiated if not already so.
• When a VBA Code Object Variable is set to the Form Class, and is referenced in VBA, the Class is instantiated only when specified in Code.

Therefore:

• Whenever an auto-instantiation might be a problem, use a VBA Object Variable to reference the Form, and note why in the comments;
• Otherwise use the Form as an Object in itself.

Why:

• The Form as Object is easier to understand and use.
• The code is simpler.
• All my code, even projects for private and commercial use, is aimed at Newbies.

YMMV

[Bob Phillips]

Personally, I always create a form object variable. I find it gives me greater control, and the code treats the form more as a class (which of course it is, a special auto-instancing case of class, but a class nonetheless), and means that I can set and read properties of that class from a standard module.

BTW, where is this hidden variable of a form documented, this is new to me.

[SamT]

GTO's Link: http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx

As explained in "Customizing Form Classes," Visual Basic creates a hidden global variable for each form class. This variable has the same name as the class; it's as though Visual Basic had declared Public Form1 As New Form1.

Customizing Form Classes Link: http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx

Me and My Hidden Global Variable
You may be wondering how it is that you can refer to Form1 in code, as if it were an object variable. There's no magic involved. Visual Basic creates a hidden global object variable for every form class. It's as if Visual Basic had added the following declaration to your project:

Public Form1 As New Form1

When you select Form1 as your startup object, or type Form1.Show in code, you're referring to this hidden global object variable. Because it's declared As New, an instance of the Form1 class is created the first time you use this predeclared variable in code.

The reason this declaration is hidden is that Visual Basic changes it every time you change the Name property of a form. In this way, the hidden variable always has the same name as the form class.

[snb]

Maybe the OP was only looking for:

Sub M_snb()
    CreateObject("wscript.shell").popup "tekst", 5, "snb"
End Sub

[Bob Phillips]

Mmmmm, that sounds like so much ###**@ to me. Agreed, they need to to track such an object, and they need to know it is a form, but that is true for all sorts of objects, and it would be done with the core of Excel and/or Windows. Why would they need a 'hidden' global variable - sounds like they are trying to de-mystify a complex operation rather than tell you what really happens. I tried the code the showed, and I went into the object explorer for that project, showed hidden members, but I couldn't see any such object variable.

I wouldn't expect an experienced developer to ever declare a variable as type New as they do, Dim f As New Form1, you don't have full control of that object.

[GTO]

Hi Bob and Sam,

...YMMV

Gosh, I sure hope that was: Your Method May Vary, or at least: Your Mileage May Vary and not: You Make Me Vomit! (Just chuckling, as I am afraid I had to look up the acronym.)

...Customizing Form Classes Link: http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx

Thank you Sam! I had 'clicked around' a bit from the link I posted, but I had not come upon that page. At least to me, very interesting :-)

Just to correct a bit of my continued lacking of clarity (jeepers...), when I said:

... I imagine in a good 95% + the form is referred to by codename, ...

I meant to say in a good 95+% of threads I have read, the form is referred...

That is, I am so used to seeing the form referred to by what I referred to (incorrectly, but I think it gets the idea across) as it's "codename" (or maybe better, it's object name), and I think in every vba help topic example as well, that I most certainly have developed the 'bad' habit of doing the same. Okay, enough clarity, let us see what I can botch up anew.

Mmmmm, that sounds like so much ###**@ to me. Agreed, they need to to track such an object, and they need to know it is a form, but that is true for all sorts of objects, and it would be done with the core of Excel and/or Windows. Why would they need a 'hidden' global variable - sounds like they are trying to de-mystify a complex operation rather than tell you what really happens. I tried the code the showed, and I went into the object explorer for that project, showed hidden members, but I couldn't see any such object variable.

I wouldn't expect an experienced developer to ever declare a variable as type New as they do, Dim f As New Form1, you don't have full control of that object.

Hi Bob,

Goodness, it has been too long since "talking" with you.

Okay, needless to say, any argument I give will be at best, touching inductive maybe; but more likely, nearly superstitious. So "with a grain of salt", here goes...

Whilst I do not know whether the magical 'it' should be referred to as a 'hidden global variable', why does this seem like BS? At least as I read it, Stephen Bullen seems to state this in different words here: http://dailydoseofexcel.com/archives...ult-instances/

I especially noticed:

"...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. ...

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." (underlining added and I left the last bit in as I just found it too funny - mws)

and (in the Comments section):

"(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." (underlining added - mws)

For what it is worth, I note Stephen's use of the word effectively and agree with you that this must be happening "deep down" someplace. I wonder if this may be happening (or at least the return stored) in Unknownn._Form (n being an auto-incremented value). I realize that you tested in VB (not VBA) and I do not have VB. That said, at least in VBA, I agree that the object browser does not show any hidden object variable. Unless I missed something though, with the VBAProject library selected, and with UserForm1 selected in the Classes window, I notice that any/all hidden members are members of MSForms.Frame. That is to say that there are no hidden members of Unknownn._Form.

Now while it is clear that I do not possess your depth of understanding VB/A, I do not see why we would really be expecting to find this "hidden" thing myself; why would we expect it to be exposed in such a manner? We already know its "name" and can use it in the code (I am not saying that auto-instancing the form is a great idea, just that whether we are writing Dim myFrm as UserForm1 or UserForm1.Show, we know what to type to have the code compile and run).

At least to me, the explanation that if we use the form's name on the left side of any property of the form, the first thing that happens (regardless of exactly where it happens) must be a test for Is Nothing and if this returns True, a new instance created. At least that is my current and probably overly simple belief, as (without anything preceding) Unload UserForm1 certainly seems to do just that (initialize the form, and only then, Unload it).

Well brother, not sure if that adds or detracts, hopefully the former.

Mark

[GTO]

ACK! I forgot to mention: I did not do any testing, but found reading these interesting.
http://www.xtremevbtalk.com/showthre...72#post1290672
http://www.xtremevbtalk.com/showthread.php?t=314915

[Mister_joe]

What a surprise! I veered off to do other things and I had moved on with the initial recommendations. I had no idea the thread was still rolling.