Macro recorder

The macro recorder converts your Excel actions into VBA code. Although it does not always generate the most efficient code, but it is an extremely useful tool that provides you clues on how to proceed your coding and it allows you to discover the VBA built-in constants, the properties and methods of objects that you are not familiar with.

 

Exercise 1-5: Recording a macro

Execute the following steps to record your Excel actions in changing the font of a cell.

  1. Start a blank workbook.
  2. Choose Developer | Code | Record Macro to display the Record Macro dialog box.
  3. In the box labelled Store macro in, choose This Workbook.
  4. Click OK to accept other default settings.
    • By clicking OK, a VBA module is created in VBE. It is named Module1, by default.
  5. Press Alt+F11 to activate VBE.
  6. In the Project Explorer window, double-click the newly created module Module1 to activate its code window.
  7. Resize and place both Excel and VBE windows side by side, so that you can watch later how Excel actions in Steps 8 – 10 below are recorded in VBE lively.
  8. Select cell B5 or any cell in a worksheet in the Excel window.
  9. Choose Home | Font | Bold and Underline, and select the Red font color.
  10. Type the word Hello into the selected cell and press Enter.
  11. Choose Developer | Code | Stop Recording or alternatively click the square icon on the status bar to stop the recording.

The Excel actions in Steps 8 – 10 were converted into VBA code by the macro recorder and appeared in the code window of Module1. The following listing is the VBA code:

Sub Macro1()
'
' Macro1 Macro
'
   Range("B5").Select
   Selection.Font.Bold = True
   Selection.Font.Underline = xlUnderlineStyleSingle
   With Selection.Font
     .Color = -16776961
     .TintAndShade = 0
   End With
   ActiveCell.FormulaR1C1 = "Hello"
   Range("B6").Select
End Sub

The generated lines of code are known as a macro, which is a Sub procedure. Even though it is not the most efficient code, but it does provide clues on how a program in changing the font of a cell can possibly be written and allows you to know the Range and Font objects, the Selection, Font, Bold, Underline, Color, TintAndShade, ActiveCell, and FormulaR1C1 properties, the Select method, and the xlUnderlineStyleSingle built-in constant.

 

The macro can be improved by removing the unnecessary actions of selecting cells and the default value of the TintAndShade property:

Range("B5").Font.Bold = True
Range("B5").Font.Underline = xlUnderlineStyleSingle
Range("B5").Font.Color = -16776961
Range("B5").FormulaR1C1 = "Hello"

 

Its efficiency can be further improved by using the With-End With construct. The construct reduces the number of dot references that VBA needs to process.

With Range("B5").Font
   .Bold = True
   .Underline = xlUnderlineStyleSingle
   .Color = -16776961
End With
Range("B5").FormulaR1C1 = "Hello"