Fix or rebuild spreadsheets?

I think the general view throughout development is that it is often easier to rebuild something from scratch than it is to fix/understand someone else’s tortured logic.

Personally I find that even more true for spreadsheets, I routinely re-copy and paste formulas across blocks rather than check they are consistent, for example.

But I wonder if spreadsheets are a special case?

I think its generally quicker to build most systems in a spreadsheet than in a more mainstream technology (database or code). And I think its harder to test/check/understand someone else’s spreadsheet than it is someones code or database. I don’t think this is just down to the skill of previous developers. I really think that spreadsheet technology itself is challenging to un-pick.

This seems like a double whammy, easier to build + harder to adapt surely means very few spreadsheets will get maintained?

what do you think?

My experience is that as business requirements change it is very common the throw away a spreadsheet and commission a replacement. But maybe when I think about it I am given the choice, and I usually choose to use only very limited parts of the legacy app.

I also wonder if this ‘disposable’ mentality is self fulfilling? No one expects the model to last long so no-one is prepared to invest a decent amount of effort to get a solid, adaptable version?

Or is it desirable, as per Fred Brooks suggestion to plan to build 2 systems, because you will anyway?

Cheers

Simon

Hammer nail spreadsheet

I regularly hear the expression – ‘if the only tool you have is a hammer, every problem looks like a nail’, as an explanation for why so many systems are built using spreadsheets when something else may have been better.

I don’t disagree with that, but I thought I would share a quick video of my favourite ‘hammer’.

Its a passy (Paslode) IM350 Nail gun, it fires upto 3 upto 90mm (3 3/4 inch) nails a second. Main use is for framing buildings, at the moment I’m using it to make some little ‘North shore’ style obstacles for the kids (incl me of course!) to ride over.

http://www.coveproductions.com/downloads/covepro_feb01_800.jpg

much smaller scale of course (but just as rickety).

cheers

Simon

SCARY SMURF STORIES

Apologies to the visitors who got here from using those search terms. I doubt very much you got what you were looking for here!

“Smurf talks about spreadsheet monsters?” My kids would be more likely to yawn and walk out than dive behind the settee in fear.

R1C1 v A1 notation

Like many people I usually use A1 notation. There was an interesting thread on the Eusprig list a while ago about the benefits of using R1C1 instead (eg easier to see if a block contains similar relative formulas).

The challenge was to use R1C1 for a few days and then go back to A1 and see which you preferred.

I find I prefer A1 in general but every now and then drop into R1C1 for the odd thing. Certainly if you are using .cells(r,c) notation in VBA using R1C1 makes it easy to get the column numbers. But as I pretty much exclusively use named ranges to coordinate VBA and worksheets, its not that big of a win.

If I’m assigning a formula to a range I usually use .FormulaR1C1.

Funnily enough Dennis also emailed me today suggesting this as a topic. I’d be interested to hear what your preference is and why (and when), and what you don’t like about the other system.

I find that since making a conscious effort to try it more, I have found R1C1 to be better than A1 for some things. eg: I find formulas easier to understand in A1, but easier to compare relative ones in R1C1.

Cheers

Simon

Dog rabbits and ancient proverb

We have a dog, and we have lots of rabbits nearby. Whenever I take the dog for a walk it always chases the rabbits. They scatter, the dog chases one for a while, then veers off and chases another. And never catches any. Thus proving the ancient proverb that ‘the hunter who chases two rabbits catches neither’.

Sometimes I feel a bit like that with technology. I’m currently juggling that many independent languages and products that I’m not sure I’m going to catch any.

The source of much of my frustration I would say is Microsofts lack of effective action with VBA. It hasn’t been updated for years, leading one to suspect its past its peak. And yet no realistic alternative is anywhere to be seen, leading me to wonder where on earth I should be investing my efforts.

And I don’t just mean within Microsoft technologies. If they don’t sort out a viable VBA replacement (soon), that has all the things that are good about VBA plus some even better stuff, then I can foresee some opportunities in Excel alternatives.

I read a great article recently saying there are no .net experts in the world. Its not possible, the technology is moving that fast no-one can realistically keep up. Maybe we’re all chasing too many rabbits?

Or maybe its just more of what Joel calls fire and motion?

Are you finding the same thing? too many possible choices? uncertain about future technologies directions?

cheers

Simon

wow add ins do not work

Another term people used to get here.

You could see this as:

Wow! Add-ins do not work (That would be irony right? – lots of add-ins don’t work right.)(and with the latest Office security features they work less and less well out of the box.)

or

wow (Windows on windows) add-ins do not work.

‘Wow’ is a compatibility layer included in 64 bit windows to allow 32 bit apps to run unchanged. here is some more info.

I am not saying this is a doomsday scenario, but I am slightly edgy about how well VB6 apps and COM add-ins are going to cope in this new 64 bit world.

