How to Debug an Excel Template Macro

LIMS’ integrated Excel interface is a powerful tool. Exporting LIMS data to your own Excel export template is a dynamic method to control the presentation of your data. However, when something goes wrong, troubleshooting Excel’s often cryptic error messages can be a challenge. For example, Excel’s all too common “Application-defined or object-defined” error shown below provides little or no insight into the cause of the problem.

This article provides a simple and systematic method you can use to troubleshoot an Excel template’s macro to locate the source of the error. Excel export template macros are typically more complex than import templates so we will focus on export templates here.

An Excel macro is a named Visual Basic for Applications (VBA) procedure comprised of any number of VBA statements. There are many Excel VBA statements available including those to execute any task you perform manually such as copying cells, deleting columns, and entering formulas. When you export a LIMS report to an Excel template, LIMS performs the following procedure:

1. Start Excel if it is not already running.

2. Direct Excel to create a new workbook from the selected template.

3. Run the new workbook’s BeforeTransferFromLIMS macro.

4. Copy the LIMS report’s underlying data to the new workbook’s LIMSData worksheet.

5. Run the new workbook’s AfterTransferFromLIMS macro.

An export template’s BeforeTransferFromLIMS macro rarely performs any tasks. However, a template’s AfterTransferFromLIMS macro normally includes many VBA statements to produce a final report from the exported LIMS data. Therefore, it is this macro that is the culprit in most errors.

Debugging a macro refers to the process of locating and removing the bug (i.e. error) within the macro. When a macro runs normally it runs at the speed at which the machine can execute its statements. At this speed it is nearly impossible for us to watch the macro’s progress as it executes each statement. When debugging the macro, we control when each VBA statement is executed so we can watch the result of each statement and the macro’s progress, and hopefully observe the specific VBA statement causing the error.

First, to debug a template’s AfterTransferFromLIMS macro, we must prevent LIMS from automatically executing the macro so that we can start the macro and control the execution of its statements. How you do this depends on your LIMS version.

With LIMS 5.x, after selecting the template simply hold down the Shift key while clicking the OK button in the Excel Data Transfer popup to prevent running the template’s macros. With LIMS 4.x we must rename the existing AfterTransferFromLIMS macro and add an empty macro. To do so, first make a copy of your template, then open the copy, use Alt+F11 to open the VBA editor, then double-click LIMSData in the project explorer’s list of Excel objects to view its macros. In the code pane on the right, find and copy the Public Sub AfterTransferFromLIMS() statement then place an ‘x’ just before the ‘A’ to rename the macro xAfterTransferFromLIMS. Now insert a new line just above this statement and paste in the original copied line. Hit Enter and the editor will automatically insert an End Sub statement to complete our new empty AfterTransferFromLIMS macro. The screen excerpt below shows the empty and renamed macros.

Second, most of the example export templates included with LIMS use VBA statements at the beginning of the AfterTransferFromLIMS macro to disable screen updating and prevent user intervention. This improves performance since Excel does not update the screen while the VBA statements are executed. However, while debugging we must see screen updates and interact with Excel so these macro statements must be disabled. If your template has a Settings worksheet with a ‘Prevent Screen Updating’ option, set it to ‘No’ now to ensure screen updating is enabled.

If your template does not have a ‘Prevent Screen Updating’ option on a Settings worksheet, locate the macro code shown below and insert a single-quote at the start of each of the three lines that begin with a period. This will turn the entire lines green indicating they are comments and will no longer execute. Exit the VBA editor then close and save your template.

Third, to debug your template begin by exporting the LIMS report to the edited template with the empty AfterTransferFromLIMS macro or use the Shift bypass key with LIMS 5.x. In the new Excel workbook created from the template the report will be incomplete since the macro has not yet run. Verify that the LIMS has copied data to the LIMSData worksheet. Use Alt+F11 to open the VBA editor and select the LIMSData sheet to display its macros.

Fourth, before running the macro, set a breakpoint in either the xAfterTransferFromLIMS macro for version 4.x or the AfterTransferFromLIMS macro for version 5.x. A breakpoint marks a VBA statement where the VBA editor will halt execution while running the macro. To set a breakpoint click within the grey left margin adjacent to the statement. The editor will place a red dot in the margin and set the entire statement red to identify the breakpoint. Note that a single VBA statement may span multiple lines. For your first debugging session it is best to set the breakpoint near the top of the macro. The excerpt below shows a good place to start with code that should exist in most export templates.

Finally, leave the VBA editor open, return to Excel, use Alt+F8, select either the xAfterTransferFromLIMS macro for version 4.x or the AfterTransferFromLIMS macro for version 5.x, then click Run. Excel will run the macro and the VBA editor will halt execution at the first breakpoint, highlighting the next statement to be executed in yellow.

Now debugging begins in earnest. Use Shift+F8 to execute the yellow highlighted statement. Continue using Shift+F8 to execute statements. Note that you can switch to Excel to see the results of the statements and view the contents of any worksheet. Using Shift+F8 may be tedious in a long macro but often single-stepping through the macro is necessary to find the statement causing the error.

When you encounter VBA statements that form a loop you may find single-stepping with Shift+F8 too time[1]consuming. In this case, set another breakpoint after the loop as shown below then use F5 to allow the macro to run up to the next breakpoint.

Continue executing the macro using a combination of single-stepping with Shift+F8, setting breakpoints, and running to the next breakpoint with F5 until you locate the statement causing the error. In our example, the next statement to execute highlighted in the excerpt below caused our error.

The VBA editor displayed the error message below when executing the highlighted statement. Although the error message is not informative and results in the dreaded “Application-defined or object-defined” error, the simplicity of the statement identifies the problem. Here, the OneAnalysisResults named range was inadvertently deleted with its original cells while editing the template. Restoring the named range solved the problem.

Note that when an error message is displayed the offending statement is no longer highlighted so it is important to make a mental note of the current statement when using Shift+F8. It is common to lose track of the current statement when rapidly single[1]stepping through the macro. In that case you will have to end the current debugging session and begin again but this time you can set your first breakpoint in the macro closer to where the error occurred.

Excel templates and their powerful VBA macros provide an important tool to analyze, report, and deliver LIMS data. However, Excel can be finicky and some of its error messages are not very informative. Use the debugging techniques described here and you can easily find the cause of the error. Consider debugging a working template now so you are prepared when you encounter a real error.