PDA

View Full Version : [SOLVED] I am Having a challenge with ThisWorkbook.Close



Mister_joe
01-10-2014, 03:24 AM
Hi All,
I am wondering whether the following command is triggering other events in my code:


ThisWorkbook.Close Savechanges:=False


I have a macro enabled Workbook on which I wanted to place a splash screen. The splash screen was intended to display for about five seconds and then disappear. However, if the user is not willing to wait for the splash screen to disappear, he or she could just click on a command buton on the form. If the Workbook is the only one in the Workbooks Collection at the time the user clicks the button, the application should close. If there are other Workbooks in the Workbooks Collection when the user clicks the button, only the ActiveWorkbook (the one that holds the splash screen) should close. Well, my code works this way, except that when I have multiple Excel files open and I close the one that has the splash screen, I get Excel security notice as shown in the image. When I click on Enable Macros, my code runs all over again. This is not what I want. I get the impression that when the Workbook closes, it triggers the UserForm_Initialize event. Could you be kindly run the code shown below and see if you can reproduce the security notice. Any idea how to work around this?
11071

'Splash screen event handler (the form was named frmSplashScreen)
Private Sub UserForm_Initialize()
Application.OnTime Now + TimeValue("00:00:05"), "KillTheSplashScreenForm"
End Sub


'Command button on the splash screen (the button was named cmdSplashScreenQuit)
Private Sub cmdSplashScreenQuit_Click()
'When the user chooses to quit the programme, the workbook should be closed
'If there are no other Excel files open, then just close the application
If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close Savechanges:=False
End If
End Sub


'This procedure is on the General module
Private Sub KillTheSplashScreenForm()
'Kill the splash screen form
Unload frmSplashScreen
End Sub


'Workbook event handler
Private Sub Workbook_Open()
frmSplashScreen.Show
End Sub

snb
01-10-2014, 03:38 AM
You have to disable the ontime procedure (dive into the VBEditor helpfiles)

GTO
01-10-2014, 04:47 AM
As snb stated, you'll want to re-read the help topic on the OnTime Method. That said, to have the Schedule arg cancel, you'll need to save the EarliestTime in a variable.

I have a question though. I could not get the picture big enough to see clearly (but then again, I am fairly bleary-eyed at the moment), but presuming the form's title bar, or more specifically the Close button is still showing/enabled (and the form's menu), then what happens if the user just clicks the "x" at the upper right-hand corner of the form?

NOT tested at all, but maybe put the (modified) code under QueryClose event. If NOW() is less than the EarliestTime variable's value, then cancel OnTime and the other stuff.

Again, not tested, just a thought.

Mark

Mister_joe
01-10-2014, 04:47 AM
Thanks, but if I disable to OnTime procedure, how would the splash screen disappear after the preset time?

GTO
01-10-2014, 04:49 AM
You would only "disable" (cancel) the OnTime if the user kills the form before OnTime's procedure has run. Does that make sense?

Mister_joe
01-10-2014, 06:33 AM
The image has been attached. You may wish to open it with Paint. Your suggestions do make sense, but I am still having problems with the OnTime procedure. If I issue

Application.OnTime EarliestTime:=Now, Procedure:="KillTheSplashScreenForm", LatestTime:=Now, Schedule:=False
ThisWorkbook.Close
I get the following error message:Run-time error '1004'
Method 'OnTime' of object '_Application' failed

If I issue

ThisWorkbook.Close
Application.OnTime EarliestTime:=Now, Procedure:="KillTheSplashScreenForm", LatestTime:=Now, Schedule:=False"
There is no effect. The OnTime procedure is not cancelled.

Bob Phillips
01-10-2014, 07:56 AM
As Mark said, you set the Ontime runtime into a variable and pass that to the initial run, then you use that variable when cancelling the OnTime. You can't use Now because the Now when the OnTime was initially issued will be different to the Now when it is cancelled, hence they do not match up.

Mister_joe
01-10-2014, 08:09 AM
Thanks. Here is what I have now. It seems to be alright.

Dim maxWait As Variant
Dim minWait As Variant