or

WOW (Woodys Office Watch) Add-ins do not work. Not sure if thats the right link?

Office watch is indeed warning of things breaking with 2k3 SP3 but I can’t find specifics.

Any other wows I’ve missed?

cheers

Simon

VBA Functions

A while ago I read an interesting post here:

http://discuss.joelonsoftware.com/default.asp?joel.3.507923.45

A guy questioning the point of breaking code up into functions. He seemingly uses as few as possible. I’m the exact opposite – I almost use as many as possible, and I make them as short as possible (1 screen ~ 40 lines). No more than 3 or 4 parameters.

His argument is that the extra code of the build up, pass control and tear down of all those functions makes things harder not easier. Its an interesting approach. Personally I don’t think it would scale to over a couple of hundred lines of code max – what about you?

I’m a bit of a fan of Millers theorum (7, + or -2), but a few people have pointed out there is no evidence that it applies to programming. Suits me though.

Whats your view on it? do you even care about function length?

cheers

Simon

[My session at the Excel user conf will be looking at this and other VBA design issues in more detail]

End of British Summer time

Happened last night – we actually remembered this time.

There are 2 times per year when you realise how ‘free’ all those clocks, that everything seems to come with these days, really are. All the PCs sorted themselves out no bother. All the clocks we had to change manually, no surprise there, but…

Then all the vehicle clocks need changing.

All the cameras, calculator, video, DVD, CD player, cooker, bike computer, ipod, mobile phones, central heating etc etc.

What a PITA. And thats not the worst thing, everyone delighting in the one hour lie-in – they don’t have young kids do they? I was up at 6:30 old time 5:30 new time with the kids this am – nice! It’ll take them days to cope with the change.

Personally I wish they would leave the clocks the same time all year, what about you?

cheers

Simon

Visual Studio Tools for Office (VSTO)

I just wanted to call out the fact that VSTO will be an integral part of Visual Studio 2008 (was Orcas).

The packaging in the 2005 versions could have been clearer I reckon (and more cost effective!). I never fully understood which of the various components were required. Especially with the free SE thing. To be frank I gave the whole 2005 thing a bit of a body swerve (after reviewing it at RC2 stage way back when). 2008 however looks far more compelling. Especially now that Excel 2003 has significant market share.

I am assuming that the decision to package it in with VS2008 pro was somehow connected to poor sales of the standalone component (I heard the last VSTO user group meeting was held in a phone box at the bottom of XL-Dennis‘ street!). Its also likely that MS are keen to encourage the VS hardcore to consider Office apps rather than 3rd party controls. I’ll post more about my concerns with this later.

VS2008 beta2 is out now on a free download and reports I have read are that its very stable. It also includes a go-live license which means you can put the stuff you build with the beta into production. I wouldn’t personally be tripping over myself to do that with any important systems. But a distributed shopping list app might be ok. I got my copy on the cover of of my VSJ from those nice people over at Visual Systems Journal.

For anyone who is not sure what VSTO is, its a set of starter projects like the old VB6 Standard exe, and Active-x dll ones. The difference being these projects also host an Excel session with a workbook. This allows simple seamless integration of code and spreadsheet, much like VBA. Except the code does not end up in the workbook, but sits as a .net component that is linked to the workbook.

VSTO is not the only way to combine .net and Excel, but it certainly seems to be the way things are moving. I’ll be looking at this in more detail as time goes on, as will Steve Hansen over at VSTOTips.com. [Edit] And of course XL-Dennis has already written a ton of useful info over here.

VSTO will be one of the things we’ll be looking at during the Excel Developer conf on Sat 1 Dec in Cambridge. (Registration now open)

Cheers

Simon

Please can I go to the xl conf?

This may help some of you/us

<quote>

Dear beloved partner

I have recently seen a great opportunity on t’intar-web. (here)

As you know I do a fair bit of work in Excel and my boss thinks I’m great because I get things working.

Well some of the top Excel folks in the UK (and beyond) are getting together to pass on some of their hard earned knowledge. In 2 or 3 days I’ll probably learn more than I have in the last 5 years.

I really think I should go and boost my skills. I’ll try not to get involved in the social side and focus purely on Excel, although I have heard its worth going to the pub, as some of them talk Excel there too.

As well as learning a ton of stuff I’ll also meet some of my peers and get some great contacts for possible future work. I’ll also meet some of the folks on that Excel list that I have been on for years. Some of them went last year and said it was excellent.

And I promise I’ll decorate the spare room as soon as I get back.

I might even be able to bring you a nice souvenir of Cambridge back [??].

</quote>

Any other good reasons you can think of?

(Mrs Smurf started to ‘help’, but that just ended up as a massive list of things I promise to do when I get back. )

cheers

Simon