What are macros and VBA, and when should you use them?

VBA (Visual Basic for Applications) is the programming language embedded in Office. A macro is a stored VBA procedure. The macro recorder captures your actions as code, which is a useful starting point but produces verbose, fragile code that relies on selecting cells.

When VBA is the right tool:

• Automating interface actions — formatting, printing, creating sheets, moving files.
• Building custom user forms for data entry.
• Custom functions that dynamic arrays and LAMBDA cannot express.
• Interacting with other applications, such as generating Outlook emails from a list.
• Loops with genuinely complex conditional logic.

When it is the wrong tool — and this is the more important half:

• Importing and cleaning data. Use Power Query. It is more maintainable, faster, and does not require anyone to read code.
• Calculations. Formulas and dynamic arrays are clearer and recalculate automatically.
• Anything that needs to run unattended or in Excel Online, where VBA does not run at all. Office Scripts or Power Automate fit better.

Practical cautions: macro-enabled files are .xlsm and are frequently blocked by security policy; VBA actions generally cannot be undone; and avoid Select and Activate — work with object references directly, which is faster and far more reliable.