Private Sub cmdSplashScreenQuit_Click()
If Application.Workbooks.Count = 1 Then
Application.Quit
Else

'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False

ThisWorkbook.Close Savechanges:=False

End If
End Sub


Private Sub UserForm_Initialize()
minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
maxWait = Now() + TimeValue("00:00:05") 'do not execute the procedure after this time

Application.OnTime Earliesttime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True
End Sub

SamT
01-10-2014, 09:59 AM
Dim maxWait As Variant
Dim minWait As Variant

Private Sub cmdSplashScreenQuit_Click()
KillFormNow
End Sub

Private Sub UserForm_click()
KillFormNow
EndSub


Private Sub UserForm_Initialize()
minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
maxWait = Now() + TimeValue("00:00:05") 'do not execute the procedure after this time

Application.OnTime Earliesttime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True
End Sub

Private Sub KillFormNow()
If Application.Workbooks.Count = 1 Then
Application.Quit
Else
'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False
ThisWorkbook.Close Savechanges:=False
End If
End Sub


Private Sub KillTheSplashScreenForm()
Unload frmSplashScreen
End Sub

Mister_joe
01-10-2014, 04:00 PM
Thanks SamT. Your's is even better.

SamT
01-10-2014, 06:14 PM
If you set cmdSplashScreenQuit's Cancel and Default properties both to True, KillFormNow will run when the Space-bar, or the Enter Key, or the Escape Key is pressed.

For guaranteed results, add the line "cmdSplashScreenQuit.SetFocus" to the Initialize sub.

For a better looking Splash Screen, try setting the CommandButton's BackStyle Property to Transparent.

When you are totally satisfied with it, will you open a new workbook, and in the VBE Project Explorer, Drag the Form module and the Module with Sub KillTheSplashScreenForm() to the new workbook's Project? Then add the Workbook_Open code to the new book.

After insuring that the new book runs the Splash screen properly, upload it here as an example for others.

GTO
01-11-2014, 05:56 AM
@SamT:

Greetings,

Maybe I am missing something, but what happens if you kill the form via the 'X' button in the upper-right, or ALT+SPACEBAR;C prior to OnTime kicking into gear? At least for me, the OnTime seems not cancelled, and UnLoad (in 2010) re-initializes the form (and restarts the OnTime).

Mark

SamT
01-11-2014, 06:50 AM
UnLoad (in 2010) re-initializes the form (and restarts the OnTime).

Wow! That's unexpected. I would think that all methods of unloading the form should give the same results. From what you're telling me the Reset OnTIme code should be in the Form_Terminate event procedure. Then rewrite the general module code to
Do Events
If Not Form is Nothing Then Form.KillMeNow

In 2003, the unload sub just runs, but nothing else happens.

The OP seems happy and he's running it in an xlsm file... :dunno

snb
01-11-2014, 07:29 AM
In the userform1 codemodule:


Private Sub UserForm_Initialize()
c00 = DateAdd("s", 5, Now)
Application.OnTime c00, "frmstop"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
frmstop
End Sub

In a macromodule:


Public c00
Public Sub frmstop()
If DateDiff("s", c00, Now) < 5 Then Application.OnTime c00, "frmstop", False
Unload UserForm1
End Sub

GTO
01-13-2014, 06:25 AM
Hi Sam,


Wow! That's unexpected. I would think that all methods of unloading the form should give the same results. From what you're telling me the Reset OnTIme code should be in the Form_Terminate event procedure.

Probably overly cautious, but I personally would think of placing the initial .OnTime somewhere other than the form's initialize event. Regardless, I do think that using QueryClose or Terminate would be better for cancelling the .OnTime, as this covers any manner of the form unloading. The problem I spotted is that in running into a Unload MyForm, where MyForm is already unloaded, this causes the form to first initialize, then unload. Hence, the .OnTime gets started again...

This happens any time that the form, while not loaded, is referred to. Thus...

Then rewrite the general module code to

Do Events
If Not Form is Nothing Then Form.KillMeNow

...will always pass the If test. Either the form is loaded, or, if the form is not loaded, it gets auto-instanced by being referred to in the If test.



