Archive for November, 2009

Information presentation

Friday, 27th November, 2009

Not one of my strengths but I like this graphic on the beeb – do you?

That Iraq war looks expensive – good job it is (was?) thoroughly, fairly and honestly justified hey? ;-) (beeb again)





Sunny Cumbria

Tuesday, 24th November, 2009

It seems the media is intent on debunking my climate claims for Cumbria.

Thanks for the concern for those that have been in touch to check we are not affected by the floods. Fortunately (for us) we are not, in fact we are about as far away as you can get in Cumbria from Cockermouth etc. They have had over a foot (300mm!) of rain in 24 hours – across a wide area.

It’s grim oop north as we say!

I’m not saying its not wet or owt:

Here is me after my dinner time ride today – on my way to hose down the bike, and my legs! (those shoes (and socks) aren’t normally brown btw) The photo doesn’t do justice to just how wet it is, I was, or how cold my feet were – I’m only just regaining feeling.

Still – beats working for a living right?

(I know purple is a fashion crime, but I think there is an exception for those over 40 still participating in non armchair sports)

What’s the weather like at yours?



Information Rights Management in Excel

Tuesday, 24th November, 2009

I was poking around the newsgroups last night when I came across a thread about IRM failing in Excel.

A few people seem to be suffering the same problem – within the last two weeks something changed and now their credentials are not accepted by Excel and they are locked out of their files.(here is the link)

My first thought was the Nov security hotfix for 2007 and 2003, but actually the thread is developing more along the lines that something changed at Hotmail (the credential authority).

I have never used IRM

  • a. because I don’t put my Excel machine on the internet
  • b. because fear of being locked out of my own files far outweighs the comfort of knowing other people who might get hold of it will be locked out.

Do you use IRM?

Do you have a fix for the issue these folks are seeing?



The world according to smurf

Monday, 23rd November, 2009

I’ve had a few chats with Ross over at MIE this last week.

One result of that is this podcast on spreadsheet development.

As far as I can remember we talk about

  • Brief chat about some of Codematics products and services
  • Some of the technical issues around various add-in technologies
  • Plenty of chat about .net and xlls and VSTA/VSTO
  • We also talk about security/ intellectual property
  • There is a bit of chat about the business of independent software development
  • And of course a bit of a natter about Linux and Open Office
  • And some other stuff I can’t remember.

The main thing I do remember is I did a reverse google. When talking about the Classic UI codematic offer I said it was all written in XLM. Slight dyslexic slip of the tongue because I have been doing a bit to much macro mischief recently. What I should have said is there is a version that has no macros or VBA and is pure custom XML. Handy for those ‘under the radar’ uses as there is no install and no trace.

It runs for about half an hour, feel free to leave your comments over at MIE or here.





Excel consulting by the hour

Sunday, 22nd November, 2009

Those Excel whizzes over at Codematic are offering a magnificent new service.

Excel support and advice by the hour on a pay as you go basis. It can be on the phone, on email, on a remote server, or on a boat in the Bahamas.

Its dead easy, get in touch to arrange a time and type of support, go and pay via the on-line shop and get your phone /email support at a time to suit.

The idea is for those people who are in a hurry, or have something confidential, or can’t find what they need in the newsgroups or Google to have another option.

Pretty much anything to do with Excel/Access/VBA/COM/XLLS/.net workbooks, add-ins, other systems etc is fair game. And any area: development, testing, deployment, support and maintenace etc. If your query is outside my area of knowledge I’ll say so and we can discuss other options. I’m not going to be bullshitting through something I have no clue about.

If you want to use it for training/coaching, that will work too, we can sort out a syllabus where to find resources and then moniter progress etc.

We can do out of hours service too, so you are not tied to the UK working week, which should be useful for those in other time zones with an emergency.



Should we care about the Clients Environment?

Friday, 20th November, 2009

Dennis made an interesting comment on a previous thread about how as developers we should be making use of multiple virtual machine technology to mimic our clients’ environments so we can better support them.

Its a good point… but I completely disagree.

Some developers should do that for sure, what Microsoft calls ‘professional’ developers perhaps. I prefer to think of Excel/VBA developers as business developers, we are a bit closer to the business and a bit further away from the bits and bytes of hardcore coding.

We express our business knowledge in Excel and VBA for a variety of reasons. One vital one for me though is ease of deployment and hence support.

If I write a decent spreadsheet in Excel 2000, I can reasonably expect it to work perfectly in Excel 2000, 2002, and 2003. I can expect it to work at least partially in 2007. That is irrespective of the wider target environment, user rights, security credentials, previous installed components, corporate build oddities etc etc. There is no dll hell in Excel*.

If the client has Excel they can run my application. full stop, end of.

(Of course there is a little excitement about macro security, the way they messed up expired signatures, the fact no one uses them because they are such a blatant scam etc)

*(ok so we sometimes get cannot find project or library, but if we keep things close to Excel/VBA and develop with care, and with some consideration for the clients environment that doesn’t happen much, and can usually be easily fixed.)

This trivial deployment leaves us business developers free to invest our time in understanding the business better and improving our software development skills. Deployment skills? system admin/security skills? heard of them, don’t want them or need them.

This is one of the biggest reasons I have not focused on .net – its a deployment nightmare. Of course that’s solvable, just invest a bunch of time and effort learning sys admin stuff and security stuff, and a bit of virtual machine trickery and jobs a good’un. But I don’t want to do that, I want to improve my business knowledge and my coding skillz. Luckily Microsoft cater for folks like me with Excel VBA.

