Trapping Errors in Macros

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

Trapping Errors in Macros

When an error occurs in a Microsoft 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 Intelligence Reporting Report it may not be convenient for this interaction to take place. In particular if a report is running unattended (for example, from a scheduler) then the report will freeze at the debug point and Microsoft Excel will hang. To prevent this from happening include an error trap in your Macro that passes the Error description back to Sage Intelligence Reporting. Sage Intelligence Reporting will then handle the error.

Method

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, for example,

   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 Intelligence Reporting 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 Intelligence Reporting will allow errors to be rendered in Sage Intelligence Reporting and stop the errors being Debugged by the VBA debugger within Microsoft Excel. Sage Intelligence Reporting 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.