Archive for March, 2008

Is Microsoft listening?

Wednesday, 12th March, 2008

I was starting to think they are. What with the decision to default to standards mode in IE8, and a glint of useful openness etc.

Then I read this…

So now I’m wondering if my crusade for a usable UI in Excel 14 may be falling of deaf ears, and really what needs to happen is some sort of anti-trust action?

Eusprig topic suggestions

Tuesday, 11th March, 2008

I wasn’t planning on submitting a paper for consideration at this years Eusprig. But now I’m thinking I should. The deadline is looming however and I havent really got an obvious topic, thats where you come in…

The conf title is ‘in pursuit of spreadsheet excellence’ and papers need to relate to that somehow.

Last year we did spreadsheet hell, covering some of the cultural issues around corporate spreadsheet use.

The year before I did the process I use for reviewing client spreadsheets

The year before that I did some of the strengths and weakness of spreadsheets compared to other technologies.

[one possible one is User defined functions in XLM or something similar around function definitions]

So my question is, is there a topic you think I should cover, or that we should cover as a group?

Many of the Eusprig prior papers have been archived on-line here.

Please leave any suggested topic areas as comments, thx

(anything that incorporates my new logo suggestion is especially welcome!)



New Eusprig logo

Monday, 10th March, 2008

I’ve been thinking for a while that the Eusprig logo is a bit tired looking and could do with a revamp.

The problem is thinking up a decent one, the obvious connection is with grids or squares or something. I found this what do you reckon?



more ribbon fun

Monday, 10th March, 2008

Got an ‘anonymous’ tip off about this excellent article here.

The author is a well known and respected quant who has contributed some excellent xll stuff to the community. He comes to a similar view on the ribbon as many of us, but raises some interesting points along the way.

The most interesting one, and one that hadn’t fully occurred to me is the impact of alienating power users. I had wondered if power users had the power to sway purchase decisions, and the general view seemed to be that the people in power in most orgs were probably not spreadsheet gurus (but they might listen to them). So direct influence = no, indirect = maybe.

I had missed the ‘role model’ aspect. He uses the example of Photoshop with pro photographers. Adobe clearly gain some non pro sales from folks wanting to emulate the experts, and the fact that most magazine articles demo Photoshop helps too.

I’m not sure the spreadsheet market is quite so competitive, but it is becoming more so. What I mean here is that Windows machines come with a basic graphic editor (Paint), but no basic spreadsheet. But Open Office and the Google offerings are starting to gain momentum…

As a power user (‘role model’?) I’d prefer to invest in learning the products that respect that investment. This has to be balanced with progress/improvements of course. Microsoft seem to be aiming somewhere different to me in this regard but then rapid obsolescence is the basis of their business model I guess. I don’t think that is conducive to a vibrant expert community, or expert consulting business models though.

A boost in on-line resources targeting OOo Calc could quickly undermine the Excel ecosystem I reckon. And the power users/role models, those that the interface shuffle hurts the most, are the providers of much of that ecosystem.

BTW my main ribbon objection is shuffling the commands, (and what that represents in the big picture) all the other stuff I would live with. It is after all just a fat clumsy toolbar.

And those of us who were on the beta program will no doubt recall the impact of the ribbon and the response it got, although I guess thats all covered by NDA?

I remain to be convinced that Office 2007 is booming sales-wise, if it were, there would be proper hard facts to back it up. If anyone has links please leave as comments, ta.



Hey Scripting Guy – RTFM

Sunday, 9th March, 2008

In perfect timing with my last post Ken Puls sent me a great link.

This is a superb illustration of a couple of points:

  1. Invest in learning or stick with what you know?
  2. Non Excel developers seriously under-estimate the power of the market-leading spreadsheet.
  3. Excel skills are ignored everywhere.

For someone with SGs background (scripting guy, Excel ignoramus) the approach probably makes the most sense. There are potentially better approaches depending on a bunch of stuff. Conditional formatting jumps out as a candidate if it is to be dynamic.

So RTFM is maybe a bit harsh in this particular case I think, but it is a shame to see MS folks treating Excel like some dumb grid when it is so powerful.

I guess thereĀ  are plenty of things I do badly rather than learn how to do it right. And there are plenty of things I actively avoid getting into at all (Java for example).

What do you think? Too harsh, too severe?




Thursday, 6th March, 2008

I was doing a bit of mechanicing the other day. As with any practical job I do, the first x amount of time is spent searching for the tools and materials.


On this particular occasion though it was more like x times 5 time spent ratching around looking for stuff.

The job involved mainly 10mm bolts (approx 3/8″?). I had no bother finding a 10mm spanner, in fact I had a couple. But a socket would have been much better for this job. Pictured is an 11mm socket – I found 2 of them, but I could not find the 10mil. In fact I think I have (had) at least 2 of them too, although it looks like I need another.

I spent a fair bit of time wielding the spanner thinking how much easier it would be with the socket. Both when I took it to bits, and again when I rebuilt it.

I find software dev to be similar, whether to go with the known, known to be poor approach, or spend an unknown amount of time and effort searching for a better way.

How might you describe that?

If (time/effort poor way) > (time/effort best way + time/effort to find it) * (1/chance of finding it)) then best way else poor way.


If (time/effort saved best way – time to find it) * chance of finding it > 0 then try the best way.

In my case I reckoned the time taken to find it (if possible) would be way more than the saving so I just did it with the spanner.

I guess there is also a ‘preference’ factor (prefer researching to working badly), and maybe a ‘pressure’ factor too?

Do you have the same though process? how would you describe yours?



Function structure

Wednesday, 5th March, 2008

Its quite common to require opposite pairs of functionality. Things like show/hide, or hide/unhide, or protect/unprotect, register/unregister, load/unload… you get the idea.

There are 2 basic approaches to this:

  1. have 1 function that takes a true/false parameter to tell it to do or undo the action
  2. have 2 clearly named functions whose names are good opposites, as above.

Which do you prefer?

I personally prefer the 2nd (I think) and I would say thats usually the way we interact with COM object models. I notice a lot of C code and Win API stuff uses the first though, so I thought about it a bit more.

I think the second (2 separate methods) is probably easier to understand, but on reflection the first is maybe more powerful. Or at least allows more concise code.


if msgb = vbyes then
end if

to the more concise = vbyes)

I guess the hard bit is getting a method name that makes sense with a true false parameter. Maybe a property gives the best of both worlds?

menu.visible = (msgb = vbyes)

What do you prefer? in your own code? in working with others (eg libraries)?



VBAIDE refresh in Office14

Tuesday, 4th March, 2008

If ‘VBA is forever’ and MS are ‘fully committed to it for the foreseeable future’.

And bearing in the mind the IDE has not been touched in 10 years (10 years FFS – what were you doing 10 years ago?). (I am not counting getting the mousewheel to work in 2007 – sorry)

What improvements can we expect in Office 14? Or more realistically, what improvements would you like to see?

Here is mine:

  1. Please please please – no dumbassed ribbon
  2. Please – no breaking VBA ‘improvements’ (parameterised constructors would be ok)
  3. Please – no unrealistic deployment requirements.
  4. code snippets
  5. Procedures in the project explorer
  6. Tabbed editor
  7. Perhaps a VSTA style editor, but writing proper VBA.
  8. window docking that is controllable

Of course it will continue to be challenging for us devs to believe MS has any commitment to VBA if the IDE is left to stagnate.

What would you like to see?

Do you think there will be any useful changes whatsoever?



Why VBA is so rubbish

Tuesday, 4th March, 2008

The last VBA post may have given the impression I’m some kind of VBA fanboi. I think it has its place, but it’s far from perfect. I put my definition in that post so I wont repeat it here.

I think its important to keep in context of what is out there live in the real world now, so I’m not going to suggest another language is better. In pure terms python (or your personal favourite language) maybe a better language for users to do basic automation etc. The reality is there is a lot of VBA code around, and more gets written everyday. I think the language would benefit from a few modern features, perhaps to support OO more fully, but not at the cost of backwards compatibility.

I’m also going to skirt around the issue of user competence. You get crappy code in every language, some languages may encourage it more, or may be more attractive, or more accessible, to developers of limited skill and experience.

It has been suggested that giving something as powerful as Excel/VBA to an unskilled user is the equivalent of giving a loaded rifle to a child. The reality, I would suggest, is a little less dramatic, as far as I know, no one has ever been killed by a poor quality spreadsheet, and very few companies have gone bust because of one. Yes SocGen just lost a stack of cash, but a person did that by trading badly, not a spreadsheet.

VBAs biggest fault?

I’d say its too easy to go beyond its design envelope.

For simple automating Excel and standalone complex worksheet functions (eg: the stuff Mike talks about) I think it is great. Its easy to slip from there to 10/12/14 KLOC systems, at which point I think VBA can work against you.

The other side to that argument is perhaps there is no realistic migration path, or no realistic alternative. That may or may not be true, but either way is not a fault of VBA.

Second – The editor. Frankly its so arcane I think Integrated Development Environment is a bit rich. If this just got updated to the VB6 one (which has more visible object model) add-in devs would fix everything else. Ideally though I’d like a VSTA style editor that writes proper VBA, or something completely compatible, in Excel 14.

That would also fix my third biggest fault – Microsofts lack of public love for VBA. Many devs are worried about its future, and hollow verbal assurances don’t cut it. A significant investment in upgrading the IDE, or replacing it with the VSTA one would settle the uncertainties for many.

Speeding up the call interface would be excellent too. As would allowing VBA UDFs to participate in multi-threaded calculation.

VBA’s reputation as a meddlers monster creation system (/toy language) hardly makes it the sort of thing you want to own up to at a party. The political correctness bureau solution to that is the change the name (eg ‘disabled’ went to ‘people with disabilities’ now moving to ‘differently abled people’) in general the ‘name’ gets longer as they use more circuitous language. So perhaps renaming VBA to something like AAS (Application Automation System) would fix any stigma? (of course it would have to be MAAS or MOCAAS (Microsoft Office Client AAS)). You get the idea.

Any other suggestions? the ruder the better.

What would be your top 2 or 3 weaknesses of VBA?



Monday bounce

Monday, 3rd March, 2008

Every Monday I get a ton of ‘message undeliverable’ returns from all over the place. Some spammer is clearly spoofing my domain name in their spam as the return address. Thanks for that…

Anyone else get that?

Also anyone else ever wonder how something as fragile and insecure as our current email system got to be so business critical?

I’m finding more and more messages to me and from me are not getting through spam filters, anyone else see that? anyone beginning to lose a bit of faith in the system?

I find more and more I put receipts on stuff, and often even follow up with a call.

Lots of replies to proper emails I get have [spam] as part of the subject, which seems a bit harsh.

Does everyone else find the system extremely reliable or is everyone seeing the same issues? have you seen other ones?