Error Handling on external Macro

Question

I am just wondering if it would be possible to do error handling on an external macro. Basically what I want to achieve is I have have thousands of excel workbooks that come in daily and I want to open each of them and run the macro from them (easily done just use the Application.run feature )

Application.Run ("'" & ActiveWorkbook & "'!Export")

What I want to achieve is I want to run error resolving function if that external macro incurs an error.

This is what I have so far

Dim str_SearchFile, str_FileName, str_SearchPath As String
Dim wb_WorkBook As Workbook


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    str_ThisBook = ActiveWorkbook.Name 'Set the current workbook for later reference
        str_SearchPath = Sheets("Control Panel").Range("E2")
        str_SearchFile = Sheets("Control Panel").Range("E2") & "\*.xls*" 'Sets the file type to search for
        str_NextFile = Dir(str_SearchFile, vbDirectory) 'Sets the amount of files in the directory matching the criterea (.xls)

        Do While Len(str_NextFile) > 0
            On Error Resume Next
            Set wb_WorkBook = Workbooks.Open(Filename:=str_SearchPath & "\" & str_NextFile, Password:="")
            If Err.Number = 0 Then
                On Error GoTo 0
                Application.Run ("'" & str_NextFile & "'!Export")
                str_FileName = str_SearchPath & "\Done" & "\" & str_NextFile
                wb_WorkBook.Save
                wb_WorkBook.Close
                FileCopy (str_SearchPath & "\" & str_NextFile), str_FileName
                Kill (str_SearchPath & "\" & str_NextFile)
            End If
            str_NextFile = Dir
        Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Any Advise is very welcome!

Thank you in advance

Answer 1

You won't be able to get this to work the way you are trying.

The MSDN on On Error Statement indicates that it (emphasis added):

Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

The VBE Glossary defines a procedure as:

A named sequence of statements executed as a unit. For example, Function, Property, and Sub are types of procedures. A procedure name is always defined at module level. All executable code must be contained in a procedure. Procedures can't be nested within other procedures.

This means that calling error handling before calling the macro in the other book, will be ignored in the called macro (confirmed through testing).

The only way that you would be able to enable error handling would be to actually modify the code in the workbook prior to calling the macro... which is very complicated. For your reference, here is a webpage giving an example of editing project code from VBA.

Answer 2

The easiest way I would deal with this is to change your external workbook's "Export" sub into a function that returns a value - Say an integer.

What you can then do is put error trapping into that function and, based upon the outcome of the procedure it can return, say:

0 = All Went Well
1 = Failed to do XXX
2 = Failed to do YYY

You could then change your code to something like this:

Select Case Application.Run ("'" & str_NextFile & "'!Export")
Case 0
   MsgBox "All Went Well"

Case 1
   MsgBox "Failed to do XXX"

Case 2
   MsgBox "Failed to do YYY"

End Select

This will allow you to put the error trapping where it belongs and know how the procedure ran.

hope this helps