Modeless Userform not terminating

One way to display status messages as a long procedure runs is to show a modeless userform and update it with text or a progress bar showing percentage completed.
I was having a problem with Excel crashing, particularly Excel 2013, and I traced it to a userform like that. Here is a sample:

http://www.sysmod.com/modeless%20userform%20not%20terminating.xlsm

Run the ShowProblem sub.
1. With the button visible, there is no problem. The userform is terminated when the oFrm variable goes out of scope after the form is Hidden.

2. With the button not visible, the form terminate event does not fire. The userform is left in memory, (iow it is not removed by the garbage collector) and its window can be seen by WinSpy++ (class ThunderDFrame) but it is no longer accessible because its object pointer has gone out of scope.
In the addin I was working with, after a number of such windows accumulate, and in Excel 2013 they are owned by a workbook rather than the main Excel window, Excel crashes when the workbook is closed and the windows start to terminate one after the other.

3. In the demo file, run the sub twice in a row, now two ThunderDFrame windows appear in WinSpy++, and an orphaned Excel process is left behind with no App window.

The way to avoid that is to use an explicit Unload command which is better practice anyway than relying on the combination of .Hide and letting oFrm go out of scope.
According to Rory Archibald, “[it] stems from not having any controls on the form that can take focus.”
It’s the kind of thing that wastes hours of debugging.

Background

In previous versions of Excel, forms were always owned by the Excel main window (XLMAIN). Excel 2013 introduced SDI so now forms are owned by the last opened document window so we have to resort to API kludges to change the owner, like this helpful contribution from Jan Karel Pieterse:
http://www.jkp-ads.com/Articles/keepuserformontop02.asp

MSDN: Window Features, definitions of Owner and Parent

Note: GWL_HWNDPARENT changes the OWNER, not the parent, of a window

Greg Maxey’s MS Word page: Userform – Advanced Notes and Tips

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s