Archive for April, 2007

MCSD worthwhile?

Tuesday, 17th April, 2007

Marcus asked how useful my MCSD has been. (Microsoft Certified Solution Developer)

Well, first off I have 2, the old one in VB5 and 6, and the newer one in .net (C#).

With the first one the VB stuff was very useful for learning how MS think you should use the product. I’d say I know more of the products nooks and crannies for doing the learning. And some of these are useful.

The first one also had a section on Windows architecture, I found that hard going (all about named pipes, memory mapped files etc) and a little bit irrelevant. Over time though I have come to value that level of understanding of whats going on under the covers.

The second one in .net I haven’t yet used that much. The language is developing so fast much of the C# I learnt could probably be better written using the latest constructs.

The second one had more about the process of developing software using the MS frameworks. That would be of use to some people, much of it was a bit fluffy for me.

I found this one to be far away from the stuff I do to earn a living, probably too far to move over without having the start at the bottom and work up. VB6 is/was an easier transition I reckon.

So the direct benefits are mainly around knowledge. And that is really background knowledge for an Excel/VBA dev.

In terms of employability, status, or whatever, I don’t think the MCSD carries much weight at all. Most clients have never heard of it, those that have heard of it don’t take it seriously. I can’t remember when I got the first one, but I don’t believe my MCSD ever got me a job that I wouldn’t have got otherwise.

I would say its useful if you have limited experience, or if you have limited knowledge of development methods/processes (sys dev lifecycle for example).

I think they are now using the qualifications as a marketing tool forcing devs to keep training in the latest tools in the hope that will drive sales somewhere. I won’t be renewing my qualifications as MS are now so out of touch with the real world. And I don’t think I could recommend them for an Excel/VBA dev.

Bottom line, in ROI terms, I’m not convinced. I think I may have been better spending that time/money/effort with family and friends or on a hobby!

Has anyone else done it?, or something similar? Anyone got any better recommendations?



Skill set v rate of pay

Saturday, 14th April, 2007

skill set graph

I’ve been thinking about which direction to go in next. A key factor for me is rate, I’m in business and I expect a reasonable rate of return on investments I make. That applies for tangible investments such as property and less tangible ones like training. Training is critical for me, as I am basically in the knowledge business.

I’ve come up with the graph above to demonstrate how rates of pay change according to skill set, well at least how I think they do. What so you think? Is there a better chart?

The basic idea is that good business knowledge or good IT skills get a reasonable rate, but a strong combination is much more valuable.

The issue I have is in looking for the right IT skills to develop next. My experience so far seems to be that Excel VBA is the tool of choice for people with decent business skills. .net seems to be further away from the business and it worries me that it is moving too far to the right on the graph – ie potentially declining pay rate (a poor investment). I wonder if VSTO or VSTA will overtake VBA in that regard in the next 5 years? Do you think so? (Personally I doubt it, in 10 years maybe, but I think its pointless trying to think that far ahead in IT).

Another option would be to go for a none programming option such as Analysis Services or Essbase. These almost ‘super power user tools’ seem to sit around the top of the chart from what I have seen. A few of the Excel heros from when I started seem to have gone this way (OLAP). I would say that SQL server or Oracle may take things too far to the right. Although I have noticed that non Microsoft technologies seem to demand a premium, eg and Oracle DBA is likely to be on 10/20% more than a SQL Server DBA. Here is a UK site to waste hours on what to learn next:

Could we influence the market? What if all Excel VBA developers suddenly started recommending and pushing and demonstrating VSTO/.net solutions (or Excel services) and talking them up as being so much better than VBA? I’m not saying they are of course. Sadly I remain to be convinced on the whole .net thing. It all just reminds me of’s_New_Clothes, maybe I’m too cynical (or maybe I’m just one of the numpties that can’t see the benefits).

My next post will look at project types because I’ve been involved in some interesting discussions about that recently.

Have you got any development plans? What things would you like to look at in the future (from a commercial POV rather than for interest/personal development)? I did a Flash/Action script project years ago, I’d love to do some more of that.



Spreadsheet training and qualifications

Thursday, 12th April, 2007

So who has got any???

There is an interesting discussion going on here:

I thought I would pull it into a main post for wider discussion.

I have a post nearly finished about skill sets but lets cover spreadsheets specifically.

I have an MCP in Excel (5.0) and VBA in 1996/7 (by self study), shortly before MS retired the exam.

I have an MCSD in VB5/6, and an in C#, some of which is vaguely relevant to spreadsheets (well the VB stuff for VBA, C# is not overly relevant). I have an MSc in software development which was very good for design and analysis type stuff, and all the programming was C++ which is handy for xlls.

I have never had any [formal] training in any spreadsheets. I think Eusprig, (or some attendees at least) have been looking at developing a course. And I think PODA have too. Personally I’m not sure, the best developers I have met have been self taught, and so have the worst. Theres self taught and theres untaught.

I think most business college courses now teach some spreadsheeting, personally I think they would be better teaching databases, but thats another discussion. Sometimes I wish the accounting bodies included spreadsheeting (or databases) in their programs.

So do you have any formal training relevant to spreadsheet use/development? Any relevant qualifications?



UK Office user group

Thursday, 12th April, 2007

I’m off to this event in Reading at the end of April:

If anyone fancies meeting up give me a shout.

Ross is going too:

It looks like I’ll be down too late for a drink on the Thur night.



Ribbon UI

Monday, 9th April, 2007

I had a couple of ribbon UI experiences over the weekend.
neither involved computers or Excel directly, which is probably appropriate for a bank holiday weekend.

We got some Cadburys creme eggs for Easter, anyway it turns out I’m the only one that likes them, result! Initially they were on the table with the kids eggs, then my wife ‘put them away’. Later on I went looking for them, not on the table, not in the chocolate box, not in any of the usual hiding places. Eventually I found them – in the egg basket with the real eggs! Now I’m not saying thats wrong, but it certainly reminded me of the frustration of trying to find commands in Excel 2007. It may be a valid classification or grouping, and logical to some people (word/outlook clickers!), but to others it makes no sense at all.

In the other ribbon story we visited my parents over the weekend. From what I understand my Dad is exactly the type of user the ribbon is aimed at. His opening comments? ‘Thanks for suggesting OpenOffice, its superb, does everything I need. Does anyone seriously need Excel?’

My thoughts exactly, The ribbon is aimed at a bunch of users who are more than happy with a free office suite from the cover of a magazine. Thats going to be a tough sell, even if a trial version of MS Office is on their new PC. Sadly in targeting these novice level users, the ribbon alienates Microsofts most loyal users, the power users that have taken the time and invested the effort to learn the 97-2003 interface. And of course there are plenty of people who need specific Excel features not found in other spreadsheets, the biggest one I suggest is VBA, which is not getting the attention it deserves (/requires!)  in my opinion.

Oh and I haven’t finished the creme eggs yet. I hope you are having a good break if you are having one.

Spreadsheet testing

Wednesday, 4th April, 2007

I have been asked by a couple of people to post about testing/reviewing spreadsheets, so we can discuss in more detail. So here it is:

One of the services codematic (ie me!) offers is to fix and maintain anyones spreadsheets no matter what state they are in. I charge a day rate, if its a total ‘frankensheet’ (credit to Rob Bruce for the perfect (polite) description), it takes me longer to sort it out so the client pays more. Nice spreadsheets are cheaper to look after, and sadly much rarer!

I presented at Eusprig last year about the approach and the tools I use to get to an estimate of how many days (or years!) it will take to fix/change the spreadsheet. This is just a review, and its pre-sales, so its not any kind of audit, and I take a fair chunk of commercial risk that my estimate is miles out and I’m working for peanuts. But hey, thats being in business, right?

The paper is here, the slides are here.

There is a page all about testing spreadsheets on codematic here. I’m not going to pretend I do all that stuff every time, but I have done pretty much all of it somewhere and I’ll do a fair few of those tests on any spreadsheet I build, or review, or take over from someone else.

What testing do you do? Any additions to (or deletions from)  that list?

Also, under what circumstances do you test? have you ever tested someone elses work and found errors and then had hassle sorting out whos going to fix it and whos going to pay? Do you protect yourself from this? and how?

Personally I work on an enagement letter basis, that is less than 2 sides of A4. This basically explains that after the product has passed user acceptance testing its their baby. I tend to fix any problems in my own stuff, but if they are using it in some wacky unexpected way that really represents an undisclosed requirement, then I’ll charge. All my work is based on relationships so its fairly flexible. Clients can do what they want with stuff I have worked on once delivered, I’ll keep repairing it and charging if they keep breaking it. If they were really bad I would move it into a compiled component. How do you set things up?

I dont do legal, and I dont issue copies of my insurances, these things do not represent the sort of working relationship I look for with clients. I walked away from a decent sized deal last year because this crap turned up at the last minute.

How do you operate? I suspect its quite different in different countries. How do you manage responsibility for potential errors as you transfer systems to and from clients and other consultants?

If I’ve missed any questions you wanted to ask around this subject please add them as comments.

As usual any other thoughts in this area very welcome.



Spreadsheet proliferation

Monday, 2nd April, 2007

On the distribution front many of us seem to be targeting a handful of users for most Excel/VBA applications. In itself thats quite interesting, but I think I missed a question.

How many copies of your spreadsheet application end up ‘live’ on the network?

Much of my stuff is for 1 or 2 users, but they may end up with 10, 20 or 30 versions per reporting cycle.

One example I mentioned in the distribution post was meant to be for 30 (named and trained) users. But at least double that use it, and they each probably have literally hundreds of copies. Maybe 1-5 are live at any time, but they probably create 20/30/40 or maybe more per year.

The thing I am thinking about is the maintenance headache when you realise you need to make a change to a live spreadsheet. If its just a couple of users, well then that seems easy enough, but actually if they are sitting on 50 copies that all need updating and assuring as consistent, then thats a bigger job.

This is one area where VSTO/.net could add some real value. But equally splitting data and logic into any separate components could help. Excel Services could also help, (if people are connected I think?).

I have a VBA based ‘patch’ tool that I use for scripting version management stuff so I can keep these distributed apps in line. It does pretty much everything, although I have found some of the VBA replacement stuff to be mildly unstable at times.

How do you manage this proliferation? Do you have tools? do you rely on good (de-coupled) design? Do you just re-issue a new master? and if so how do users move their existing analysis to the new version?

Have you found an approach that totally makes the issue go away? Have you been able to use it for justification to migrate a spreadsheet to another technology?



Blog stats

Monday, 2nd April, 2007

Just a quick FYI:

on sos so far (in 3 months ish):

55 posts


180~200 page views a day (during the week, just me mum on a weekend)

~50 feed readers.

As the comments for most posts generate at least one or two ideas for further posts, I don’t seem to be running out of topics which was my big worry.

If there is anything you would like us to cover please leave a comment. If there are any areas you think we should leave alone let us know that too. The current plan is to continue on looking at the issues we face as professional software/spreadsheet developers in current times and into the next few years.

I’m going to shuffle things around soon(ish) and arrange for other people to post. Ive got the domain and hosting stuff set up, as soon as I get through my other admin misery, getting this up and running is my next job. (dont hold your breath!)



Application distribution

Monday, 2nd April, 2007

How many users does your average Excel/VBA application have? and how widely distributed are they? and who are your users?

Most of my stuff is only for a handful of people. The most widely distributed Excel/VBA application that I look after is officially for about 30 people worldwide, although we know at least double that use it. Most of my apps are for 1 or 2 people in the same office. They may run multiple copies though.

I have done projects for operations teams and for marketing types, but in general most of my work is in finance for accountants.

Not really supporting multiple simultaneous users has only once been an issue, normally with a target user base of 1 or 2 its a bit of a non issue. What about you?

As I visit clients I dont see many spreadsheets that the whole team all use. I do plenty of ‘probably similar’ versions of spreadsheets, but the users are probably still from the same small group.

VB6/web/.net apps that I have worked on are much more widely distributed, but the Excel stuff tends to be very localised.

Some of the codematic addins have 000’s of downloads, but I’m thinking here more of custom work for clients.

Is your experience similar or have you got bazillions (Dick are you there?) of users?