Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: VBA IFError Function?

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA IFError Function?

    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

  2. #2
    Board Regular
    Join Date
    May 2009
    Location
    Colombia
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    Hi,

    take a look at this link, might help you:

    http://support.microsoft.com/kb/280094/

  3. #3
    New Member
    Join Date
    Nov 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    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.

  4. #4
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    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
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  5. #5
    Board Regular
    Join Date
    May 2009
    Location
    Colombia
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    =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")

  6. #6
    New Member
    Join Date
    Nov 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    Quote Originally Posted by sous2817 View Post
    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?

  7. #7
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    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
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  8. #8
    New Member
    Join Date
    Nov 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    Ahh thanks mate, how would i get it to work from the insert function command in excel?

  9. #9
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    Did you look at my or mjrofra's post above? That's how you'd use it in a worksheet.
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  10. #10
    New Member
    Join Date
    Nov 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IFError Function?

    Quote Originally Posted by sous2817 View Post
    Did you look at my or mjrofra's post above? That's how you'd use it in a worksheet.
    Hmm, when i run the code in VBA, it prompts me to select a macro which is confusing.

    Should it be like that?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •