Visual Basic for Applications/Recording Macros

This lesson introduces recording macros.

Objectives and Skills

Objectives and skills for recording macros include:

• Generating code by recording macros
• Inserting code into an existing procedure by using the Macro Recorder
• Comparing and contrasting relative and absolute references by using the Macro Recorder

Readings

1. Wikipedia: Macro (computer science)
2. Microsoft: Create a Macro
3. Microsoft: Create or Delete a Macro
4. Excel-Easy: Macro Recorder

Multimedia

1. YouTube: Recording Macros in Excel

Examples

'This macro enters Hello! into the active cell.

Sub Hello()
    ActiveCell.FormulaR1C1 = "Hello!"
End Sub

Activities

1. Record a macro.
   1. Using Excel, record a macro that enters your name in the current cell and formats it as italic. Name the macro something similar to EnterMyName and assign it to any key you wish.
   2. Experiment with running your macro using the keyboard shortcut you created as well as the Macros dialog box.
   3. Edit your macro to ensure that it doesn't have any extraneous statements in it. Try commenting out one or more lines of code using an apostrophe at the beginning of the line to identify exactly what each line of code does.
2. Record a macro.
   1. Using Excel, record a macro that enters a calculation in the current cell and formats it as bold. Name the macro something similar to EnterCalculation and assign it to any key you wish.
   2. Experiment with running your macro using the keyboard shortcut you created as well as the Macros dialog box.
   3. Edit your macro to ensure that it doesn't have any extraneous statements in it. Try commenting out one or more lines of code using an apostrophe at the beginning of the line to identify exactly what each line of code does.
3. Record a macro.
   1. Using Excel, record a macro that enters a value in the current cell and then a formula in a neighboring cell that performs a calculation using the first cell. Name the macro something similar to EnterFormula and assign it to any key you wish.
   2. Experiment with running your macro using the keyboard shortcut you created as well as the Macros dialog box. Does the macro use relative or absolute cell references?
   3. Edit your macro to ensure that it doesn't have any extraneous statements in it. Try commenting out one or more lines of code using an apostrophe at the beginning of the line to identify exactly what each line of code does.
4. Record a macro using relative references.
   1. Search the Internet for information on how to record a macro in Excel using relative cell references rather than absolute cell references.
   2. Repeat the activities above, this time first setting Excel to record using relative cell references.

See Also

• EasyExcelVBA: How to Record a Macro
• PCWorld: 5 Essential Tips for Creating Excel Macros

References

Type classification: this is a lesson resource.

Completion status: this resource is considered to be complete.

← Introduction to Macros

Visual Basic for Applications

Editing Macros →