VBA Ignores On Error Resume Next

Posted on 2009-12-20
Last Modified: 2013-11-25
I have the following two lines of code in a procedure that is called by the Workbook_Close event:
     On Error Resume Next
     Application.Run "Printing.RemovePrintStatement"
The second line causes an error because the macro is in an AddIn which isn't available.  That is why I placed the Resume Next statement before it.  It is OK for the Macro not to be run if the Addin isn't available.  However, I would like to avoid the error message, preferrably without looking through all available addins.
Question by:awink
    LVL 84
    You would be best to trap the error, and then go from there:

    On Error GoTo Err_Handler

    Application.Run "Your Macro"

    Exit Sub

      Select Case Err.Number
        Case 123345
          Resume Next
        Case Else
          Msgbox blah lbha
      End Select

    Obviously you'd need to determine which Error number you need to trap and use that.
    LVL 5

    Assisted Solution

    Its not trapping it because its not really an error
    Its just asking where is that macro

    I would test for the presence of the addin and exit the code if addin not available
    LVL 85

    Assisted Solution

    by:Rory Archibald
    What error are you actually getting? Your syntax works fine for me when trying to run a non-existent macros.
    LVL 5

    Accepted Solution

    it throws up the macro selection dialog for me...... :-\
    LVL 85

    Assisted Solution

    by:Rory Archibald
    FYI, you could also simply do something like this:

    Dim wbkAddIn as Workbook
    On error resume next
    set wbkAddin = workbooks("add-in name.xla")
    on error goto 0
    if wbkaddin is nothing then
       ' not installed
    Application.Run "Printing.RemovePrintStatement"
    End if

    Open in new window

    LVL 1

    Author Closing Comment

    II fear that I owe an apology:
    The error was 1004 (The Macro ... can't be found), but it wasn't caused by the code line I published but by a similar one responding to the Sheet.Activate event where the On Error statement was omitted.  Moreover, it seems that it wasn't even caused by the non-availability of the macro but by oversupply of it, I suspect, because I have an XLA and and XLS version of the Addin open at the time while working on the XLS, and there seems to be nothing wrong with the macro itself.  Anyway, the message seems to disappear when the XLS version is closed.
    I am still working on this project and might get back with another question.
    Meanwhile: Merry Christmas!
    LVL 5

    Expert Comment

    rorya -u da man! :-)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now