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?

Dutch

August 22, 2012

VBA Add-Ins stop working after installing Security Patch MS12-060

Filed under: Excel,VBA,Windows — dutchgemini @ 11:34 am
Tags: , , , , , , ,

Last Wednesday August 15, Security Patch MS12-060 from Microsoft hit the community and created quite some panic, because all of a sudden Add-Ins developed in VBA for Excel as well as for other Office applications stopped working. Depending on the Add-In you could either get error messages, crippled command bars or ribbons, or no warning at all.

In this patch, Microsoft replaced the ActiveX Windows Common Controls file MSCOMCTL.OCX with a more secure version. The same file has caused trouble earlier this year (April, 10).

Luckily, it appears that the problems are being caused by a faulty registration of this file into the Registry and that re-registering this file makes them disappear. This is a copy from TechNet. Mind that you must register the file as an Administrator otherwise the registration will fail.

The fix for this problem is to re-register MSCOMCTL.OCX. To do this, follow these steps:

  1. Open an administrative-level command prompt. Click START, and type RUN in the run command line. On Windows Vista and Windows 7 you will see CMD.EXE appear in the quick search list. Right click CMD.EXE and left click Run As Administrator. A black command prompt opens.
  2. If on a 64-bit machine, type the following:

regsvr32.exe /u  C:\Windows\SysWOW64\MSCOMCTL.OCX

Press ENTER, then type

regsvr32.exe  C:\Windows\SysWOW64\MSCOMCTL.OCX

If on a 32-bit machine, type the following:

regsvr32.exe /u C:\Windows\System32\MSCOMCTL.OCX

Press ENTER, then type

regsvr32.exe C:\Windows\System32\MSCOMCTL.OCX

Each time you press ENTER you will see a small informative message. This is normal. The first ENTER unregisters the file, the second registers the file.

According to this same link, you may also need to remove a key in the registry (I did not add it because for me this was not necessary). You may have to repeat the above actions.

I also re-registered MSCOMCT2.OCX, although not mentioned in the article, but only after I did my VBA running Office 2007 on W7 resumed full operation.

Your Office program should work again after doing this.

Dutch

April 17, 2012

Excel (VBA) error: Could not load an object because it is not available on this machine

Filed under: Excel,VBA,Windows — dutchgemini @ 10:09 am
Tags: , , , , , , , ,

Update: if you are having problems after installing Security Patch MS12-060 then click here.


Update (Thu Oct 18, 2012): Security Patch MS12-060 also appears to modify the TypeLib information of MSCOMCTL.OCX in the registry. Before the patch, references to MSCOMCTL in the VBA code would resolve to version “2.0” of the library. With MS12-060, this version has been updated to “2.1”. This was discovered using ProcMon on 2 different PC’s, one patch and one unpatched.

The result is that if in your VBA code you make explicit, early bonded, reference to this library or to components in the library such as in “Dim oListItems As MSComctlLib.ListItems“, Excel stores the newer version of MSCOMCTL.OCX in your project. When you transfer the workbook to a PC without the MS12-060 Security Patch (or even without MS12-027) then your project will inevitable fail.

Currently, the only way I found to recover my work (excluding building up the project from scratch inserting one by one all modules) is to add to the registry the node on the MSCOMCTL typeLib for version “2.1” (which is exactly the same as the “2.0” node):

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\TypeLib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.1]
@="Microsoft Windows Common Controls 6.0 (SP6)"

[HKEY_CLASSES_ROOT\TypeLib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.1]

[HKEY_CLASSES_ROOT\TypeLib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.1\win32]
@="C:\\WINDOWS\\system32\\MSCOMCTL.OCX"

[HKEY_CLASSES_ROOT\TypeLib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.1\FLAGS]
@="2"

[HKEY_CLASSES_ROOT\TypeLib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.1\HELPDIR]
@=""

Remind that this is not a universal solution and it will not allow you to run VBA projects on every PC around, but at least it does allow you to get back to a working system. Hopefully I am able to provide a permanent fix for this issue as well.


Security Patch MS12-027 (Vulnerability in MSCOMCTL.OCX could allow Remote Code Execution) of April 10, 2012 causes a problem that may result in the “Could not load an object because it is not available on this machine” error message when starting Excel with a workbook or an Add-In that uses components from the selected file. Usually a second other error message is also triggered is “Compile error in hidden module: <name of module>“.

