VBA IFError Function?

[JakeVBA2009]

Hi guys, I need to write an IFError Function that closely mimics the Excel 2007 IFError Function.

(It does not need to consider the case of the function being used as part of an array formula)

I've got this code, but feel like i'm barking up the wrong tree completely...

Thanks in advance.

Sub openVBADocument()
Dim strFolder As String
Dim strVBADocument As String

strFolder = Range("Folder")
strVBADocument = Range("VBADocument")
If Dir("VBA Document" & strFolder & "\" & strVBADocument & ".xls") = "" Then
MsgBox "Error", vbExclamation
Else
Workbooks.Open Filename:="VBA Document" & strFolder & "\" & strVBADocument & ".xls"
End If
End Sub

 

[mjrofra]

just follow the instructions at the page: http://support.microsoft.com/kb/280094/

there is a step by step guide there.

this is a UDF you don´t have to excute it, you just copy the code in a module and then use the functions =iferror(formula,show) in any worksheet.

 

[JakeVBA2009]

Ahh ok, so i'm trying this formula but it doesnt seem to work

=IFERR(SUM(A5+B5),"Not Found")

Whats the problem there?

My Function is called IFERR and its displayed on the function list...

 

[sous2817]

works on my test sheet. Can you post the code as you have it in your module?

 

[Jonmo1]

If you changed the name of the function to IFERR, you ALSO need to change the other references to IFERROR in the rest of the code to IFERR

In the code below, If you change the name of the funtion, you need to change all HIGHLIGHTED lines need to be the same...

Function IFError(Formula As Variant, Show As String)
On Error GoTo ErrorHandler
If IsError(Formula) Then
 IFError = Show
Else
 IFError = Formula
End If
 
Exit Function
 
ErrorHandler:
Resume Next
 
End Function

 

[JakeVBA2009]

Function IFERR(Formula As Variant, Show As String)
On Error GoTo ErrorHandler
If IsError(Formula) Then
IFERR = Show
Else
IFERR = Formula
End If

Exit Function

ErrorHandler:
Resume Next

End Function

Yeah, i changed it where necessary anyway.

Everytime i insert the function in the formula tab, it asks me to select function arguements. Do i need to set these in concrete somehow? Or will it always ask me to define function arguements before i run the function?

 

[JakeVBA2009]

works on my test sheet. Can you post the code as you have it in your module?

What are you typing into your test sheet to prove wheter it works or not?

 

[sous2817]

Excel Workbook
	A	B
3		10
4		Test
5	5	5
6	0	5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B3	=IFERR(SUM(A5+B5),"Test")
B4	=IFERR(B6/A6,"Test")

 

[JakeVBA2009]

Excel Workbook
	A	B
3		10
4		Test
5	5	5
6	0	5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B3	=IFERR(SUM(A5+B5),"Test")
B4	=IFERR(B6/A6,"Test")

So where do you type in the Formula in order to test it out?

 

[Jonmo1]

So where do you type in the Formula in order to test it out?

In a cell on your sheet.

Do i need to set these in concrete somehow? Or will it always ask me to define function arguements before i run the function?

Yes, that's the whole Idea.

You're creating your own function. Designed to do what XL2007's IFERROR function does.
so you write the formula in a cell..
=IFERR(theformulayouwanttotestforerror,whatitshouldshowifitisanerror)

 

[sous2817]

Worksheet Formulas
Cell Formula
B3 =IFERR(SUM(A5+B5),"Test")
B4 =IFERR(B6/A6,"Test")

I'm not quite sure I understand your question. In order this is what I did
1) Copied in code in new module
2) in cell a5 I typed 5
3) in cell b5 I typed 5
4) in cell a6 I typed 0
5) in cell b6 I typed 5
6) in cell B3 I typed: =IFERR(SUM(A5+B5),"Test")
7) in cell b4 I typed : =IFERR(B6/A6,"Test")