Dutch Gemini's Weblog Pages

February 28, 2020

Obtain a Union-Exclusive Range from Excel via VBA

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

In a recent project I’m involved in, I was asked to develop a function that would return a range containing all the cells of 2 ranges with the exclusion of the cells they had in common. In other words, the requirement was to obtain a Union between 2 ranges without the Intersection of the 2 ranges.

Excel VBA does not allow subtracting ranges, e.g. Set rngR3 = rngR1 - rngR2, so I had to develop a function doing this for me. The effort has resulted in a function I named UnionExclusive():

Function UnionExclusive(ByRef r1 As Excel.Range, r2 As Excel.Range) As Excel.Range
'
' This function returns the range of cells that is the Union of both ranges with the
' exclusion of the ranges or cells that they have in common.
'
On Error Resume Next
    Dim rngWholeArea      As Excel.Range
    Dim rngIndividualArea As Excel.Range
    Dim rngIntersection   As Excel.Range
    Dim rngIntersectArea  As Excel.Range
    Dim rngUnion          As Excel.Range
    Dim rngSection        As Excel.Range
    Dim rngResultingRange As Excel.Range
    Dim lngWholeTop       As Long
    Dim lngWholeLeft      As Long
    Dim lngWholeBottom    As Long
    Dim lngWholeRight     As Long
    Dim arrIntersection   As Variant
    Dim arrWholeArea      As Variant
'
' Must be on same sheet, return only first range.
'
    If Not r1.Parent Is r2.Parent Then Set UnionExclusive = r1: Exit Function
'
' No overlapping cells, return the union.
'
    If Application.Intersect(r1, r2) Is Nothing Then Set UnionExclusive = Application.Union(r1, r2): Exit Function
'
' Range to subtract must be contiguous. If the second range has multiple areas, loop through all the individual areas.
'
    If (r2.Areas.Count > 1) _
    Then
        Set rngResultingRange = r1
        For Each rngIndividualArea In r2.Areas
            Set rngResultingRange = UnionExclusive(rngResultingRange, rngIndividualArea)
        Next rngIndividualArea
        Set UnionExclusive = rngResultingRange
        Exit Function
    End If
'
' Get the overall size of the Union() since Rows/Columns "Count" is based on the first area only.
'
    Set rngUnion = Application.Union(r1, r2)
    For Each rngIndividualArea In rngUnion.Areas
        If (lngWholeTop = 0) Then lngWholeTop = rngIndividualArea.Row Else lngWholeTop = Application.WorksheetFunction.Min(lngWholeTop, rngIndividualArea.Row)
        If (lngWholeLeft = 0) Then lngWholeLeft = rngIndividualArea.Column Else lngWholeLeft = Application.WorksheetFunction.Min(lngWholeLeft, rngIndividualArea.Column)
        If (lngWholeBottom = 0) Then lngWholeBottom = (rngIndividualArea.Row + rngIndividualArea.Rows.Count - 1) Else lngWholeBottom = Application.WorksheetFunction.Max(lngWholeBottom, (rngIndividualArea.Row + rngIndividualArea.Rows.Count - 1))
        If (lngWholeRight = 0) Then lngWholeRight = (rngIndividualArea.Column + rngIndividualArea.Columns.Count - 1) Else lngWholeRight = Application.WorksheetFunction.Max(lngWholeRight, (rngIndividualArea.Column + rngIndividualArea.Columns.Count - 1))
    Next rngIndividualArea
    arrWholeArea = Array(lngWholeTop, lngWholeLeft, lngWholeBottom, lngWholeRight)
'
' Get the entire area covered by the various areas.
'
    Set rngWholeArea = rngUnion.Parent.Range(rngUnion.Parent.Cells(lngWholeTop, lngWholeLeft), rngUnion.Parent.Cells(lngWholeBottom, lngWholeRight))
'
' Get intersection, this is or are the area(s) to remove.
'
    Set rngIntersection = Application.Intersect(r1, r2)
    For Each rngIntersectArea In rngIntersection.Areas
        arrIntersection = Array(rngIntersectArea.Row, _
                                rngIntersectArea.Column, _
                                rngIntersectArea.Row + rngIntersectArea.Rows.Count - 1, _
                                rngIntersectArea.Column + rngIntersectArea.Columns.Count - 1)
