Archive for October, 2007

App or workbook Level 2

Tuesday, 16th October, 2007

I totally accept the arguments put forward that the print thing I released as a worksheet, could/should have been an app level add-in instead.

And I totally agree that centralising code and minimising duplication are excellent ideas, and I certainly try to do that where appropriate.

On the other hand I believe one of the key reasons spreadsheets have become so critical to most orgs is that they are not centralised – they devolve power, and decentralise control. If centralising code etc was so popular with users, why are they continuing to use massively duplicated spreadsheets instead of dumb clients to super smart central servers?

So I’m trying to weigh up these 2 opposite arguments, centralising code is good, but spreadsheet users so far seem to prefer decentralised.

Is the world moving to a more centralised style but just slowly, or are there real benefits to keeping things decentralised that spreadsheet users recognise and software devs ignore?

I worked on a project once to replace a complex spreadsheet with a web app (VB6 ASP, SQL Server etc). We did it, it totally worked, passed all testing, dev team v. happy. The users never once used it, they continued using the same old spreadsheet(s), with all that bad duplication stuff.

Do you think now those same users could be persuaded to use a centralised app? I think the technology is much better now than then, and things like VSTO with click once deployment, on-line/off-line working, simple updating seem to make it more appealing, or should that be palatable?

To me there seems to be a cut off somewhere when decentralised becomes too unwieldy and a more centralised approach would have been better. Key words being ‘would have been’. By the time you know how widely the thing will be distributed its too late to decide app level or workbook level.

Maybe its:

  • optimist (everyone is going to use this – best make it centralised and easy to manage) v
  • pessimist (this will get used only a handful of times, better make it simple)

In the case of these little utilities I reckon that translates as:

  • if few users use it in lots of wbs – then app level best
  • if loads of users use it in just a couple of s/s (ie multi user s/s) then probably wb level is best

What do you think?



Workbook or App level?

Monday, 15th October, 2007

I’ve released a few free utilities aimed at making working with big workbooks easier.

I did an index one that lets you add an index sheet with a few navigation tricks to make hiding and unhiding multiple sheets easier.

And I just did a print one to let you group sheets into various lists and then print them out with a single double click instead of having to visit each one and print it.

I’ve done them as worksheets with code behind that people can just copy into their own workbooks, refresh and use. I chose worksheets + code because much of the info would be specific to that individual workbook. This also keeps the one file solution thing going. And it keeps the info on worksheets where I personally like to work with it.

The down side of course is that you end up with potentially hundreds of copies of the same code, one copy in each wb.

Another alternative that I have used at clients is to have an application level addin, so just one copy per user. Each workbook could then contain just the data part and call out to the add-in for the code side of things. This strikes me as a better approach mainly from an updatability POV. But also commercially, not distributing all the code behind the worksheets gives more opportunity for better IP protection. The big downside is that now everyone who wants to use the index or print functionality needs a copy of the add-in. I.e the one file solution is no more. This might be an advantage from a commercial POV I guess.

Obviously I know the answer is going to be ‘it depends’ but, which do you prefer as a developer, and as a user?

For my own personal use I like the simplicity of code behind a worksheet, until I’ve got loads of copies and then find an error! I am thinking that for users who may have 20 or 30 workbooks they use regularly, keeping the main code in an add-in and just having simple call-out stubs in the code behind the worksheet will probably be best.

What do you think?



Excel 2007 calc bug paper

Monday, 15th October, 2007

Chris Lomont has recently released an excellent in-depth paper analysing the cause of the Excel 2007 calc/presentation bug. It makes a refreshing change from all those wondering if the problem is to do with the number of rows changing over on the Excel team blog.

His work is based on debugging and disassembling Excel.exe and shows the assembler code around the error. And he’s also checked the effect of the MS patch. Here is the page with a link to the paper. He also compares to the equivalent code in Excel 2002, and notes significant changes between versions.

Its reassuring that after some proper analysis he also reckons the bug is not that big an issue.

With the Excel team not anticipating providing any more info about the error this is the best we’ll get. Lucky then that its very clear and thorough. As these routines were probably written in assembly anyway, there was never going to be any C equivalent made available.



Free beta testers wanted

Monday, 15th October, 2007

