Archive for November, 2007

Conference social

November 27, 2007

Here is the plan for the social:

• Wednesday 28th November – pre-conference

Fountain Inn – 7pm Local pub serving Belgian beer, traditional food and pizzas.  Free WiFi.
12 Regent Street

• Thursday 29th November

Pizza Express – 6pm Great pizzas and nice beers.
7a Jesus Lane

• Friday 30th November

Ugly Duckling – 6.30pm Excellent Chinese cuisine in Cambridge’s historic centre.
12 St Johns Street

• Saturday 1st December

Saffron Brassiere – 6.30pm Indian restaurant serving authentic dishes.
36-28 Hills Road

Here is a map (click to open in a new window to print/see properly):


And here is the Word doc we’ll be giving out on Thurs. excel-user-conferencesocial.doc

Any problems let me know



Wednesday night

November 26, 2007

All the social is set up and booked now, as soon as we have a decent document done I’ll post it here. They’ll be a printed one at the event too.

In the meantime the current plan for Wednesday night is to meet here around 7:30 for a few beers and some food.

Fountain Inn is just along the road from the Crowne Plaza and not too far from the Travellodge. We can always go on from here if required.



Returning a RANGE from a UDF

November 22, 2007

[From Charles: ]

I always wanted to write a UDF that returned a Range rather than the usual things like numbers. It sounded like a cool idea, and so when I was writing the FSUMIFS UDF for my talk at the forthcoming Cambridge UK Excel Users Conference I realised that this was my chance!

(FSUMIFS is a VBA UDF that handles multiple condition SUMs faster than SUMPRODUCT)

I could write a UDF that returned a range (well OK actually for error handling reasons it returns a variant containing a Range Object), and then the user could embed it in any other Excel function that handled a range (SUM, MEDIAN, INDEX, STDEV, MIN, MAX, SMALL …) without needing a special variation of FSUMIFS for each case.

I wrote the FSUMIFS version first, and then did the FRANGEIFS version.
Of course in a lot of cases the returned Range is a multi-area range created by a loop using UNION, which has some disadvantages:
– UNION is quite slow
– Some Excel functions like OFFSET do not work with multi-area ranges.

It all works well and the performance of the current beta version in almost every case so far except for small ranges is substantially (typically 5 to 30 times, up to 100 times in extreme cases) faster than the equivalent ubiquitous SUMPRODUCT formula.

BUT, having done all this work, I am now wondering why return a multi-area range rather when you could just return a (single-area) array (ARRAYIFS)?

ARRAYIFS would probably work just as well if not better (would avoid all those slow UNIONs and problems with function that don’t like multi-area ranges).
What do you think the pros and cons of returning Arrays rather than Ranges from UDFs are?

Charles Williams

Pivot data

November 19, 2007

I’m busily working on getting some moderately rich data for the Thursday pivot table session. I already have the data, its just a case of cleaning any potentially identifying info out of it.

Anyway I got an email from a fellow Eusprigger who had heard I was doing a pivot table slot and can he have my data, as his examples are ‘boring’. Well I’m not sure my data is that exciting but I do believe it has enough to demonstrate some of the exciting features of pivot tables.

I think its easier to see the value pivot tables can bring once you have seen them in action on your own data, or producing your own reports. Certainly when I have built them for clients, they seem to need little further persuasion of the value.

The biggest win is when you need multiple reports off the same basic data. A classic example would be reporting monthly, quarterly, and annually (calender year and/or financial year). With pivot tables reliable, robust reports take seconds including testing.

When quoting clients for work, if its going to be pivot table based I rarely budget more than a day or so for reporting. If its not pivots the estimate could be several weeks.

I’ll make the slides, workbooks, databases and code available after the session for anyone to download from the either here or the Codematic website, or both. If you want the chat that goes with, you’ll have to come along on Thursday.



Beat the price rise

November 14, 2007

Seems I got it wrong, I thought prices went up today, but actually today is the last day of the old 10% off prices, they go up tomorrow.

Hurry hurry hurry – theres only a few hours left.

The future of VBA

November 14, 2007

I have been preparing for the Future of VBA session that kicks off the Excel developer conference on Sat 1 Dec.

Somewhat coincidentally Dick over on DDOE has recently done a couple of posts in this very area.

It’s reassuring to see there are a few people with the same concerns.

