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

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

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

runtime messages can be defined here

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

CurPageID = wpWelcome then




XLIsRunning do


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

mpRet = IDCancel then Abort;




function XLIsRunning(): Boolean;


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



            Result :=

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

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

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

= .Cells(1, j).Value

= 0


mpDataType <> “” Then

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


                For i
= 2 To mpLastRow

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


mpNextItem = mpNextItem + 1

Select Case True


Case mpDataType Like “Number<*”



Case mpDataType Like “Decimal<*”

                                ‘decimal processing


Case mpDataType Like “Currency<*”



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


= UBound(mpBefores) + 1000

Preserve mpBefores(1 To mpSizeArray)

Preserve mpAfters(1 To mpSizeArray)

                                End If


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

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



 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


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


Public Function ObfuscateData()


    With Application


        AppValues.ScreenUpdating =

        AppValues.Calculation = .Calculation


        .ScreenUpdating = False

        .Calculation = xlCalculationManual

    End With


    On Error GoTo func_error


… main code



    With Application


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



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
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
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!