How check if Application.Run fails??

[Robert Crandal]

Hello! I am using Application.Run to run macros in another
workbook that is currently open. How can I check or test
if the Application.Run function call fails or returns an error
code?? Would it also be wise to use the "On Error Resume Next"
before calling the Application.Run function??


thank you!

 

[OssieMac]

Hi Robert,

Firstly I assume that you mean you are running a Sub in another workbook;
not a Function. A Function usually refers to User Defined Function (UDF for
short).

Now to check if the called Sub actually runs, insert a MsgBox with a message
immediately after the Sub name that indicates that the sub has been called.
(Can delete it after testing)

As for errors, the real test of this is "Does it do what it is supposed to
do?"
However, you can insert an On Error Goto ErrorRoutine where ErrorRoutine is
a label which is normally just before the End Sub. On the line prior to the
label you insert Exit Sub so that if it gets to the Exit Sub then it has
completed without error and exits and the only way it processes the code
after ErrorRoutine is if an error sent it to there.

Example where Test is the called sub.

Sub Test()

MsgBox "Sub Test has been called. Click OK to continue."

On Error Goto ErrorRoutine

'Your code here

Exit Sub 'If gets to here then finished so exit.

ErrorRoutine: 'Note the Colon at the end of the label.

'You might be able to insert more info in the message re the error
Msgbox "An error has been encountered"

End Sub.


Having said all that, if you are calling the sub from code in a Forms
module, sometimes if there is a compile error in the code then the sub simply
does not run or it locks up Excel. Always compile your code before running it
and there is a possiblility that some errors witll be detected. To compile
code, Select Menu item Debug -> Compile. Note that compile does not
necessarily detect errors of logic in the code.

 

[Bob Phillips]

I would turn the macros into function and add error handling to return True
or False depending on success, something like

Public Function TestRun() As Boolean

On Error GoTo TestRun_Error

TestRun = True

'some code that if it errors calls the errorhandler

'such as

Debug.Print 1 / 0

Exit Function

TestRun_Error:
TestRun = False
End Function


and thn call it like so

If Not Application.Run("Personal 2003.xls!TestRun") Then

MsgBox "oops!"
End If


HTH

Bob