I’m slowly but surely releasing bits and bobs that I think people might find useful. They are targeted towards the pain points I have seen (and felt) whilst working with large and complex spreadsheets.

There are a bunch of them over at codematic. And I have a few others in various states of readiness. They are all free until or unless I can find a (pain free) way to make a few bob on them.

The latest one is here. No liability blah blah.

print manager

Its a print manager worksheet. Copy the sheet into a new workbook and it then lets you keep upto 10 lists of sheets that can be printed together with a simple double click.

Hopefully its self explanatory. All the code is in the class behind the sheet, you can do pretty much what you like with it. If you improve it (or fix it ;-)), it would be nice if you leave a comment with what you did and why. If you see any gaping holes in the functionality then leave a comment on that too.

Test it in a fresh Excel with non critical workbooks first. Tested on Excel 2003 and Excel XP, not 2007 or 2000, or 97.

Known issue 1: I had a few crashes, but I think it was a conflict with another add-in. My usually rock solid 2003 (SP1) has gone all soft recently – keels over a the first sign of trouble. The code is fully visible so feel free to tinker.

Known issue 2: The number of printed pages doesn’t work right if you have ‘fit to x high by y wide’ set.

If you do use it, then thanks for being a free beta tester please post back any feedback. Or email me.Other ideas of simple (ish) self-contained components welcome.



Chip in with Access 14 dev

Friday, 12th October, 2007

The Access 14 dev team are asking for input on where to invest their dev efforts:


Be sure and be heard. I think its great that the product teams are reaching out to the community for input. Of course they have to balance all the differing views, so don’t be upset if your request doesn’t make it.

I’m also pleased to see the Access blog come to life.



Visual Studio version usage v Excel

Thursday, 11th October, 2007

I saw an interesting survey over at Code Project asking which version of Visual studio devs were using. I am not claiming their figures are representative, they could easily be skewed.

For fun I tried to line up the nearest equivalent Excel version and the numbers we discussed a few weeks back.

[the numbers are percentages of respondents, multiple replies allowed for VS (and a bit for Excel)]

VS 2008 11.8 Excel 14 0
VS 2005 76.0 Excel 2007 2
VS .NET 2003 27.5 Excel 2003 60
VS .NET (2002) 3.1 Excel XP (2002) 20
Visual Studio 6 23.1 Excel 2000 15
Visual Studio 97 1.2 Excel 97 5
I don’t use VS 2.6 Other s/s 5

I sort of made up the Excel 2007 and none Excel numbers. The VS numbers include users of multiple versions, but the Excel ones we discussed didn’t really. For the record I use 2003 and XP and OOo 2.3 most days. ( I did the above table in OOo, it generates nicer HTML than Excel I think). And I use VS6 and 2003 most days.

The E14 v VS 2008 may seem a bit harsh, but neither is released. Excel 2007 has been out a year now, whereas VS 2005 has been out over 2. I wonder if that 23.1% using VS6 are all the VB6 die hards?

So the dates don’t line up all that well, but I still think that the VS number show a significant skew to the more recent versions that is not there in Excel usage. Do you agree?

Maybe this explains why VS devs wont touch Office with a barge pole (too old skool)?

I somehow feel this explains to some extent the Visual Studio teams limited success at providing a viable replacement for VBA. (VSTO in VS2008 will be their 4th iteration)

I think .net is great for server based apps where the devs have decent control over the destination machine. I can’t see it gaining real traction in the corporate desktop Office development area until at least the runtimes are distributed with Office, or maybe even a VSTA style .net editor. I see a world of difference between one machine/farm server based deployments, and touching thousands of desktops as part of a solutions deployment (the latter being pretty much out of reach for many Office devs).

Its interesting to note that many software vendors selling standalone apps prefer Delphi or Java to VS.

What do you reckon?



Thousands of mistakes

Wednesday, 10th October, 2007

One of the kids (smurflings?) was moaning that he can’t do a decent bunny hop on his bike. And its true he can’t.

bunny hop

But I pointed out to him that he has to do 100’s or 1,000’s of poor ones before he’ll be able to do a good one. I’ve done thousands and I can get about a foot off the ground (300mm for the metric folks, 12 inches for anyone who doesn’t use feet). The top riders can do about 3 feet I think, which is high enough to get over most railings in style (rather than pain).

