Comedy support

I had a call from a user the other day about a report that had stopped working.

I had a look at the VBA, not bad, but damn hard to follow (suck a loads of stuff into a random set of arrays of various dimensions, shuffle it and miraculously drop the right values into a range elsewhere.) well normally the right values – now it wasn’t working right.

I adjusted the caller parameters to fit a minor change in the data, tested it and had no clue if it was working. (It’s that type code that gives you no clue what its doing just presents the answer (Paul Daniels stylee)). I sat with the user to understand what it was meant to do. Lots of buggering around and a few back and forths, until it finally dawned on me:

The last few rows were being cut off by the print area!

Change the print area, user happy, ticket closed. Only about a bazilion hours longer than it should have taken – d’oh!

I truly can’t remember the last time I printed out a spreadsheet, and thats my excuse and I’m sticking to it.

Do you print out much stuff?

cheers

Simon

Excel Developer conference London Monday July 12 2010

Here are some more details for the forthcoming Excel Developer Conference on Monday July 12 2010.

Outline initial Agenda
A one day event focussing on real world development with Excel. The event targets Corporate Excel developers and individuals considering selling Excel add-ins, tools, and templates on a commercial basis. There is an excellent mix of the business of Excel tools and the technical aspects of getting the best from Excel. And probably most usefully it is an excellent (and rare) opportunity to network with fellow professionals operating in the same technologies.
The presenters are all commercial developers, most of whom have been selling Excel related products for years.
The format is relaxed and informal, the numbers are strictly limted to maintain a friendly atmosphere. (the expectation is for an intimate event of 20-30 people)

The business of Excel:
Back Office systems and activities to sell on-line
security, piracy, sales and marketing tools
(both of these are based on the real world experience of Charles Williams and his highly acclaimed Fast Excel optimisation toolset).

Technology of Excel:
How to choose the most appropriate implementation technology, both in a corporate environment and as a independent software vendor.
Creating fast User defined functions with .net and ExcelDNA and XLL+, and by hand.
Using Excel as a window to your corporate data
Designing Excel based systems for easy maintenance, support and modification
Extending Excel with VSTO and Add-in Express based add-ins
Review of Office 2010 and Visual Studio 2010 and the possible implications
Discussion of the future direction of Excel development, which technologies to back which to drop.
Panel based questions and answers – we guarantee to answer all pre-submitted questions at the event, and will follow up any on the days ones we can’t answer there and then.

Presenters:
Currently confirmed:
Charles Williams – Excel calculation master
Ross Mclean – international lute playing spreadsheet developer
Me – general spreadsheet botherer and Visual Studio tinkerer

Social:
A key part of these event is the opportunity to meet other people going through the same issues. We will be in the pub the night before and the night after to discuss all things Excel, corporate, commercial and technological, and possibly a little footy too.

The event will include unlimited tea and coffee but the midday meal is left open to delegates to arrange. Some of us will no doubt be going to a local pub.

In order to ensure that cost is not a barrier to attendence, the event is priced at only 150 GBP (+vat) for the day.

I will put up a booking and payment page on the codematic site in the next day or 2.

Any questions, comments or topic suggestions – please leave a comment below.

Cheers

Simon

non error error

how come this = 0 and not #REF or something?

