Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use IfError in Excel 2007 VBA ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use IfError in Excel 2007 VBA ?
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use IfError in Excel 2007 VBA ?
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. :-( "Dave Peterson" wrote in message ... 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use IfError in Excel 2007 VBA ?
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 "Xavier" wrote in message ... 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. :-( "Dave Peterson" wrote in message ... 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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use IfError in Excel 2007 VBA ?
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 "Peter T" <peter_t@discussions wrote in message ... 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 "Xavier" wrote in message ... 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. :-( "Dave Peterson" wrote in message ... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IFERROR in Excel 2007 | Excel Worksheet Functions | |||
Can IFERROR be used in Excel 2003 Compatibility View? | Excel Discussion (Misc queries) | |||
function =IFERROR LOOKUP works in excel 2007 not in excel 2003 | Excel Worksheet Functions | |||
IFERROR | Excel Worksheet Functions | |||
iferror | Excel Worksheet Functions |