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:
- =RESULT(7)
- =ARGUMENT(“PossError”,31)
- =ARGUMENT(“Default”,31)
- =PossError
- =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
Smurf on Spreadsheets 
Thursday, 3rd July, 2008 at 1:31 pm |
HI Simon,
I think you missed out line 6! should be RETURN() or HALT()
Thursday, 3rd July, 2008 at 2:05 pm |
Simon-
I think I’d isolate “some big thing” in its own cell before adding an XLM sheet. Btw, haven’t you heard no XLM support in Excel 14?
Andy-
The code never gets to line 6.
Thursday, 3rd July, 2008 at 2:12 pm |
Hi Simon,
Good example.
Just in case you want a challenge how about making it work for multi-cell array formulae?
Thursday, 3rd July, 2008 at 4:09 pm |
Andy
I did have a return in as it stops the editor messing with you while you are messing with the code. But as Jim says it returns from line 5 , probably good practice to leave one in? (probably better practice not to use xlm anymore ;-)).
Jim, I’ve used it so I don’t have to make structural changes to frankensheets, and in some messy latechange stuff I did myself. But in general I’d agree with you and break out the cell first.
I’ve heard the rumours but as MS are not talking about 14 yet I reckon they are still rumours at this stage. I would be surprised if XLM didn’t run, I can imagine them removing the ability to edit existing ones, but leaving the ability to run existing.
And anyway they promised not to remove it till all features were available in the OM, if that includes performant VBA UDFs with auto calc then happys days.
Charles I’ll have a look, not if its too hard though.
Thursday, 3rd July, 2008 at 4:13 pm |
There has been a couple of threads on PeachEase Excel L involving an Excel “developer” who doesn’t use VBA but only uses XLM.
Thursday, 3rd July, 2008 at 5:04 pm |
Simon
My only current use of XLM is to get a list of all open workbooks including addins as VBA’s workbook collection doesn’t include addins.
But this function looks like it might have a place in my Personal.xls.
You are missing a final closing bracket in line 5, and also if you copy and paste into a macro sheet, your smart quotes in lines 2 and 3 need replacing with ordinary quotes.
Curiously, when I tried the function with a single vlookup in a new workbook, it clearly didn’t get added to Excel’s dependency tree correctly and showed the error result of the previous calculation cycle (using Excel 2003 SP3).
Friday, 4th July, 2008 at 12:47 am |
For those of us who came to Excel late in life, don’t know XLM, and don’t propose to learn it, here is a VBA array UDF that works pretty fast:
Function IfErr(CheckRange As Variant, ErrVal As Variant) As Variant
Dim ErrA() As Variant, TempVal As Variant
Dim NumRows As Long, NumCols As Long, i As Long, j As Long
‘ Check for a single cell range
If Not IsArray(CheckRange) Then
CheckRange = Array(CheckRange)
TempVal = CheckRange(0)
ReDim CheckRange(1 To 1, 1 To 1)
CheckRange(1, 1) = TempVal
NumRows = 1
NumCols = 1
‘ Else convert the range to an array and get the size
Else
CheckRange = CheckRange.Value
NumRows = UBound(CheckRange) – LBound(CheckRange) + 1
NumCols = UBound(CheckRange, 2) – LBound(CheckRange, 2) + 1
End If
ReDim ErrA(1 To NumRows, 1 To NumCols)
For j = 1 To NumCols
For i = 1 To NumRows
If IsError(CheckRange(i, j)) = True Then
ErrA(i, j) = ErrVal
Else
ErrA(i, j) = CheckRange(i, j)
End If
Next i
Next j
IfErr = ErrA
End Function
If CheckRange is entered as a single cell, and the function is copied down over 10,000 rows, it takes about 30 seconds to recalc in XL2007 with the editor open, or 10 second with it closed.
If checkrange is entered as the full 10,000 row range, and the function entered as an array function, recalculation is almost instantaneous with the editor open or closed.
One drawback in XL2000 is that the maximum array size seems to be about 5460 rows, but it’s still a useful function for those who don’t want to move to 2007.
Friday, 4th July, 2008 at 10:21 pm |
General udfs need to handle arbitrary arrays, which includes BOTH 1D and 2D arrays. That requires checking how many dimensions the array has, and that’s something the VBA udf offered doesn’t.
This has been covered many times before in various Excel newsgroups, so I won’t repeat anything here. I will point out that one could add an optional argument for which error types to trap or which to propagate. In XLM you could use CHOOSE and ERROR.TYPE to determine that. It’d require a bushy If..Else If or Select Case in VBA.
Saturday, 5th July, 2008 at 12:11 am |
Harlan – I usually stick:
arrayname = getarray(arrayname)
at the top of my array UDFs. Getarray is a function that will convert a range (single cell or multi-cell) or any type of array into a 2D base 1 array. I just left it off in this case to keep things simple.
Anyone interested can pick up getarray() here:
http://newtonexcelbach.wordpress.com/2008/03/04/ranges-and-arrays/
Monday, 7th July, 2008 at 1:52 am |
So a subprocedure call as well to really slow things down? If you’re going to use multiple procedure calls anyway, it’d usually be more efficient to use different routines for 1D and 2D arrays rather than convert 1D arrays to degenerate 2D arrays.
Better to check whether general variant arguments were scalars, and if so process them immediately without array overhead. Also, there are obscure places where Excel distinguishes between scalars like 1 and arrays like {1}, so usually best to return scalars when passed scalars and true 1D arrays when passed 1D arrays.
Use of .Value rather than .Value2 further indicates you’re not overly concerned with execution speed efficiency.
Monday, 7th July, 2008 at 3:30 am |
>>
So a subprocedure call as well to really slow things down?
<>
If you’re going to use multiple procedure calls anyway, it’d usually be more efficient to use different routines for 1D and 2D arrays rather than convert 1D arrays to degenerate 2D arrays.
<>
Better to check whether general variant arguments were scalars, and if so process them immediately without array overhead.
<>
Also, there are obscure places where Excel distinguishes between scalars like 1 and arrays like {1}, so usually best to return scalars when passed scalars and true 1D arrays when passed 1D arrays.
<>
Use of .Value rather than .Value2 further indicates you’re not overly concerned with execution speed efficiency.
<<
Yes, the .Value should be .Value2.
Monday, 7th July, 2008 at 3:49 am |
OK, using greater than and less than symbols to quote text in a WordPress blog wasn’t a great idea. I’ll try again:
“So a subprocedure call as well to really slow things down? If you’re going to use multiple procedure calls anyway, it’d usually be more efficient to use different routines for 1D and 2D arrays rather than convert 1D arrays to degenerate 2D arrays.”
The original array function was almost instantaneous over 10,000 cells, compared with 10 to 30 seconds for a similar UDF working on individual ranges. When getarray was inserted at the front it was still almost instantaneous.
“Better to check whether general variant arguments were scalars, and if so process them immediately without array overhead. Also, there are obscure places where Excel distinguishes between scalars like 1 and arrays like {1}, so usually best to return scalars when passed scalars and true 1D arrays when passed 1D arrays.”
The purpose of the getvalue routine is to return a consistant array, no matter what is passed to it, to simplify the calling routine. No doubt there are times when it would be worthwhile to keep scalars as scalars in the calling routine, but I find getvalue useful the way it is.
“Use of .Value rather than .Value2 further indicates you’re not overly concerned with execution speed efficiency.”
Yes, .Value2 would be better than .Value
Monday, 7th July, 2008 at 8:00 am |
Speed arguments usually can’t be settled on different systems, so I’ll let that point pass.
Consistent processing is for whose benefit? Not the calling formula’s.
To repeat, there are places where Excel distinguishes 1 from {1}, meaning there are places where 1 leads to the expected/correct result and {1} leads to an error, often a difficult to locate error.
Other than catering to the laziness of the udf developer, when is there a good reason to force any passed argument into a 2D array?
Monday, 7th July, 2008 at 8:50 am |
Can you give me an example of where {1} leads to an error, but 1 doesn’t? If there is something I’m missing here I’d like to correct it.
As for whether it is a good idea to force passed arguments into a 2D array, it seems like a good idea to me, but anyone who disagrees is free not to do it. The point of my original post was that processing an array once in a UDF is much faster than passing individual cell data to the UDF every time it is called, and I don’t think there is any doubt about that.
Tuesday, 8th July, 2008 at 4:08 am |
Not quite the same thing,
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_frm/thread/5ed958eb02aa66af
Basically, other functions with arguments calling INDIRECT or OFFSET function with array arguments can cause trouble. A contrived example,
X99:
=SUM(OFFSET(A1,SMALL(IF(B$1:B$10>x,C$1:C$10),ROW(X99)-98),,2))
and
Y99:
=SUM(OFFSET(A1,SMALL(IF(B$1:B$10>x,C$1:C$10),ROWS(Y$99:Y99),,2))
usually don’t return the same thing.
Tuesday, 8th July, 2008 at 4:11 am |
This might be an example
If you use Application.MMult to multiply two vectors together that return an array such as {1} – to convert it into a scalar 1, I wrap Application.Sum round it
Monday, 20th April, 2009 at 4:54 pm |
Line 5 is missing the last closing paren.