IFERROR

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

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

UK Excel User Conf

I’ve had a few enquiries about a possible event this year.

I have no current plans to organise an event, but would be happy to help anyone else who fancies it. I’m not sure if anyone else is currently working on an event?

If you are up for it give me a shout. Also if you work for a well financed corporation who might stand to gain by raising the profile of Excel and would be interested in underwriting/sponsoring/hosting the event then get in touch too.

cheers

Simon

Office News

I often follow the Office News section on Office Zealot (is it just me or is that the slowest site on t’intarweb since they moved it to SP?).

They have lots of good content, but I have noticed actually very little of it really relates to Office. I don’t think that’s a failing on their behalf, I actually think there isn’t very much Office news.

What do you think?

Is there a torrent of fresh exciting Office news somewhere I’m missing. Or is it about as exciting as the pens and paper it replaced 20 odd years ago?

cheers

Simon

FrankenPoint

Great post here especially for those of us concerned that SharePoint is stealing some of the limelight from the real office apps.

By real I mean the tools that people use to actually do their work, rather than talk about/ share work.

(Excel - yes, Access - yes, Word - spose so, Outlook - no not really, etc)

Are you using/installing sharepoint?

cheers

Simon

Campaign for XLM

I have been doing a load of XLM work the last few days. Its brilliant!

So many of us have made the leap to VBA (and it is a leap from spreadsheet formula type stuff) that XLM is completely overlooked now.

Thats a shame because for real User Defined Functions, once you get over the =ARGUMENT/=RESULT hurdle XLM is way way more accessible to your average Excel user. And its a boatload faster than VBA too.

I do remember when I first looked at XLM many years ago and it made my head explode. A decent book would probably have fixed that though. Now when I use it it seems more intuitive than VBA, and much much less verbose. Ages ago I posted about having User Defined Functions sheets, I reckon XLM macro sheets answer most of my requests.

I have always avoided VBA UDFS because they kill performance when calculation is set to auto (and I’m not a manual calc fan). XLM UDFS on the other hand are fast. I have had thousands in a workbook on auto calc - you just can’t do that with VBA.

The only real downside is lack of folks with XLM skills, I’d feel bad about locking a client into something that so few people could maintain.

Anyone else still using XLM? Any one interested in a post about XLM UDFs? Got any simple VBA UDFs you want converting to XLM? (leave VBA as a comment)

cheers

Simon

Leave as is

Eusprig season is upon us, its time to get on our high horses and poke fun at other peoples blunders. (2 things instantly spring to mind from that sentence:

1. its always time to poke fun at other peoples blunders
2. Out of my office window I see someone has put a horse in our nextdoor neighbours garden)

Anyway back on topic, I was sent this screenshot from a City financial institution:

cheers

Simon

Eusprig 2008

Its coming soon - 10-12 July.

Biggus Dickus is one of the presenters, having seen him speak before I’m looking forward to his presentation. Nearly as much as I’m looking forward to the timekeeper trying to get him off after his allotted time! I’m thinking of starting an Encore, Encore! chant so we can hear his wrap up.

I think it will be another excellent event this year, having reviewed some of the papers. there still seems plenty of new interesting research and thinking around spreadsheet quality.

I think there will be some sort of pre conference event on the Wednesday with possibly some sort of spreadsheet safety test, or error hunt. Worst score buys the beers for the night! (expect a big night)

If you are planning on coming along leave a comment with time, so we can arrange to meet up for drinks. I’m planning on getting there Wednesday early/mid afternoon I reckon. I think this will be the third time in Greenwich so I’m hoping to get lost slightly less than previously.

cheers

Simon

Freee eee pcs

I had heard about broadband providers potentially offering Eee pcs free to subscribers, but this article looks a bit deeper.

What is frightening is that I was planning (in the loosest sense ;-)) to move more into Web development, and one of my big marketing ideas was to give away a (Eee) pc with each decent sized project. Looks like that wasn’t as original as I thought.

What do you think, will these sub notebooks become the freebie that gets dropped on you for every service you subscribe to? A bit like those crappy ISDN modems that I have stacked in my ’should have been binned ages ago’ basket.

I suppose that exactly what happens with mobile phones, its a few hundred quid bit of kit that gets given away with a 12/18 month contract. The only issue is whether there are services as lucrative as mobiles that are applicable to sub notebooks (Rob objection to laptot noted and accepted). That are better than sitting in Starbucks drinking their coffee and using their broadband.

I’d be very interested in a dual sim service where I get one for a handset and one for a sub nb. All for a single sensible monthly fee.

I resent paying 2 monthly rents when we don’t even have mobile reception at my house. (I’d be very very interested in a sensibly priced mobile phone that can use my landline or internet connection when in range of my home router. BT claim to have one but it didn’t meet the sensible price requirement last time I looked.)

Would you be interested in combining your mobile phone and pc connections/contracts for mobile internet surfing?  and would you need a ‘free’ Eee class bung to make up your mind?

cheers

Simon

Excel jobs

I tripped over a couple of open jobs in the Excel team the other day from here. You have to select Excel then search as its all session based so if I gave a direct link it would be expired. You did know Microsoft are not world leaders in search right?

Sadly the daily commute from Carlisle would make Redmond almost (!) unrealistic for me - I would have to set off before I got home… but I reckon working in that team would be great. All the folks I’ve met have been smart, enthusiastic and really switched on.

The VSTO team are also looking, that would be pretty interesting work too I reckon. I’ve met a few of them too and they seem to be going places.

I couldn’t find any effluent UI jobs - I reckon it would be easy to shine in that team -’yes I have actually used MS Office in the real world…’. Or perhaps the jobs are there but hidden away using the same ‘logic’ they used in Excel. Self selecting?

I didn’t check for Access jobs, but that could be good too, it seems to have a (deserved) new lease of life recently.

Let us know if you get lucky

cheers

Simon