Using the macro recorder in Excel and Word
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.
|A session with the macro recorder can be stopped from the same place where you started it.|
|Alt-CPR | Menu: Code VBA » Procedure » Record Macro|
Example: make a range BoldAs 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
- Specify that you want something with the font of the cell;
- Code VBA asks you to specify the range, here you just did a certain Range;
- Finally, you select the Bold property, and set it True
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.