When we develop Excel applications we make every effort to prevent program errors, but there will be times, when one of our applications does not work the way it was intended. This could be for any number of reasons, such as conflicts with other Excel add-ins or low physical memory.
In such cases, it would be very helpful for us to see a snapshot of the system state when an error occurs, and to this end we log all untrapped errors in an error-log file. But we'll only see this information when you share it with us via email or through our secure hosted Microsoft Sharepoint site and no information is automatically sent back to the developers.
In such cases, it would be very helpful for us to see a snapshot of the system state when an error occurs, and to this end we log all untrapped errors in an error-log file. But we'll only see this information when you share it with us via email or through our secure hosted Microsoft Sharepoint site and no information is automatically sent back to the developers.
- All our Excel VBA applications use an error log to record untrapped errors, unless otherwise requested by the client.
- Detailed error and programming information is written to the file 'Excel_VBA_Error_Log.txt for debugging purposes.
- The full path of the file is shown on the lower window. This is usually the path of the Excel workbook with VBA code.
- The text file can be deleted at any time by the client without any side effect to the Excel application.
- The upper window shows the time and description of the error(s). By double-clicking the error description the contents of the error log can be read or edited in Notepad.
Is any user information recorded in the file ?
Yes. The only user information written to the file is the user name as reported by Excel. Additional user information could be revealed, if it exists, in path names along the path of the Excel workbooks in use, installed add-ins or Excel libraries.
Is any information about the client's computer hardware or software recorded in the file ?
Yes. Some errors could be triggered by low physical memory, legacy MS-Office software or operating system, outdated or lack of MS-Office service packs, conflicts with installed Excel add-ins or open Excel workbooks or any MS-Office applications in use. Such computer and MS-Office / Excel software related information could help us to diagnose the problem with your Excel application.
Is any application data information recorded in the file ?
Perhaps. The contents of all variables (public, module and local) used in VBA code are written (dumped) to the file, when the error occurs. This information is extremely useful in diagnosing the problem, but may reveal some application data held in these variables at runtime when an error occurs.
Is the error log file transmitted to the developer automatically, with/ or without consent from the user ?
Never. A user has to email the file to us or upload it to our secure hosted Microsoft Sharepoint site manually. Our Excel applications do NOT transmit any information back to us, unless such a feature is explicitly requested by the client during the design stage of the Excel application.
All error log information is recorded in human readable form only. The client can review and edit the contents of the text file in order to remove any information, which should not be transmitted back to us.
The error log file could be emailed to us or uploaded to our secure hosted Microsoft Sharepoint site, if the client chooses to do, so we can continue to improve our software.
The error log file could be emailed to us or uploaded to our secure hosted Microsoft Sharepoint site, if the client chooses to do, so we can continue to improve our software.
Problems from poor VBA Error Handling
Does your VBA code breaks with a message like this ?
Error handling is the most commonly omitted feature in Excel applications. This is not an acceptable state of affairs for us.
The last thing we want our users to see is an unvarnished and distressing VBA runtime error. Another common bad practice is to cover-up errors without reporting them by using "On Error Resume Next" inappropriately.
Does your macro stop half way through execution without any warnings or error prompt ?
You may think that your complex macro has finished execution normally, only to realize that execution has stopped suddenly without an error prompt. This behavior is due to poor error handling practices.
Error handling is the most commonly omitted feature in Excel applications. This is not an acceptable state of affairs for us.
The last thing we want our users to see is an unvarnished and distressing VBA runtime error. Another common bad practice is to cover-up errors without reporting them by using "On Error Resume Next" inappropriately.
Does your macro stop half way through execution without any warnings or error prompt ?
You may think that your complex macro has finished execution normally, only to realize that execution has stopped suddenly without an error prompt. This behavior is due to poor error handling practices.
Error Handling Tips
Here are some VBA error handling tips worth considering:
- Use a single exit from procedures and functions
- Place error handlers at the bottom of procedures for readibility
- Try to handle and display diagnostic messages at the lowest level of code, unless a Global error handler is used
- To avoid the risk of 'programming minefields', use 'On Error Resume Next' inside isolated procedures, as the error stack is reset on procedure exit.
If your Excel application lacks or has poor error handling, please contact us for a consultation