In 2003, the unload sub just runs, but nothing else happens.

The OP seems happy and he's running it in an xlsm file... :dunno

If I understood your post at all, everything in the first block of code (at #9) is in the form and KillTheSpashScreenForm is in a standard module, right? I blew the dust off of my fried-keyboard laptop w/XP and Excel 2000; same results.

Try stepping thru this in a new workbook:

UserForm1 Code:


Option Explicit

Private Sub UserForm_Initialize()
Stop
End Sub


Standard Module:

Option Explicit

Sub example()

If Not UserForm1 Is Nothing Then 'Always passes test

Unload UserForm1 'unloads the form

MsgBox UserForm1 Is Nothing 're-auto-initializes the form

Unload UserForm1 'Unloads the form

Unload UserForm1 're-auto-initializes the form, then unloads

End If

End Sub

Hopefully I expressed that a bit better?

Mark

PS - you might enjoy reading http://msdn.microsoft.com/en-us/library/aa242139(v=vs.60).aspx

SamT
01-13-2014, 09:06 AM
GTO, as usual, you are right. Thank you for the lesson and the link. It is always good to learn more bout my favorite subject.

In my "test" in #13, I obviously used way too simple of an experiment and the form was merely loading and unloading without my notice. My bad :whip

This explains why in #14, snb used a public variable in the MacroModule, his sub frmstop doesn't have to refer to the form to see if it is still open.

The research and rereading all the code herein made me realize that the sub KillFormNow in my #9 must actually read:


Private Sub KillFormNow()

'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False

If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close Savechanges:=False
End If
End Sub

What do you think about the OPs comment in re minWait = maxWait?

Again, I thank you for your tutoring time.

GTO
01-14-2014, 05:31 AM
GTO, as usual, you are right. Thank you for the lesson and the link. It is always good to learn more bout my favorite subject.

Hi Sam,

:blush My pleasure and of course you are most welcome. I just wish I had your "brain" for relationships and DB design. I would so much like to be able to combine some stuff that I "know" can go together. I know that learning is what makes it interesting and challenging, but some days, I sure would like to be able to just "download" others knowledge. (I realize that life would become quite the bore if that were possible, just saying...)


Well, here we go...



In my "test" in #13, I obviously used way too simple of an experiment and the form was merely loading and unloading without my notice. My bad :whip

This explains why in #14, snb used a public variable in the MacroModule, his sub frmstop doesn't have to refer to the form to see if it is still open.

The research and rereading all the code herein made me realize that the sub KillFormNow in my #9 must actually read:


Private Sub KillFormNow()

'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False

If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close Savechanges:=False
End If
End Sub


@snb:

No offense intended, but even a blond guy can paste that little bit of code in a workbook accurately and run it. I am awfully confident I did so accurately, and when run, !

@SamT:


Errr... well... about snb's suggestion (at all: please take this in the light-hearted manner intended) , did you try it? Before you do, close anything else you are working on.

See, yours would continue to call Unload, which in turn (as the form was already unloaded), would re-initialize the form - then re-unload it if-you-will, and during initialize, set another .OnTime. snb's does similarly, just way worse. As snb has called frmstop in QueryClose, if we 'manually' kill the form (the 'X' close button) before .OnTime calls frmstop, then we have already unloaded the form. Now when QueryClose calls frmstop, the UnLoad is run into again, and re-initializes, setting another new .OnTime, before again unloading. Same 'recurse'.

If we let the form run, then the .OnTime calls frmstop before the first QueryClose, so we just started our "evil loop" from a different place.

I have attached a modified version of snb's suggested code to sort of show what is happening, but it is not really spot-on, as collecting the values to variables and writing them to a textfile takes time, so some of the processing appears lost from snb's literal suggestion. If you have everything else closed, you can run snb's code from the vbe windows and watch. (Seriously, don't have anything else open)

In the form's initialize, hit F5. Once the userform disappears, you will be staring at vbe. If the form is displayed, close that window quickly, so that you are staring at:


