TL;DR
I was a lab technician at a cannabis testing lab. The LC-MS/MS instrument that screened for 74 pesticides worked fine. The data processing was the problem.
Every sample exported 56 columns and 206 rows of data, and you had to process it twice: once for ESI, once for APCI. Two ionization modes, two complete workflows. I needed 10 columns and 74 rows from each. The rest was metadata, duplicate readings, and internal standards that had to be cleaned out manually. Then came unit conversions, summed analytes, and checking every result against regulatory limits. For a single sample, you’re looking at 90+ minutes of tedious, error-prone work.
I taught myself VBA and built Excel macros that did each in 12 seconds.
Connecticut banned pesticides on cannabis. With only four tightly regulated suppliers, positives were rare - but when they happened, we had to catch them. One missed exceedance meant a recall, regulatory scrutiny, and a ruined client relationship. The workflow needed to be perfect.
Why VBA instead of Python? Everyone already had Excel. I could hand someone a workbook and say “click these four buttons.” When I left, the macros kept running. No environment to maintain. No dependencies to break.
If you want the technical details and code, read on.
When someone deletes the wrong column or misses a limit check, the instinct is to blame them. But if your workflow requires a human to manually process 206 rows without mistakes, the workflow is broken. Humans make errors. Systems should catch them, or better yet, make them impossible.
In 2021, I was a lab technician at a cannabis testing laboratory in Connecticut. The lab ran compliance testing for cultivators and dispensaries: potency analysis, pesticide screening, mycotoxin detection. My corner of the operation was a PerkinElmer QSight 420 triple-quadrupole LC-MS/MS, which screened samples for 74 target compounds.
The instrument worked well enough, save for some hiccups and particularly challenging analytes. The data processing did not.
The Problem
Every sample produced two data exports: one from ESI (electrospray ionization) and one from APCI (atmospheric pressure chemical ionization). Different compounds ionize better with different techniques, so we ran both. Each export had 56 columns and over 200 rows. The method used heavy confirmatory ion coverage to reduce chromatography run time, allowing compounds to coelute without ambiguity. Good for throughput. Brutal for data processing.
The instrument software, Simplicity 3Q, exported everything: sample identification, mass transitions, ion ratios, internal standard responses, retention times, peak areas, instrument serial numbers, method file paths, plate positions, injection volumes.
For the final report, I needed about 10 of those columns. The other 46 were metadata useful for troubleshooting but not for client reports. They had to be deleted manually. Column by column. For every batch.
Then came row filtering. LC-MS/MS uses multiple reaction monitoring (MRM), where each compound is measured by fragmenting a precursor ion and detecting specific product ions. For each analyte, the method tracked 2-3 different transitions:
- Quantifier: The primary transition used for calculating concentration
- Qualifiers: Secondary transitions used to confirm identity
The software exported rows for all of them. A 74-analyte panel with internal standards and multiple confirmatory ions meant roughly 206 rows. I only needed the 74 quantifier rows for the report.
After cleaning came calculations:
- Converting ng/mL (instrument units) to ppm (regulatory units)
- Summing related compounds (some limits apply to totals, not individuals)
- Checking results against Connecticut’s action limits
Then formatting: headers, borders, print areas, conditional highlighting for anything exceeding regulatory thresholds.
Now imagine doing this by hand. Clicking through 56 columns, checking headers, deciding what stays and what goes. Scrolling through 206 rows, filtering, deleting. Entering formulas one cell at a time, hoping you don’t fat-finger a reference. Comparing numbers against a mental list of limits that vary by compound: 0.1 ppm for this one, 0.02 for that one, 0.5 for another.
For a single sample, you’re looking at well over an hour, and that’s just one ionization mode. Double it for the full workflow. And we needed to run multiple samples per day.
This was a compliance testing lab. The results determined whether products could be sold legally. An error didn’t mean a correction. It meant a recall, regulatory scrutiny, and a ruined client relationship. We had four clients at the time. Losing one would be catastrophic. The workflow needed to be perfect.
A manual workflow wasn’t slow. It was impossible. The math didn’t work. The error rate wouldn’t work. It simply wasn’t an option.
I had already proven myself by reducing turnaround time on our cannabinoid potency testing by 80%. So when the pesticide workflow needed to get up and running, the task landed on me: figure out how to make this work. Automation wasn’t an optimization. It was the only path forward.
Why VBA?
I had never written VBA before this. But I knew one thing: the solution had to work with what the lab already had.
- Install Python on lab computers
- Manage dependencies and environments
- Teach chemists to use a terminal
- Hope IT doesn't break it
- Leave behind unmaintainable code
- Excel already on every computer
- Macros live inside the workbook
- A button that says "Run"
- Anyone can use it without training
- Still works years after I left
I taught myself VBA by Googling error messages and reading Stack Overflow. The learning curve was steep. The first macro took days to write. But once it worked, it worked every time.
The Solution: VBA Macros
Macro 1: Column Killer
The raw export had 56 columns. I needed 10. That meant identifying 46 columns by header name and deleting them.
The key insight I learned (after breaking things): loop backwards.
' Loop backwards from last column to first
For i = Nend To 1 Step -1
If ShouldDelete(Cells(1, i).Value) Then
Cells(1, i).EntireColumn.Delete
End If
Next i
- Find the last column that has data start here
- Check the column header
- If it's on the "delete list" → remove the entire column
- If not → leave it alone
- Move left one column repeat
- Stop when you reach column 1
When you delete column C in a forward loop, column D becomes the new column C. Your loop index advances to what it thinks is D, but the original D is now at C and gets skipped.
Imagine you're in a line of people numbered 1-10, and you need to remove everyone wearing red. If you start at person #3 (red shirt) and remove them, everyone shifts forward. Now the person who WAS #4 is now #3. When you check position #4, you've actually skipped someone. Starting from the back avoids this shuffle.
The first version checked 46 column names one by one in a chain of If...Or statements. Not elegant. I eventually refactored it to use an array and helper function.
Full ColumnKiller code (click to expand)
Sub ColumnKiller()
Dim Nrow As Long, Nend As Long, i As Long
Nrow = 1
Nend = Cells(Nrow, Columns.Count).End(xlToLeft).Column
For i = Nend To 1 Step -1
If Cells(Nrow, i) <> "" Then
If Cells(Nrow, i) = "Sample File Name" _
Or Cells(Nrow, i) = "Mass Transition Q1/Q2" _
Or Cells(Nrow, i) = "Group" _
Or Cells(Nrow, i) = "Include in Standard Curve" _
Or Cells(Nrow, i) = "Scan Type" _
Or Cells(Nrow, i) = "Ion Source" _
Or Cells(Nrow, i) = "Sample ID" _
Or Cells(Nrow, i) = "Internal Standard" _
Or Cells(Nrow, i) = "Acquisition Time" _
Or Cells(Nrow, i) = "Analyst" _
Or Cells(Nrow, i) = "Instrument Serial Number" _
Or Cells(Nrow, i) = "Dilution" _
Or Cells(Nrow, i) = "Peak Left Boundary" _
Or Cells(Nrow, i) = "Retention Time Search Window" _
Or Cells(Nrow, i) = "Peak Selection Method" _
Or Cells(Nrow, i) = "Peak Right Boundary" _
Or Cells(Nrow, i) = "Peak Height" _
Or Cells(Nrow, i) = "Peak Height Average" _
Or Cells(Nrow, i) = "Peak Area IS Ratio" _
Or Cells(Nrow, i) = "Peak Height IS Ratio" _
Or Cells(Nrow, i) = "Concentration by Height" _
Or Cells(Nrow, i) = "Ion Ratio Area" _
Or Cells(Nrow, i) = "Expected Ion Ratio Area Range" _
Or Cells(Nrow, i) = "Expected Ion Ratio Height Range" _
Or Cells(Nrow, i) = "Sample Accuracy % (Area)" _
Or Cells(Nrow, i) = "Sample Accuracy % (Height)" _
Or Cells(Nrow, i) = "CV% (Area)" _
Or Cells(Nrow, i) = "CV% (Height)" _
Or Cells(Nrow, i) = "Retention Time Deviation %" _
Or Cells(Nrow, i) = "Internal Standard Retention Time" _
Or Cells(Nrow, i) = "Internal Standard Peak Area" _
Or Cells(Nrow, i) = "Internal Standard Peak Height" _
Or Cells(Nrow, i) = "Extrapolation by Area %" _
Or Cells(Nrow, i) = "Extrapolation by Height %" _
Or Cells(Nrow, i) = "Instrument Model" _
Or Cells(Nrow, i) = "LC Method" _
Or Cells(Nrow, i) = "MS Method" _
Or Cells(Nrow, i) = "Plate Code" _
Or Cells(Nrow, i) = "Plate Position" _
Or Cells(Nrow, i) = "Vial Position" _
Or Cells(Nrow, i) = "Injection Volume" _
Or Cells(Nrow, i) = "Sample Batch Quantitation Method" _
Or Cells(Nrow, i) = "Flagging Thresholds" _
Or Cells(Nrow, i) = "Signal to Noise Ratio" _
Or Cells(Nrow, i) = "Smoothing" _
Or Cells(Nrow, i) = "Ion Ratio Height" _
Or Cells(Nrow, i) = "Known Concentration" _
Or Cells(Nrow, i) = "Peak Confidence" _
Or Cells(Nrow, i) = "Concentration Unit" Then
Cells(Nrow, i).EntireColumn.Delete
End If
End If
Next i
End Sub
' Refactored version with helper function:
Private Function IsColumnToDelete(headerName As String) As Boolean
Dim deleteList As Variant
deleteList = Array("Sample File Name", "Mass Transition Q1/Q2", _
"Group", "Include in Standard Curve", "Scan Type", _
' ... 46 column names total
"Peak Confidence", "Concentration Unit")
IsColumnToDelete = False
For Each item In deleteList
If headerName = item Then
IsColumnToDelete = True
Exit Function
End If
Next item
End Function
Both versions did the job. That’s what mattered.
Macro 2: Row Filter (Quantifiers Only)
After cleaning columns, I needed to filter rows. The “Component Type” column indicated whether each row was a Quantifier or Qualifier. I only needed quantifiers.
LC-MS/MS identifies chemicals by breaking them apart and measuring the fragments. For each pesticide, we track multiple fragment patterns (called "transitions"). The quantifier is the primary transition used to measure concentration. If we get a positive result, we go back and check the qualifier transitions to verify it's real and not a false positive.
Imagine a security checkpoint. The quantifier is like scanning your ID to get your name. If your name shows up on a watchlist, they check the qualifiers: your photo, your fingerprints. The report only needs the ID scan results, but the verification data is there if something flags.
Same principle: loop backwards to avoid index shifting when deleting rows.
' Keep only Quantifier rows, delete everything else
For x = cRange.Cells.Count To 1 Step -1
If cRange.Cells(x).Value <> "Quantifier" Then
cRange.Cells(x).EntireRow.Delete
End If
Next x
- Start at the last row of data start here
- Check the "Component Type" column
- If it says "Quantifier" → keep the row
- If it says anything else → delete the entire row
- Move up one row repeat
- Stop when you've checked all rows
Full DeleteRows code (click to expand)
Sub DeleteRows()
Dim cRange As Range, LastRow As Long, x As Long
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set cRange = Range("C2:C" & LastRow)
For x = cRange.Cells.Count To 1 Step -1
With cRange.Cells(x)
If .Value <> "Quantifier" Then
.EntireRow.Delete
End If
End With
Next x
End Sub
This took roughly 206 rows down to 74.
Macro 3: Report Formatting
With clean data, I needed to add calculations and formatting:
PPM calculation: The instrument reported concentration in ng/mL. Regulatory limits are in ppm (micrograms per gram). The formula also used IFERROR to display “N.D.” (not detected) instead of division errors when sample mass was zero.
PPM means "parts per million." The instrument measures in ng/mL (nanograms per milliliter of solution), but regulators set limits in ppm (micrograms per gram of actual cannabis).
Imagine measuring how much salt is in seawater versus how much is in the dried sea salt on your table. Same salt, different forms. We need to account for the water we used to dissolve the sample and the original weight of cannabis to get a meaningful number.
The PPM formula handled unit conversion and error cases:
' =IFERROR(((conc * dilution) / mass) / 1000, "N.D.")
Range("E2").FormulaR1C1 = "=IFERROR(((RC[-1]*R78C2)/R78C1)/1000,""N.D."")"
Range("E2").AutoFill Destination:=Range("E2:E75")
- Take the instrument reading (ng/mL)
- Multiply by the dilution factor (how much we diluted the sample)
- Divide by the sample mass (how much cannabis we started with)
- Divide by 1000 (convert nanograms to micrograms)
- If anything goes wrong (like dividing by zero) → show "N.D." instead of an error
Summed analyte calculations: Some regulatory limits apply to compound totals. Natural pyrethrin insecticides contain six related compounds (Pyrethrin I & II, Cinerin I & II, Jasmolin I & II). The limit applies to their sum.
Pyrethrin ratio table: This one we learned the hard way. Cannabis naturally contains compounds that trigger positive responses on some pyrethrin MRM transitions, but not all of them. We were getting “positive” pyrethrin results on samples that had never seen an insecticide.
Natural pyrethrins have a characteristic component distribution: roughly 71% pyrethrin, 21% cinerin, 7% jasmolin. A quick glance at the ratio table told you immediately if something was off. If the ratios didn’t match, you knew to dig deeper into the daughter ion checks. The ratio table became a first-line scan before diving into the chromatography.
The ratio check compared measured vs expected percentages:
Range("A87").Value = "Pyrethrin I & II"
Range("B87").FormulaR1C1 = "=IFERROR(SUM(R[-25]C[3]:R[-24]C[3])/R[-6]C,"""")"
Range("C87").Value = "71%"
- Label the row "Pyrethrin I & II"
- Calculate the measured percentage:
- Add up all pyrethrin values detected
- Divide by the total pyrethrins
- If math fails (no pyrethrins detected) → show blank
- Show the expected value: 71%
Full ESIPestStaging13 code (click to expand)
Sub ESIPestStaging13()
' --- HEADER SETUP ---
Range("H1").Value = "PPM (micrograms/g)"
Columns("H:H").EntireColumn.AutoFit
Range("A:A,C:C,D:D").Delete Shift:=xlToLeft
' --- SAMPLE INFO SECTION ---
Range("A77").Value = "Sample Mass (g)"
Range("B77").Value = "Dilution Factor"
Range("B78").Value = "5"
' --- PPM FORMULA ---
Range("E2").FormulaR1C1 = "=IFERROR(((RC[-1]*R78C2)/R78C1)/1000,""N.D."")"
Range("E2").AutoFill Destination:=Range("E2:E75")
' --- SUMMED ANALYTES TABLE ---
Range("A80").Value = "Summed Analyte"
Range("B80").Value = "PPM (micrograms/g)"
Range("A81").Value = "Total Pyrethrins"
Range("B81").FormulaR1C1 = _
"=SUM(R[-19]C[3]:R[-18]C[3],R[-38]C[3]:R[-37]C[3],R[-61]C[3]:R[-60]C[3])"
Range("A82").Value = "Total Spinetoram"
Range("B82").FormulaR1C1 = "=SUM(R[-17]C[3]:R[-16]C[3])"
Range("A83").Value = "Total Spinosad"
Range("B83").FormulaR1C1 = "=SUM(R[-16]C[3]:R[-15]C[3])"
' --- PYRETHRIN RATIOS TABLE ---
Range("A85:C85").Merge
Range("A85").Value = "Pyrethrins Ratios"
Range("A86").Value = "Component"
Range("B86").Value = "Measured %"
Range("C86").Value = "Expected %"
Range("A87").Value = "Pyrethrin I & II"
Range("B87").FormulaR1C1 = "=IFERROR(SUM(R[-25]C[3]:R[-24]C[3])/R[-6]C,"""")"
Range("C87").Value = "71%"
Range("A88").Value = "Jasmolin I & II"
Range("B88").FormulaR1C1 = "=IFERROR(SUM(R[-45]C[3]:R[-44]C[3])/R[-7]C,"""")"
Range("C88").Value = "7%"
Range("A89").Value = "Cinerin I & II"
Range("B89").FormulaR1C1 = "=IFERROR(SUM(R[-69]C[3]:R[-68]C[3])/R[-8]C,"""")"
Range("C89").Value = "21%"
' --- NUMBER FORMATTING ---
Range("D2:E75").NumberFormat = "0.0000"
Range("A78,B81:B83").NumberFormat = "0.0000"
Range("B87:B89").NumberFormat = "0.0%"
' --- PAGE SETUP ---
With ActiveSheet.PageSetup
.LeftHeader = "Page &P of &N" & Chr(10) & "Pesticide & Mycotoxin Screen"
.CenterHeader = "&14Sample ID: &""Calibri,Bold""&A"
.RightHeader = "&D &T" & Chr(10) & "&F"
.PrintArea = "$A$1:$E$93"
End With
End Sub
Macro 4: Regulatory Flagging
Connecticut’s cannabis regulations specified action limits for each pesticide. If a result exceeded the limit, the product failed compliance testing and couldn’t be sold.
The limits varied by compound. Most were 0.1 ppm, but some were as low as 0.02 ppm (carbamates, organophosphates) and others as high as 10 ppm.
I used conditional formatting to highlight exceedances in pink with dark red text:
Private Sub ApplyLimitFormat(rng As Range, limit As Double)
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" & limit
With rng.FormatConditions(rng.FormatConditions.Count)
.Font.Color = RGB(156, 0, 6) ' Dark red text
.Interior.Color = RGB(255, 199, 206) ' Pink background
End With
End Sub
- Take a group of cells and a limit value
- Add a rule: "If this cell is greater than the limit..."
- Apply formatting:
- Text color → dark red
- Background → pink
- Now any value over the limit automatically turns pink
The formatting is "live." If someone changes a value, the highlighting updates automatically. No need to re-run anything. The rules stay with the spreadsheet forever.
It's like setting a speed trap that paints speeding cars red. You don't check each car manually. The system catches violations on its own, even years after you set it up.
Different analytes had different limits (0.02, 0.1, 0.5, 1.0, 2.0 ppm, etc.). The full macro grouped cells by their regulatory threshold and applied the appropriate conditional format.
Full ESIAllPestFlagging code (click to expand)
Sub ESIAllPestFlagging()
' --- 0.1 ppm LIMIT (most analytes) ---
Union(Range("E2:E5"), Range("E10:E12"), Range("E14"), Range("E17"), _
Range("E19"), Range("E22:E23"), Range("E26:E29"), Range("E31:E41"), _
Range("E45"), Range("E48"), Range("E50:E52"), Range("E55"), _
Range("E57"), Range("E59:E61"), Range("E64:E73"), Range("E75"), _
Range("B82:B83")).Select
Call ApplyLimitFormat(Selection, 0.1)
' --- 0.02 ppm LIMIT ---
Range("E6:E9,E53").Select
Call ApplyLimitFormat(Selection, 0.02)
' --- 0.5 ppm LIMIT ---
Union(Range("E16"), Range("E46"), Range("E54"), Range("E56"), _
Range("E62:E63"), Range("E43:E44"), Range("E20:E21"), _
Range("B81")).Select
Call ApplyLimitFormat(Selection, 0.5)
' Additional limits: 0.7, 1.0, 2.0, 3.0, 5.0, 10.0 ppm
' ... (similar pattern for each threshold)
End Sub
Private Sub ApplyLimitFormat(rng As Range, limit As Double)
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" & limit
With rng.FormatConditions(rng.FormatConditions.Count)
.SetFirstPriority
.Font.Color = RGB(156, 0, 6)
.Interior.Color = RGB(255, 199, 206)
.StopIfTrue = False
End With
End Sub
For products intended for inhalation (flower, vape cartridges), I added a three-color “traffic light” system:
- Green: Below limit (pass)
- Yellow: 100-110% of limit (marginal, may warrant retest)
- Pink: Above 110% of limit (fail)
The yellow zone helped flag results that were technically passing but close enough to warrant extra scrutiny.
Batch Wrappers
Each sample in a batch got its own worksheet. Running macros manually on ten worksheets would take time and invite mistakes.
Batch wrappers ran a macro across all worksheets at once. The key optimization: disabling ScreenUpdating prevented Excel from redrawing after every operation.
Sub BatchColumnKiller()
Application.ScreenUpdating = False ' Speed boost
For Each xSh In Worksheets
xSh.Select
Call ColumnKiller
Next
Application.ScreenUpdating = True
End Sub
- Turn off screen updates (don't redraw while working)
- For each worksheet in the workbook: repeat
- Go to that worksheet
- Run the ColumnKiller macro
- Turn on screen updates (show the final result)
Every time Excel changes something, it redraws the screen. With hundreds of operations, that's hundreds of redraws. Turning off ScreenUpdating tells Excel "just do the work, I'll look at the end."
Imagine repainting a room while someone watches through a window, and you have to wave at them after every brush stroke. It would take forever. ScreenUpdating = False is like closing the blinds until you're done.
All batch wrappers (click to expand)
Sub BatchColumnKiller()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call ColumnKiller
Next
Application.ScreenUpdating = True
End Sub
Sub BatchDeleteRows()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call DeleteRows
Next
Application.ScreenUpdating = True
End Sub
Sub BatchESIAllPestStaging()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call ESIPestStaging13
Next
Application.ScreenUpdating = True
End Sub
Sub BatchESIAllPestFlagging()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call ESIAllPestFlagging
Next
Application.ScreenUpdating = True
End Sub
The full suite processed a ten-sample batch in about twelve seconds.
The Workflow After Automation
Total time per batch: 2-3 minutes (including manual mass entry and review).
Results
For context: if someone tried to do this manually, I’d estimate 90-120 minutes per batch. Maybe faster if you’re rushing, but rushing through compliance data is how you end up with recalls.
And errors? With 46 columns to identify and delete, 132 qualifier rows to filter out, 74 formulas to enter, and limit checks across ten different thresholds, I’d guess a 10-20% chance of at least one mistake per batch. Maybe higher. Run multiple batches per day and errors become a near-certainty.
The automation didn’t improve an existing workflow. It made a workflow possible that otherwise wouldn’t have existed.
The conditional formatting caught every limit exceedance automatically. No more scanning columns of numbers against a mental list of thresholds. No more wondering if you missed something at the end of a long day.
Training became “run these four buttons in order” instead of walking someone through dozens of manual steps they’d inevitably get wrong.
Lessons
Column deletion was the most tedious part, so I automated it first. Early wins built momentum.
The first macros were rough. Features got added as needs emerged. The pyrethrin ratio table came after we discovered the false positive problem the hard way.
Edge cases revealed bugs. What happens when sample mass is zero? Blank samples? QC samples with unusual naming? Running macros on actual batches exposed issues that desk-checking missed.
VBA wasn't my first choice as a programmer. But the macros kept running after I left. No Python environment to maintain. They just opened Excel and clicked buttons.
Looking Back
This was 2021. The macros are still on my hard drive. They’re not sophisticated by any standard.
But they solved a real problem. They turned over an hour of tedious, error-prone work into seconds of automated processing. They freed time for work that actually required thinking: troubleshooting instrument issues, developing new methods, training colleagues.
The approach was the same one I use now: identify the repetitive, error-prone work and make it automatic. The tools change. The instinct doesn’t.