Editing a Recorded Macro

An edited macro is a recorded macro whose VBA code has been changed manually after recording.

The Macro Recorder creates the initial code, and you can modify that code to change how the macro works.

Record Macro
     ↓
Generate VBA Code
     ↓
Edit VBA Code
     ↓
Run Macro

The Macro Recorder is useful for creating a starting point, but the generated code may contain unnecessary selections or actions.

Editing the code allows you to customize the macro for your requirements.

For example, you can:

• Change a cell reference
• Change displayed text
• Change a formula
• Remove an unnecessary action
• Add another instruction

First, create a simple macro using the Macro Recorder.

For example, record a macro that enters the text:

Hello Excel

The Macro Recorder will create VBA instructions for this action.

The VBA Editor can be opened from the Developer tab.

Go to:

Developer → Visual Basic

This opens the Visual Basic for Applications Editor.

Recorded macros are usually stored inside a VBA module.

In the VBA Editor, look at the Project Explorer and locate the workbook containing your macro.

Expand the workbook and open the relevant module.

Inside the module, you will see the recorded macro between a Sub statement and an End Sub statement.

Sub MyMacro()

    'Recorded instructions

End Sub

This is the procedure containing the macro instructions.

A recorded macro is normally created as a VBA Sub procedure.

Sub MyMacro()

End Sub

The code between Sub and End Sub contains the instructions that the macro executes.

Recorded macros frequently use the Range object to work with cells.

For example:

Range("A1").Select

This instruction selects cell A1.

The cell reference can be changed by editing the VBA code.

One of the easiest edits is changing a cell reference.

Suppose the recorded code contains:

Range("A1").Select

You can change it to:

Range("B1").Select

The macro will now work with cell B1 instead of A1.

A recorded macro may contain text entered into a cell.

For example:

ActiveCell.Value = "Hello Excel"

You can edit the text:

ActiveCell.Value = "Welcome Students"

The macro will enter the new text when it runs.

You can also change a recorded formula.

For example:

ActiveCell.Formula = "=SUM(B2:F2)"

You could change it to:

ActiveCell.Formula = "=AVERAGE(B2:F2)"

The macro will then calculate the average instead of the total.

Recorded macros can sometimes contain extra selections or actions.

If an instruction is not required for your macro, you can remove it after understanding what it does.

Always test the macro after removing code.

A recorded macro may contain code such as:

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

This selects A1 and makes its font bold.

The code can be edited to work with another cell.

Recorded formatting instructions can also be changed.

For example, a macro may contain:

Selection.Font.Bold = True

Changing True to False can remove the bold formatting when the macro runs.

A recorded macro may contain a font size setting.

For example:

Selection.Font.Size = 14

You can change the value to another size, such as:

Selection.Font.Size = 18

The macro will then apply the new font size.

You can add new VBA instructions to a recorded macro.

For example, a macro that enters a heading can be extended to make the heading bold.

Range("A1").Value = "Student Report"
Range("A1").Font.Bold = True

This adds an additional formatting action.

You can add a MsgBox instruction to a recorded macro.

MsgBox "Report completed!"

After the main task finishes, Excel can display the message.

Recorded copy and paste operations can also be edited.

For example:

Range("A1:A5").Copy
Range("C1").PasteSpecial

You can change the source or destination range according to your requirements.

A recorded macro can contain many instructions.

You can edit individual instructions without necessarily recording the complete macro again.

Action 1
Action 2
Action 3
Action 4

For example, you can change only Action 3 while keeping the other actions.

After making changes to the VBA code, save the workbook.

For a workbook containing VBA code, use a macro-enabled Excel format such as .xlsm.

This helps preserve the VBA project when the workbook is saved.

After editing the code, always test the macro.

A simple testing process is:

1. Save the workbook.
2. Return to Excel.
3. Run the macro.
4. Check the output.
5. Confirm that the edited instruction works correctly.

If the macro does not work after editing, check the code carefully.

Common problems include:

• Incorrect cell references
• Misspelled VBA keywords
• Missing quotation marks
• Incorrect formula text
• Incorrect object or property names

The Macro Recorder is a useful way for beginners to see how Excel actions can be represented as VBA instructions.

You can record an action, open the generated code, and study the instructions created by Excel.

Recorded code is generated automatically from your Excel actions.

Manually written VBA code is created by the programmer.

Suppose a recorded macro enters a student report heading into A1.

Original code:

Range("A1").Value = "Student Report"

You can edit it to:

Range("A1").Value = "Monthly Student Report"
Range("A1").Font.Bold = True

The edited macro now enters a different heading and makes it bold.

Follow these practices:

• Understand the code before changing it.
• Make small changes at a time.
• Save your workbook before major changes.
• Test the macro after editing.
• Use meaningful variable and procedure names when writing new code.
• Keep a backup of important workbooks.

The complete workflow is:

Record Macro
     ↓
Open VBA Editor
     ↓
Open Module
     ↓
Find Macro
     ↓
Edit Code
     ↓
Save Workbook
     ↓
Run Macro
     ↓
Check Result

Editing recorded code is an important step toward learning VBA programming.

Instead of relying only on the exact actions recorded by Excel, you can begin to customize the code according to your requirements.

This provides a transition from simple Macro Recorder tasks to VBA programming.

Try the following exercise:

1. Record a macro that enters Student Report into cell A1.
2. Stop recording.
3. Open the VBA Editor.
4. Find the recorded macro.
5. Change the text to Monthly Student Report.
6. Add code to make cell A1 bold.
7. Save the workbook.
8. Run the macro.
9. Check the result in cell A1.

This exercise will help you understand how recorded VBA code can be modified.

The Macro Recorder provides a starting point for Excel automation. After recording a macro, you can open its VBA code and make changes to customize the behavior.

You can change cell references, text, formulas, formatting instructions, and other parts of the recorded code. Testing after every important change helps ensure that the macro works correctly.

Record
  ↓
View VBA
  ↓
Edit
  ↓
Save
  ↓
Test
  ↓
Improve