Private Sub UserForm_Initialize()
c00 = DateAdd("s", 5, Now)
Application.OnTime c00, "frmstop"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
frmstop
End Sub


Keep your mouse pointer left of
Private Sub UserForm_Initialize() so that you can click and put a break in. Now watch the titlebar. You will see that code gets run every few seconds, and as far as I can tell, starts adding OnTime's as it starts running more of the time. If you leave it go, pretty soon Excel is unresponsive...

As mentioned, I included a modified copy, to sort of see what happens. Again though, not exactly accurate due to the slowing of overall execution. It does however include a constant that limits how many times we'll 'recurse'.

To try this:

F5 from A_RunIt.

When the form disappears, just wait until the Stop in UserForm_Initialize is reached, Now F8 until all done (the second time the End Sub is reached in frmstop).

Now, F8 or F5 thru Cleanup to close the created text file and reset everything. You can read thru the created text file.

Okay - as to your code, I think KillFormNow is fixed, but we did not handle the user closing the form "manually". In summary, I think we are facing a "bad" combination of three things.

We start the .OnTime in the form's initialize, which means accidently starting a new .OnTime if we accidently initialize.
We refer to the form by its CodeName, rather than setting a variable to a new instance of the form.
We aren't handling if the user closes the form "manually".


Here (included in the zip) is your last, modified a tiny bit. It seems to work for me.


