xldynamic Returns







I have a website http://www.xldynamic.com/ which tries to provide some useful Excel information, different to the many other Exel websites. This site includes what is probably the most useful SUMPRODUCT discussion on the web.

Around about August 2008, my host provider, 123-reg.co.uk, started to tear down my site on or around the 20th of every month, due to exceeding my bandwidth limit (I admit, until then I had not been aware I had a limit). I was confused as to why this should happen, my site was popular, but was never one of the most frequented sites, and I had no new material to tempt anyone. Unfortunately, seeking clarification from 123-reg was a frustrating exercise, I was just bounced back with cut-and-pasted responses that did little to address my actual question.

So, when my renewal came up, I was looking to be off. I found a new provider, and wanted to move my site across. Again, 123-reg were just as unhelpful in transferring my domain as they had been in helping me to understand the problems, so it has taken a while.

All of this is a long preamble to saying my site is back in operation, and it looks as it did before. It is my hope and intention to give it a refresh when the time allows, make it more dynamic, and to add some new content, but for now I am just happy for it to be back up.

One thing I have managed to do is to update the SUMPRODUCT page. This page has been split into two parts, the first part deals with the mechanics of SUMPRODUCT, how it works, and considerations of this, whilst the second part has all of the examples. In addition, the first part has 3 new sections:

-          Conditional Counting And Summing in VBA – using SUMPRODUCT
in VBA

-          SUMPRODUCT and Excel 2007 – the implications of

-          Performance considerations – of using SUMPRODUCT

This page is going to get another overhaul soon, but in the meantime I hope that these changes are useful.

I would just add that my new host provider orcsWeb, has been immeasurably better in providing technical support. So far, it seems to be a great service.


Shreds And Patches

“Man is physically as well as metaphysically a thing of shreds and patches, borrowed unequally from good and bad ancestors, and a misfit from the start” (Ralph Waldo Emerson)


A number of us British Excel’ers (I nearly said English, Roger would never forgive me) have been holding Excel conferences for a few years. It had been a paid event, but there was no such event in 2008 as the 2007 event was not well enough attended. In 2009, we decided to try something different, and make the event free! Naturally, interest was high, and at the conference (held at Microsoft’s Victoria Street offices) we had over 100 delegates each day, and had to wait-list another 100+. Because of the fantastic interest, and the general good reception to the first event, we have decided to hold a second conference in October, offering first choice to those wait-listed at the March event.

Conferences are great opportunities for like minded saddos like us to get together and talk about the things our partners just pretend to be interested in. Microsoft have very generously provided a room with all of the presentation facilities needed, and provide coffee and lunch on both days. This support from Microsoft is what makes it possible to hold a free event, conference facilities are very expensive, and to have a central London location is fantastic.Again, many thanks to Microsoft, and the UK MVP leads, Vicki Collins and Akim Boukhelif, for their help in setting this all up.

The conference is free, but of course the cost of accommodation and meals, outside of the lunch and coffees provided during the event, are the delegate’s responsibility. This applies to the presenters as well, we are not subsidised by Microsoft or anyone else (of course, if any corporation wishes to do so …) , we bear our own costs. We like these events; we like the socialising and meeting Excel colleagues that we may know virtually but have not yet met in person, and of course, talking Excel over a beer in the evening is no bad thing.

As socialising, or to use that hideous term, networking, is an important facet of these event, we will be organising a group meal (again at own expense) on the Wednesday evening so that we can carry on great conversations that develop throughout these days.

The details of the event are:

Microsoft London (Cardinal Place)

100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100


The agenda is below

Agenda Wednesday 7th October 2009

Time Event Speaker
9:15am – 9:45am Registration & Coffee
9:45am – 10:00am Welcome and Housekeeping Nick Hodge
10:00am – 10:45am Working Smart with the Excel Grid Simon Murphy
10:45am – 11:45am Intermediate Functions Bob Phillips
11:45am – 12:15pm Coffee
12:15pm – 1:15pm What’s in a Name? Charles Williams
1:15pm – 2:00pm Lunch
2:00pm – 2:45pm Excel as an Enterprise BI Tool Carl Mackinder
2:45pm – 3:30pm Tips and Tricks in Charting Andy Pope
3:30pm – 4:00pm Tea & Coffee
4:00pm – 5:00pm Pivot Tables Roger Govier
5:00pm – 5:30pm Pre- submitted Q&A All
5:30pm – Late Drinks, Dinner and Socialising All (Inc Delegates)