I think its similar in software dev. You have to write loads of crappy apps before you write good ones. There is an alternative argument that practicing moves badly will only make you good at doing them badly. I think for most switched on devs, the mistakes you made become apparent as time goes on. And mistakes others made show themselves when you have to maintain their mess.

When I look back at some of the code I have released in the past I think I have learned tons. I can write more code in an hour now than I could in a few days years ago. And it will have better functionality, better reliability and be easier to extend, test and maintain.

I’d say a lot of that improvement is down to a process of eliminating all the approaches that did not work well (alright, most of those bad approaches then). I guess more specifically its about finding what doesn’t work and replacing it with something that does.

But where are the best places to find proven approaches to replace your old broken ways?

The Excel developer books are good, the Excel-l newsgroup is superb, Google can often help, experience is a good teacher too. Any others? I sometimes feel the resources at the higher end of development are a bit lacking for Excel/VBA. (And something tells me that MS are not going to ever invest in this area).

Asking fellow devs would be ideal, but its so rare to get get more than 1 Excel/VBA dev in an org at any one time.

In mainstream s/w dev its common to start out maintaining other peoples code – I think this is a great way to learn what to do and what to avoid. In Excel/VBA though in my experience most work seems to be new build. Do you find that?



Excel 2007 Calculation bug fix available

Wednesday, 10th October, 2007

The Excel team have got a fix for ‘the 850*77.1 = 100,000 rather than the more traditional 65,535’ issue.

More info here. They are in the process of getting it onto windows update.

I reckon thats pretty good going, and a full 8 days faster than my estimate, but its a week after Dicks so I’m not sure if there is a clear winner to the sweepstakes. Let us know if it was you.

I still didn’t hear of anyone actually losing out directly because of this error – did you?

Do let us know if it causes any unintentional side effects. It looks from the comments that is doesn’t install well if you don’t have Excel 2007 (funny old thing!).

I notice there is a 64 bit version of Excel services, I didn’t realise that.

Now all they need to do is fix the UI and I’ll migrate in a jiffy.



ps, is the fix:

if(v == 100000)
v = 65535;



I just saw this in a reply by Dan B (one of the Excel Team): “We’re not planning to share details on this beyond what we’ve already communicated…”

On the one hand it would be great if they did tell us, then I look down the 200 and odd comments on the orginal blog entry warning of the issue, and I realise there are some things Microsoft just can’t do because its Microsoft. I almost feel sorry for them (until I read the next rant from El Klaxon).

Office usage info

Tuesday, 9th October, 2007

I tripped over this the other day. Its a survey on the different office productivity suites in use by readers of my fave IT news site El Reg.

This survey is a bit out of date now having been run in April, but still interesting I think.

I’d like to see a more recent follow-up. Anyone got one?



Office admin policies

Tuesday, 9th October, 2007

I assume most of us here are aware of MS Office admin policies (.adm files)?

I was going to point you here but that seems to link to some intrusive spyware page that wants to ratch around my machine (without permission) to see whats installed. So here is a KB article that doesn’t seem to contain the same malware.

These (adms) enable system administrators to limit access to certain features of MS Office applications. The classic one being VBA, I’ve worked a few places where you needed to specifically request access to the VBAIDE. VBA other people write still works, you just can’t see it (unless it errors ;-)).

Thats fine – seems like a reasonable idea.

Then I got to thinking about dictator apps (where the developer spends (/wastes) loads of time and effort swimming against the tide trying to lock users out of the standard functionality of the host app). I’m no fan of these (in case you missed that), I think if you need to interfere with the host that much then a grid control would be a better start point than Excel.

It’s just dawned on me that office policies are basically a bit of a dictator application. So my previous logic should apply. Rather than start with a full version of Office and lock people out, better start with something that provides the right starting feature set and add what they need. (A bit like black lists v white lists in security.)

Is that OpenOffice? MS Works? Google Docs? Some new as yet unannounced limited MS Office? Office Live? Something else?

Everyone knows the stats – ‘everyone only uses 10% of their Office suite, but its a different 10%’. How does that breakdown?

  • 5% need to author VBA?
  • 20% need to run it?
  • 20% need complete Excel compatibility?
  • 55% would be happy with OOo or Google?

Pure guess of course, and what is the break-even point where it makes sense to support different suites due to licence savings? Is there one?
What do you think?