Category Archives: 11520

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.

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!


Why Does Excel Make It So Hard?

I had a simple requirement recently, I wanted to create a form where it was possible to navigate around using shortcut keys. Easy you would think, just assign accelerator keys to labels and/or commandbuttons … problem solved.

Unfortunately, I also have a number of frames with multiple checkboxes in them, groups of
checkboxes. And guess what, frames don’t support the accelerator property.

My first thought was to overlay the frame with a label and blank out the frame caption. As labels have an accelerator property, this should work, using a shortcut key would hotkey to the label and then you tab through the checkboxes within the frame. Unfortunately, I couldn’t blank out the frame caption because the top of the frame is taken from the top of the caption, when there isn’t one it gets shunted up 6 points, so the label has to be precisely positioned to avoid blurring. I
managed to get this approach to work as long as the overlaid label has a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forscing that control to take focus on hotkeying.

So, there you are, a bit of creative thought and the problem was solved, albeit a tad kludgy and inelegant.

Of course, life is never that simple, certainly not with computers and certainly not with Excel. I have since come across a problem
with this approach. These frames are on a single page of a
multipage control, and if it is switched to another page and then back again, some of the underlined characters are losing their underline.
Not all, just some.

Presumably the frame caption has come forward over the label caption, but how
and why? What seems to be happening is that the label doesn’t overlay the frame caption as I had hoped, but the top half gets hidden by the caption, the bottom half still shows. Thus, the underlined character that is the accelerator key does show, or at least it does on first showing. I still have no idea why some disappear when showing another page and returning.

This is a picture of part of the form, showing how it looks on startup

I was stuck, I couldn’t think of a solution, so at this point I decided to call on the help of friends and colleagues in the Excel community.

Two Excel MVPs offered their help. Peter Thornton suggested an approach that was intrinsically the same as mine, although he used a nice method of using a commandbutton to accept the actual accelerator key, and then redirect the focus to the first checkbox within that frame. This was a technique that I had employed on another page of the multipage to have a shortcut key to a listbox, so why I hadn’t thought of it here as well is a bit odd, but although I could not demonstrate on Peter’s example file that it could also lose the underlines, as it was essentially the same as my solution I felt that it would probably suffer the same fate.

When I first saw the other solution, I thought that it too was a similar approach, although I was at first confused as to how this approach was managing to get the label to properly overlay the frame caption, and how it had the frame caption blanked out without the effects mine had suffered in this way. Further investigation showed it was a lot cuter than this. Here are the essentials of what had been done:

- rather than using a frame to contain the checkboxes, there is a label, sized to encompass all of the original frame’s controls. This label’s SpecialEffect property was set to fmSpecialEffectEtched and the BackStyle property to fmBackStyleTransparent, to make it look like a frame. The label caption was blank

- a standard label was overlaid onto this label, this being the label for which the accelerator key was targettedr, and as before, this was given a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forcing the first real control to take focus when the shortcut is used

- finally, both of these labels, and all of the encompassed controls were encapsulated in a frame, to keep the grouping aspect required, but this frame’s SpecialEffect property was set to fmSpecialEffectFlat, and its caption to blank, so that it didn’t show.

As can be seen from this image, the effect is exactly as required,

but because the target label is not half-buried at the top of a frame, it doesn’t suffer the loss of the underline when navigating through the pages. The invisible frame(s) can be positioned so that they overlap a little, in order to reduce the gap between them.

I have to thank Andy Pope for this really creative solution. Nice one Andy! And thanks again to Peter, an Excel MVP who is interested in the atypical Excel capability.

But I have to ask, why is it so difficult to do something so simple?


For anyone interested, an example file of this technique is attached in the RSS feed.