Agenda Thursday 8th October 2009

Time Event Speaker
8:30am – 9:00am Registration & Coffee  
9:00am – 9:15am Welcome and Housekeeping Nick Hodge
9:15am – 10:00am Data Exchange Nick Hodge
10:00am – 11:00am VBA – It’s in Everyone’s Reach Simon Murphy
11:00am – 11:30am Coffee
11:30am – 12:45pm Information – Visualising Data In Excel Andy Pope & Bob Phillips
12:45pm – 1:30pm Lunch
1:30pm – 2:15pm Arrays and Array Functions Bob Phillips
2:15pm – 3:15pm Speeding up Excel Charles Williams
3:15pm – 3:45pm Tea & Coffee
3:45pm – 4:30pm Pre-submitted Q&A All
4:30pm Close



I hope to see you there.

Hand-holding for Dummies




I am an inveterate builder of addins for Excel. They are so flexible, so easy to build, but they have a potential deployment issue. Yes I know that for most of you installing an addin is trivial, but I create them for corporations as well, and they either

a) cannot afford to go to every desktop and install addins manually, or

b) cannot assume that all of their staff who might need the addin can install it themselves (even if they have the necessary permissions) .

Of course, the solution is well known, build a self-installing executable. There are many good products around, such as Wise, Setup Factory. The downside of these is that there is a cost, a healthy cost, and as well as being an inveterate addin builder, I am an inveterate cheapskate. We also have the Windows Installer (is this free?), but I have only ever used it to install a product, I have never built installers with this.

Which brings me to the point of this post. I have been using a remarkable free installer for some time now, Inno Setup. Jordan Russell’s tool is superb, to quote it’s own PR … Inno Setup is a free installer for Windows programs. First introduced in 1997, Inno Setup today rivals and even surpasses many commercial installers in feature set and stability… I don’t think there is anything to argue with there. It is even free for commercial use.

The installation details are scripted in an .iss file, basically a batch instruction file, which is divided into various sections where you add the various installation specific details. The sections include:

[Setup] defines the basic setup details, such as the default directory name for the application (it has some constants, such as pf for Program Files), license file if there is one, the images to use in the installer and so on

[Messages] runtime messages can be defined here

[CustomMessages] custom messages can be defined here, and used in the code section (see later)

[Files] a simple list of all of the files to be installed, and where to store them

[Registry] any registry key updates that the installer will run

[Code] where you can add installation specific code. This code has to be written in Pascal, the product is written in Delphi after all. Here you can use the messages mentioned above, such as a message to shutdown all versions of Excel before continuing. It could test if Excel is running

            if CurPageID = wpWelcome then




                                    while XLIsRunning do


                                                            mpRet := MsgBox(‘Please close Excel before continuing’, mbError, mb_RETRYCANCEL);

                                                            if mpRet = IDCancel then Abort;




function XLIsRunning(): Boolean;


// Note – this will not detect invisible instances of xl running (but that should be unlikely)



            Result := False;

            if FindWindowByClassName(‘XLMAIN’) <> 0 then Result := True;


Most interestingly for me, it can be used to automatically update the registry so that the next time that Excel starts the addins are already added to the addins list, and thus will build their menus, initiate and so on when Excel starts. If you want your addin in the addins list automatically, you have to update the registry (when you install an addin manually, the registry is updated the next time that you close Excel, that is why it is there next time).

In Excel addin details are stored in HKCU\software\Microsoft\Office\n.n\Excel\Options, where n.n is the version number. Each addin is assigned the next available key OPEN, OPEN1, OPEN2 etc. These are simple string value keys with the full path of the addin.

