I've written VBA macros that saved 40 hours a month. I've also watched those same macros become unmaintainable nightmares 18 months later — dependencies nobody understood, security warnings nobody knew how to dismiss, and a team afraid to touch the file. Here's what I've learned about when VBA is the right tool and when it becomes your biggest liability.
VBA has a bad reputation among developers who've never used it for serious finance work, and a dangerously good reputation among accountants who've used it to solve every problem. Both camps are wrong. VBA is a powerful, pragmatic tool with a specific and well-defined ceiling — and understanding where that ceiling is will save you from either under-using or over-depending on it.
Before we get to the limits, let's be honest about the power. VBA is deeply integrated into Excel's object model. It can do things that no other tool does with the same ease and fidelity — specifically within the Excel environment your team already uses every day.
The key insight: VBA's power is precisely scoped. Within Microsoft Office, particularly Excel, it's one of the most effective automation tools available. The moment you need to go outside that environment — web APIs, cloud data, multi-user access — its limitations become structural.
VBA can read a row of invoice data and produce a beautifully formatted invoice in seconds — logo, address, line items, tax calculations, all laid out on a print-ready template. A macro that generates 50 invoices in the time it used to take to format one is genuinely valuable and absolutely within VBA's strength zone.
Banks export statements in formats designed for archiving, not analysis. VBA can take a raw .CSV export, normalise the columns, apply category rules (keywords → expense type), and output a clean categorised ledger. What takes an analyst 3 hours manually takes VBA about 20 seconds.
If your business units each maintain their own P&L sheet and you need to consolidate them into a group report, VBA handles this elegantly — looping through sheets, pulling the right ranges, and writing them into a summary with formatting intact. No formulas to break. No manual copy-paste.
Sub GenerateInvoices()
Dim ws As Worksheet
Dim tmpl As Worksheet
Dim i As Long
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("InvoiceData")
Set tmpl = ThisWorkbook.Sheets("Template")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow ' Skip header row
Dim newSht As Worksheet
tmpl.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set newSht = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
newSht.Name = "INV-" & ws.Cells(i, 1).Value
' Populate from data row
newSht.Range("B4").Value = ws.Cells(i, 2).Value ' Client name
newSht.Range("B5").Value = ws.Cells(i, 3).Value ' Address
newSht.Range("D10").Value = ws.Cells(i, 4).Value ' Amount
newSht.Range("D11").Value = ws.Cells(i, 5).Value ' Tax
' Export to PDF
newSht.ExportAsFixedFormat xlTypePDF, _
Environ("USERPROFILE") & "\Desktop\INV-" & _
ws.Cells(i,1).Value & ".pdf"
Application.DisplayAlerts = False
newSht.Delete
Application.DisplayAlerts = True
Next i
MsgBox "Done! " & (lastRow - 1) & " invoices exported to Desktop."
End Sub"VBA's power and its fragility come from the same source: it lives entirely inside the file. That makes it fast to build, fast to run — and impossible to maintain when the person who built it is no longer around."
Every VBA macro is implicitly owned by whoever wrote it. There's no formal documentation system. No code review culture. No version control. The knowledge lives in the head of the author and the comments they left (usually none).
When that person leaves, gets promoted, or goes on leave, the macro becomes a black box. You run it and hope. You don't touch it because you're afraid you'll break it. This is not a hypothetical — it happens in the majority of businesses that use VBA for anything important.
VBA macros run in a single-user Excel environment. If two people open the file simultaneously, behaviour is unpredictable. Data can be overwritten. Macros can fail silently. There's no locking mechanism, no conflict resolution, and no audit trail of who ran what and when.
For a solo analyst, this isn't a problem. For a finance team of 4+, it's a structural constraint that limits how you can design your workflows.
By default, Excel blocks macros from running unless the user explicitly enables them. In corporate environments with strict Group Policy settings, macros may be disabled entirely. Every new machine requires a trust configuration. Every new team member needs to be walked through "Enable Macros." This friction compounds as teams grow — and it's a reason VBA automations often get abandoned quietly rather than maintained.
VBA can make HTTP requests, but doing so requires WinHTTP or XMLHTTP objects, manual JSON parsing (no native library), and significant error handling. Calling a bank API, pulling GST data, or connecting to Zoho CRM in VBA is technically possible but practically painful — and the results are fragile.
Anything requiring cloud data — live exchange rates, bank balances, CRM data — requires workarounds that create their own maintenance overhead.
VBA runs on Windows Excel. Mac Excel has partial VBA support, with significant gaps. Excel Online (browser-based) has zero VBA support. As more finance teams move to remote work, cloud tools, and mixed operating environments, VBA becomes an increasingly poor foundation for anything business-critical.
| Use Case | VBA? | Better Alternative |
|---|---|---|
| Invoice generation from template | ✓ Yes | VBA works well here |
| One-off data parsing / formatting | ✓ Yes | Python if large volume |
| Multi-sheet consolidation | ⚠ Maybe | ERPNext reports / Power Query |
| Daily payment reminders | ✗ No | Google Apps Script / ERPNext |
| Real-time bank reconciliation | ✗ No | ERPNext bank integration |
| Multi-user AP/AR tracking | ✗ No | ERPNext / Zoho Books |
| GST/TDS calculation and filing prep | ⚠ Maybe | GST-integrated ERP |
| Month-end close workflow | ✗ No | ERPNext close checklist |
Here are the signals I watch for that tell me a business has pushed VBA past its useful life:
If three or more of these are true, the macro is no longer an asset. It's technical debt with Excel formatting.
The transition out of VBA doesn't have to be sudden. Here's the order I recommend:
The guiding principle: Automate tasks at the layer where the data already lives. If the data is in Excel, VBA is fine. If it should be in a database, no amount of VBA will substitute for a system that was designed for it.
I help finance leaders audit their current tools — VBA, Sheets, Tally, ERPNext — and design a stack that's actually maintainable, scalable, and built for growth. One focused conversation is usually enough to have clarity.
Book a Free Strategy Call