UDF and Div/0!

[David Evans]

Ninjas!

I've inherited some massive, convoluted spreadsheets which I cannot trash!

One of the most annoying factors is the prevalence of Div/0! errors, especially when the worksheet is "empty" ...

Would it make sense to create a User Defined Function to suppress Div/0! ?

There are literally hundreds of these things in these sheets and although I can recreate many of them through pasting, its still a lot of work .....

Dai

 

[Smallman]

Hi Once again Dave

No need to create a UDF when normal formula will do the job for you. Just change the formula to say if there is a 0 in a cell then display ""

If you want to clear these errors (not recommended) then maybe some coding.

Option Explicit
 
Sub RemErr()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange.SpecialCells(3, 16)
        If rng = CVErr(xlErrDiv0) Then
            rng.ClearContents
        End If
    Next
End Sub

But this would leave you exposed if data gets entered where there once was formula.

So not sure what path you are travelling down.

Take care

Smallman

 

[David Evans]

Hi Smallman!

Thanks for taking the time to reply.
This is not a massive need on my part, but I'm getting very interested n UDFs!
I have plant of cells in this legacy sheet that are basically =A2/BA7. of course if BA7 is a zero, it returns the "DIV/0!" and these sheets are full of them!

I was wondering ( and wandering ) if instead of writing =IF(ISERROR(A2/BA7),0,A2/BA7) i could create a UDF named DAI , and thus =DAI(A2/BA7) that would accomplish my goal of eradicating DIV/0!.

Anyway thanks again for you help - I'll be posting my next UDF challenge shortly, but I want to get home first to see my daughter first!

D

 

[Smallman]

Hi Dave

I don't' use UDFs at all. I have seen very few instances where you can't create a custom formula and UDFs are memory hogs. I am not a big fan that is not to say that they do not have their uses, I just always find a way for native Excel to get the job done. It is faster and easier for others looking at my work to follow. I can understand the fascination with UDF's but simple is always the way to roll.

Take care

Smallman

 

[jeffreyweir]

Further to Smallman's comments, if you have Excel 2007 or later, then instead of
=IF(ISERROR(A2/BA7),0,A2/BA7)
use
=IFERROR(A2/BA7,0)
...which is twice as efficient.

In terms of writing UDFs, read Charles Williams series on efficient UDFs. You might not understand it all at first, but you will find yourself coming back to it again and again:

http://fastexcel.wordpress.com/2011/05/25/writing-efficient-vba-udfs-part-1/

http://fastexcel.wordpress.com/2011/06/06/writing-efficient-vba-udfs-part-2/

http://fastexcel.wordpress.com/2011...vba-udfs-part-3-avoiding-the-vbe-refresh-bug/

http://fastexcel.wordpress.com/2011...rences-arrays-calculated-expressions-scalars/

http://fastexcel.wordpress.com/2011...-vba-udfs-part5-udf-array-formulas-go-faster/

http://fastexcel.wordpress.com/2011...a-udfs-part-6-faster-strings-and-byte-arrays/

http://fastexcel.wordpress.com/2011...a-udfs-part-7-udfs-calculated-multiple-times/

http://fastexcel.wordpress.com/2012...usly-calculated-value-from-the-calling-cells/

http://fastexcel.wordpress.com/2012/01/31/writing-efficient-udfs-part-9-an-example/

http://fastexcel.wordpress.com/2012...10-volatile-functions-and-function-arguments/

http://fastexcel.wordpress.com/2012...column-references-in-udfs-used-range-is-slow/

 

[David Evans]

Thanks Jeffrey - I shall read Mr Williams with interest and appreciate your IFERROR - which I did not know existed .... duh ...

 

[jeffreyweir]

Also, here's another hint re common bad use of IF. This is actually draft content from my forthcoming book, Excel for Superheroes and Evil Geniuses




Use IFERROR() to half the processing cost of error checking
Many of you who cut your teeth on Excel 2003 or earlier are probably still using this to handle errors:
IF(ISERROR(Some_Complex_Formula), Alternate_Formula, Some_Complex_Formula)

A word to the wise: Don’t. Why? Because Cobbling together an IF and an ISERROR is computationally intensive:

· Excel evaluates the Some_Complex_Formula bit purely to see if it returns an error or not. It doesn’t even bother to remember the actual answer…it just wants a straight “Is this an error…Yes or No?

· If there is NO error, then Excel thinks “Great, no error. Now…what was the answer again? Damn, I didn’t think to store it. *Sigh*. I’ll just work it out again.”. So even though things went swimmingly well with the Some_Complex_Formula bit the first time around, it gets evaluated again, which is damn resource intensive, if not downright irresponsible.

· If there IS an error, then the Alternate_Formula bit gets evaluated instead. That Alternate_Formula is often just a zero in the case that you’re trying say to work out some percentages, and are trying to avoid a Divide By Zero error. But even in this very simple Alternate_Formula case, Excel has had to evaluate TWO functions in order to return that zero: an ISERROR check on theSome_Complex_Formula bit as well as an IF formula. Again, pretty resource intensive.

Now here’s the thing, my erroneous friends: Microsoft introduced a new formula IFERROR in 2007 which is much more efficient: IFERROR(Some_Complex_Formula, Alternate_Formula). But for some unfathomable reason,lots of you still aren’t using it! *Jeff wagging finger* Tut, tut, you naughty, naughty analysts.

This new IFERROR formula rocks, because it cuts the processing down by more than half:

• the Some_Complex_Formula bit is only ever evaluated once
• The Alternate_Formula bit only gets evaluated if the Some_Complex_Formula bit throws an error.
• Excel only needs ONE function to do all this, as opposed to the ISERROR and IF duo it must contend with in the previous example

 

[David Evans]

Hey Jeff - stop wagging your finger and get back to writing that book - I just bought it and I'm in a hurry ....

 

[jeffreyweir]

Cool. The book is really pitched at people such as yourself Dave...smart cookies who are already in the innards of Excel, but who might have missed a few key lessons along the way, or missed some of the new functionality that MS have snuck into the box since 2003.

And I have met many many people way smarter than me that have missed things like IFERROR because they don't have as much time as I do to read blogs for the sake of it.

But the main reason I'm writing the book is that in 99.99% of cases, noone has taught users how to program Excel (and I'm not just talking about VBA here). Noone has even told the users that they are programmers, or that formulas are subroutines. (There is a really, really, really good presentation on this at http://www.slideshare.net/Felienne/spreadsheets-are-code-online that everyone reading this should check out.)

So I believe we need to consider where end users are on the spectrum from user (someone who uses spreadsheets designed by others) to programmers (someone who puts spreadsheets together, or who makes changes to existing spreadsheets designed by others). And we need to give people towards the user end some tools that allow them to efficiently do what a competent programmer can do.

Also, some explanation about the book title: Excel for Superheroes and Evil Geniuses. The 'Superhero' bit refers to knowing how Excel works under the covers, and knowing what tools the interface puts at your fingers right out of the box. And the 'Evil Genius' bit refers to leveraging off technology (Point-and-Click macros as well as UDFs) to do things with a single formula/click that would otherwise require lots of manual clicks or megaformulas.

I also will have some UDFs that Excel should have natively, but doesn't. And there will be heaps of code to make working with PivotTables and Named Ranges a joy compared to the current state.