To protect your PC, this patch installs an updated version of MSCOMCTL.OCX with the same COM Interface so you can use it without changing one line of code but with a different Class Id (CLSID). Via the Registry all applications seeking the original CLSID are redirected to the new CLSID so that you can continue using it safely. The section of the Registry doing this redirection is “HKLM\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility“.

You may ask yourself: «what does Internet Explorer’s ActiveX Compatibility have to do with Excel?» Well, it does, and it does a lot. Excel scans the same registry section for understanding whether it can safely load a component and what CLSID it should use as an alternative. Try ProcessMonitor and you’ll see.

When you edit an Excel workbook —doesn’t matter the format, XLS/XLA/XLSM/XLAM all have it— on a PC where the patched MSCOMCTL.OCX is installed, then Excel links internally the control to the new CLSID.

In principle the patch integrates flawlessly and you should see no difference when running Excel with a workbook or an Add-In using components from MSCOMCTL.OCX. I heavily rely in my VBA projects on the Microsoft ListView Control 6.0 ActiveX control which is contained in this file and have been able to use it without any trouble.

In the event that opening Excel does trigger the above error message(s), then this link can provide a fix. If you want to read individual info for a specific Office release, click on one of the release numbers: 2003  2007 2010

Office 2000 or 2002 are likely only affected if you have 2003 components installed, for instance when having Outlook 2003 side-by-side with Office 2002.

Wouldn’t it be that if at that point you transfer (or distribute) the Excel workbook or the Excel Add-In to a PC without the Security Patch installed, Excel will look for the new CLSID but cannot find it and that will trigger the above error message. Note that the fix mentioned earlier will not work. As a confirmation of this issue you can open the VBA project, edit a UserForm with such control in design mode and see that the particular control is indeed missing.

My main development environment is based on Windows XP and Excel 2002 and I use various Virtual PC instances running all possible combinations of Windows XP, Vista and 7 with Excel 2000, 2003, 2007 and 2010 32-bit to test the developed applications. Only the main environment is patched with Windows Update but the Virtual PC’s are not.

This week I finished a project and saved the XLS, copied it to the Virtual PC with Excel 2007 and opened it for the transformation in XLSM and to my surprise I got the error. The bad thing is that I can’t open the XLS (nor the XLSM, XLA or XLAM) on any of the mentioned Virtual PC’s because all them give the error.

I even tried to rescue the project starting a blank workbook and importing the UserForms exported from the XLS on the only PC that is working, but nada, niente, nothing, zero success. I deduct from this that in the associated .FRX file there must be a link to the new CLSID but am still unable to find out where. This makes also sense: Excel uses early binding when using ActiveX control at design time, and early binding generally uses CLSID’s and not the component’s class name.

Of course you can redesign the forms by adding the ListView again because on the failing PC there is indeed a valid ListView ActiveX control, but this is quite a PITB and is no guarantee that it will work.

As it looks now, I will need to install the Security Patch everywhere. I am looking if there is a way to bypass this need.

Security Patch MS12-027 cannot be uninstalled, this patch is considered critical and each PC should be updated as soon as possible but this is not always the case.

Update (and temporary solution)

I have found a trick —a Registry hack to be honest— that helps me open and use the failing workbook on un-patched PC’s. This hack consists in telling Excel to use the old interface instead of the new interface.

Note: Excel workbooks or Excel Add-Ins which are edited/saved on a PC with the Registry hack will open/work on both the patched and un-patched PC’s, thus including PC’s having MS12-027 installed.

Keep in mind, and I cannot say this often enough:

Do not change stuff in the Registry unless you are absolutely sure about what you are doing.

For each control you use from MSCOMCTL.OCX you need to add a Key under “HKLM\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility“. The value for each Key is in the following table:

