CODEWITHSUNDEEP
vbaexcel
LynseyAbbott
LynseyAbbott

Reputation: 35

Using IFERROR in VBA

I hope someone can help me please? I've done a lot of googling and can't figure out what the issue is. I only dabble in vba, so I'm certainly no expert...

I am trying to automate some calculations in a huge spreadsheet at work, and I think I'm probably missing something really silly. Basically, one of the calculations is a simple divide of one cell by another cell. When it hits an error, I want it to return a 0. Here is the code that keeps tripping over:

Sheets("Bridge").Range("W" & SumIfInt) = Application.WorksheetFunction.IfError(Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt), 0)

I get a Run-time error 6 Overflow

Thanks in advance

Upvotes: 2

Views: 158466

Answers (5)

Fleischbender
Fleischbender

Reputation: 21

Use this

On Error Resume Next 'Disable break when error occurs
'Enter the code you want to error check, say a cell reference, here.
If Err.Number >0 Then 'Err.Number=0 only if there were no errors
'Enter what you want happening if there was an error
Else
'Enter what you want happening if there is no error
End if
On Error GoTo 0 'Delete error message and re-activate break when (a new) error occurs

Upvotes: -1

Middle
Middle

Reputation: 143

Try this without If error function. As mentioned below, you should use On error resume next very carefully.

Warning: On error resume next will skip ALL cases of error for rest of the code unless On error goto 0 is present. Also, if you would like to catch a specific error, you can use if error = 'number' then to handle them accordingly. Application.worksheetfunction.iserror is better used in excel, than in excel vba.

    On error resume next
    Sheets("Bridge").Range("W" & SumIfInt) = Sheets("Bridge").Range("AA" & 
    SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt)

    if err <>0 then
       Sheets("Bridge").Range("W" & SumIfInt) = 0
    end if
    On error goto 0

Upvotes: 7

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

What is the declared type of SumIfInt? If that's Integer, its maximum legal value is 32,767, because Integer is a 16-bit type - and using a 16-bit signed integer type to represent row numbers in an Excel worksheet is a good way to achieve run-time error 6 "Overflow".

Use a 32-bit integer type instead:

Dim SumIfInt As Long

If Sheets("Bridge").Range("D" & SumIfInt) is a very small value, it's possible that using it as a denominator makes the result overflow an Integer, or possibly even a Long - in that case you should probably use a Double floating-point type instead.

When it hits an error, I want it to return a 0.

Better code wouldn't hit an error at all. The only error you should be worried about here, is "Division by Zero" - actually also "Type Mismatch" if any of the involved cells may contain an error value - in any case, shoving the error under the carpet with On Error Resume Next isn't showing you anything about how to avoid that situation in the future.

The best error-handling code is code that avoids raising avoidable errors in the first place.

Dim divisorValue As Variant 
divisorValue = Sheets("Bridge").Range("D" & SumIfInt)

If IsError(divisorValue) Or IsEmpty(divisorValue) Then
    ' return 0 and bail out
    Exit Function
End If

Dim divisor As Double
divisor = CDbl(divisorValue)

Now use the known-to-be-ok divisor value instead.

A note about your object management: you're dereferencing the exact same object reference multiple times - that's inefficient.

If Sheets("Bridge") exists at compile-time in ThisWorkbook (the workbook that's running the code), then you never need to dereference it that way - VBA creates a global-scope object variable for every single worksheet in the workbook - select it in the VBE's Project Explorer (Ctrl+R), bring up its Properties (F4), then set its (Name) to something meaningful, e.g. BridgeSheet. Then you can do this:

BridgeSheet.Range("D" & SumIfInt)

Another tip: the more you cram into a single instruction, the harder debugging becomes, because the more possible failing points there are.

Consider pulling the invidividual values involved into their own variables, then validating them, then performing the division once you know doing that isn't going to blow up.

Upvotes: 4

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Try to avoid creating errors in the first place - On Error should generally be used to handle unexpected errors.
The reasoning behind this is that if a genuine error occurred after the On Error Resume Next it would be ignored and your sum would be 0.
E.g. If someone wrote Five hundred instead of a 500 you'll just get a 0 (far fetched, but ... actually, not far fetched - never under estimate a sufficiently talented idiot).

Anyway....

If Sheets("Bridge").Range("D" & SumIfInt) is zero or empty you'll get a division by 0 error.
Providing everything else is correct (no text, all other numbers present) then this is the only figure you need to check:

Sub Test()

    Dim SumIfInt As Long
    SumIfInt = 2

    With ThisWorkbook.Sheets("Bridge")
        If .Range("D" & SumIfInt) = 0 Then
            .Range("W" & SumIfInt) = 0
        Else
            .Range("W" & SumIfInt) = .Range("AA" & SumIfInt) / .Range("D" & SumIfInt)
        End If
    End With

End Sub

Upvotes: 1

Absinthe
Absinthe

Reputation: 3391

When programming it's always best to avoid an error by checking for it's possibility first, rather than triggering one then dealing with it. Check if the cell is empty or zero before performing the calculation:

If IsEmpty(Sheets("Bridge").Range("D" & SumIfInt)) Or Sheets("Bridge").Range("D" & SumIfInt) = 0 Then
    Sheets("Bridge").Range("W" & SumIfInt) = 0
Else
    Sheets("Bridge").Range("W" & SumIfInt) = Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt)
End If

On Error Resume Next basically says "If you encounter an error just ignore it". This can cause all sorts of unexpected problems and should only be used as a last resort.

Also check out the IsError function in VBA.

Upvotes: 9

Related Questions