Dutch Gemini's Weblog Pages

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.



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

@="Microsoft Windows Common Controls 6.0 (SP6)"





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

    rownum = rownum + 1


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

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.

March 21, 2012

Range from Array – Limits on length of Strings

Filed under: Excel,Programming,VBA — dutchgemini @ 11:05 am
Tags: , , , , , , , ,

In a recent post (click here) I discussed an error I ran into using CopyFromRecordSet. While trying to resolve the problem, I reverted to using the ADODB GetRows method but ended up the same situation: incomplete data set.

Lucky enough, I ran into a strictly related problem: if you write to ranges using arrays, the method will fail if the array contains a string element sized larger than 8203 characters.

Note: CopyFromRecordSet method fails for the same reason and on the same limit.

Paste the above code in a VBA module to see this happen:

Sub RangeFromArrayFails()
On Error Resume Next
    Dim i As Long, a As Variant

    ReDim a(1 To 2, 1 To 1) As Variant     ' Need an array with 2 rows

    For i = 1 To 32767 Step 2              ' 32767 is Excel's cell limit
        a(1, 1) = String(i, "*")           ' Create a string
        a(2, 1) = String(i + 1, "*")       ' As above
        ActiveSheet.Range("A1:A2").Clear   ' clear the area
        ActiveSheet.Range("A1:A2") = a     ' Assign array to range
        If (Err.Number <> 0) Then Exit For ' Error, quit.
    Next i

    Debug.Print IIf(i > 32767, "No limit", "Limit reached at: " & i)
    Debug.Print "Length A1", Len(ActiveSheet.Range("A1"))
    Debug.Print "Length A2", Len(ActiveSheet.Range("A2"))
End Sub

This should be your result:

Limit: 8203
Length A1      8203
Length A2      0

You can try the above code with only 1 array element and see it end without problems.

Have a nice day.

Error -2147467259 Method ‘CopyFromRecordset’ of object ‘Range’ failed

Filed under: Excel,Programming,VBA — dutchgemini @ 10:28 am
Tags: , , , , , , , ,

Recently I ran into error number ‘-2147467259‘ with description “Method ‘CopyFromRecordset’ of object ‘Range’ failed” while transferring a data set from SQL Server into Excel using the aforementioned method on an ADODB.RecordSet.

Of course I Googled for the error but the answers were vague and often pointed to Null values in the RecordSet’s data, but I had other sets that had such empty values and these were dropped without problems, so it had to be somewhere else, in particularly in the data.

The reason for this error is not so easy to find, since CopyFromRecordset may indeed copy data to the worksheet (in my case 31,655 rows of data were copied out of 41,899). From Excel’s point of view, the RecordSet is like a huge array and CopyFromRecordset drops the values by columns and by rows (“Over, then down” in Excel terms). At row 31,655 data was dropped in columns 1 til 4 but no data from column 5 til the last column (31).

After investigation I have been able to isolate the problem and discovered that a very long string in column 5 of the RecordSet —retrieved from a MEMO data type— generated the error. The offending string was 10366 characters long, about a third of Excel’s maximum cell size of 32767 characters. I also discovered that other records having very large text —about 8100 characters—  in the same field in the RecordSet were copied to the worksheet successfully. This made me curious.

I prepared a small test using Excel and Access (2007) and finally was able to establish the upper limit at 8203 character that CopyFromRecordset could handle without problems. Any string length above 8203 characters inevitably fails.

Note: this error is likely generated only when there are at least 2 rows or columns in the RecordSet. A RecordSet with a single value may not even trigger it, or at least I was unable to.

If you have control on the way the RecordSet is generated and you want to use CopyFromRecordset then you should make sure no strings are retrieved large than 8203 characters. If not, then you should revert to use the GetRows method from ADODB and walk your way through the array chopping off any excess characters from the string values before dropping the array to the Range, otherwise this will fail also.

March 19, 2012

Excel VBA Oddity – Replace function not returning entire string

I just boomed into this funny oddity using VBA with Excel.

I wanted to generate data to a text file in the TEMP folder, so I used the FileSystemObject’s GetSpecialFolder() to obtain the TEMP folder name and I crafted a file name using the system time. Then I joined the 2 strings with the backslash character (\).

To avoid problems with double backslash characters, I used Replace() to replace them with single backslash. Since I did not want to replace them at the beginning of the string because I am not sure if the temp folder is not something like for instance in ‘\\server\share\...‘, I decided that I would start replacing from position 3 onwards, like in:

