Introduction to Macros (Automation)

A Macro is a recorded sequence of instructions that Excel repeats on command. Think of it like a "Macro-Recorder" or a camcorder: you perform the task once, and Excel writes the code (VBA) to do it again instantly.

Step 0: The "Hidden" Menu

Before you can work with Macros, you must enable the Developer Tab. * Action: Right-click any tab on the Ribbon > Customize the Ribbon > Check the box for Developer.

Part 1: Recording & Naming Macros

When you record a macro, Excel captures every click and keystroke.

🛠 How to Record

1. Go to the Developer tab > Record Macro.
2. Macro Name: Must start with a letter and contain no spaces (e.g., Format_Monthly_Report).
3. Store Macro in: Usually "This Workbook."
4. Shortcut Key: (Optional) e.g., Ctrl + Shift + M.
5. Perform your tasks (e.g., make headers bold, change font to 12pt, add a border).
6. Click Stop Recording on the Developer tab.

💡 The "Relative References" Secret

By default, Excel records absolute positions (e.g., "Go to cell A1"). If you want your macro to work starting from wherever your mouse is currently clicked, you must toggle on Use Relative References before you start recording.

Part 2: Editing Simple Macros (Behind the Scenes)

Sometimes a macro is 90% perfect, but you need to change a small detail (like a Header title or a specific color code). You don't need to re-record the whole thing; you can edit the VBA (Visual Basic for Applications) code.

🛠 How to Edit

1. Go to Developer > Macros.
2. Select your macro and click Edit. This opens the VBA Editor.
3. Look for the "Green Text" (Comments) and find the lines of code.

Practical Example: The "Surgical" Edit

Scenario: You recorded a macro that puts "March 2026" in a cell. Now it is April, and you want to update it. * Original Code: ActiveCell.FormulaR1C1 = "March 2026" * Your Edit: Simply click into the code window and change "March 2026" to "April 2026". * Action: Close the window and Save. You have just "coded"!

Part 3: Running and Assigning Macros

You can trigger a macro in three ways: 1. Shortcut Key: (The Ctrl + Shift combo you set). 2. The Macro List: Developer > Macros > Run. 3. Buttons: Go to Insert > Button (Form Control). Draw a button on your sheet, and Excel will immediately ask which Macro to assign to it.

⚠️ The "Macro-Enabled" Warning

Standard Excel files (.xlsx) cannot store macros. If you save as a normal file, your macros will be deleted forever. * Requirement: You must save your file as an Excel Macro-Enabled Workbook (.xlsm).

Part 4: The Personal Macro Workbook (PERSONAL.XLSB)

A macro stored in This Workbook is only available in that one file. If you want a macro—say, "format any selection as a clean header row"—to be available in every workbook you open, store it in your Personal Macro Workbook.

What it is

• A hidden binary workbook named PERSONAL.XLSB that Excel auto-opens (invisibly) every time you launch the application.
• Default file location: %APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB
• The file is created the first time you record a macro and choose Personal Macro Workbook as the destination.

Storing a macro there

1. Developer > Record Macro.
2. In the Store macro in dropdown, choose Personal Macro Workbook.
3. Record as usual and click Stop Recording.
4. The macro is now available from Developer > Macros in any workbook you open.

Editing macros in PERSONAL.XLSB

Because the workbook is hidden, you cannot just "open" it. 1. View tab > Unhide > select PERSONAL > OK. 2. Press Alt + F11 to open the VBA Editor and edit the modules under VBAProject (PERSONAL.XLSB). 3. When done, View > Hide to re-hide the workbook (otherwise it will be visible every session).

Saving changes

When you close Excel after editing PERSONAL.XLSB, Excel prompts: "Do you want to save the changes you made to PERSONAL.XLSB?" Click Save to keep your edits—forgetting to save is the #1 reason "my macro disappeared."

• Official Docs: Copy your macros to a Personal Macro Workbook

Part 5: Form Controls (Buttons, Check Boxes, and Friends)

