How to use IfError in Excel 2007 VBA ?

[Xavier]

Hello,

I am trying to figure out how to use the IfError function with Excel 2007
VBA. I have built a simple spreadsheet to test division by zero error
handling. The following line either performs the division or throws an error
11 (division by 0) instead of returning 99.

ActiveSheet.Cells(1, 3) = WorksheetFunction.IfError((ActiveSheet.Cells(1, 1)
/ ActiveSheet.Cells(1, 2)), 99)

Any help would be appreciated.

Thanks,

Xavier

 

[Dave Peterson]

It would make more sense to me to just check the denominator cell for 0.

dim myVal as double
myVal = 99
With activesheet
if application.isnumber(.cells(1,1).value) then
if application.isnumber(.cells(1,2).value) then
if .cells(1,2).value <> 0 then
myval = .cells(1,1).value / .cells(1,2).value
end if
end if
end if
.cells(1,3).value = myval
end with

 

[Xavier]

The division by zero that I mention is just a way for me to quickly test the
IfError function. I need to use it in a different context to trap other
errors. This is the simplest example I have been able to produce but still
can't get it to work. :-(

 

[Peter T]

When VBA tries to evaluate the div/0 an error is raised before getting to
the IFError worksheet function. Here's a couple of examples

Sub test()
Dim s As String
Dim x As Double, v

s = "=" & ActiveSheet.Cells(1, 1).Address & "/" & ActiveSheet.Cells(1,
2).Address
ActiveSheet.Cells(1, 3).Formula = s
x = WorksheetFunction.IfError(ActiveSheet.Cells(1, 3), 99)
ActiveSheet.Cells(1, 4) = x

v = CVErr(xlErrDiv0)
x = WorksheetFunction.IfError(v, 123)
ActiveSheet.Cells(1, 5) = x

End Sub

Wouldn't normally use the IFError function in VBA
On error resume next
v = 1/0
if err.number = 9 then
' code
elseif etc
End if
On error goto 0

Regards,
Peter T

The division by zero that I mention is just a way for me to quickly test
the IfError function. I need to use it in a different context to trap
other errors. This is the simplest example I have been able to produce but
still can't get it to work. :-(

 

[Xavier]

OK, if I understand you correctly, I picked up the wrong example to test
IFERROR under VBA. Thanks for the code sample you provided. I usually use
some similar case, but in the current situation, I am working with
GetPivotData and need to catch some #REF errors and IfError is the easiest
way to achieve what I am trying to do.

Thanks,

Xavier

When VBA tries to evaluate the div/0 an error is raised before getting to
the IFError worksheet function. Here's a couple of examples

Sub test()
Dim s As String
Dim x As Double, v

s = "=" & ActiveSheet.Cells(1, 1).Address & "/" & ActiveSheet.Cells(1,
2).Address
ActiveSheet.Cells(1, 3).Formula = s
x = WorksheetFunction.IfError(ActiveSheet.Cells(1, 3), 99)
ActiveSheet.Cells(1, 4) = x

v = CVErr(xlErrDiv0)
x = WorksheetFunction.IfError(v, 123)
ActiveSheet.Cells(1, 5) = x

End Sub

Wouldn't normally use the IFError function in VBA
On error resume next
v = 1/0
if err.number = 9 then
' code
elseif etc
End if
On error goto 0

Regards,
Peter T