CLSID in new MSCOMCTL.OCX Exposed Component Name
{87DACC48-F1C5-4AF3-84BA-A2A72C2AB959} Microsoft ImageComboBox Control 6.0 (SP6)
{F91CAF91-225B-43A7-BB9E-472F991FC402} Microsoft ImageList Control 6.0 (SP6)
{979127D3-7D01-4FDE-AF65-A698091468AF} Microsoft ListView Control 6.0 (SP6)
{A0E7BF67-8D30-4620-8825-7111714C7CAB} Microsoft ProgressBar Control 6.0 (SP6)
{0B314611-2C19-4AB4-8513-A6EEA569D3C4} Microsoft Slider Control 6.0 (SP6)
{627C8B79-918A-4C5C-9E19-20F66BF30B86} Microsoft StatusBar Control 6.0 (SP6)
{24B224E0-9545-4A2F-ABD5-86AA8A849385} Microsoft TabStrip Control 6.0 (SP6)
{7DC6F291-BF55-4E50-B619-EF672D9DCC58} Microsoft Toolbar Control 6.0 (SP6)
{95F0B3BE-E8AC-4995-9DCA-419849E06410} Microsoft TreeView Control 6.0 (SP6)

Inside each newly created key you must add 2 values, a REG_SZ named AlternateCLSID and a DWORD (32bit) value named Compatibility Flags (remember to includes the space between the 2 words). For convenience (and for finding my hack back quickly) I have added a third REG_SZ value using my name.

Value Name Value Type Value
AlternateCLSID REG_SZ {CLSID of the “old” component including braces}
Compatibility Flags DWORD 0x00000400 (1024)
Dutch.Gemini REG_SZ Backwards compatibility for component ‘whatever’ following MS012-027 (MSCOMCTL.OCX)

To find the old CLSID values, search for the control’s name (2nd column in 1st table) under the Registry branch “HKEY_CLASSES_ROOT\CLSID“. Watch out: the same component may have been registered multiple times with different CLSID’s, which occurs when you install updated versions of MSCOMCTL.OCX on the PC. For instance this is the case on my PC with the ListView component . So you may have to make a few attempts before you get it all back working.

Tip: the quickest way to understand what CLSID your system is using is looking for the component’s class name (e.g. MSComctlLib.ListViewCtrl) under “HKEY_CLASSES_ROOT“, read its CurVer key value, locate that key value under the same branch and from that one take the CLSID Key value:

The following registry script redirects the CLSID’s of the patched MSCOMCTL.OCX v6.1.98.33 of November 3, 2011 back to those exposed by v6.1.95.45 of December 20, 2002:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{0B314611-2C19-4AB4-8513-A6EEA569D3C4}]
"AlternateCLSID"="{F08DF954-8592-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft Slider Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{24B224E0-9545-4A2F-ABD5-86AA8A849385}]
"AlternateCLSID"="{1EFB6596-857C-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft TabStrip Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{627C8B79-918A-4C5C-9E19-20F66BF30B86}]
"AlternateCLSID"="{8E3867A3-8586-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft StatusBar Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{7DC6F291-BF55-4E50-B619-EF672D9DCC58}]
"AlternateCLSID"="{66833FE6-8583-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft Toolbar Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{87DACC48-F1C5-4AF3-84BA-A2A72C2AB959}]
"AlternateCLSID"="{DD9DA666-8594-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft ImageComboBox Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{95F0B3BE-E8AC-4995-9DCA-419849E06410}]
"AlternateCLSID"="{C74190B6-8589-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft TreeView Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{979127D3-7D01-4FDE-AF65-A698091468AF}]
"AlternateCLSID"="{BDD1F04B-858B-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft ListView Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{A0E7BF67-8D30-4620-8825-7111714C7CAB}]
"AlternateCLSID"="{35053A22-8589-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft ProgressBar Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{F91CAF91-225B-43A7-BB9E-472F991FC402}]
"AlternateCLSID"="{2C247F23-8591-11D1-B16A-00C0F0283628}"
"Compatibility Flags"=dword:00000400
"Dutch.Gemini"="Backwards compatibility for Microsoft ImageList Control 6.0 (SP6) component following MS012-027 (MSCOMCTL.OCX)"

The following script removes the above keys and values:

Windows Registry Editor Version 5.00

[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{0B314611-2C19-4AB4-8513-A6EEA569D3C4}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{24B224E0-9545-4A2F-ABD5-86AA8A849385}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{627C8B79-918A-4C5C-9E19-20F66BF30B86}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{7DC6F291-BF55-4E50-B619-EF672D9DCC58}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{87DACC48-F1C5-4AF3-84BA-A2A72C2AB959}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{95F0B3BE-E8AC-4995-9DCA-419849E06410}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{979127D3-7D01-4FDE-AF65-A698091468AF}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{A0E7BF67-8D30-4620-8825-7111714C7CAB}]
[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{F91CAF91-225B-43A7-BB9E-472F991FC402}]

