Dutch Gemini's Weblog Pages

September 12, 2013

Undocumented QueryClose enum ‘vbQueryClose’ value of ‘5’

Filed under: Excel,Programming,VBA,Windows — dutchgemini @ 2:39 pm

In my Excel 2013 32-bit VBA application, while monitoring the ‘UserForm_QueryClose‘ event, Excel threw up a value of ‘5’.

So far I am acquainted with vbQueryClose enums ‘0’ … ‘4’ which are documented by Microsoft (http://msdn.microsoft.com/en-us/library/ee177104.aspx) but never traced nor saw a ‘5’. Not a single hit on the Internet, this value is a genuine mystery to me.

This value of ‘5’ occurs during closing down an Excel window —either single window workbook or multiple window workbook— to which a modeless window is hooked.

Excel 2013, being now an SDI application, causes all kind of Z-Order issues with legacy application using UserForms which are well documented on various Excel MVP sites. For instance Jan Karel Pieterse has dedicated time and resources to document and provide workarounds and solutions.

During my programming work, I have seen that modeless UserForms are linked to the window active at the time the UserForm was instantiated. Up to Excel 2010, all windows share the same Windows window handle called Application.HWnd. In Excel 2013 each Excel window has its own Windows window handle property called HWnd, which is equivalent to Application.ActiveWindow.HWnd but finally reduces to Application.HWnd. Hence there is no single Application.HWnd any more in Excel 2013, but its value changes when a different Excel window becomes the active one.

In order to show always on top a modeless UserForm created in an XLAM Add-In, I have used a Win32 API called SetWindowLong() which hooks the modeless userform to the active Excel window when I switch between them.

Because the modeless UserForm shall not be killed when an Excel window is closed, as long as the application is living, that is, excel.exe is running, I do not unload the UserForm but keep it alive with all the data inside by hiding it, using the form’s event called UserForm_QueryClose(). In this routine I detect the ‘CloseMode‘ value and set ‘Cancel = True‘ if necessary.

If I close an Excel window on 2013, first of all I receive value ‘5’ which I do not know how to handle. On top of it setting ‘Cancel = True‘ also does not prevent the UserForm from unloading, it is killed without further notice or errors, leaving my application crippled.

I would like to know what this value ‘5’ is about, maybe it’s new to Excel 2013? Are there other undocumented values and what are they designed for? Can someone help?



1 Comment »

  1. Reblogged this on Sutoprise Avenue, A SutoCom Source.

    Comment by SutoCom — September 13, 2013 @ 10:33 am | Reply

RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: