Archive for July, 2008

Sell Codematic

Wednesday, 16th July, 2008

I got an email today asking if I wanted to sell Codematic.

All I have to do is call their premium rate number to arrange a valuation.

I deleted it as spam, Mrs S thought it might be someone trying to recruit me – bless her, what an optimist!

Makes a change from the usual junk I guess.

Have you had any novel spam recently?

cheers

Simon

Eusprig 2008 report

Monday, 14th July, 2008

Another very good conference, and with good attendance (about 60 people I think).

I won’t go through all the papers as I’m sure they will get themselves on-line in due course.

Allied Irish Bank did a presentation about what they did to bring their spreadsheet usage under control. Most financial institutions wouldn’t even own up to having spreadsheets, never mind having a spreadsheet problem. So the AIB openness is very welcome. Having suffered from a monumental spreadsheet control blunder a few years ago is a factor in that. I reckon they are probably leading the field in spreadsheet control now.

Jamie Chambers did a talk on another End User Computing control project. Its always interesting to hear the challenges of real world projects in this area. These two papers between them provide most of the framework needed to start an EUC control project, and some great snippets of things to ensure and things to avoid.

Dick M did a talk on his approach to developing and delivering spreadsheet based systems. Which had a good focus on longevity and ease/lack of maintenance.

Brian Bishop gave a good talk about their work recording how people test workbooks. David Colver talked about the amount of internal checks some of the different modelling consultancies include. The results varied enormously.

This year there were 2 tracks, and speakers had longer slots. I liked the longer slots, but didn’t like having to choose between academic and commercial. I have always enjoyed the inventiveness and originality of the academic papers. Even if some of it is completely unimplementable. I chose to stick on the commercial track, so I’ll just have to read about the latest blue sky academic thinking.

We found a great late night bar in Greenwich which was handy as the pubs mainly shut at 11, quaint, but frustrating. 2 am is far more civilised (until the morning of course).

I was thinking this would probably be my last Eusprig, but actually the atmosphere was quite lively with plenty of providers of spreadsheet services in attendance. Not too many potential customers, but it was good to chat with others operating in the same field. So I may well be up for next year, may even submit a paper.

Did you go?

cheers

Simon

Why I prefer my apps local

Wednesday, 9th July, 2008

Here is one of the reasons (the other being responsiveness) that I like my apps installed locally.

Now I’m not saying I’m perfect (did I ever mention the time I tried to replicate my laptop hardrive and inadvertently reversed the power polarity (used the wrong lead) and fried the drive?) but I’m happy to take responsibility for keeping my systems up and running and pay the price when I fail.

The whole issue about this cloud nonsense is I can only use it when those responsible for it make it available. I know 4 9’s (99.99%) uptime is better than I can achieve, but I still prefer the control I have with my apps and data locally. Never mind our flaky power supply which make UPS or laptop essential, and delicate t’intarweb connections, and overall I don’t think I could rely on something remote for important stuff.

Could you?

Eusprig 2008

Tuesday, 8th July, 2008

Well its coming in a couple of days. Content looks good as usual.

This will be my 5th or 6th year attending and I have spoken at 3 I think, but this will be probably be my last Eusprig.

Looking around I get the feeling that the ‘spreadsheet quality’ message is falling on deaf ears. I don’t know if thats because everyone is now doing spreadsheet quality, or if its because its a message people don’t want to hear. But I have my suspicions…

I also half wonder if physical conferences have maybe had their day too? Although some of the software dev ones seem to be booming (although they often have a heavy training focus).

If you have been to previous Eusprigs but not going to this one, why?

If you have never been why? what would need to change for you to prioritise attending in future?

cheers

Simon

(btw no more posts this week as I’m going to a conference!)

IFERROR

Thursday, 3rd July, 2008

Do you find yourself writing:

IF(ISERROR(some big thing), 0, some big thing)?

One of the more useful features of Excel 2007 was support for IFERROR(some big thing, 0). The ability to wrap up errors and provide a default value. As well as making your functions easier to cope with this also halves the calc time as ‘some big thing’ only calculates once.

Bullen Bovey and Green used IFERROR as their example of xlls in PED. So if you have that you should have the iferror xll that works in 97-2003.

Personally I think IFERROR is a bit too broad brush because it also wraps #REF! errors which could be a sign someone just killed you spreadsheet. But thats not the point of this post, we’re talking XLM.

You could write IFERROR in VBA and suffer that calling overhead, But what if you don’t have PED, or time for VBA?

TaDaa

XLM

an XLM IFERROR is as fast at the xll version (nearly) and can be included with every workbook that needs it. And the code is simple:

Right click the tabs and insert a macro sheet.

put this in from A1 downwards:

  1. =RESULT(7)
  2. =ARGUMENT(“PossError”,31)
  3. =ARGUMENT(“Default”,31)
  4. =PossError
  5. =IF(ISERROR(A4),RETURN(Default),RETURN(A4)

Click on A1, Insert>>Names>Define, call it IFERROR, make sure to select function in the bottom right section of the dialog and click OK.

Back in a worksheet enter =IFERROR(someref, some other ref)

And job done. Charles Williams suggests that hiding the macro sheet can boost performance.

If you compare performance of a couple of thousand cells worth of these formulas with a VBA version you will be impressed, especially if you prefer auto calc (I do).

To explain the above macro:

1 – a function should have a return type, this says what it is, details in macrofun.hlp

2 & 3- each argument needs a type and a temporary ‘variable’ to holds its value

4 – take whatever was passed in in the first argument (PossError) and stick it in a cell

5 – Do your IF(ISERROR stuff on that cell, so it only needs calculating once.

Its all very close to normal worksheet Excel, once you get over some of the arcane syntax. But in fairness thats Excel too – who’d know that SUBTOTAL(1,..) means average.

Have a go and let us know how you get on. If anyone has any suggestions or improvements or alternatives for the above let me know. I did try a few other ways but this seemed the best performance where PossError was a chunky formula. And its easy to explain as its similar to the the worksheet formula way.

cheers

Simon

MS file formats

Tuesday, 1st July, 2008

I see here MS have published the specification for the BIFF (.xls) file format. Just by a quick glance it looks they have also released the first ever info on the mysterious .xlb toobar files. The file format stuff is updated from 97 I think, which was the last time it got published.

I wouldn’t rush to print it though, its a thousand pages!

Blimey, they have also published the format of VBA projects, another first.

Are you planning any apps that use the file formats directly?

(I have one for removing VBA projects, but its a bit aggressive – I can now find out where I should be deleting from, hopefully).

cheers

Simon