Just a last reminder: the above instructions only help you out of a quirky situation. You should patch your PC to the latest versions available as soon as you can.

How does ActiveX redirect on my PC?

The following code for Excel/VBA can be used to understand how ActiveX redirection is on set on your PC. Copy it in a module of your document (Thisworkbook is ok) and let it run.

Option Explicit

Sub Main()
Dim strComputer As String
Dim oReg As Object
Dim strKeyPath As String
Dim strCompName As String
Dim arrSubKeys As Variant
Dim subkey As Variant
Dim strAltClsId As String
Dim strValue As String
Dim rownum As Long

Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_LOCAL_MACHINE = &H80000002

Application.Cursor = xlWait

strComputer = "."

Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility"

oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

ActiveSheet.Cells.Clear
ActiveSheet.Range("A1").Value = "CLSID"
ActiveSheet.Range("B1").Value = "Component"
ActiveSheet.Range("C1").Value = "Path"
ActiveSheet.Range("D1").Value = "AlternateCLSID"
ActiveSheet.Range("E1").Value = "Component"
ActiveSheet.Range("F1").Value = "Path"
ActiveSheet.Range("G1").Value = "AlternateCLSID"
ActiveSheet.Range("H1").Value = "Component"
ActiveSheet.Range("I1").Value = "Path"
ActiveSheet.Range("J1").Value = "AlternateCLSID"

rownum = 2

On Error Resume Next

For Each subkey In arrSubKeys
    ' get component name and file
    strCompName = vbNullString
    strValue = vbNullString
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & subkey, "", strCompName
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & subkey & "\InprocServer32", "", strValue
    If (strCompName = "") Then GoTo NextSubKey ' class does not exist

    ActiveSheet.Range("A" & rownum).Value = subkey
    ActiveSheet.Range("B" & rownum).Value = strCompName
    ActiveSheet.Range("C" & rownum).Value = strValue

    ' get alternate CLSID.
    strAltClsId = vbNullString
    oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, "AlternateCLSID", strAltClsId
    If (strAltClsId = "") Then GoTo NextValue ' alternate does not exist

    ActiveSheet.Range("D" & rownum).Value = strAltClsId

    ' get component name and file
    strCompName = vbNullString
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & strAltClsId, "", strCompName
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & strAltClsId & "\InprocServer32", "", strValue
    If (strCompName = "") Then GoTo NextValue ' alternate does not exist

    ActiveSheet.Range("E" & rownum).Value = strCompName
    ActiveSheet.Range("F" & rownum).Value = strValue

    ' this one also remapped?
    strValue = vbNullString
    oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath & "\" & strAltClsId, "AlternateCLSID", strValue
    If (strValue = "") Then GoTo NextValue ' alternate of alternate does not exist
    ActiveSheet.Range("G" & rownum).Value = strValue

    ' get component name and file
    strAltClsId = strValue
    strCompName = vbNullString
    strValue = vbNullString
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & strAltClsId, "", strCompName
    oReg.GetStringValue HKEY_CLASSES_ROOT, "CLSID\" & strAltClsId & "\InprocServer32", "", strValue
    If (strCompName = "") Then GoTo NextValue ' alternate of alternate does not exist

    ActiveSheet.Range("H" & rownum).Value = strCompName
    ActiveSheet.Range("I" & rownum).Value = strValue

    ' this one also remapped?
    strValue = vbNullString
    oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath & "\" & strAltClsId, "AlternateCLSID", strValue
    If (strValue = "") Then GoTo NextValue ' alternate of alternate does not exist
    ActiveSheet.Range("J" & rownum).Value = strValue

NextValue:
    rownum = rownum + 1

NextSubKey:
Next

ActiveSheet.Range("A1").Sort Header:=xlYes, _
                             Key1:=ActiveSheet.Columns("C"), Order1:=xlAscending, _
                             Key2:=ActiveSheet.Columns("B"), Order2:=xlAscending
ActiveSheet.Columns.AutoFit

Application.Cursor = xlDefault
End Sub

