Introduction to LIMS’ Excel VBA Macros

LIMS’ integrated Excel interface has become an indispensable tool for many LIMS users. With appropriate templates, the Excel interface is widely used to create custom reports, export data to other information systems, import instrument data, and create data entry screens to calculate and import analytical results.

The value of the Excel interface is apparent given the frequency of technical support requests we receive for assistance with Excel templates. With this and future Insights articles, we will explore the Visual Basic for Applications (VBA) macros essential in an Excel template used with LIMS.

An Excel macro provides a means to automate repetitive tasks. To cite a simple example, if you frequently resize cells and change their font, text alignment and text wrapping to a preferred layout, you can record a macro while you perform the task then invoke the macro to repeat the process on any cell. When you record a macro, Excel will add the equivalent VBA statements or commands to duplicate the tasks you performed.

In templates used with LIMS, macros are also employed to perform tasks preceding or following the transfer of data to and from the LIMS and Excel. For example, a macro organizes and displays data on a final report after the data is exported from the LIMS. To support separate tasks both before and after data is transferred from the LIMS all templates must include a worksheet named LIMSData with macros BeforeTransferFromLIMS and AfterTransferFromLIMS. Templates used to import data into the LIMS must also include macros BeforeTransferToLIMS and AfterTransferToLIMS. These macros may be empty with no executable VBA statements but they must exist.

To illustrate the location and format of these macros and their use with LIMS, we will create a simple example. Begin by opening Excel with a blank workbook and change the name of the first worksheet from “Sheet1” to “LIMSData”.

Use File | Save, set the save as type to “Template (*.xlt)” and the file name to “My Template” and save the file in your Excel export templates folder. Use Tools | Macros | Visual Basic Editor to open the VBA Editor. If the Project Explorer and Properties windows are not visible on the left side of the VBA screen, use View | Project Explorer and View | Properties to display. Select Sheet1 in the project explorer and change its Name property in the Properties window to “LIMSData”. Since our macros must be stored with the LIMSData worksheet, double[1]click the LIMSData sheet in the project explorer to open the LIMSData sheet’s VBA code window. Enter the VBA statements shown in the code window below.

The “Public Sub” statements define a public subroutine or macro with the given name. Public macros are accessible to Excel users and to external applications such as LIMS. The parentheses after the macro name enclose parameters with additional data sent to the macro. LIMS sends data directly to the LIMSData worksheet and not to the macro so there are no parameters.

The “End Sub” statement identifies the end of the macro. With no statements between the “Public Sub” and “End Sub” statements these macros are empty and they are the minimum required by an LIMS export template.

Use File | Close and Return to Microsoft Excel to close the VBA editor. Close and save your template. Open LIMS, preview any LIMS report then export the report to your new template. The image below shows the results of exporting the Database Records report to the template.

You now have a basic export template with empty macros. You can use Excel’s macro recorder to perform a task you want to repeat each time you export to the template then copy the VBA statements Excel creates to your template’s macro.

For example, if we want to sort the Database Record report’s data by descending record count so we can quickly see which tables have the most data, use Tools | Macro | Record New Macro, note that our new macro will be called Macro1 then click [OK]. Use Data | Sort and sort by descending Records then click [OK]. Now use Tools | Macro | Stop Recording then Tools | Macro | Visual Basic Editor. Double-click Module1 in the project explorer to view the Macro1 macro created by Excel.

Copy any VBA statements within the macro to your template’s AfterTransferFromLIMS macro. The image below shows the new sort statement added to our macro.

When LIMS exports a report’s data to any template it will first have Excel create a new workbook from the template. LIMS will then run the new workbook’s BeforeTransferFromLIMS macro. Next, LIMS will write the report’s underlying data to the LIMSData worksheet and finally it will run the workbook’s AfterTransferFromLIMS macro.

Using Excel’s macro recorder you can create and copy VBA code excerpts into your own template’s macros to automate tasks to sort, graph, report, and analyze LIMS data with Excel.