Microsoft Excel · Automation bounded introductory track · Intermediate
Record, run and safely store a desktop VBA macro
Record a bounded formatting macro in your own workbook, test its fixed range, and retain it in macro-enabled format.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Use Developer > Record Macro, store the recording in This Workbook, perform only the intended actions, and choose Stop Recording. Save in an Excel macro-enabled format so the VBA is retained. Test on a copy: running a macro cannot be undone like ordinary cell editing.
Safe prerequisites
Use English Excel for Microsoft 365 desktop on Windows with the Developer tab available and your organization’s existing macro policy permitting your own local recording. This is not an Excel for the web workflow: the browser can retain a macro-enabled workbook but cannot create, edit, or run VBA macros.
Use a new, nonsensitive practice workbook. Do not import code, open a downloaded macro workbook, weaken security settings, or choose a global trust change for this exercise. If policy blocks execution, stop and ask your authorized administrator about the supported route. Never treat a macro warning as proof that code is safe.
On a sheet named Practice, enter Amount in B1 and 10,20,30,40 in B2:B5. In D1 enter Check total and in D2 enter =SUM(B2:B5), which should return 100. Save a backup before recording.
Record only the intended formatting
- Choose Developer > Record Macro.
- Name the macro FormatAmounts. Use no spaces and leave the shortcut key empty, avoiding accidental replacement of a familiar Excel shortcut.
- Set Store macro in to This Workbook, not Personal Macro Workbook. Add a description such as Format B2:B5 on the practice sheet; no value changes.
- Select OK to begin recording.
- Explicitly select B2:B5. Apply bold and a number format with two decimal places.
- Choose Developer > Stop Recording immediately. Avoid extra selections, typing, or unrelated commands while recording, since the recorder captures your actions.
Before testing the range boundary, choose Developer > Macros, select FormatAmounts, and choose Edit to inspect your generated routine. Require a fixed selection such as Range("B2:B5").Select. If it instead uses relative Offset/Resize operations, do not use it for this fixed-range exercise. Return to a known fixed-range recording before proceeding; do not assume the current selection makes a relative recording absolute. Close the editor without changing security settings.
The visible result should be bold amounts displayed as 10.00,20.00,30.00,40.00. D2 remains 100 because formatting does not change the underlying numbers. This recording owns a fixed range; it is not automatically a whole-column or expanding-table operation.
Rerun and test the boundary
After recording has stopped, manually remove bold and return B2:B5 to General format. Make sure Practice is the active sheet. Choose Developer > Macros, select FormatAmounts in this workbook, and run it. Verify that the four cells regain the chosen formatting and the total remains 100.
Now put 50 in B6 and remove any formatting there. Run FormatAmounts again. B2:B5 should be formatted; B6 should remain outside the recorded range. The original D2 check still totals B2:B5=100, while =SUM(B2:B6) is 150. This demonstrates that a newly added row is not included merely because it sits beneath the recorded range.
If the wrong cells change, stop using the recording. Restore a saved copy or close the unsaved test without saving, rather than relying on Undo. Rerecord a short, controlled sequence before considering any real workbook.
Save the macro, not only the cells
Save the working copy using Excel Macro-Enabled Workbook (.xlsm). A macro-free XLSX cannot retain VBA; do not accept a conversion that discards the macro if you need it. The Microsoft save-format reference listed below is historical Excel 2013 documentation used for the XLSM requirement, not a claim about current dialog layout.
Close and reopen your locally created XLSM under your existing approved policy. Check Developer > Macros for FormatAmounts and verify the same bounded behavior on the practice sheet. Do not change trust settings just to finish this persistence check.
Keep the backup and a clear description of the target range. For the independent total, see SUM. Recording is useful when the sequence is predictable; safe reuse still depends on verifying the workbook, sheet, and range before every run.