Click this link to download an Excel 97-2003 compatible XLS file with the same code. Open and enable macros, press Alt-F8, select ThisWorkbook.Main and click Run.

October 28, 2011

Weird bug in Excel’s Application.GetSaveAsFileName() function on Vista and 7

Filed under: Excel,VBA,Windows — dutchgemini @ 2:55 pm
Tags: , , , ,

I have found a bug and a strange problem while using Excel’s native Application.GetSaveAsFileName() function.

First to the bug.

Using Excel 2007 on top of Windows 7, this dialogue cleared the initial name I provided (obtained manipulating the current workbook’s name) while it was working fine on Windows XP. A quick test on Vista made me aware that this was also occurring on that OS.

Obviously, by coincidence, the answer:

  • if the extension of the filemane you provide is the same as the last one in the filter criteria, the name is not cleared
  • if you do not provide an extension on the filename, the name is also not cleared
  • if you supply an extension that is either not in the list of wildcard extensions or not the last one, the name is cleared (in XP the full name would be enclosed in double quotes)

To replicate this behaviour do the following:

  1. In a virgin workbook open the code pane for ThisWorkbook
  2. Paste the following code:
    Public Sub TestGetSaveAsFileName()
    Dim sName As String
    Dim sFilter As String
    sFilter = "Excel Macro Enabled filter,*.xlsm;*.xlsb;*.xls,All Files,*.*"
    sName = "File name (no ext) (filter is xlsm & xlsb)"
    Debug.Print "1) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xlsm) (filter is xlsm & xlsb).xlsm"
    Debug.Print "2) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xlsb) (filter is xlsm & xlsb).xlsb"
    Debug.Print "3) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xls) (filter is xlsm & xlsb).xls"
    Debug.Print "4) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    End Sub
  3. Run the code and watch the file’s name in the dialogue.

I presume this bug is not caused by Excel 2007 since the behaviour is consistent on XP but not on Vista nor on Windows 7, so for me this is a Vista/7 bug. I ran a quick test with Excel 2003 on Vista and 7 and there also the names disappear when the above conditions are met so this confirms it.

Now the strange behaviour.

According to the documentation (Excel 2000 up to Excel 2010) the GetSaveAsFileName() defines the following for the FileFilter parameter:

This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: “Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla”.

To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, “Visual Basic Files (*.bas; *.txt),*.bas;*.txt”.

I have found that this is not true. If you use a file filter in the dialogue’s label, this file filter gets priority over the real file filter(s). For example

“Text Files (*.pdf), *.txt;*.log, Add-In Files (*.doc), *.xla”.

will show a dialogue filtering on either “*.pdf” (from first label) instead of “*.txt” or “*.log” or on “*.doc” (from second label) instead of “*.xla”. Not convinced? Try by yourself! Solution? Do not use wildcard extensions in the label of the filter criteria.

On Microsoft XP and 7 the wildcard extension will automatically be appended to the filter’s label. However, on Vista the wildcard extensions are never displayed, so if you open the dialogue then you can only guess what wildcard extension is being applied. I have tried not hiding known extensions but on Vista I was nevertheless unable to see what wildcard extensions were associated to each entry.

A programmer friend of mine confirms that the bug and the strange behaviour are caused by the Common Dialog control of Windows Vista and Windows 7 which is used by the Excel function. He also mentioned a third one (which I have not seen yet, could related to a localised version of Windows) being that under circumstances the wildcard extension is appended to the label and therefore appears twice on the entry. For instance this filter “Text Files (*.txt),*.txt” would display as “Text Files (*.txt) (*.txt)”. This strengthens the recommendation for not putting any wildcard expressions in the label.

To avoid the bug mentioned in the beginning of my post, i.e. the filename that disappears when not with the correct extension, I recommend splitting filter definitions with multiple wildcard expressions into many single definitions with only one wildcard extension each.

This would be incorrect:

“Excel Macro Enabled files,*.xlsm;*.xlsb;*.xls,All Files,*.*”

This would be correct:

“Excel Macro Enabled document,*.xlsm,Excel Macro Binary document,*.xlsb,Excel 2003 document,*.xls,All Files,*.*”

It’s a little cumbersome but effective. Obviously, if you use filter preselection on the dialog, make sure the correct one is selected when setting the filename.

Dutch

March 14, 2011