=SUMIFS(AccQTYMT,AccProduct,F$56,AccTradeType,F$57,AccStrategy,#REF!,AccMaturity,$D104)

=SUM(G8,#REF!) = #REF! as expected

=SUMIF(E7:E12,#REF!,F7:F12) = 0 too.

All in 2007 (and 2010), I don’t have a 2003 to check just now, is it the same? (just the SUMIF obviously, you dinosaurs don’t have the luxury of SUMIFS ;-))

OpenOffice returns the #REF! I would expect.

Would you expect a formula to return an error if one of its required arguments is an error?

In fairness if there are #REF!s in the data it matches them and returns the total, so in a literal sense it ‘works’, but I’m not sure its what I would expect. What about you?

cheers

Simon

Excel DDE

Just recently we have been having some fun with DDE at work.
For those of you young enough to not know what DDE is – well done.
DDE (Dynamic data Exchange) is a 1980′s/1990′s technology for enabling disparate systems to pass data between themselves. As with most Microsoft technology of that era they favoured ease, and getting the job done over security. Hence they now discourage its use.

Of course that doesn’t stop most of the market data providers using it.

Anyway, long story short, I wrote a server in VB6 so I could play with it in Excel and I wrote about it here.

I had a quick look around and it didn’t seem that obvious how to write one with C#, anyone got a link?

It seems that if you have a bad enough Excel crash you can hang the whole DDE messaging process, well somebody can, in our case we suspect IE.

Anyone else doing working with DDE?

Anyone actually developing a DDE server app?

cheers

Simon

Why I love and hate project management methodologies (long)

I’m thinking of documentation focused, process map driven, meeting oriented, service level agreement based, gated review, buzzword encompassing, busy work, job-fer-life methodologies. Perhaps there are others but I havent seen them in the wild.

So why Hate?
Well, here is my ‘how to address a business need’ shopping list in preference order (fave at the top)

1. Drop in an off the shelf system (nice in theory, rare to find a good match)
2. Customised off the shelf system (the breeding ground for big methodologies)
3. In house IT team developed ‘mainstream’ system (often relational database CRUD with .net/java/whatever business logic and presentation layers.)
4. Professionally developed spreadsheet based system (pro means knows spreadsheets, knows databases, knows coding, knows software development. (funnily enough there aren’t many people who fit this bill))
5. and last  but not least, an end user developed solution, probably in a spreadsheet (or friendly desktop database).

This list is based on my estimated cost/benefit over the lifetime.
In a great many cases a spreadsheet solution will be the quickest and cheapest, no doubt, but their lifetime is often short, and they can be a maintenance burden.

The purveyors of snake oil methodologies are mainly feeding off their big consulting firm background in implementing massively customised, big fee earning ERP systems in the job-fer-life timescales. End user developed spreadsheet proliferation is the direct response to the gross user neglect that is core part of these big systems. ‘Take the pain now, we have a brilliant system that will fix this’… in 2017! Isn’t SAP the one that no-one has actually finished implementing yet?

In a 100 dev year, assembly coded, man-on-the moon project, yeah, sure bring in a heavyweight methodology suitable for life critical systems. For a two week spreadsheet spring clean? give me a break. For modern business systems tools like .net and java etc I think the tools are powerful enough that if the initial design is good the tools can be used to adapt to the inevitable emergent requirements.

The idea that a burdensome methodology will improve business systems quality is utter nonesense. The handful of systems that finally makes it through might be of some sort of higher quality by some measure somewhere. But the shanty town of spreadsheet hell that sprouts up to cover those things that didn’t make it in or out really really isn’t an overall step forward.

These methodologies always stink of waterfall to me, and surely that is the most discredited swear word in business system development.

I think these plan, document, approve, analyse, document, approve, design, document, approve…. processes really increase the risk of building the wrong thing too late. To me they are just so much more risky than partnering with the business to jointly develop the required system with trust. And they back load all the risk as the customer gets zero value until close to the end when if the system meets their then current need all well and good. Compare and contrast with iterative development where the customer gets a rough partially working system that address their most critical needs first and then is gradually extended and expanded to meet their developing need. These methodoligies don’t consider this because they were all developed back in the 60′s when the waterfall was the bible and C was just a glint in K&R’s eyes. (remember the 60′s was 50 years ago!!)

So when the the IS department implements their SLA based, documentation heavy, change control hell what actually happens?
Crap spreadsheets spring up absolutely everywhere (thats the least favourite from above for the hard of reading (I didn’t put offshoring on there)). Like the most tenacious of tenacious weeds these instantly become a huge support burden. The support effort takes away from the replacement development effort and the whole thing spirals down out of control like a dying fly that got burnt on a (non eco, non carcegenic old skool filament) bulb. Buzzz..t.

So thats why I hate them – they directly cause a massive deteriation in organisational data quality and cause major paralysis, scores of missed business opportunities, and lots of frustrated business people.

And thats why I love them – those frustrated business people who are busy building castles in the sand with their ratty spreadsheets who have just had their confidence in their in house IS team shattered will be wanting someone to fix their spreadsheet hell at some point. Thats a job for us.

The other reason I love them is even more mercenry. Places I have worked that adopted these heavy methodoligies have a rapid loss of their more dynamic staff. So there are normally a few vacancies there too. And as a contractor you don’t normally have to adhere to their box ticking extravaganza.

So: hate they because they kill organisations love because they provide great work opportunities.

My biggest relief is that I didn’t cough up for that Prince2 course I considered years ago – I couldn’t work in a Prince2 environment so I’m better off with it not on my cv.

Any of you hiding behind an SLA? checking your traffic light (or kimball ball these days, fashion, eh?) progress KPI dashboard against the inevitable landscape printed gantt chart? (what size paper is yours?(dot matrix fanfold??))

How many of you have worked on short term systems whilst the customer waits for the ‘big fix’? (I have, loads)

cheers

Simon

Pareto gave me whiplash

Hopefully you all know the pareto principle, the 80:20 rule. The idea is that the first 20% of effort gives 80% of the benefit, and a million other combinations all suggesting that there are worthwhile benefits even in just doing part of a job. Its the rallying call of half job harrys and bodgers everywhere, and all under the guise of increased return on investment.

Don’t get me wrong, I think there are many areas where the 80:20 principle is completely valid. But I’m here to tell you skateboarding is not one of them!

This is a reconstruction obviously, I was actually skating the bowl at the local park. A proper skateboarder came up and did a back side kick turn, where you go up the ramp, and as you slow down, lift the front wheels, rotate around the back wheels in the direction your heels are pointing 180 degrees and come back down.

I did at least 80% the same as him, same approach speed, same angle, similar stance, same smile, but I got my weight slightly wrong and went flying – my board nearly decapitated him, and I landed on my arse, hard.

I got sent a spreadsheet recently to review, it was the same thing, on the surface it seemed reasonable, the formulas were a little long but not to bad, the VBA was long but seemed understandable, there were lots of sheets, but the names seemed to make sense, etc etc.

When I came to properly investigate it though, it was incomprehensible, the developer had used 80% reasonable practice, but had really fallen on his arse. I won’t go into the gory detail but the model was a monster.

I’m all for 80/20, maximising return on investment, cutting out features where the cost outweighs the benefit, but I think its important to recognise that you can’t do that everywhere. In most of the work that I do a system that is 80% right is 100% wrong and 100% useless, or worse.

What areas are there that you can think of, where pareto does not apply?

cheers

Simon

And yes, thanks for asking, I can move my head again finally.

The Better Spreadsheet Fallacy

There are, and always have been a stack of ‘better than spreadsheet’ applications trying to encourage spreadsheeters away from their big grids. Improv, I’m looking at you.

Many of the live spreadsheets I see are ugly, damned ugly. Most of them would be far better in almost any other tech,( even an abacus for some of them!).

And I think from this mess of live spreadsheets, people think that a better tool will result in better applications to replace theses ugly pugly indirect offset nightmares.

Thats a fallacy though. Heres why:

The spreadsheet is the journey not the destination. When you look at a live spreadsheet you are not seeing what the developer initially intended, you are seeing all the twists and turns, scope creep, blind alleys and good days and bad days of the development lifecycle. And more often than not you see the shortcuts the developer had to make to meet the (perhaps constantly changing) timescales.

The main strengths of spreadsheets is their ability to support the developer laying out their ideas, and developing them over time. This is why I think they are a great (unmatched?) prototyping tool. What’s left after it goes live is often suboptimal, which is why I’m a big fan of migrating stabilised spreadsheets to a more production ready technology. But I have yet to see a tools that supports that freeform brain dump-organise-enhance-use cycle as well as a spreadsheet.

I think the opportunity is in migrating spreadsheets to technologies that better support live use, things like databases to organise data into an easy to maintain structure, and code to allow the full suite of dev tools like source control, static analysis, code metrics etc. I am convinced the spreadsheet step needs to be in there in a great many cases.

What do you think?

cheers

Simon

Spreadsheet Risk Call For Papers

Eusprig, the European Spreadsheet Risk Interest Group are calling for papers for their conference next summer in Greenwich.

More details here.

Eusprig is the premier (and/or only) body (worldwide – the ‘European’ bit is a bit misleading) dedicated to raising awareness of the risks of enterprise spreadsheet use.

Some might argue that they have been a little *too* successful, I certainly get that feeling sometimes when decent spreadsheets are treated like radioactive waste but crappy unusable web apps are feted as the brave new world of productivity.

This years conf is all about how to minimise the risk whilst maximising the value. And its about practical steps to achieve that not just lots are handwaving.

Hope to see you there.

cheers

Simon

Offset WTF

=OFFSET(BC153, -1, 0)

Why would yer?

What advantage does this have over ‘=BC152′?

oh, unless you are deleting stuff and want to avoid #Refs maybe?

Others?

cheers

Simon

Jobserve Alchemy virus

I’ve been keeping an eye out for a contract on the (UK IT industry) default standard website Jobserve.

They have a ‘service’ called Alchemy, where they email you details of any jobs they have on their database which are like ones you apply for.

It’s doing my head in!

I’m getting half a dozen emails a day, and there seems to be no way to turn it off. I’ve deleted as much info as I can from their site, if it doesn’t calm down I’ll have to delete my whole profile, or maybe change my email address. They give you the option to pause emails for a day, month or 3 months, but no options to FOAD.

It wouldn’t be so made if it wasn’t so dumb – ‘oh look he once applied for a job in Edinburgh – send him an email about any job in Edinburgh’ SAS? Me?

What a crap design, they have a really intrusive ‘feature’ that you can’t turn off. WTF?

.

That’s it!, just got another, I have unregistered myself. what a load of rubbish. Its never been this bad when I have used it in the past.

Are you having the same problems?

cheers

Simon