VBA to SQL: Report Automation

Many VBA macros really do what a database should (aggregate, filter, join). Rewriting them as SQL is faster and more accurate.

Scenario Mapping

VBA SQL
WorksheetFunction.Sum(Range) SELECT SUM(col) FROM t
If x > 1000 Then CASE WHEN x > 1000 THEN ... END
For i = 1 To N accumulating SUM aggregate (no loop)
VLookup JOIN
rs.Open "SELECT..." Extract embedded SQL as-is

Core Idea

What VBA does row by row in a loop, SQL does in one set operation. That's the biggest mental shift.

Worked Example

VBA (row-by-row):

vba
Sub Total()
    Dim t As Double, i As Integer
    For i = 2 To 100
        t = t + Cells(i, 3).Value
    Next i
    MsgBox t
End Sub

To SQL (one aggregate):

sql
SELECT SUM(Amount) AS Total FROM Orders;

Common Issues

  • VBA with OpenRecordset embeds SQL — the converter extracts it as-is
  • Conditional aggregates use CASE WHEN or SUM(CASE...)

Tools

Use the online VBA-to-SQL tool to auto-extract aggregate/filter/join patterns.