Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


VBA Ignores On Error Resume Next

Posted on 2009-12-20
Medium Priority
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 85
ID: 26090731
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.

Assisted Solution

by:sir plus
sir plus earned 1000 total points
ID: 26091534
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
Rory Archibald earned 1000 total points
ID: 26092756
What error are you actually getting? Your syntax works fine for me when trying to run a non-existent macros.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

sir plus earned 1000 total points
ID: 26093390
it throws up the macro selection dialog for me...... :-\
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1000 total points
ID: 26095301
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


Author Closing Comment

ID: 31668225
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!

Expert Comment

by:sir plus
ID: 26101941
rorya -u da man! :-)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

581 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