'
' Get the difference. This is the whole area above, left, below and right of the intersection.
' Identify if there is anything above the intersection.
'
        Set rngSection = Nothing
        If (arrWholeArea(0) < arrIntersection(0)) _
        Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrWholeArea(1)), _
                                                                              rngWholeArea.Parent.Cells(arrIntersection(0) - 1, arrWholeArea(3))), _
                                                    rngUnion)
        If Not rngSection Is Nothing _
        Then
            If rngResultingRange Is Nothing _
            Then Set rngResultingRange = rngSection _
            Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
        End If
'
' Identify if there is anything left of the intersection.
'
        Set rngSection = Nothing
        If arrWholeArea(1) > arrIntersection(1) _ Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrWholeArea(1)), _ rngWholeArea.Parent.Cells(arrWholeArea(2), arrIntersection(1) - 1)), _ rngUnion) If Not rngSection Is Nothing _ Then If rngResultingRange Is Nothing _ Then Set rngResultingRange = rngSection _ Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection) End If ' ' Identify if there is anything right of the intersection. ' Set rngSection = Nothing If arrWholeArea(3) &amp;amp;gt; arrIntersection(3) _
        Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrIntersection(3) + 1), _
                                                                              rngWholeArea.Parent.Cells(arrWholeArea(2), arrWholeArea(3))), _
                                                    rngUnion)
        If Not rngSection Is Nothing _
        Then
            If rngResultingRange Is Nothing _
            Then Set rngResultingRange = rngSection _
            Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
        End If
'
' Identify if there is anything below the intersection.
'
        Set rngSection = Nothing
        If arrWholeArea(2) > arrIntersection(2) _
        Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrIntersection(2) + 1, arrWholeArea(1)), _
                                                                              rngWholeArea.Parent.Cells(arrWholeArea(2), arrWholeArea(3))), _
                                                    rngUnion)
        If Not rngSection Is Nothing _
        Then
            If rngResultingRange Is Nothing _
            Then Set rngResultingRange = rngSection _
            Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
        End If
        Set rngUnion = rngResultingRange
        Set rngResultingRange = Nothing
    Next rngIntersectArea
'
' Return the result. This is the area "around" the intersection.
'
    Set UnionExclusive = rngUnion
End Function

The function takes 2 parameters, the first is the range to start with, the second is the range that you want to union-exclusive with the first. The following code snippet shows the effect using colour coding:

Sub Test()
Dim r As Excel.Range

ActiveSheet.Cells.Clear

Set r = UnionExclusive([A3:C11], [B2:B16])
r.Interior.ColorIndex = 6 ' Yellow

End Sub

The function uses only Application.Union() and Application.Intersect() and avoids at all time looping on cells within a range or area of a range, as doing this terribly affects performance when the ranges used are (very) large (e.g. whole columns).

The trick consists in looking at the intersection and the whole area occupied by the union of the ranges, i.e., the lowest and the highest row and column, while working with the bare union of both ranges. The whole area is needed for including any cell the union covers, the original union is needed for including only cells actually part of the union.

The function will look at any area above, left, right and below the intersection. From any of the mentioned areas, the function only returns the intersection and finally unions them for obtaining the range to return.

Let’s take the following example: you want to union-exclusive [A3:C10] with [B1:B12]. I have highlighted the cells that represent (top, left) and (bottom, right) of the whole area covered by the union.

  • The union consists of a range made out of 2 areas [A3:C10,B1:B12]
  • The intersection is [B3:B10]
  • The whole area occupied by the union is [A1:C12] thus from row 1 to row 12 and column 1 to column 3
  • The area above the intersection is Application.Intersect([A1:C2],[B1:B2]) = [B1:B2]
  • The area left of the intersection is Application.Intersect([A1:A12],[A3:A10]) = [A3:A10]
  • The area right of the intersection is Application.Intersect([C1:C12],[C3:C10]) = [C3:C10]
  • The area below the intersection is Application.Intersect([A11:C12],[B11:B12]) = [B11:B12]
  • The resulting range is Application.Union([B1:B2],[A3:A10],[C3:C10],[B11:B12]) with 4 individual areas

