Trapping Errors in Macros

Home > Report Manager > Troubleshooting the Report Manager > Trapping Errors in Macros

Trapping Errors in Macros

When an error occurs in an Excel Macro with no error traps the VBA (Visual Basic for Applications) Editor automatically goes into Debug Mode and awaits a response from the user. If the Macro is being called from a Sage Accpac Intelligence Report it may not be convenient for this interaction to take place. In particular if a report is running unattended (e.g. from a scheduler) then the report will freeze at the debug point and Excel will hang. To prevent this from happening include an error trap in your Macro that passes the Error description back to Sage Accpac Intelligence. Sage Accpac Intelligence will then handle the error. To do this perform the following steps:

1. Open your template with the Macro in it.
2. Change the Sub Keyword to Function (this allows the Macro to return a value).

3. Turn Error Trapping on in the Routine with the "On Error Goto [TrapName]" VBA syntax, e.g.

   On Error GoTo MyErrorTrap
   	

4. As the last lines in the Routine include the Error Trap. The error trap should return the error from the Macro as a string so that Sage Accpac Intelligence can catch the error and handle it. The error trap should be of the following structure:

   Exit Function
   MyErrorTrap:  
        MyMacro = Err.Description

   An Example of the full structure of a Macro with an error trap is shown below:

   Function MyMacro()
   On Error GoTo MyErrorTrap
   '----------------------------------------------------------
   '[Included here should be the functional code of the Macro]
   '----------------------------------------------------------
   Exit Function
   MyErrorTrap:
        MyMacro = Err.Description
   End Function

Note: Using this method to trap Errors and pass them back to Sage Accpac Intelligence will allow errors to be rendered in Sage Accpac Intelligence and stop the errors being Debugged by the VBA debugger within Excel. Sage Accpac Intelligence will not take any action on the errors. For intelligent error trapping for bespoke Macros, more intelligent error handlers will have to be implemented within your macros and will require a good working knowledge of VBA.