Task Automation Program to Save Lab Work Time
The problem concerned the processing of results from a chemical laboratory. The client who approached me conducted tests to determine the content of chemical compounds in samples using the chromatographic method. To clearly present the issue, I’ll provide a brief explanation of how this method works.
Without delving into the technical details of the apparatus, the analyzed sample is introduced into a special column. Due to chemical or physical interactions, different compounds remain in the column for different amounts of time, known as retention time. To identify what is in the sample, the obtained values are compared with the retention times of known standards previously measured under the same chromatographic conditions.
Returning to the resolved problem, the lab technician had the task of comparing the retention time values from the measurement with the standard values, which he did in an Excel spreadsheet. He manually went through each reading and looked for which compound corresponded to that value. The task was complicated by two factors. First, the measured values never match the standard values exactly. Second, some compounds have very similar retention times. For these reasons, the technician had to decide, based on specific criteria, which compound was actually in the sample. Since the number of detected compounds in a single sample could be in the hundreds, such analysis could take up to two weeks!
The solution to the problem involved using a fairly simple program in VBA. For those unfamiliar, VBA is a language for automating tasks in MS Office applications. In other words, any repetitive operations in Excel, for example, can be programmed to occur without user intervention. I created an Excel spreadsheet for the client in which the operations performed manually by the technician were programmed. The developed functions included strictly defined rules for selecting the “matching” chemical compound, thus eliminating potential human errors. From a technical standpoint, the task was not complicated, and to avoid boring the reader, I will not go into details. The effect of using such a “programmed” Excel spreadsheet was to reduce the technician’s work time from two weeks to almost zero. It was enough to load the measurement data, after which the entire procedure for all readings was automatically performed, resulting in an Excel sheet with the chemical composition of the sample.
This example demonstrates the essence of task automation. This concept frequently appears in business topics, such as automating email sending or responding to customer inquiries. As you can see, this method can be used in issues related to the processing of laboratory data. The benefits of this approach include a significant reduction in human work time, minimization of human errors, and cost reduction, as the skilled worker can be assigned to more creative tasks requiring specialized knowledge.
