Archive for November, 2011

UK Excel dev conf date sorted

Wednesday, 30th November, 2011

I have booked a venue for our event so now have time and place.

All info here.

I will sort out a booking method over the weekend and put up a link.




Saturday, 26th November, 2011

I was reviewing someone elses code recently and I noticed they had used what I consider excessive line spacing.

Public Sub RunAllThree()

Dim objWorksheet  As Worksheet

Dim intResponse   As Integer

I will henceforth call this scardeycat spacing and use it as a derogatory term. Leaving aside for a minute the silly name prefixes, what about all that white space?

In brace languages I tend to to open and close on separate lines which breaks things up anyway without need for additional spacing.

I use the odd blank line in my VBA to give eyes a break before or after some intense code, or to give extra emphasis to something.

But like all formatting, if you overdo it and emphasise everything then you emphasise nothing. The reality is that programming code is relatively information dense, adding white space can make it less scary up to a point, and after that you are just forcing the reader to scroll more than they should need to.

What is your approach to spacing?




Friday, 25th November, 2011

=Table2146383[[#This Row],[TPT Value]]-Table2146383[[#This Row],

So it seems you can get some messy names if you are copying tables in Excel 2007.

Cleaned it up with this:

   For Each lo In ws.ListObjects
   lo.Name = “DataTable” & (lo.Range.Cells(1, 1).Column / 10)

I have no idea of the naming/numbering logic, but it reminds me of the old VBA module bug, where the numbers increased geometrically until they overflowed and the workbook was corrupted. I didnt try and push the tables the same way – I need this workbook to work.



Rate cuts

Thursday, 24th November, 2011

News reaches codematic central of financial services contractor rate cuts hitting London.

Nice timing – Happy Christmas back at ya!

Are you affected?
(I’m not but friends are)

Excel Developer conference update

Tuesday, 22nd November, 2011

I have published the latest draft agenda here.

I’m already looking forward to it…

I am still investigating a couple of possible venues so havent got aything new to report on that. Just that it will be in London, fairly central. Date wise – still a bit in the air as it depends on venue to some extent. The plan is for somewhere between Tue 24 Jan and Thur 26.

If you want to be emailed as things progress drop me an email via the contact page and I will start a list.

Comments and suggestions on the agenda welcome, here, there or via email.



Dead Acer Aspire one

Sunday, 13th November, 2011


So the same netbook whose screen broke a few weeks ago suddenly died last week.

The odd light came on and the fan whirred but the screen was dead.

Following a largely proven theory that the last change probably caused the latest issue, I assumed my screen replacement had somehow failed. I reached straight for the screwdriver to check the leads and swap out the new for the old (which still lit up but was unreadable – therefore a reasonable test). Sadly this did not fix my issue.

Because I had the screwy in my had I pulled the whole thing to bits following the magnificent instructions here.

A quick blow around of the dust and general tyre kicking confirmed I had no clue what the problem or the solutions was. So I put it all in a box and left it. (I marked all the bits up this time so I could put it back together without too many spares left over).

Fast forward this week and a bit of googling laterz and I tripped over this article which seemed too good to be true. Just reformat one of my bazillion usb drives (not one of the magnificent Office 2010 ones MS have given for our conference in Jan of course) and flash the BIOS.

Obviously I had to rebuild my box of nuts and bots first, but bloody hell… IT WORKED!.

Still no idea what caused it so I will assume operator error, no idea if this is a permanent fix or if the usage pattern means its likely to happen again.

Anyway there you go if you have a Acer Aspire one that suddenly refuses to start up try the BIOS before wasting hours with a screwdriver.



ps I don’t have a single screw or bent bit of plastic left over!

Stock out

Sunday, 6th November, 2011

Retailers understand the cost of stock outs, well, the successful ones do. Customer comes in to buy something, you don’t have it in stock, they leave the rest of their basket in the aisle and go to a competitor.  Then they shop there for the rest of their life, or until they have a stock out. In principle the cost of not having something in stock can be way more than the missed margin on that single item. Its easy to think of this as being relevant to just physical goods.

However a similar thing happened at work recently.

One of the projects needed a dll writing to sit in front of a vendor component. Only trouble being there were no licencses left for the vendors tool.

Sys admin to dev team and project manager ‘If I had known you were going to need additional licenses I could have ordered them last week and they would be here now’

Dev team to sys admin ‘we only decided this was the only solution today’

Sys admin ‘I’ll order the license now, it should be here in a couple of days.’

Leaving aside for a minute the nonsense of a purely electronic item taking more than nano seconds to arrive…

The cost to the project is several day of no progress, so lets say it is 3 days late because of this and is delivering business benefits of 200 k per year, then thats 3 grand just lost. Against a license of a few hundred quid.

To me it seems that keeping a couple of spare license for core enterprise systems in the top drawer could be a good investment. The alternative would be to work with suppliers who can get you moving within minutes not weeks I guess.

I suppose some less scrupulous admins might propose to ‘borrow’ a license for a few days, and I suspect many vendors would live with that too.

Anyone else seen this license delay problem?

I can see it as an issue if you have hundreds of disparate tools to cover. I don’t know, for example if you had a policy of using the best tool for the job. But as most places obsess about minimising the number of tools no matter how inappropriate, I think a bit of slack in the license stock would actually be cashflow positive.

I have seen similar at places with concurrent database user limits, come the period end everyone starts fighting to get on the system because there are 100 busy users sharing 10 licences.

Have you seen this sort of thing?



Excel Developer Conference London Jan 2012

Thursday, 3rd November, 2011

Well folks there has been a really positive response so we are game on for an event towards the end of Jan in Londonium.

I will sort out a more detailed draft agenda as things settle down with the speakers, but to give you some idea…

The following topic areas are likely to make it into the agenda

  • Excel Business Intelligence
  • PowerPivot and DAX
  • Migrating from VBA to ExcelDNA with
  • Aerobics
  • Effective Excel Add-ins with VSTO
  • Technology independent software development
  • Beer and chips (and gravy)
  • Practical advice on commercialising your add-ins/workbooks.
  • Various approaches to XLL development
  • Hairdressing
  • Excel and XML
  • Chess boxing
  • Quick tips for effective VBA use.
  • Programming paradigms and how they can help or hinder, a history lesson
  • My snowboarding holiday
  • A smart way to build spreadsheets
  • Beer
  • Curry

I am hoping the broad agenda will attract a diverse set of delegates…

Please start spreading the word: UK Excel conference Jan 2012 London…

I still need to sort a venue, but I am aiming for one day the week ending Fri 27th, probably the Tue or Wed.

Our friends at Microsoft have generously donated some gifts which we will give away on the day in return for cleverest question, dumbest question, nicest biscuits, most beer drunk, that sort of thing.

This event is specifically targeted at the more technical audience. The plan is to have a follow up event in July in Madchester. That event will be more user oriented and less developer oriented.

If you have any other suggestions for topics please let us know below.



Reader Request

Tuesday, 1st November, 2011

I have had a request from a blog reader:

It would appear there is some pent up demand for another Excel developer conference…

The plan is to hold an event in January 2012 in London. If there is enough interest…

If you would like to attend a one day event covering all manner of content relevant to commercial Excel developers then please leave a comment below. The price would be in the 1-300 GBP range. There will of course be a full social scene too. And I seem to recall Ross promising to sort out the tee-shirts this time as he wasn’t happy with my performance.

Eusprig is moving to Manchester for 2012 (its in July) so I would be willing (again if enough interest) to arrange a second event oop north in the same week as Eusprig. Again leave a comment if you would be likely to attend.

If there is enough interest I will put together a proposal agenda and see what people think. If you would like to present and feel you have a topic fellow devs would be interested in, or should be interested in, then drop me an email.