Don’t get me wrong .net works well for corporate developers (once they have the required sys admin knowledge) but for independent devs like me, there is way too much pain to trawl through to distribute and support custom built .net components.

So I care a little bit about my clients environment, but not much. And frankly I think the fact that developers have to spend time and effort creating such close replicas of a clients environment is a hugh fail for Windows software development and for Microsoft. ‘Write once deploy everywhere’ – in yer dreams!

Major service packs? fair enough.  When you need to remotely replicate their level of hotfixes across a broad swathe of operating system components and applications the process is seriously broken IMO.

When I did asp development I had to get intimate with IIS to be able to work out when things went wrong whether it was our code or the server environment. If my Excel apps goes wrong, it’s my code, no investigation required (roughly).

I don’t have anything against .net, there is much about it I like, I just don’t think its aimed at pragmatic delivery focused independent desktop developers (like me). (Hence for the observant, the pic is from .net 1.1 from 2003). I jump at any chances I get to develop in C#, the joy of a modern language and a modern IDE, but this tends to be when I am contracted on-site in the role of corporate dev, rather than independent software developer.

What about you? do you find distributing your .net apps a true joy, the real highlight of your dev cycle?

Are you juggling more than 10 virtual machines, and keeping the patching in step with clients?

Which do you prefer development or deployment?

Do you agree with the separate roles of corp dev and business dev?



How did you learn VBA?

Thursday, 19th November, 2009

Dick has a post over at DDOE about encouraging people to have a go at VBA.

I completely agree, and in the spirit of “Recruit a new VBA programmer” week, I would like to hear how you got started in VBA.

  • What books did you use?
  • What courses did you attend?
  • Did you have an on-site expert to help?
  • Did you use MS help?(could you find it?)
  • When, and which version did you learn on?
  • Maybe you havent learnt, in which case do you have a plan?
  • If you have no plans to learn VBA, why not?
  • oh, and why did you learn VBA?


I learnt VBA in Excel 5.0 when it was first introduced (1995?). I had already done some XLM, VBA was a BIG change.

I used some deadly dull teach yersen in 21 days type book initially. Then I spent about 10 years with the help constantly open. Then t’intawebs happened and I now go to Google first help second. Can’t remember when we got broadband but even MS help is better than a dial up to Google.

I learnt VBA to escape the monotony of management accounting, after 30 odd period ends I was truly sick of the same accruals and prepayments the day before, and the same sales, gross profit reports after.

At one place I worked my colleagues were concerned that my desk was full of technical books ‘you’re meant to be the expert’ they said. Accepting that you don’t already know everything is perhaps the first step to becoming an expert.

I have never been on a VBA course, although I have now taught plenty. Never had an on-site expert I could bounce ideas off, although more recently I have often been in a team of multiple devs which is great.

My most recommended VBA book would be Excel VBA Programming for Dummies

Mainly because it dives right in there with real world useful stuff without boring you to tears with anal levels of background/foundations.

I guess this is the latest version for all you ribbon lovers Excel 2007 VBA Programming for Dummies.

If after that you think VBA is something you want to persue further, then is the time to dredge through all that tedium about datatypes etc. We’ll cover that ‘next steps’ in another post later.

What about you?



Processing speed

Wednesday, 18th November, 2009

John Walkenbach has a post comparing peoples processor speed here. If you havent added yours please do so, especially if you have a monster machine.

I duly did the tests and posted my results. Mathius did the equiv code in C#, so of course I had to fire up C++. It ran in about 2.7 seconds compared to 13.7 in VBA. No surprises there right?

So then I fired up my beloved VB6 – 19 Seconds! WTF??? I did all the optimisations and got it back down to 15.something. But why would a VB6 exe be so much slower than VBA?

If you have VB6 perhaps you could run your own check in case I’m doing something dumb. It took me ages to realise/remember that VB6 doesn’t have separate debug and release builds (too long in VC++)

Double bizzaro, if I debug in VB6 i get 13.odd, if I build the exe and double click that I get 15 or so.

Why would the compiled version be slower than the debug version?

I tried it as a dll too and VBA is still faster.

I know recent VBAs use slightly different libraries, but I thought they all went through the same MSVBVM60 runtime? Maybe this test doesn’t need that runtime from VBA?

I don’t want to spend much time on this, VB6 is pretty much an ex-parrot at this stage, but anyone has a plausible answer off the top of their head please leave a comment.



Whats for tea

Tuesday, 17th November, 2009

Just saw this

made me laff




Excel Screen flicker

Tuesday, 17th November, 2009

It seems the November patch for Excel has an unwelcome side effect – It totally messes with screen updating and causes content from multiple sheets to appear mixed up.

So if you get automatic updates, or just like being bang up to date, and in the last few days have started to have unusual screen updating issues check out this link:


That page also tells you how to remove the patch to test (Add/Remove programs).

Note this issue may affect 2007 and 2003 as the patch is for both versions.

The patch fixes some security vulns so I guess thats the choice you make, more secure with flaky screen, or less secure with smooth screen updating.

I made my choice a long time ago when I stopped applying patches because they just broke stuff or ripped out useful functionality.

Anyone else suffering from this issue?




Get every new post delivered to your Inbox.

Join 64 other followers