Both function parameters allows for ranges with multiple areas, and the function also captures multiple intersections between areas of one range and the individual areas of the other range.

When the second range is indeed formed by multiple areas then the resulting range will be the sequential application of each area of the second range:

Sub Test()
Dim r As Excel.Range

ActiveSheet.Cells.Clear

Set r = UnionExclusive([F2:J14], [G2:G6,G9:G11,I2:I11,J4:J5,F12:J13]) ' 5 areas in one run
r.Interior.ColorIndex = 5 ' Blue

is the same as:

Sub Test()
Dim r As Excel.Range

ActiveSheet.Cells.Clear

Set r = UnionExclusive([F17:J29], [G17:G21]) ' First area
Set r = UnionExclusive(r, [G24:G26])         ' Second area
Set r = UnionExclusive(r, [I17:I26])         ' Third area
Set r = UnionExclusive(r, [J19:J20])         ' Fourth area
Set r = UnionExclusive(r, [F27:J28])         ' Fifth area
r.Interior.ColorIndex = 7 ' Magenta

End Sub

Applying union-exclusion sequentially has one drawback: a subsequent area may restore cells that were excluded by a previous operation. For instance the following code creates a cross because cells B3 and B4 are restored by the second area:

Sub Test()
Dim r As Excel.Range

ActiveSheet.Cells.Clear

Set r = UnionExclusive([A1:C6], [B1:B6,A3:C4])
r.Interior.ColorIndex = 4 ' Green

End Sub

If time allows then I will try to resolve this issue and return a range that is the true union-exclusion of both areas.

Enjoy.

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

February 26, 2013

Show a specific page of a CHM help file from your Office application with VBA

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

Update for users of Office 2010: starting this version things have changed slightly, and the information provided here does not work anymore.  I have added the necessary modifications at the bottom of this article.

Users of a pre-2010 version of Office may benefit, too.


This thread is not about creating a help file for your VBA application – there are many other pages on the Internet that tell or sell better solutions than you will find here – but about accessing pages of a CHM via VBA code.

As most programmers know, a strong added value of any application developed is on-line help. In the application I develop I had the need to provide some on-line help on the format that one could use for customising the display and printout of dates.

Office (XP/2003/2007) VBA provides a method called ‘Application.Help’. This method has 2 parameters:

  1. (string) The name of the help file, either HLP or CHM
  2. (long) The Help Context ID

Thanks to Ron De Bruin – a dutch MVP making tons of great tips available to the public  – I obtained the list of Help Context ID’s that give access to Excel’s Help pages.

However, I could not use it because what I was looking for was not in Excel’s Help, but is provided by VBA in a file called ‘Visual Basic Language Reference’ and named ‘VBLR6.CHM’. Where is this file? In a standard installation of Office, it is located in the folder called ‘C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033’. In addition, for VBLR6.CHM there is no such list as the Help Context IDs, or I was not able to find it which is far more likely.

I was finally able to resolve the problem because the above mentioned method is capable to open help pages from URL as well, that is, the CHM does not have to be on a local disk.

I am not an expert and have only seen someone else create CHM’s (we outsource this activity). But since a CHM is a compiled help file, I thought it had to have some kind of file or menu structure embedded inside (how can it otherwise organise and create cross-references?) and that it should be possible, knowing the ‘key’, to target a specific page in the CHM file structure and display it. How?

The answer came from investigating the page I was displaying. I have summarised what I did in the following steps.

Step 1 – from the VB-IDE I located the help page related to the information I was looking for, in my example ‘User Defined Formats’ and opened the related help page:

Looking for help in VBIDE

Visual Basic Help

Step 2 – I right clicked on the page and selected ‘Properties‘. The key we need is in the highlighted box:

Page Properties

Step 3 – I selected all the text in the Address (URL) field and copied it to the clipboard.

Address to this page

In my case, the full URL sounds:

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove the first part being ‘mk:@MSITStore:‘ leaving us with:

C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. Note that I omitted the Help Context ID here (which is optional, btw).

Application.Help "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm"

There is still one problem to tackle, and that is the actual path to the file. Depending on your language and installation of Windows and on your version of Office, this path can change. For instance in french we would have (I have highlighted the differences in bold):

