Archive for the ‘quality’ Category

Good Spreadsheet practice

Wednesday, 27th November, 2013

Something a bit more realistic and less dramatic than ‘don’t use them’, from the ICAEW.

Please have a read and make some (constructive) comments on that site.

I can think of a counter example to all of their suggestions but I guess in general they are mostly fair enough, if perhaps a little woolly.

Some of them read a little like workarounds for poor fundamental design (eg protection – I’m never a fan!).





Apple v Google

Sunday, 3rd March, 2013

Santa was very generous at Smurf towers this year. Especially considering how some people hadn’t really delivered on their blog updates and conference organising commitments.

I got a Nexus 7 tablet and a ipod touch. In that order.


The Nexus 7 was the worst unboxing set up experience of my young geek life. That thing is infested with Google spyware and intrustive ads. horrible. and technically impossible to use due to retarded set up requirements. (you need to go on line to set it up, but it wouldn’t let me set up a functioning internet connection with the hotel wifi. totally shit. You can’t move files on and off it via USB (in Linux). Maybe you can now several months on with some faff, but really that stuff should work out of the box. and it just did not. at all.

Google forced me to set up a new user ID whose credentials I promptly forgot. They now want me to pay them to retrieve them. Seriously. I have to pay for a password reset? Foook off.

So when Google are bragging about how Google+ has passed 1% of facebooks popularity and swiftly heading for 1.1%, bear in mind 90% of those accounts are probably zombie accounts that no one can access. Raahbish.

I used it for a few hours then I put it away and now it just weighs my bag down. It must be close to 2 months since I turned the thing on. total waste of money, not to mention the scamful cover farce with the PCWorld sharks (Yes I was so desperate to own this revolutionary piece of tech I bought from PCW!). 10 quid discount on the cover, oh, but its by internet rebate. no sorry forgot to mention there is a 3 page form to fill in. Serves me right, but lesson learned.

Compare and contrast with my iplop touche…

Great set up, limited spam, same games my kids play (their high scores tend to be min 10x mine!) and Facetime. Pur-lease – this is truly the killer app here, and all tucked away in Apples walled garden.

Our whole family is facetiming all our family and friends. Also since a few high profile kid/credit card haemorrhages its now quite simples to set up an Apple account without registering a credit card.

In retrospect I think there is a lot of sense in buying hardware from a hardware company that know what they are. If you buy from an ad pimp they are always going to be scouring through your most personal activities looking for the opportunity  to sell you Excel consultancy services!

I notice Google won’t even search properly any more unless you are logged in so they can record your every move. They are now a truly creepy company. I know they bypassed Msft’s market cap in 2012 (as predicted) but they are getting a little too crap and too heavy handed for my liking, as well as creepy and intrusive.

I know lots of people are waiting for Apple to founder, and iPhone 5 seemed to be pretty close, but whilst they have a huge stock of mini fanbois on ipod touches desperate for iPhones I think they are fairly safe.

Unlike Blackerry. My BB Bold has been the most unreliable phone I have ever owned (and I had one of those truly shitty motorola droids), everytime I unlock the screen its hit or miss if the thing is alive. I have even been contemplating replacing it with an iPhone! imagine that!!

I could probably sell or palm off my nexus, iplop and BB and scrape together enough to buy an old secondhand not too battered iPhone. Or not.

So in summary:

Google: creepy crap, Blackberry: unreliable crap, Apple: actually quite expensively good at the mo.

Have you enjoyed or endured any tech updates recently?



the only spreadsheet angle here would be the fact that if any of this tech has a spreadsheet app, I haven’t tried it, or felt the need to. Anyone thinking of implementing newly draconian office app licensing might want to think about that…

LinkedIn comedy

Wednesday, 4th January, 2012

I don’t really use LinkedIn, but I happened to be on there today and saw something that made me laugh.

Recently I was approached about doing some work for a potential client. After a brief discussion and issues with my availability (I didnt have any), the project went to someone else. No problem (for me), but lets just say the work was quite specific, oh I don’t know let just say taking some data from Excel and creating a drawing.

I skipped over one of the Excel groups on linkedIn and lo, a question from ‘recently’ : ‘How do you create a [specific type of] drawing from [a very particular type of] Excel data?’ Made me laugh…

No idea if it is exactly the same project, but its a pretty damn close coincidence. (I didn’t bother to look if it had been solved.)

At least they didn’t wait until I had scoped it out and incurred time and costs then blow me out on rate. Although I guess if the forum approach fails badly they could be back in touch. With even less budget having blown half of it on the failed hobbyist version.

This sort of thing always makes me wonder if I should move over to a server based tech so I am not always in competition with hobbyists? No disrespect at all, I got into VBA as a hobby because it was more interesting than my proper job. But I am not sure how many people would get into the nitty gritty of Oracle, or Sharepoint just for fun?

Any of you passed up some work only to see it hawked around the forums shortly after?



Excel Dev conf presenters

Friday, 23rd December, 2011

In the run up to the magnificent, exciting, exclusive, once in a lifetime Excel Developer conference, I thought it would be interesting to get to know the presenters a little more.

First up on the conf blog is Stephen Allen. Stephen will be taking us through some tools and techniques for reviewing real world spreadsheets. And dealing with the review findings.

If you haven’t booked yet you risk missing out on THE hottest event in the 2012 Excel calendar. The organisers are generously offering a full 50 quid early bird discount for those folks organised enough to book before Friday 6th Jan 2012. This isn’t just to punish those people who don’t know who they are or where they will be from one day to the next, it’s also so Mike knows how many custard creams to bring.

I can confirm that there will be a prize for whoever brings the nicest biscuits (hint: custard creams won’t win – even if they are the only entry).

Get over to Codematic now to book, then you can relax and just worry about the biscuits.




Monday, 12th December, 2011

On reviewing a colleagues’ code recently I suggested he might want to change the name of his SortAndDelete sub to something that more accurately described what it did.

My proposal was FuckEverythingRightUp!

His response? That was meant to be a one time use system, the user had reloaded it with data of a very different structure.

My reply? if it was one time use then he should have run it and given the user the results only. Sods Law dictates that everything you give to your users(/customers) will be brutally abused and destroyed with no mercy.

He then pointed out he was trying to fix something I wrote a while ago that was initially quick and dirty fix, but was at that moment just dirty, and broken.

I asked what the comments said (comments are vital to quality code). He confirmed there was a

‘// sm 2010 quick and dirty bodge

comment, which I believe is better than a get out of gaol (jail) free card.

He also pointed out another of my comments from a while ago on a different system, he was now handholding:

‘// sm 2009 please god don’t let this bag of shite still be live in 2011.

Like I said comments are vital to back covering.

What’s the funniest, most inappropriate comment you have seen/written?

(I’m not sure if I should mention the dictionary object one of my colleagues used, using correct reddick tlp:

Dim dicHead as Scripting.Dictionary)



People or Process?

Saturday, 1st October, 2011

I’ve worked in quite a few different departments in quite a few different organisations, some were good, some not so.

But I have finally worked out what the fundamental difference between the good and the bad is.

The bad places to work focus on process, and believe if the process is ‘correct’ any ‘resource’ can perform it in an acceptable way to an acceptable standard. As long as Resource1 fills out the requirements template, Resource2, 3 or 4, or 1 can complete the design docs, and any resource can code it. Once its coded another resource can test and release it, job done. All resource can now be reassigned, sacked or outsourced – youpee.

The good places to work value people and recognise that individual skills and experience are the vital ingredients to a projects success or failure. Need to understand what the user Jo needs? ask Harry, he used to work in the biz he will be able to understand and translate it. Need a good design, get Harry to collaborate with Debs, she has architected loads of systems that all worked well. Need it coding? get Jack he knows whatever language Harry and Debs agreed will be best, He can help Harry write the code. Oh and while we are at it, why not get Jo the user involved frequently to make sure it is heading the way she expects?

All people, all social, all rewarding work, for everyone involved. But more importantly the collaboration means you don’t have to follow the ridiculous outdated, completely discredited, waterfall implicit in the faceless soulless bad co approach. So even the user Jo wins with people based development as she gets early access to shape and start using the system, and she learns a bit about trade offs.

Of course one of these represents all that was bad about 1970’s waterfall, and one represents all that is good about Agile approaches. Seeing IT people still doing waterfall is nearly as sad as seeing users retyping from one screen or printout to another. What’s doubly depressing is these two go hand in hand, in the same sad companies.

The other thing is, if you are, like me, trying to charge a premium over less experienced developers, process based places are scary. As they believe process is key, your experience and track record aren’t important or valuable to them. So, you will struggle to get anything over the general rate, and if you get a good rate you can expect an early chop. (After a painful period of being pulled back by ‘the lowest common denominator’).

Why are cos still trying to do waterfall? It looks easer to project manage. Once you have those requirements, someone can make up a guess of how long it will take and it can be programmed into the department workflow. Of course the unknown unknowns will have a bigger impact than the known requirements, so savvy PMs will at least double the guess. But a doubled guess is still a guess. You can still guess using a more agile approach, but maybe it would be better to totally revolutionise the project management and just work a prioritised list to time/cost constraints. But the inexperienced theorists in charge didnt cover that at college.

What do you think is the key difference between good places and bad places to work?



why why?

Tuesday, 6th September, 2011

For Each yy In Worksheets(ws).Range(Cells(8, 1), Cells(8, 160))
If yy.Value = “Stop” Then
jj = yy.Column
End If
Next yy

Why would you use yy ? (nervous twitch??) (or jj)

Also note how I have generously indented it for your delight, unlike the original.



Dawn of realisation

Wednesday, 29th June, 2011

For pretty much as long as I can remember I have wondered why people in organisations persist in doing things in Excel and VBA that would be sooo much better in almost any other technology. We have discussed many of the reasons in the past but I got hit by a new one the other day:

The corporate process for requesting the right tools for the job is so long winded, painful and uncertain that no one bothers. Its quicker, easier and less pain to build an Excel monster instead.

To me this is a disaster, a dereliction of duty by IT, and just an all round FAIL.

IT should be proactively monitoring the markets and trialling interesting looking software and services with a view to proposing them to the business. Not sitting back, blocking all the interesting websites and file shares waiting to obstruct any other attempts to get the job done. (Hardly the work of a Strategic Business Partner is it?)

I have been through these processes a few times, some stuff like getting your Office install upgraded to Pro to get Access is normally a few clicks, an approval email and a cross charge. Getting approval to deploy some VSTO components and some C++ dlls at one place was a bit harder, including a half hour interview on the benefits and risks. I totally don’t mind needing to justify this stuff, but in many cases IT should already have proactively tested and approved it, imo. (or in the case of C++ understand they already have it all over)

I was looking at these processes for not so standard stuff at a few places that publish the detail, and sure enough it can be 6-8 weeks to get approval for an install. That’s ok if its bill and bobs virus ridden utility, but if its a part of what should be core infrastructure then you’ve already missed the boat.

So if you are in IT here are some tips

Developers need developer tools, they can and will develop without proper ones, but it wont be pretty – and it will be your fault for not providing the right tools.

Users need intelligent flexible access to complex data. If you don’t have the right tools, (Think OLAP/BI), then it is your fault when they do it badly in Excel.

just sayin…

What is the most long winded, laborious, misguided IT process you have had to go through? was it for software approval?



Eusprig 2011

Friday, 17th June, 2011

This years spreadsheet risk and quality extravaganza is almost upon us. 

It is exactly just less than a month away in mid July.

You can book here.

I am not presenting this year, as I thought I would let someone else have a turn speaking (and of course I missed the submission deadline).

In fact I probably wont be attending as I’m not sure where I will be working/holidaying then.

I would be expecting a good talk from Patrick as we worked together this year on a few spreadsheet related projects. Indeed he came face to face with the source of several of my formula horrors from previous years!

oh looks like he is not presenting this year, but on the bright side there is some more original research on the power (or not) of range names, amongst other interesting papers.

Here is the (current) draft outline schedule.

Are you going?



Getting the data right

Wednesday, 15th June, 2011

I was chatting with a fellow dev recently.

He said for him the most important first step (in design/development rather then analysis) was to get the data right.

I started off disagreeing and suggesting the most important first step was proving the concept will even work. But then in our discussion and thinking about it after I decided that I agree with him. To an extent anyway.

My new most important first step is to be clear how you will be able to check the data is correct. Because it may work in concept, but if you can’t prove your data is correct your system cannot be proven correct, and therefore not a right lot of use. I use prove and correct here in the traditional business consulting sense rather than legal. (correct = near enough to blame the old system, prove = no one will find out before you are out of there, invoice paid.) ;-)

We were talking about a standard, load data to sever db-run some queries-extract to Excel reports, kind of a system.

What would your most important first step be? (assuming you have some idea on what the users want/need of course)