Of course, life is never that simple. If your addin could be deployed across many Excel versions, you have to cater for them all. The key given above is the key location for Excel 2003 (version 11.0),  is also true for Excel 2002 (version 10.0), Excel 2000 (version (9.0), and even Excel 2007 (version 12.0), but is not true Excel 97 (version  8.0), this key is HKCU\Software\Microsoft\Office\8.0\Excel\Microsoft Excel. Thus any installation code needs to account for this.

My usage of the tool just scratches the surface of its full capabilities, but even so, it has become indispensable to me. I look forward to seeing how it fares when I start deploying .Net solutions, I am very confident.

As I said, another great tool which I use for all of my addins. There is even an active support forum.


Because it is such a good tool, there is the usual spate of added-value addons.

Bjørnar Henden has created a GUI front-end for creating and editing Inno Setup Scripts, ISTool. I have used this, and it is good, but personally I am not GUI mad, so I stick to the batch file style. After all, most are copies and then a few updates.

Another GUI front-end for creating and editing Inno Setup Scripts is Jonny Kwekkeboom’s ScriptMaker. I have not tried this myself.

A new one that I haven’t tried, but looks very interesting, is a tool that adds customizable skin support to Inno Setup installations, Codejock Software’s ISSkin.

I did also find a decompiler somewhere, that saved me once when I lost my source file, but it is not listed on the Inno site, so I will have to look again.


Time has come today




I saw a question in the newsgroups today where a guy was asking how he could get more than 10,000 hours in a cell, adding a hour number larger that 10,000 to another time number the
formula did not give a correct answer. 

Some of the answers suggested that Excel cannot hold a value of 10,000 or more hours. This is actually incorrect. It is true that it is only possible to enter a single value of 9,999:59:59 into a cell, but it can be tricked into holding more.

For example, enter 5000:00 in cell A1 and the same in B1, and a formula of =A1+B1 in another cell will show 10000:00.

It is even possible to enter it in a cell using a formula such as


and we get an answer of 11554:00.

Of course, the cell does have to be entered as [h]:mm to see the result as more than 24 hours.


Names Should Be Seen







Doing some work on an Excel spreadsheet this morning reminded me that there are some great products that have been developed that enormously enhance the ease of use, flexibility, and general usefulness  of Excel. Whilst this blog is not really about promoting Excel products, there is one product that I believe stands head and shoulders above any other out there. I am not a great fan of installing Excel addins, they usually have 200 functions of which I only want 1 or 2, but I have installed this addin and I don’t believe there is ever a day that I do not use it. Because of this, and because the price is spot on (it is free), I am going to shout the praises of Jan Karel Pieterse’s NameManager addin.

This tool has been around for a number of years, and has been indispensable if you use Excel names extensively (which I do). There is a debate to be had about whether we should use names, some swear by them, some swear at them, but that is for another day.

Using the names dialog in pre 2007 versions of Excel was painful. I am of course referring to the Insert>Name>Define… dialog which threw up this incredibly helpful beast

There were a few other concessions to usability, Debra Dalgleish is highlighting the Create Names From Excel Labels facility on her blog today, but generally it was hard work. That is, until JKPs addin came along. Suddenly, it was possible to see all of your names in a sensibly structured dialog, there were filtering options, you could evaluate names, see if they were being used, and much more. Compare this dialog

to that previous dialog, look at the richness of facilities, the options, but most of all the sensible presentation. When managing names, it is imperative in my view to see as much information as possible, limited by my choice, not the limitations of the tool.

Of course, MS have revamped Excel, and in Excel 2007, they introduced their own version of Name Manager. With the experience of running the old dialog for many years; the example of better version to draw upon (JKPs addin); and the fact that they can tap into the heart of Excel, MS were bound to produce the definitive Name Manager. Right? Well, not quite. This is an example of the dialog

It is undoubtedly better than MS’ previous attempt. Seeing the names in a resizable dialog, with the Refersto Value and the scope is good, but it still falls far short of JKPs NameManager. It is cleaner than JKPs NameManager, but that is because it lacks so much. There is no option to evaluate a name, not all names resolve to a single value, which is incredibly useful; no option to highlight where names are used; no capability to redefine the scope of a name (if you try in the Edit dialog, it tells you that the scope cannot be changed – why?); changing a name’s name does not interact with VBA as NameManager does; but worst of all, it seems totally oblivious to hidden names. (BTW, you can add comments to names in Excel 2007. I cannot see where they appear, so fail to see their usefulness. Does anyone think this is a good addition that they will use?).

All in all, the 2007 Name Manger is a big disappointment to me, and JKP’s NameManger cannot be retired just yet. If you use names a lot, do yourself a favour, rush out and buy a copy of JKPs NameManger today. You CAN afford it, it is available here.

Perhaps JKP should rename it to ‘The Real NameManager’.


As Constant As The Wind

I had an odd problem with VBA today.

I used a table driven menu builder (don’t we all), and I define the column numbers of that table in an enumerated list.  This is that list

Private Enum CB_COLUMNS
    CB_LEVEL_COL = 1
    CB_TAG_COL = 4
    CB_TYPE_COL = 12
    CB_DEBUG_COL = 13
End Enum

I was making some changes to the application today, and I suddenly got an error where the values in this list were being used, an error suggesting that a constant expression was required (sic!).

Very odd. How did I fix it? I changed the enum scope from Private to Public, and then my application compiled fine. I set it back to Private and it still compiled fine, and is working okay again.

Anyone else ever seen this?

Clear As Mud




A question on one of the forums recently asked about hiding sheets in a workbook to be posted as an example, but where that workbook contained sensitive information. As I replied, hiding the sheets doesn’t really hide the data so he would be just as exposed.

Jimmy Pena has a a great application for generating random data, but sometimes it is just better to scramble the data that you already have. I suggested to the poster that they should really be scrambling the data, and further suggested that it should not be too difficult to write some code to do so.

That got me thinking as to how to do it, in a generic manner. It should cater for names, ids, amounts, currencies and so on.

A Simple Scrarmbling App

My first thought was to insert a row at the top of the target sheet, and add an entry for all columns to be obfuscated. So, for instance, if it had ‘Name’ at the top, each entry in that column would be changed to Name #1, Name #2 etc. If it had Number<7>, you would generate a random number up to 9,999,999. If it had Decimal<5,2> you would generate numbers up 99,999.99, and so on. If the value in row 1 was blank, that column gets ignored.

So you would insert a row with values such as ‘Name’, ‘Number<7>’, and so on.

The code for this was relatively simple to write, just a loop through each column and change the values. To ensure that all like values get changed to the same value, I did a Replace on the whole column once a value to be changed was found. In other words, if Bob appeared 100 times in the column, each instance of Bob would be changed to the same value. To ensure that I didn’t then go and change the second, changed, instance of Bob to some new value, I pre-pended the new value with ‘Obfuscate’, which I stripped off with a Replace at the end.

The main loop was like this

            mpDataType = .Cells(1, j).Value

            mpNextItem = 0


            If mpDataType <> “” Then

                mpLastRow = .Cells(.Rows.Count, j).End(xlUp).Row


                For i = 2 To mpLastRow

                    If Not .Cells(i, j).Value Like “Obfuscated*” Then


                        mpNextItem = mpNextItem + 1

                        Select Case True


                            Case mpDataType Like “Number<*”

                                ‘number processing


                            Case mpDataType Like “Decimal<*”

                                ‘decimal processing


                            Case mpDataType Like “Currency<*”

                               ‘currency processing


                            Case Else                                

                                .Columns(j).Replace What:=.Cells(i, j).Value, _

                                                    Replacement:=”Obfuscated” & mpDataType & ” #” & mpNextItem, _

                                                    LookAt:=xlWhole, _


                        End Select

                    End If

                Next i


            .UsedRange.Replace What:=”Obfuscated”, _

                           Replacement:=””, _

                           LookAt:=xlPart, _



So far, so good, but I could see one major problem. If there is a formula that refers to data somewhere else on the spreadsheet, that table needs to be obfuscated too, but in a smart way. For instance, say that there is a lookup table of names Bob, Simon and Alex. If there is a formula somewhere of =VLOOKUP(A20,lookup_table,2,FALSE), and A20 is one of those values in the table, then that value in the lookup table should change to the same value that A20 switches too. Unfortunately, it isn’t only VLOOKUP, it is LOOKUP, HLOOKUP, COUNTIF, and so on. Tough!

It is actually worse if the values are used elsewhere a simple cell reference or by a code event update, there is no way in my code to recognise that.


In the end, I decided to avoid this route, far too difficult, and I opted to save all the before values, and all of after values in separate arrays, and after having removed the ‘Obfuscated’ tag I went through each sheet and checked if any of the before values still remained, if so I replaced them.

A bit brute force, but it seems to work okay.

The data is first changed like so


                            Case Else

                                mpIdxChange = mpIdxChange + 1

                                mpBefores(mpIdxChange) = .Cells(i, j).Value

                                mpAfters(mpIdxChange) = mpDataType & ” #” & mpNextItem

                                If mpIdxChange Mod 1000 = 0 Then


                                    mpSizeArray = UBound(mpBefores) + 1000

                                    ReDim Preserve mpBefores(1 To mpSizeArray)

                                    ReDim Preserve mpAfters(1 To mpSizeArray)

                                End If


                                .Columns(j).Replace What:=.Cells(i, j).Value, _

                                                    Replacement:=”Obfuscated” & mpAfters(mpIdxChange), _

                                                    LookAt:=xlWhole, _


 And finally ‘corrected’ like so


        ‘now make the changes for names, ids, etc.

        ReDim Preserve mpBefores(1 To mpIdxChange)

        ReDim Preserve mpAfters(1 To mpIdxChange)


        ‘first remove the tag

        .UsedRange.Replace What:=”Obfuscated”, _

                       Replacement:=””, _

                       LookAt:=xlPart, _



        For Each mpWS In ActiveWorkbook.Worksheets


            ‘then update any associated values

            For i = 1 To mpIdxChange


                mpWS.UsedRange.Replace What:=mpBefores(i), _

                                       Replacement:=mpAfters(i), _

                                       LookAt:=xlWhole, _


            Next i

        Next mpWS



Of course, it can be taken a lot further, adding further sophistication. For example, it doesn’t explicitly cater for an Excel spreadsheet that is constructed as a pseudo-database, separate, linked tables. And it probably needs a decent progress bar  as it could take some time on large data.

An addin with the code and an example file can be downloaded via the RSS feed. The addin adds an item to the Data menu.


What’s My Type?

I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I try to use it in anger, it breaks on me. As such, I tend to avoid it and create classes.

Earlier this week, it occurred to me that there was a case in which I could implement a Type, an elegant solution, with no chance of it breaking on me.

We all know that when your masterpiece sets application variables, such as calculation mode, in its processing, they should be reset at the end to whatever state the user originally had them. The normal way to do this is to save them in a bunch of variables at the start of the process, and restore them at the end.

My solution is very similar, but the benefits of Type add an elegance that I don’t feel the variables give it.

Here is that solution.


Public Type ApplicationValues

    ScreenUpdating As Boolean

    Calculation As XlCalculation

End Type


Private AppValues As ApplicationValues


Public Function ObfuscateData()


    With Application


        AppValues.ScreenUpdating = .ScreenUpdating

        AppValues.Calculation = .Calculation


        .ScreenUpdating = False

        .Calculation = xlCalculationManual

    End With


    On Error GoTo func_error


… main code



    With Application


        .ScreenUpdating = AppValues.ScreenUpdating

        .Calculation = AppValues.Calculation

    End With



    ‘your error handler

    Resume func_exit

End Function


I think this is a neat little approach. Furthermore, it is simple and extendible, what more can we ask?

This may be obvious to you, it may be the sort of thing that you do all of the time, but it made me smile after I had implemented it. So much so that it is now a code snippet in my MZ-Tools, implementable at the drop (click) of a button.


Nostalgia IS What It Used To Be

Excel 2007 has had much comment since its introduction, most of which has been centred around the ribbon, is it a piece of inspired insight by MS, or a blunder of enormous proportions? This post will not concern itself over that issue directly, but will take a look at how some have addressed the introduction of the ribbon, seeing product opportunity. I am referring of course to the advent of various applications that provide the old 2003 style menus within Excel.


Over the next few weeks I am going to look at a number of these applications, cover their main functionality, and say what I think of them. I have to start by declaring a prejudice against such aplications, as I believe that if you want classic menus, why not use classic Excel? But of course, some may want the 1M+ rows, I don’t but some may, and yet still crave the old style menus, so I guess these products have a place. And of course, it might help the transition to Excel 2007. Personally I think it is like smoking, either give up or carry on, trying to do it by stealth is ultimately pointless.


The first Classic menu that I looked at was UBitMenu, supplied by Ubit Schweiz. This can be found at http://www.ubit.ch/software/ubitmenu-languages/



The price for UBitmenu is reasonable, free for private use, and € 10 base fee +  € 0.65 per user for commercial use (+ VAT if applicable).



UBitMenu can be installed with standard user rights on any Windows® Office 2007 / Office 2010 environment. The suppliers suggest that you may need to save the file to a trusted location on your hard disk before you run the setup. I had no need to do so.


The setup application installs UBitMenu AddIn-files for Excel, Word and PowerPoint. I had expected different menus for Excel and Word and so  on, but oddly on my system, the Word menu was the same Excel menu. I don’t know if I did something incorrectly, not being a big Word or PowerPoint user I was not too concerned with it. All changes are registered for uninstallation.

[Update – it has been pointed out to me that they are not the same, they are just very similar, which was a deliberate choice. My only excuse is that I saw things in Word that are not on my Word 2003 toolbars, such as the charting icon, but as Ubit Schweiz seem to have done this as something that would be value added for most users, and it is intended, I will stop digging and accept my fate].


UBitMenu is a simple Excel 2007 addin, with the menu items defined in the CustomUI XML. You can view the XML using CustomUI, and can see that it just invokes the builtin functions within Excel 2007.


When installed, there is a new ribbon tab added called Menu (why not UBitMenu? [Update – I am told this is to restrict space encroachement in restricted situations, such as a laptop, which given the space grabbing proclivity of the Ribbon, I guess I should applaud this]) which looks like the classic Excel 2003 menu, Standard and Formatting toolbars, with the Drawing toolbar thrown in for good measure.


One aspect of the installation is unusual is that it installed into my XLSTART directory, it did not give me the option to direct its placement.



The tool is very simple to use, works well, and does exactly what the suppliers suggest. It cannot be customised as Excel 2003 commandbars can, it is simply a means to use a familiar format within an unfamiliar environment, a transition tool until one is comfortable with the ribbon.



The suppliers suggest that you uninstall using the ‘Software’ applet in the Windows Control Panel, although as it is just an addin, you can uninstall it using the Addins dialog, and then delete the file. Any further selection in the addins dialog throws up a message that allows you to remove it from the list.


In Summary

This may be a good option for corporates that are looking to install Excel 2007, but are concerned with the effect that the ribbon may have on their user productivity, as it allows a smoother transition, at a small cost to the potential productivity loss. Its ease of deployment should not create any logistical problems.

A personal user may also find it useful in transitioning to Excel 2007, especially as it is free for personal use.

It is not for the power user who wants to continue using classic menus in an Excel 2007 world as it does not support commandbar customisation. That would require delving into XML, which defeats the point somewhat.


Ribbons – Gain Without The Pain







If you are in the habit of building or customising Excel 2007 Ribbons, there is an indispensable new tool that has just hit the market. Andy Pope has released his RibbonX Visual Designer, which can be found here.


I have blogged previously about how I find cutting XML tedious, and the inadequate tools available, so this is a more than useful addition to make ribbon design and coding simpler. It provides a GUI to facilitate the Ribbon build, simple point and click rather than the tedious line coding. The big advantage of this approach is that you can see the hierarchy of your ribbon, and visualise what it will finally look like. To make it even better, there is a preview option, which will build a workbook and inject the XML so that you can ACTUALLY SEE what it will look like.


The tool adds a group to the Developer ribbon tab (so make sure that you make this visible)

and here is a screenshot of the designer, with a few example controls added.


In addition, you can load an existing workbook, and amend its ribbon. It has many other goodies which I won’t list here, check out the link above to read about them all.


As I said, a great tool, and at a good price – free!