Excel fails to center Userform correctly on Dual Screens

Editor’s Note: this post has been updated with the comments and the code example integrates the necessary corrections.

If you prefer working in pixels (used by Windows) instead of screen points (by Excel/VBA) then you may want to explore the following functions from Win32 API for the placement of the UserForm on screen: GetWindowRect() and SetwindowPos().

 


Since some time I’m working dual screen (recently I’ve moved to 3 screens on my workplace, and I can’t tell you how happy I am with it).

At the time I made the switch I immediately noticed that Excel (it was 2002, but also 2003 and higher have this problem) couldn’t correctly position the UserForm on the correct screen when using CenterOwner or CenterScreen as the form’s StartupPosition.

I’ve found a solution for this problem using a few Win32 API’s. Here it is:

Put this in a Module:

Public Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Public Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Public Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long

' Used for getting the metrics of the system devices.
'
Public Const SM_XVIRTUALSCREEN As Long = &H4C&
Public Const SM_YVIRTUALSCREEN As Long = &H4D&
Public Const SM_CXVIRTUALSCREEN As Long = &H4E&
Public Const SM_CYVIRTUALSCREEN As Long = &H4F&
Public Const LOGPIXELSX = 88
Public Const LOGPIXELSY = 90
Public Const TWIPSPERINCH = 1440

' Helper for the Select Case...
'
Public Enum StartupPosition
    Manual = 0
    CenterOwner = 1
    CenterScreen = 2
    WindowsDefault = 3
End Enum

Public Sub AdjustStartupPosition(ByRef pUserForm As Object, _
                                 Optional ByRef pOwner As Object)
On Error Resume Next
    Dim wVirtualScreenLeft   As Single
    Dim wVirtualScreenTop    As Single
    Dim wVirtualScreenWidth  As Single
    Dim wVirtualScreenHeight As Single

    ' Get coordinates of top-left corner and size of entire screen (stretched over
    ' all monitors) and convert to Points.
    '
    wVirtualScreenLeft = GetSystemMetrics(SM_XVIRTUALSCREEN)
    wVirtualScreenTop = GetSystemMetrics(SM_YVIRTUALSCREEN)
    wVirtualScreenWidth = GetSystemMetrics(SM_CXVIRTUALSCREEN)
    wVirtualScreenHeight = GetSystemMetrics(SM_CYVIRTUALSCREEN)
    '
    ConvertPixelsToPoints wVirtualScreenLeft, wVirtualScreenTop
    ConvertPixelsToPoints wVirtualScreenWidth, wVirtualScreenHeight

    Select Case pUserForm.StartupPosition
    Case StartupPosition.Manual, StartupPosition.WindowsDefault
        ' Do nothing
    Case StartupPosition.CenterOwner
        ' Position centered on top of the 'Owner'. Usually this is Application.
        '
        If Not pOwner Is Nothing Then Set pOwner = Application
        With pUserForm
            .StartupPosition = 0
            .Left = pOwner.Left + ((pOwner.Width - .Width) / 2)
            .Top = pOwner.Top + ((pOwner.Height - .Height) / 2)
        End With
        '
    Case StartupPosition.CenterScreen
        ' Assign the Left and Top properties after switching to Manual positioning.
        '
        With pUserForm
            .StartupPosition = StartupPosition.Manual
            .Left = (wVirtualScreenWidth - .Width) / 2
            .Top = (wVirtualScreenHeight - .Height) / 2
        End With
    End Select

    ' Avoid falling off screen. Misplacement can be caused by multiple screens when the primary display
    ' is not the left-most screen (which causes "pOwner.Left" to be negative). First make sure the bottom
    ' right fits, then check if the top-left is still on the screen (which gets priority).
    '
    With pUserForm
        If ((.Left + .Width) > (wVirtualScreenLeft + wVirtualScreenWidth)) _
        Then .Left = ((wVirtualScreenLeft + wVirtualScreenWidth) - .Width)
        If ((.Top + .Height) > (wVirtualScreenTop + wVirtualScreenHeight)) _
        Then .Top = ((wVirtualScreenTop + wVirtualScreenHeight) - .Height)
        If (.Left < wVirtualScreenLeft) Then .Left = wVirtualScreenLeft
        If (.Top < wVirtualScreenTop) Then .Top = wVirtualScreenTop
    End With
