On Error doesn't trap Application.Run error

[Guest]

I have a macro in an XLA that is trying to run a macro in a different
workbook, if the macro does not exist, I just want to ignore it and continue,
otherwise run it.

It works fine if the macro exists, but if it doesn't I get the runtime 1004
error dialog box, and no matter what I do, I cannot seem to prevent it.
Here is a sample of my code:

Public Sub RunMacro( FileName )
On Error GoTo Finish
Set ReportWorkbook = Workbooks.Open(FileName, , True)
Application.Run ReportWorkbook.Name & "!Execute"
On Error GoTo 0
Finish:
End Sub

Am I missing something here????

 

[Guest]

NickHK

Thanks for the reply. No, I do not have "Break On All Errors" set (Nice
catch though!), I have "Break On Unhandled Errors" checked.

Any other suggestions?

P.S. I went down the route of looking at the code modules using VBE, but
that proved to be problemmatic also, because the sheet that I am calling has
to have trusted programmatic access. This also generates an error if it is
not set and I have no way of knowing if the function exists again, so that
shoots it out the window.

 

[NickHK]

blackhawk,
Do you have "Break On All Errors" checked, under Tools>Options>General ?

See this recent thread in "microsoft.public.vb.general.discussion" for an
explaination of the different settings.

From: "Gerald Hernandez"
Subject: Re: Why don't I get an error here?
Date: Thu, 3 Mar 2005 16:54:45 -0700

NickHK

 

[Guest]

This worked for me

Sub tester()
Dim wb As Workbook

On Error GoTo finish

Set wb = Workbooks.Open("S:\staff\patrick\excel stuff\welcome.xls")
Application.Run "Welcome.xls!Welcome"
wb.Close False

finish:
On Error GoTo 0

End Sub

Patrick Molloy
Microsoft Excel MVP

 

[NickHK]

blackhawk,
I still correct error handling with
Err.Number=1004
Err.Description="The macro 'Book4.xls!Execute' cannot be found."

Actually, using this method to open this Book4 containing VBA code does not
fire the "Do you want to enable macros ?" dialog, as it should with security
set to medium.
If fact I can even run the unsigned code in Book4.xls with security set to
high and step through the code.
This seems able to completely circumvent the macro security.
I suppose that Excel assumes that if you know the name of a macro in a WB
then you are happy to let it run.

What is the declaration of the Execute macro ?

NickHK

 

[Guest]

Patrick,

Thanks for the reply. Ok, I am losing my mind....I can put the same macro
you have in a new workbook and it works fine (the error gets trapped). As
soon as I try to place it into my module within the add-in, it does not work.

My hair is getting thinner by the moment......Any suggestions?