[Option Explicit

'***Post 9
Dim maxWait As Variant
Dim minWait As Variant

Private Sub cmdSplashScreenQuit_Click()
'KillFormNow
Unload Me
End Sub

Private Sub UserForm_click()
'KillFormNow
Unload Me
End Sub

Private Sub UserForm_Initialize()

minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
maxWait = Now() + TimeValue("00:00:05") 'do not execute the procedure after this time

Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True

End Sub
'***End Post 9

'***Post 16
Private Sub KillFormNow()

If Now < minWait Then 'added _mws

'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False

If Application.Workbooks.Count = 1 Then
'Application.Quit
ThisWorkbook.Close SaveChanges:=False
Else
ThisWorkbook.Close SaveChanges:=False
End If

End If

End Sub

Private Sub UserForm_Terminate()
KillFormNow
End Sub

In gist, have Terminate call KillFormNow, and in that procedure, test to see if the user dismissed the form before the .OnTime's call ran.

FWIW, here is what I came up with (also attached):

[B]In a Standard Module:

Option Explicit

Private MyForm As frmOnTimeTest
Public timeEarliest As Date

Public Sub ButtonClick01()

'//Less problems if we use a variable to reference an instance of the form. //
Set MyForm = New frmOnTimeTest

If IsLoaded(MyForm) Then

timeEarliest = Now + TimeSerial(0, 0, 5)
'// Might well be better ways, but to ensure specific instance of the form is loaded... //
Application.OnTime EarliestTime:=timeEarliest, _
Procedure:=ThisWorkbook.Name & "!'basOnTimeTest.KillSplash GetForm'"
MyForm.Show

End If

End Sub

Private Sub KillSplash(frm As Object)

'// As 'Is Nothing' doesn't seem reliable, test some other way to see if the form is loaded.//
If IsLoaded(frm) Then
Unload frm
'Debug.Print "Unloaded by .OnTime"
End If

End Sub

'// I would imagine this to be similar to a read-only property get? Anyways, it is //
'// what I managed, in order to be able to pass an arg w/the .OnTime //
Public Function GetForm() As Object
Set GetForm = MyForm
End Function

Private Function IsLoaded(ByVal frm As Object) As Boolean
Dim sTemp As String

On Error Resume Next
'// I have seen examples looping through the userforms collection to test against the //
'// form's name, but I was thinking maybe producing an error by testing against a //
'// built-in property. An error should indicate that either no reference has been set,//
'// or the reference has been but unloaded (i.e. Callee...has disappeared). //
sTemp = frm.Name
IsLoaded = Err.Number = 0
On Error GoTo 0

End Function

In a userform named frmOnTimeTest:

[Option Explicit

Private Sub cmdUnload_Click()
Unload Me
End Sub

Private Sub UserForm_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
'// Just to see if stepping thru //
End Sub

Private Sub UserForm_Terminate()

'// Ensure the .Ontime hasn't already expired before cancelling //
If timeEarliest > Now Then

Application.OnTime EarliestTime:=timeEarliest, _
Procedure:=ThisWorkbook.Name & "!'basOnTimeTest.KillSplash GetForm'", _
Schedule:=False

'Debug.Print ".Ontime cancelled, user dismissed form"

End If

End Sub


What do you think about the OPs comment in re minWait = maxWait?

It seems neat, but I have not tested yet.

Way past when I should have hit the rack, a great day to you :-)

Mark

GTO
01-14-2014, 05:35 AM
ACK! I forgot, read through: http://www.mrexcel.com/forum/excel-questions/705504-pass-string-variable-user-form-using-property-procedures.html

SamT
01-14-2014, 08:58 AM
See, yours would continue to call Unload, which in turn (as the form was already unloaded), would re-initialize the form - then re-unload it if-you-will, and during initialize, set another .OnTime.

But... It can't be already unloaded if the User can click on the form. If the User does click, the OnTIme is canceled. The only exception I can see would be if the User clicks at 4.999... seconds.


This version (#9+#13) works fine on XL2002
Dim maxWait As Variant
Dim minWait As Variant

Private Sub cmdSplashScreenQuit_Click() 'Form is visible
KillFormNow
End Sub

Private Sub UserForm_click() 'Form is visible
KillFormNow
End Sub

Private Sub UserForm_Initialize()
minWait = Now() + TimeValue("00:00:05")
maxWait = minWait

Application.OnTime Earliesttime:=minWait, LatestTime:=maxWait, _
Procedure:="KillTheSplashScreenForm", Schedule:=True
End Sub

Private Sub KillFormNow()
Application.OnTime Earliesttime:=minWait, LatestTime:=maxWait, _
Procedure:="KillTheSplashScreenForm", Schedule:=False 'Cancel OnTime

If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close Savechanges:=False
End If
End Sub
Private Sub KillTheSplashScreenForm()
Unload frmSplashScreen 'form is no longer visible
End Sub

GTO
01-14-2014, 10:37 AM
But... It can't be already unloaded if the User can click on the form. If the User does click, the OnTIme is canceled...



... about snb's suggestion...

...See, yours would continue...


Okay - as to your code, I think KillFormNow is fixed, but we did not handle the user closing the form "manually"...

I am sorry Sam, I thought I was wording that okay, but was less than clear. Where you quoted from, what I was trying, albeit poorly, to communicate, was more at what yours would previously do (as in, your original suggestion). I was trying to compare this to where snb's suggestion faced similar issues (you had mentioned his suggestion).

Reference your current code, I very much agree that if the user clicks either the form or the command button, then the OnTime gets cancelled; I did say this later in the post. When I commented as to your updated code, what I meant by '...closing "manually"...' was if the user dismissed the form through the form's menu or close ('X') button.

I hope I've been at least a bit more articulate?

BTW, 'cat-killing' curiosity striking, did you try the workbooks I attached?

Mark

SamT
01-15-2014, 08:37 AM
Yeah, I saw them.


Then I took two aspirins.

GTO
01-16-2014, 02:04 AM
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 :beerchug: ,

Mark

SamT
01-16-2014, 09:32 AM
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
01-16-2014, 03:16 PM
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
01-17-2014, 08:53 AM
GTO's Link: http://msdn.microsoft.com/en-us/library/aa242139%28v=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/library/aa262343%28v=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
01-17-2014, 09:02 AM
Maybe the OP was only looking for:


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

Bob Phillips
01-17-2014, 10:58 AM
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
01-17-2014, 06:58 PM
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/library/aa262343%28v=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/2005/05/02/userform-default-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
01-17-2014, 07:05 PM
ACK! I forgot to mention: I did not do any testing, but found reading these interesting.
http://www.xtremevbtalk.com/showthread.php?p=1290672#post1290672
http://www.xtremevbtalk.com/showthread.php?t=314915

Mister_joe
01-19-2014, 11:18 AM
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.