Overview

Introduction

The Recording and Editing Excel Macros module provides you with the instructions and devices to develop your hands on skills in the following topics.

  • Recording Macros
  • Using the Macro Recorder
  • Viewing the Macro VBA Code
  • Understand References in Recorded Macros

Lab time: It will take approximately 90 minutes to complete this lab.

Exercise 1 - Recording Macros

Macros are small programs that can perform tasks for you in Excel, such as formatting or entering data. Fortunately, you don't need to be a programmer to create macros. Excel has a macro recorder that you can use to capture your actions so that you can play them back in the future as a macro.

In this lab, you will learn:

  • What a macro is and how to run one
  • How to record a simple formatting macro
  • How to examine and edit code in a recorded macro
  • How to control the recording of relative and absolute references in a macro

Macros are short programs that you use to perform repetitive tasks in Excel, such as formatting a report, entering formulas in a column, or rearranging data. Excel macros are written in a language called Visual Basic for Applications (VBA), and if you're familiar with programming, you might want to write code for macros yourself.

But Excel provides a great macro recording feature that can translate your actions into VBA code, so that you can play them back in the future without your having to know much or anything about programming.

To work with macros, you should display the Developer tab on the ribbon, so you can use the Code-group commands.

Macros can contain harmful programs called viruses that can hurt your computer or your data. For this reason, you should pay careful attention to where your macros come from. You should also use Excel's Trust Center to be sure that you always know when a workbook contains macros.

Exercise 2 - Using the Macro Recorder

Although macros are, technically, programs written in VBA, you don't need to be a programmer to create a macro. The macro recorder lets you record what you're doing—selecting cells, entering formulas, formatting ranges—translated into VBA code. There are certainly things VBA programs can do that you can't record, but so many useful macros can be completely recorded without ever having to look at the code.

To get the most out of recording macros, you'll want to think through what you want to accomplish, and practice the actions a few times before you turn on the recorder.

Exercise 3 - Viewing the Macro VBA Code

Excel macros are actually programs written in Visual Basic for Application (VBA). VBA code lives in modules, and a particular macro is stored in a procedure within a module.

Programming in VBA is beyond the scope of this course, but if you find yourself using macros regularly, you might want to learn the basics. With just a little coding knowledge, you can make simple tweaks without having to re-record the macro.

Exercise 4 - Understand References in Recorded Macros

Just as you need to pay attention to absolute and relative references in formulas, the same is true when recording macros. By default, Excel will record references absolutely, meaning that if you select cell A1, the macro will select A1, no matter where the active cell is when it runs that statement.

But often, you want a macro to move over one cell, or select to the beginning of a row, or to the end of a column, and so on. There are a number of techniques for recording references and range selection the way you want.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.