End Sub

' ------------------------------------------------------
' This function converts screen pixels (device dependent) to Points (used by Excel).
' ------------------------------------------------------
'
Public Sub ConvertPixelsToPoints(ByRef x As Single, ByRef y As Single)
On Error Resume Next
    Dim hDC            As Long
    Dim RetVal         As Long
    Dim PixelsPerInchX As Long
    Dim PixelsPerInchY As Long

    hDC = GetDC(0)
    PixelsPerInchX = GetDeviceCaps(hDC, LOGPIXELSX)
    PixelsPerInchY = GetDeviceCaps(hDC, LOGPIXELSY)
    RetVal = ReleaseDC(0, hDC)
    x = x * TWIPSPERINCH / 20 / PixelsPerInchX
    y = y * TWIPSPERINCH / 20 / PixelsPerInchY
End Sub

Put this in a the Userform’s code module:

Private Sub UserForm_Initialize()
    AdjustStartupPosition Me
End Sub

Dutch

December 23, 2010

VBA cannot create MSComDlg.CommonDialog ActiveX

Filed under: Excel,Programming,Setup,VBA,Windows — dutchgemini @ 12:53 pm

Within an application I’m developing for Excel (version doesn’t matter) I use the Microsoft Common Dialog Control, version 6 (SP6), contained in COMDLG32.OCX. For some reason I did not want to put the control on a UserForm and I decided to use it with late binding in my code.

So far so good, the application ran like a charm, and everyone in my office environment was happy with it.

However, when we started distributing the application to other people, it all of a sudden stopped working and the common dialog never popped up and of course, nobody could save or open files.

After investigation, I found out that this statement didn’t work:

Dim cDlg As Object
Set cDlg = VBA.CreateObject("MSComDlg.CommonDialog")

To my surprise, I got a run-time error 429 stating ActiveX component can’t create object. First I thought it was missing but this was not the case (you will find it on every PC). I tried to re-register the component but without success.

In short, and after a lot of googling, I found out that on the failing PC there wasn’t a problem with the component but with the permissions of this component. This particular component can be used in a custom application only if properly licensed and Microsoft provides the license for this component only if you are running a developer’s version of Office or of Visual Studio (and indeed in my office all PCs have). Of course, installing such edition together with my application or asking my customers to buy one was not an option for me and I had to find another solution.

Although Microsoft caused me trouble but they also provided me with a solution.

It turns out that these “component-licenses” are stored in the Registry, in particularly under the key named HKCR\Licenses. Each licensed component has its own sub-key —unfortunately with no correspondence with the CLSID of the component— and a license string stored in the (Default) item.

After some trial-and-error I was able to isolate the key used by the Common Dialog (see below) and after adding it to the PC where my application was failing I finally got everything working again, making me a happy camper.

To add the license information for the Common Dialog copy the next section to a text file, save it with a “.REG” extension and merge it into the Registry (I do this via InnoSetup):

Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\Licenses]
@="Licensing: Copying the keys may be a violation of established copyrights."
[HKEY_CLASSES_ROOT\Licenses\4D553650-6ABE-11cf-8ADB-00AA00C00905]
@="gfjmrfkfifkmkfffrlmmgmhmnlulkmfmqkqj"

I must remind that messing with the Registry can cause trouble and that creating entries under the “Licenses” key may be a violation of established copyrights, and for that purpose I have added the first line in the above text (copied from my PC’s registry).

Dutch.

October 13, 2009

Excel looses ‘modal’ status of Userforms

Filed under: Excel,Programming,VBA,Windows — dutchgemini @ 4:54 pm

I am developing an Excel Add-In that uses modal and modeless Userforms. While testing the Add-In, at some point I noticed to my surprise I could all of a sudden click outside of my modal Userform and right into Excel, select ranges, click menus and command bar buttons, and so on.

I had bounced into this problem earlier when working with the Application.GetOpenFileName function (here) and blamed this function to be the guilty guy.

Now it happened again, after showing a modal window (from an automation server) on top of my modal Userform. From a quick test with the same automation server in VB, the modal state was maintained correctly so it had to be Excel. A little later I discovered that the main Excel window had restored its state right after closing the automation server while still running code in the modal Userform.

