Day 23: Macros & VBA Basics

A macro is a recorded or written sequence of actions in VBA (Visual Basic for Applications) that automates repetitive tasks. Analysts should use macros when: (1) The same formatting or data manipulation steps are repeated frequently. (2) Tasks require loops or logic that formulas cannot handle. (3) Generating standardised reports from raw data with one click. However, macros have downsides: they make workbooks harder to share (.xlsm format, security warnings), are brittle when layouts change, and are hard to maintain if not documented. Power Query handles most data transformation macros better today.

View → Macros → Record Macro (or click the record button in the status bar). Give the macro a name (no spaces), assign a shortcut key if desired, and choose where to store it (This Workbook for workbook-specific, Personal Macro Workbook for all workbooks). Perform the actions you want to record. Click Stop Recording. To run: View → Macros → Run, or press the shortcut key. To edit the recorded code: View → Macros → Edit opens the VBA editor.

A Sub (subroutine) is the basic unit of VBA code. Structure: Sub MacroName() ... code ... End Sub. Variables are declared with Dim: Dim ws As Worksheet. Reference a worksheet: Set ws = ThisWorkbook.Sheets("Data"). Reference a cell: ws.Range("A1").Value = "Hello". Loop through rows: For i = 2 To 100 ... Next i. Conditional: If ws.Cells(i,2).Value = "North" Then ... End If. Open the VBA editor with Alt+F11.

In the VBA editor: Tools → VBAProject Properties → Protection tab → check "Lock project for viewing" → set a password → OK. Save the file as .xlsm. Now the VBA code cannot be viewed or edited without the password. Note: this protects the code from casual viewing, not from determined reverse engineering. For shared workbooks where you want users to run macros but not see the code, this is the standard approach.

.xlsx is the standard Excel format — cannot contain macros. .xlsm is the macro-enabled format — required to save files containing VBA code. When a user opens an .xlsm file, Excel shows a security warning "Macros have been disabled" — the user must click Enable Content to run macros. In corporate environments, macros may be blocked by IT policy. This is one reason Power Query and formulas are preferred over macros for shared workbooks — they work in standard .xlsx files without security prompts.