How to Automate Excel with VBA — Beginner to Advanced

VBA is unfashionable. It also runs offline, has zero install friction, and saves finance teams enormous time. Here's the path from beginner to production.

Setup

Excel → Developer tab → Visual Basic Editor (Alt+F11 on Windows, Fn+Option+F11 on Mac). Insert → Module. Write code there.

Beginner: a useful macro in 5 lines

Sum the visible cells of a filtered range:

Function VisibleSum(rng As Range) As Double
  Dim c As Range, total As Double
  For Each c In rng.SpecialCells(xlCellTypeVisible)
    total = total + c.Value
  Next c
  VisibleSum = total
End Function

Use as =VisibleSum(A:A). Now SUM respects your filter.

Intermediate: batch process all sheets

Sub CleanAllSheets()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    ws.Cells.WrapText = False
    ws.Columns.AutoFit
    ws.Cells.Replace " ", " ", xlPart
  Next ws
End Sub

Bind to a button or keyboard shortcut (Macros → Options → Ctrl+Shift+C).

Advanced: pull data from API

Sub FetchData()
  Dim http As Object
  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "GET", "https://api.example.com/data", False
  http.send
  ' Parse http.responseText (JSON) → write to cells
End Sub

Performance tips

• Turn off Application.ScreenUpdating and .Calculation = xlManual at start, restore at end
• Avoid loops over individual cells — use array operations (arr = ws.UsedRange.Value)
• Read once, process in memory, write once

When to use Apps Script instead

Apps Script is Google Sheets' VBA equivalent — but cloud-native and triggerable on a schedule. For Google Workspace shops, Apps Script wins.

Hire an Excel VBA developer →