The Saturday session will look briefly at the history of VBA and its predecessor XLM and trace that through some of the very latest thinking in language design both within Microsoft and outside. That will of course involve .net, VSTO and VSTA.

It will be an open session so if you have any comments or questions bring them along and chip in.

Some of the key questions people involved with VBA have revolve around where is the language going, what are the timescales and implications so on. Whilst definitive answers to many of these questions are hard come by (I suspect some are still in discussion at Microsoft) we will attempt to give some usable guidance.

Another common question in the forums is what should I be doing now to prepare for VBAs future direction? We’ll take a look at that too, with advice for all of the difference sectors of the VBA user/developer community.

A few events I have attended recently have touched on this future of VBA hot potato, this session will be the first (public) one dedicated to this topic, that I have been involved with. I’m looking forward to hearing what other devs think is coming up, and how we might handle it.

Hope to see you there



Conference numbers

November 13, 2007

We had a query recently about the number of people coming to the conference. I thought others might be interested too.

Last year I think there were around 40-50 delegates, this year we are expecting similar, or possibly slightly less due to the shorter booking window. There is still plenty of time to book of course. And we might even be accepting registrations on the day? (I’ll need to check that one). Thursday and Friday we are running 2 simultaneous tracks, 1 conference one, and one add-on class one. (Saturday is just one track).

So the attendance was neatly summed up as:

Enough to create a lively atmosphere, but not so many that you get lost in the crowd.

I think thats a good description.

I just looked at Microsofts TechEd that happened in Barcelona last week – they had over 4,000 delegates. Thats bigger than my local town!

The Excel User conference is a much smaller scale, friendly, informal gathering of a bunch of people who want to share their knowledge and share in the knowledge of others. Plenty of that will be happening outside of the formal sessions in the breaks, and in the pub.



[And today is the last day of early bird discounts so go and book ASAP]

psst – wanna save a few quid??

November 12, 2007

Today and tomorrow are great days for booking your place at the Excel User and Developer conference.

On Wednesday the early bird discount ends and prices revert to the normal price which is about 10% more.

Get over to :

And book your place ASAP to beat the price rise.

Still not sure you want to invest a few hundred pounds in some of the best Excel training around?

Check out the schedule and contact one of the organisers if you have any questions. My contact details are here: (email will work best)



Excel versions

November 12, 2007

A few people have asked about which version of Excel will be used at the conference.

There is no conference preference, each presenter will use whatever they like. For some that is likely to be 2003, for others 2007, and maybe even a bit of 2000 and/or 2002. (And a bit of Excel 5 on Saturday)

Nick is doing a couple of sessions that are specific to 2007 for which I’d be impressed if he tried to use anything but 2007.

My stuff on pivot tables and VBA etc will be 2003 as that is the version I use for client work.

Any other questions just ask



Excel VBA training

November 9, 2007

One of the great strengths of Excel compared to alternative spreadsheets is the power, simplicity and refinement of the integrated programming language VBA. Whilst the conference aims to cover as broad a range of Excel based content as possible, it would be remiss to ignore VBA. A little bit of VBA can go a long way in improving a spreadsheet, in a variety of ways.

There are several sessions at the Excel User conference focusing on VBA. The outlines for all the sessions are here.

On Thursday, Andy Pope is doing an advanced level add-on class covering Class modules.

Using classes and the associated object oriented design can often make a solution much more simple to code and maintain. This OO approach is also followed by most of the modern Visual Studio languages.

On Friday there are 2 advanced level VBA sessions in the conference, which reflects how useful VBA can be.

In the morning I’m doing a session on good design in VBA, this will touch on classes, but focus more on general areas that apply whatever design is used.

In the afternoon Charles Williams is doing a session of writing efficient user defined functions in VBA. Its common to be able to swap fairly ugly worksheet formulas for simple elegant VBA functions. Charles will be showing how to do that without affecting performance.

Also on Friday afternoon Bob Phillips is running an intermediate add-on class covering some of the VBA tips and tricks he has picked up over the years. This is an excellent opportunity if you are just finding your feet in VBA to really boost your knowledge, and learn some tried and tested ways to to get things done.

There is also plenty to learn at the conference if you have no interest in VBA of course.



[And if you are staying for the Excel Developer conference on the Saturday we will be looking at what the future might hold for the VBA language and our solutions based on it. Well worth attending.]