sFileName = VBA.Replace(sFileName, "\\", "\", 3, -1, vbTextCompare)

From Help, I read (and highlight only the relevant parts):


Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description

start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.

Reading this, I presume that the Replace() statement above should start replacing only from position 3 onwards. Well, it does not. What happens is this:

Replace() replaces ‘find‘ in ‘expression‘ with ‘replace‘, starting at position ‘start‘ but returns a string that does not contain the characters up to ‘start‘.

In other words, it appears that VBA first Mid()‘s the string at position 3, then starts replacing, and finally returns the shortened string. See here as well (includes workaround).


PS: if you read Help to the end, it says «If start > Len(expression) then zero-length string is returned». This implicitly confirms that if you want something from the string back using Replace() then you should definitively set start to a value below Len(Expression). And I say that if you want back everything (obviously, with the replacements done), set start to 1.

March 12, 2012

Excel oddity – Embedded UDF is not executed when workbook is opened

The Situation

I have a workbook containing a UDF, let’s call it for convenience MyUDF(), that returns a calculated value. I also have an Add-In (XLA/XLAM) that contains a function with the exact same name. This is not coincidental: the function in the workbook acts as a wrapper for the one in the Add-In.

In the workbook, on one of the sheets I have (these are just a sample):

Cell Function
[A1] =MyUDF("String")
[A2] =MyUDF("String", 0)
[A3] =MyUDF("String", 1, TRUE, FALSE, 200)

In the workbook, MyUDF has 2 parameters:

Public Function MyUDF(ByVal p1 As Variant, ParamArray p2 As Variant) As Variant

The ParamArray p2 As Variant allows me to specify any number of additional parameters to my workbook function, and transforms it into an Array of values (this is what I want). This workbook function finally calls the Add-In equivalent as per:

vResult = Application.Run("MyAddIn.MyUDF", p1, p2)

The UDF in the Add-In has a small but determining difference with the one in the workbook, and that is the number and type of parameter declaration. The function in the Add-In also has 2 parameters but these are defined as:

Public Function MyUDF(ByVal p1 As Variant, Optional ByVal p2 As Variant) As Variant

By design, the Optional ByVal p2 as Variant is processed inside the UDF as an Array of values, which is how it passed by the UDF in the workbook.

In general this works fine, and when I debug I see that control goes first to the workbook UDF and then to the Add-In UDF. So what’s the deal, isn’t Excel designed to do this? Yes, if only…

The Problem

…if only it did not show this when I opened the workbook:

Cell Function Result (sample)
[A1] =MyUDF("String") 25
[A2] =MyUDF("String", 0) 25
[A3] =MyUDF("String", 1, TRUE, FALSE, 200) #VALUE

I was surprised to see the #VALUE but I also saw it rapidly disappear after I refreshed the workbook, so for me it was just a matter of refreshing the data and blamed Excel for it also because I saw sound values in the other cells. Until a customer reported it to me as a bug and I gave it some extra question time to finally discover why it was returning #VALUE and why only for those cells where I used 3 or more parameter values.

This is what I discovered:

  • If you have a workbook containing a UDF, that you have used in your cells and that you have embedded in the VBA code, then Excel will NOT use it when you open the workbook.
  • If you have a module loaded in Excel that provides the namely equal UDF, Excel WILL use that UDF when you open the workbook

  • When you refresh the workbook/worksheets, Excel will use the UDF embedded in the workbook

The Technical Explanation

Using my examples above, in the UDF in the workbook the parameters are a variant value (p1) and a variant array of values (p2). In the UDF in the Add-In the first parameter (p1) is the same but the second parameter (p2) is also a variant value.

When the workbook’s UDF is triggered (situation #3, second image), it forwards the call to the namely equal UDF in the Add-In passing the same number of parameters which do not change, and the Add-In receives a variant value and a variant array of values.

However, if the UDF in the Add-In is called directly (by Excel when opening the workbook—situation #2, first image) then the first parameter is passed as a variant value, the second parameter is passed as a variant value instead of a variant array of values, and from the third parameter onwards they are discarded. This caused the UDF in the Add-In to fail and that was the reason for the #VALUE.

Presumably, when you open a workbook Excel first loads the worksheets and evaluates the cell formulas but it is yet unaware of any UDF in the code modules that the cells may refer to. Only at a later stage these will become active but at that point no recalculation is performed.

The Solution

What lesson have I learned? Well, it’s simple: never trust Excel.

For the rest, make sure the name for a UDF in your workbook’s code modules is unique and cannot be confused with any other [public] name provided by any other module loaded in Excel’s memory (Add-In, XLL, etc.).

In the desperate case you do want the same names and you want to use the UDF in the workbook as a wrapper for a UDF in your Add-In, make sure the parameters match exactly —OR— make sure the either call processes the input parameters correctly.


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.


September 7, 2011

Excel’s 2007 internal Fonts dialog has an intruder

Filed under: Excel,VBA — dutchgemini @ 12:11 pm
Tags: , , , , ,

I was experimenting with Excel 2007’s internal dialogs when I came across this little “intruder” of the Font dialog:

Intruder on font Dialog

Right to the Samples frame’s caption there is a small dropdown (shown open above) which manages the forecolor of the font, exactly like the main selector on the upper-right corner.

To look for yourself, type “Application.Dialogs(xlDialogFont).Show” in the immediate window of the VB-IDE.


September 6, 2011

Excel 2007 stops working using the Object Browser on Windows 7

Filed under: Excel — dutchgemini @ 3:19 pm
Tags: , , , ,

I encounter a severe crash in Excel 2007 while browsing my project’s objects in the Object Browser. This crash occurs only on my Windows 7 PCs (Vista not tested) and does NEVER occur on XP.

These are the steps to reproduce it:

  • Start Excel
  • Open the VB-IDE
  • Go to Tools | References
  • Click Browse… and add COMDLG32.OCX
  • Click Ok
  • Open the Object Browser (F2)
  • Keeping <All Libraries> search for “Fonts“; you will see that “FontsConstants” is listed from MSComDlg
  • Open the Library Selector and select MSComDlg

At that point, Excel instantly crashes (“Excel has stopped working”).

In the Event Viewer I have the following data:

Faulting application name: EXCEL.EXE, version: 12.0.6425.1000, time stamp: 0x49d64dd6
Faulting module name: OLEAUT32.dll, version: 6.1.7600.16385, time stamp: 0x4a5bdaca
Exception code: 0xc0000005
Fault offset: 0x00021e1a
Faulting process id: 0xed0
Faulting application start time: 0x01cc6c9c11c61a50
Faulting application path: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
Faulting module path: C:\Windows\system32\OLEAUT32.dll
Report Id: fcab5220-d88f-11e0-98d2-0003ffc68011

What turns me mad is that I can use the objects from MSComDlg in my code, IntelliSense works normally, and the code runs without problem.

I have deleted EXD files, re-registered components, ran cleaners, etc. without success. I hunted all possible sites and resources for a clue but nothing, nothing at all.

Does anyone have a tip?


April 21, 2011

Two Serious Flaws with Excel’s CopyFromRecordset Method

Filed under: Excel,Programming,VBA — dutchgemini @ 11:26 am
Tags: , , , , , , ,

I just bounced into 2 serious flaws using the CopyFromRecordset method for dropping an ADODB.RecordSet (based on Microsoft ActiveX Data Objects Library 2.7) on a worksheet running Excel 2007.

Flaw 1 – Dates are shifted by 1 day

The first flaw is related to a ‘adDBDate’ column containing “time-only” values.

These values, to be compatile with Excel’s Time values, include “01/01/1900” as a dummy date (Excel does not handle dates before Jan 1st, 1900). For example “01/01/1900 15:30:00”.

When the Recordset is copied to the worksheet, all dates are shifted by one day, thus “01/01/1900 15:30:00” (shown in the VBIDE Locals Window) becomes “02/01/1900 15:30:00” (I’m using the European format).

I have not yet discovered at what point Excel (or the CopyFromRecordset method) stops adding this extra day, since dates like July 16th, 2003 do not suffer from this problem.

To complete the story, the “1904” date setting is turned off.

Flaw 2 – NumberFormats on other sheets in the workbook are overruled

The second flaw is IMHO even more serious.

I noted that after using the CopyFromRecordset method, all cell (read: column) formats had changed.

In the attempt to discipline Excel’s bad behaviour to self-decide what cell format to use when dropping data, I pre-set the column’s NumberFormatLocal to “hh:mm:ss” before dropping the RecordSet. Unfortunately, this did not work as Excel forced the entire column back to “Date”. As a workaround, I now set the NumberFormatLocal to use the time string after I copy the RecordSet, and this appears to work.

Wouldn’t it that I copy the contents of the same RecordSet on another worksheet, after having applied a RecordSet Filter. And here I had a another bad surprise: the column’s format on the first sheet had “magically” reverted back to “Date”. This really bugs me.

I have tried all possible combinations of flags in Excel and on the worksheet(s), like turning events off, disabling calculations, and so on, without success.

The only solution that finally worked for me was scanning at the end of my routine all sheets where I used the CopyFromRecordset method on, and force the desired NumberFormatLocal on each column. But I still have the little voice in my head that if someone else, working with my workbook, performs a recalculation, all formats will again boom.

I presume both flaws are bugs and will verify if they also appear in other versions of Excel (2000, 2002, 2003 and 2010) as I have all versions on board.

Does anyone else have had this same experience?


Next Page »

Blog at WordPress.com.