VBA IFError Function?

JakeVBA2009

New Member
Joined
Nov 30, 2009
Messages
16
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Thanks mate, this is quite useful, I'm a bit confused at this point though....


  1. Next to Formula, type the formula for which you want to hide the error value. Do not include the equal sign (=).
  2. Next to Show, type what you want to show in place of the error value. If you want to hide the error value, type double quotes ("")
Any idea what i should put here as examples?

Thanks.
 
Upvote 0
The "formula" is whatever you're trying to evaluate and the "show" is what you want the error message to be instead of the default Excel error message.

so copy the code in to a module and in a cell type (as an example):
Code:
=iferror(10/0,"Test")

and you should see "Test" instead of the "#DIV/0" message
 
Upvote 0
=iferror(formula,show)

for example:

formula: =VLOOKUP(E11,E2:E9,2)

show: "Not found"

then the formula should be: =IFERROR(VLOOKUP(E11,E2:E9,2),"Not found")
 
Upvote 0
The "formula" is whatever you're trying to evaluate and the "show" is what you want the error message to be instead of the default Excel error message.

so copy the code in to a module and in a cell type (as an example):
Code:
=iferror(10/0,"Test")
and you should see "Test" instead of the "#DIV/0" message

Ok, so this is the code I have at the moment

Sub Error()
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

When i try and run the code, I get the message "Compile Error Expected : expression"

Any ideas whats wrong?
 
Upvote 0
You don't need the sub part. The whole code is:

Code:
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
 
Upvote 0
Did you look at my or mjrofra's post above? That's how you'd use it in a worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top