Using the macro recorder in Excel and Word

View Macros in procedure menu

The macro recorder provided with Excel and Word allows you to record the actions on your workbook or document-interpreted as a sequence of VBA statements. As such using the macro recorder is the simplest way to create a macro that can reproduce exactly those actions. It thus understands sequence, but it does not understand the other two structure elements of a program: selection and iteration (choice and repetition)-which are required in almost any program. The macro recorder will provide you with some initial code, which in most cases need adaptation. In many cases it will be easier to write the macro from scratch as you as The main use of the macro recorder is to identify the elements needed in your code.

The Macro Recorder can be started from the ribbon » Developer tab » Visual Basic group. Alternatively, with Code VBA installed, from menu: Code VBA » Procedure » Record Macro

A session with the macro recorder can be stopped from the same place where you started it.

Example: make a range Bold

As an example, let's assume I want to know what code allows you to make text in a cell Bold. First, we use the macro recorder.

Sub Macro1()
' Macro1 Macro
Selection.Font.Bold = True
End Sub

According to the generated code, there is a Font class property of Selection, which has the propert Bold which my be set to true. That is indeed what I wanted to know. What is missing though is that the cell is a range object, which in many cases is not whatever happened to be selected, but on a specific worksheet location.

If you, on the other hand use Code VBA menu code, your could would be built in three steps

  1. Specify that you want something with the font of the cell;
  2. Code VBA asks you to specify the range, here you just did a certain Range;
  3. Finally, you select the Bold property, and set it True set font bold using codevba

Sub Demo()
Dim rngRange As Range
Dim rng As Range: Set rng = Application.Range("D4")
Dim fntFont As Font
Set fntFont = rng.Font
fntFont.Bold = True

End Sub

Creating the code following the latter approach is more elaborate, but has its advantages. It makes clear a Range is involved, and also that this Range needs to be specified. These aspects are not transparent in the code created using the macro recorder.