C:\Programmes\Fichiers Communs\Microsoft Shared\VBA\VBA6\1036\vblr6.chm::/html/vafmtStringFormats.htm

VBA again is able to help us, offering us these 2 functions:

  • Environ() retrieving settings from the OS, and
  • Application.LanguageSettings.LanguageID() returning the ID of the language.

With this final information at hand we create this chunk of code and VBA is going to show us exactly the page we wanted:

Application.Help VBA.Environ$("commonprogramfiles") & _
                 "\Microsoft Shared\VBA\VBA6\" & _
                 Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                 "\vblr6.chm::/html/vafmtUserDefinedDateFormats.htm"

That’s it. Hook it to a command button or menu and you have your context sensitive help.

Should work with any CHM file (no guarantee though, my name is not Bill G.)


Instructions for Office 2010 users

Office 2010 uses the new VBA7 Visual Basic for Application engine, and I have seen that this one does not really cope correctly with the Application.Help instruction. Instead of opening the given file, I always end up with standard Office Help. But there is a solution that also works on pre-2010 versions of Office, based on a Win32API function.

First we need to load the Win32API function in our project. The declaration is for a 32-bit version of Office

Public Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Any) As Long
Public Const HH_DISPLAY_TOPIC = &H0

The important part in the definition is the last parameter declaration: it use ‘As Any‘ instead of ‘As Long’ what you may find in other posts, which will allow us providing help ID’s as Longs and as Strings, the latter we will use here. The only constant declared (there are others) is used when calling this function to display the desired topic from our help file.

Finally we need to call this function. The next example uses again the instructions explained above for Office XP/2003/2007, but with the necessary differentiation for handling 2010’s new VBA7 engine while supporting legacy VBA6 engine.

The page reference is slightly different from the one for Office XP/2003/2007. You must take the path to the desired page working the HTML structure from the root up. Similar to above, the full URL for 2010 is (note the different folder VBA7 instead of VBA6):

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA7\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove everything up to the double colon including the first backslash:

html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. The next statements include localisation support for retrieving the correct language file.

Dim sCHMHelpFile As String, sPageReference As String
#If VBA7 Then
    ' Office 2010: the help file is in a folder named 'VBA7'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA7</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#Else
    ' Office pre-2010: the help file is in a folder named 'VBA6'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA6</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#End If
sPageReference = "html/vafmtStringFormats.htm"
Call HtmlHelp(0, sCHMHelpFile, HH_DISPLAY_TOPIC, sPageReference)

Have fun.

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.

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):

Syntax

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).

Dutch

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.

Dutch

January 4, 2012

Install (and Uninstall) an Excel Add-In with InnoSetup

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 9:00 am
Tags: , , , ,

I have written a script for InnoSetup 5.3, now updated to 5.4.2 (and soon to 5.4.3 made available during the 2011 Xmas holidays), that allows me to install and uninstall an Excel Add-In (XLA or XLAM). Before InnoSetup I was using a VBS script to perform this same task.

This InnoSetup script removes old copies, updates Excel, activates or disables the Add-In, clears the Registry, and more. It also checks and optionally allows setting the “Trust Access To Visual Basic Project” flag under the Macro Security settings (in the Trust Center if on Excel 2007 and 2010).

The nice thing is that now I also have an entry in the Control Panel with which I can uninstall the Add-In (gives a much more professional look).

I have dissected the script I use and have commented the various parts. On the last page I have put the biggest part. If you assemble them you end up with quite a big script , somewhere around 1700 lines of Pascal Script (roughly 1500 lines only for the Code section), considering that only a few files are installed but the script takes care of a large number of issues. In addition, over the years I developed a tendency to comment each instruction carefully, helps when you need to review the code after long periods of times doing something really else.

Here’s the full story. I’ve divided it in pages for easier reading (all links should work now):

Page 1 | Page 2 | Page 3 | Page 4 | Page 5

Small update pending: I have made some changes to the script parts for dealing better with 64-bit versions of Windows and Excel. Expect these changes to be posted within a few days. Thanks for waiting.

Dutch.

Next Page »

Blog at WordPress.com.

Create your own website, free on WordPress.com.Create your own website.
Start free