Simple VBA Macros: Asking for Them Safely and Testing Them

A macro is a small program inside Excel that repeats a series of steps for you: formatting a report, splitting a sheet, cleaning a column, saving copies. Writing VBA (the language of Excel macros) used to need a programmer. Now an AI can write simple macros for you, but you must use them carefully, because a macro's changes cannot be undone with Ctrl+Z.

When a macro is worth it

Use a macro when you repeat the same clicks every day or week and the steps never change. For one-off jobs, formulas, Flash Fill or Power Query are usually safer.

Good first macros:

• Format a raw export (fonts, column widths, header colour, freeze top row)
• Delete blank rows in a range
• Copy each region's rows to its own sheet
• Save the active sheet as a PDF with today's date in the file name

Turn on the Developer tab

1. File → Options → Customize Ribbon.
2. Tick Developer on the right, click OK.
3. Press Alt+F11 to open the VBA editor. In the editor, Insert → Module gives you a place to paste code.

Ask clearly

Describe the sheet and every step, exactly as you would explain it to a new colleague:

Write an Excel VBA macro for Excel 365 that works on the sheet named
"Export":
1. Delete every row where column A is empty, from row 2 down to the last
   used row.
2. Make row 1 bold with a light grey fill.
3. Autofit columns A to H.
4. Freeze the top row.
Do not touch any other sheet. Add a comment above each step explaining it,
and show a message box at the end saying how many rows were deleted.

Asking for comments and a final message makes the macro easier to understand and check.

Read before you run

Before running any macro, AI-written or not, ask:

Explain this macro line by line. Does it delete, overwrite or save
anything? Does it affect any sheet other than "Export"? Is there any
risk of it running on the wrong data?

Look for words like Delete, Clear, Save, Kill (deletes files) and loops over all sheets. If you do not understand what a line does, ask before running.

Test safely

1. Save a copy of the workbook and test only on the copy.
2. Run the macro: in the VBA editor, click inside it and press F5, or in Excel use Developer → Macros → Run.
3. Check the result: row counts, totals, formatting.
4. If something goes wrong, close the copy without saving and try again.

If you get an error, copy the error message and the highlighted line back to the AI:

The macro stops with "Run-time error 9: Subscript out of range" on the
line: Set ws = ThisWorkbook.Sheets("Export"). What is wrong?

(In this case, the sheet is probably named differently, for example "export " with a space.)

Save it correctly

A normal .xlsx file cannot keep macros. Use File → Save As → Excel Macro-Enabled Workbook (.xlsm). If you save as .xlsx, Excel warns you and the macro is removed.

Stay safe with macros

• Never enable macros in files from unknown senders or downloads. Macros can contain harmful code.
• Your company may block macros by policy. Ask IT before relying on them.
• Keep a short note in the workbook explaining what each macro does and who wrote it.

Practice task

Pick one formatting or clean-up job you repeat every week. Ask the AI for a commented macro with a final message box. Ask it to explain the code, test it on a copy, fix any error with the AI's help, and save the working version as .xlsm.

Then mark this lesson complete. You have finished the course!

Where to go next

• Sharpen your prompts with Prompt Writing That Works.
• See all free courses for your field.

Key takeaways

• Macros automate fixed, repeated steps; Ctrl+Z cannot undo them.
• Describe the sheet and each step clearly, and ask for comments and a final message.
• Ask the AI to explain the code and its risks before you run it, and test on a copy.
• Save as .xlsm, and never enable macros from unknown files.