Form Controls turn a macro-enabled workbook into a small application: clickable buttons run macros, spin buttons increment values, combo boxes feed selections into formulas. You insert them from Developer > Insert > Form Controls (the top row of icons in the dropdown).

Inserting a Button

1. Developer > Insert > Button (Form Control).
2. Click-drag on the worksheet to draw the button.
3. The Assign Macro dialog opens automatically—select the macro to run on click and click OK.
4. Right-click the button to Edit Text (rename it) or Assign Macro (change the assignment later).
5. Click anywhere off the button to "deselect," then click the button to run the macro.

Other Useful Form Controls

Right-click any control > Format Control… > Control tab to set the Cell link and ranges.

Form Controls vs. ActiveX Controls

Form Controls are the simpler, older family. They have no events or properties beyond what Format Control… exposes, they cannot be programmed against directly, and—critically—they continue to function in protected workbooks and on Excel for Mac. ActiveX Controls (the second row of the Insert dropdown) are full COM objects with properties, methods, and events you can write VBA against (Private Sub CommandButton1_Click()), but they are Windows-only and can be blocked by corporate security policies. For exam-focused work and cross-platform compatibility, prefer Form Controls unless you specifically need event handlers.

• Official Docs: Add a button and assign a macro

Part 6: Editing Recorded Code in the VBE

The macro recorder produces verbose, literal code. Real productivity comes from cleaning it up after the fact.

Open the editor

• Alt + F11 (anywhere in Excel) opens the Visual Basic Editor (VBE).
• In the Project Explorer (left pane), expand your workbook > Modules > double-click the module (typically Module1) to view the code.

Common edits

1. Replace a hard-coded cell reference

' Recorded:
Range("A1").Select
Selection.Font.Bold = True

' Cleaner:
Range("A1").Font.Bold = True

2. Change a hard-coded value

' Was:
ActiveCell.Value = 100

' Update to:
ActiveCell.Value = 200

3. Wrap the action in a For…Next loop to repeat across many cells:

Dim i As Long
For i = 1 To 10
    Cells(i, 1).Font.Bold = True
Next i

4. Replace Selection. with explicit references

Recorded code is full of .Select followed by Selection.Something. This is slow and fragile.

' Recorded:
Range("B2:B100").Select
Selection.NumberFormat = "$#,##0.00"

' Cleaner:
Range("B2:B100").NumberFormat = "$#,##0.00"

5. Add a MsgBox for user feedback

Sub FormatReport()
    Range("A1:F1").Font.Bold = True
    MsgBox "Headers formatted successfully.", vbInformation, "Done"
End Sub

Part 7: Adding Macros to the Quick Access Toolbar or Ribbon

Shortcut keys (Ctrl + Shift + M) collide easily. A more discoverable option is to put macros directly on the Ribbon or QAT.

Add to the Quick Access Toolbar

1. File > Options > Quick Access Toolbar.
2. Choose commands from: dropdown > Macros.
3. Select your macro in the left list > click Add >>.
4. (Optional) Click Modify… to pick an icon and friendly display name.
5. Click OK. The macro now has its own button at the very top of the Excel window.

Add to a Ribbon tab

1. File > Options > Customize Ribbon.
2. Choose commands from: dropdown > Macros.
3. In the right pane, select an existing tab (e.g., Home) and click New Group to create a custom group. (You cannot add commands to Microsoft's built-in groups.)
4. Select the macro on the left and the new group on the right > click Add >>.
5. Click Rename… to set the icon and label.
6. Click OK.

Part 8: Deleting a Macro

When a macro is obsolete, remove it cleanly:

1. View tab > Macros > View Macros (or press Alt + F8).
2. In the dialog, set Macros in: to the workbook that holds the macro (or All Open Workbooks).
3. Select the macro name > click Delete > confirm.

To delete a macro stored in PERSONAL.XLSB, set Macros in: to PERSONAL.XLSB in the same dialog—then save the personal workbook on close.

💡 Practice Summary Table

• Official Docs:
• Quick start: Create a macro
• Automate Tasks with Macro Recorder
• Copy your macros to a Personal Macro Workbook
• Add a button and assign a macro
• Run a macro