I presume that Excel, which I was explained is not a pure SDI o MDI, does not correctly trap the ‘modal window’ stack and restores its window state while other modal Userforms are still on screen.

I have found this workaround to tackle the problem:

In a module:

Public Declare Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long
Public Declare Function IsWindowEnabled Lib "user32" (ByVal hwnd As Long) As Long

Somewhere in my form (for instance the CommandButton1_Click() event)

Dim bExcelState As Boolean   ' This boolean stores the current status of the Excel Window

' Save the current state (returns False if disabled and being isolated by a modal dialog)
'
bExcelState = IsWindowEnabled(Application.hwnd)

' Call the external or internal function that creates and destroys a window.
'
MyActiveXAutomationServer.ShowModal

' Test again. If Excel has become enabled, force disable.
'
If Not bExcelState And IsWindowEnabled(Application.hwnd) Then bExcelState = EnableWindow(Application.hwnd, False)

The above code works with Excel 2002 and higher.

If you still use Excel 2000 then you will need to use the Win32 API function FindWindow() to obtain the window handle:

This article is pretty exhaustive about using FindWindow() with Excel.

Dutch

April 14, 2008

Open Office not working because of Acrobat?

Filed under: Acrobat,Open Office,Windows — dutchgemini @ 10:36 am

Some time ago I decided to install and run Open Office on my home PC mainly because it was free, although I was entitled through my employer to install Microsoft Office.

During the last update from release 2.3.0 to 2.4.0 (unfortunately OOo does still not use incremental updates so the installation required me to download the entire 137Mb package – when will it be possible to use incremental updates like in Firefox or Thunderbird?), I discovered that on the other (non-administrator) accounts that I have on the box, all of the file associations in Windows’ Explorer were gone, making it impossible to start OOo applications from the Recent documents or by double-clicking on the file. Starting OOo first and then opening a file using the menus worked, luckily.

Frustrating to mention that on my own account (administrator equivalent) everything was working fine.

Initially I thought of a problem with the installation, so I repaired it but without success. Even a total un-install, using CCleaner to remove the garbage from the registry, and a re-install did not work. I reverted the installation to release 2.3.1 but also without success.

After some investigation, it turned out to be a registry problem. In fact, the HKCR\.odt key did not have the required permissions set for the non-administrator accounts. Usually, most keys include the ‘Users’ group with read permissions, and on my install the group was missing.

Being convinced that the problem was related to the installation of OOo (the HKCR\.pdf was fine), I filed a problem report on the Issue Tracker.

At first I tried to add the missing entries manually but it simply didn’t work out (probably because the association to OOo is not just the .odt key).

Waiting for a reply from OOo, in the mean time I installed a YouTube Flash Video downloader and a FLV Player because my wife wanted to show her mother some funny clips. Both applications came without installer, so I created a new folder under Program Files where I copied them and I created shortcuts which I placed in a new folder under the Start menu.

Bizarre enough, I had again quite some problems creating the file association for the .FLV extension to the player. Aware of the problem I had with OOo, I went back into the registry and found wrong permission settings again.

At that point, I wanted to tackle the problem for good and went looking on the Internet for some answers. Many pages, forums and tips & tricks later – not to speak about the time spent, I bumped into a page where someone made a reference to Acrobat Reader 8.1 and messing up the root of the HKCR key. The solution was within reach!

By searching again with a new set of criteria in Google, finally the problem was explained and the solution given. Acrobat Reader 8.1, which I had installed in the mean time, had messed up my registry with deep impact on the root and the entire branch of the HKCR key.

The permissions of my root HKCR key all I got was:

  • ‘Everyone’ with ‘Full Control’ on the key and on all its sub-keys

According to the same page, I should have had:

  • ‘Administrators’ as well as ‘SYSTEM’ with ‘Full Control’ on the key and on all its sub-keys
  • ‘CREATOR OWNER’ with ‘Full Control’ on the sub-keys only
  • ‘Users’ with only ‘Read control’ on the key and all its sub-keys

I did not have ‘Power Users’ since I was running XP Home but if you do, then google for it since it has some special permissions on the root of  HKCR. Taking a look at the permissions on a healthy XP Pro may also help.

I set the permissions as indicated, and as a miracle my OOo installation, as well as my FLV downloader and player worked like hell on all the accounts!

Problem solved.

Blog at WordPress.com.