VBA and macros

Recording first

Developer → Record Macro captures your actions as VBA. It is the fastest way to learn the object model: do the thing, then read what Excel wrote.

Recorded code is verbose and full of Select and Activate statements. Editing it down teaches you more than starting from a blank module.

Save as .xlsm — a plain .xlsx silently discards macros.

Cleaner code

Recorded:

Range("A1").Select
Selection.Value = "Total"

Better:

Range("A1").Value = "Total"

Avoid Select entirely; work with objects directly. Declare variables with Dim and put Option Explicit at the top of every module so typos become errors instead of silent bugs.

A simple loop

Sub FlagLarge()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For i = 2 To lastRow
        If ws.Cells(i, "B").Value > 1000 Then
            ws.Cells(i, "C").Value = "Large"
        End If
    Next i
End Sub

End(xlUp) from the bottom is the standard way to find the last used row. For large ranges, reading into a variant array and writing back once is dramatically faster than touching cells individually.

When not to use VBA

If the task is importing and reshaping data, Power Query is better — it is maintainable, refreshable, and does not require macro-enabled files that IT may block.

If the workbook lives in Excel on the web or needs to run in a modern cloud workflow, Office Scripts (TypeScript) is the supported path; VBA does not run there.

VBA remains right for interactive desktop automation, custom dialogs, and manipulating Excel's own objects — printing, formatting, sheet management.