Excel’s macro recorder translates your actions into VBA code. To start the macro recorder, choose Developer Code Record Macro (or, click the Record Macro icon in the status bar). Excel displays the Record Macro dialog box, shown in Figure 39.7.

Figure 39.7. The Record Macro dialog box.

The Record Macro dialog box presents several options:

To begin recording your actions, click OK. When you finish recording the macro, choose Developer Code Stop Recording (or click the Stop Recording button in the status bar).

This example demonstrates how to record a very simple macro that inserts your name in the active cell.

To create the macro, follow these steps:

The macro was recorded in a new module named Module1. To view the code in this module, you must activate the Visual Basic Editor. You can activate the VB Editor in either of two ways:

In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

Figure 39.8 shows the recorded macro, as displayed in the Code window.

Figure 39.8. The MyName procedure was generated by Excel’s macro recorder.

The macro should look something like this:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "John Walkenbach"
End Sub

The macro recorded is a Sub procedure that is named MyName. The statements tell Excel what to do when the macro is executed.

Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you’ll see that this procedure has only one VBA statement:

ActiveCell.FormulaR1C1 = "John Walkenbach"

This single statement causes the name to be inserted into the active cell. The FormulaR1C1 part is a property — but I’m getting ahead of myself.

Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:

When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.

After you record a macro, you can make changes to it (although you must know what you’re doing). For example, assume that you want your name to be bold. You could rerecord the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert the following statement before the End Sub statement:

ActiveCell.Font.Bold = True

The edited macro appears as follows:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "John Walkenbach"
    ActiveCell.Font.Bold = True
End Sub

Test this new macro, and you see that it performs as it should.

This example demonstrates how to record a time-stamp macro that inserts the current date and time into the active cell. To create the macro, follow these steps:

Activate the VB Editor and take a look at the recorded code. Figure 39.9 shows the recorded macro, as displayed in the Code window.

Figure 39.9. The TimeStamp procedure was generated by Excel’s macro recorder.

Double-click the module in the Projects window to activate the module so that you can examine the macro. It should consist of the following code:

Sub TimeStamp()
'
' TimeStamp Macro
' Keyboard Shortcut: Ctrl+Shift+T
'
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:= xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

The procedure has four statements. The first inserts the NOW() formula into the active cell. The second statement copies the cell. The third statement, which is displayed on three lines (the underscore character means that the statement continues on the next line), pastes the Clipboard contents (as a value) to the current selection. The fourth statement cancels the moving border around the selected range.

You may notice that the macro recorded some actions that you didn’t take. For example, it specified several options for the PasteSpecial operation. Recording actions that you don’t specifically make is just a byproduct of the method that Excel uses to translate actions into code.

When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+T. The macro immediately enters the current date and time into the cell. You may need to widen the column to see the date and time. To widen the column automatically, just add this statement to the end of the macro (before the End Sub statement):

ActiveCell.EntireColumn.AutoFit

If you’re going to work with recorded macros, you need to understand the concept of relative versus absolute recording. Normally, when you record a macro, Excel stores exact references to the cells that you select. (That is, it performs absolute recording.) If you select the range B1:B10 while you’re recording a macro, for example, Excel records this selection as

Range("B1:B10").Select

This statement means exactly what it says: “Select the cells in the range B1:B10.” When you invoke the macro that contains this statement, the same cells are always selected, regardless of where the active cell is located.

You may have noticed that the Developer Code section of the Ribbon has a control named Use Relative References. When you click this control, Excel changes its recording mode from absolute (the default) to relative. When recording in relative mode, selecting a range of cells is translated differently, depending on where the active cell is located. For example, if you’re recording in relative mode and cell A1 is active, selecting the range B1:B10 generates the following statement:

ActiveCell.Offset(0, 1).Range("A1:A10").Select

This statement can be translated as “From the active cell, move 0 rows down and 1 column right, and then treat this new cell as if it were cell A1. Now select what would be A1:A10.” In other words, a macro that is recorded in relative mode starts out by using the active cell as its base and then stores relative references to this cell. As a result, you get different results, depending on the location of the active cell. When you replay this macro, the cells that are selected depend on the active cell. This macro selects a range that is 10 rows by 1 column, offset from the active cell by 0 rows and 1 column.

When Excel is recording in relative mode, the Use Relative Reference control appears depressed. To return to absolute recording, click the Use Relative Reference control again (and it displays its normal, undepressed state).

Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the Personal Macro Workbook so that they’re always available to you. The Personal Macro Workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn’t exist until you record a macro, using Personal Macro Workbook as the destination.

To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down box.

If you store macros in the Personal Macro Workbook, you don’t have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.

When you begin recording a macro, the Record Macro dialog box gives you an opportunity to provide a shortcut key for the macro. If you’d later like to change the shortcut key or provide a shortcut key for a macro that doesn’t have one:

After you record a macro and test it, you may want to assign the macro to a button placed in a worksheet. You can follow these steps to do so:

After performing these steps, clicking the button executes the assigned macro.

Before you can enter code, you must insert a VBA module into the workbook. If the workbook already has a VBA module, you can use the existing module sheet for your new code.

Use the following steps to insert a new VBA module:

A VBA module, which is displayed in a separate window, works like a text editor. You can move through the sheet, select text, insert, copy, cut, paste, and so on.

VBA is by far the most complex feature in Excel, and you can easily get overwhelmed. To set the stage for the details of VBA, here is a concise summary of how VBA works:

The preceding describes VBA in a nutshell. Now you just have to learn the details, some of which are covered in the rest of this chapter.

VBA is an object-oriented language, which means that it manipulates objects, such as Ranges, Charts, AutoShapes, and so on. These objects are arranged in a hierarchy. The Application object (which is Excel) contains other objects. For example, the Application object contains a number of objects, including the following:

Most of these objects can contain other objects. For example, a Workbook object can contain the following objects:

Each of these objects, in turn, can contain other objects. A Worksheet object, for example, can contain the following objects:

A collection consists of all like objects. For example, the collection of all Workbook objects is known as the Workbooks collection. You can refer to an individual object in a collection by using an index number or a name. For example, if a workbook has three worksheets (named Sheet1, Sheet2, and Sheet3), you can refer to the first object in the Worksheets collection in either of these ways:

Worksheets(1)
Worksheets("Sheet1")

The objects that you work with have properties, which you can think of as attributes of the objects. For example, a Range object has properties, such as Column, Row, Width, and Value. A Chart object has properties, such as Legend, ChartTitle, and so on. ChartTitle is also an object, with properties such as Font, Orientation, and Text. Excel has many objects, and each has its own set of properties. You can write VBA code to do the following:

You refer to a property in your VBA code by placing a period (a dot) and the property name after the object’s name. For example, the following VBA statement sets the Value property of a range named frequency to 15. (That is, the statement causes the number 15 to appear in the range’s cells.)

Range("frequency").Value = 15

Some properties are read-only, which means that you can examine the property, but you can’t change the property. For a single-cell Range object, the Row and Column properties are read-only properties: You can determine where a cell is located (in which row and column), but you can’t change the cell’s location by changing these properties.

A Range object also has a Formula property, which is not read-only; that is, you can insert a formula into a cell by changing its Formula property. The following statement inserts a formula into cell A1 by changing the cell’s Formula property:

Range("A11").Formula = "=SUM(A1:A10)"

At the top of the object hierarchy is the Application object, which is actually Excel, the program. The Application object has several useful properties:

You also should understand that properties can return objects. In fact, that’s exactly what the preceding examples do. The result of Application.ActiveCell, for example, is a Range object. Therefore, you can access properties by using a statement such as the following:

Application.ActiveCell.Font.Size = 15

In this case, the ActiveCell property returns a Range object. The Font property returns a Font object, which is contained in the Range object. Size is a property of the Font object. The preceding statement sets the Size property to 15—that is, it causes the font in the currently selected cell to have a size of 15 points.

ActiveCell.Row

In many cases, you can refer to the same object in a number of different ways. Assume that you have a workbook named Sales.xlsx and it’s the only workbook open. Furthermore, assume that this workbook has one worksheet, named Summary. Your VBA code can refer to the Summary sheet in any of the following ways:

Workbooks("Sales.xlsx").Worksheets("Summary")
Workbooks(1).Worksheets(1)
Workbooks(1).Sheets(1)
Application.ActiveWorkbook.ActiveSheet
ActiveWorkbook.ActiveSheet
ActiveSheet

The method that you use is determined by how much you know about the workspace. For example, if more than one workbook is open, the second or third method is not reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure that you’re referring to a specific sheet on a specific workbook, the first method is your best choice.

Objects also have methods. You can think of a method as an action taken with an object. For example, Range objects have a Clear method. The following VBA statement clears a Range, an action that is equivalent to selecting the Range and then choosing Home Editing Clear Clear All:

Range("A1:C12").Clear

In VBA code, methods look like properties because they are connected to the object with a “dot.” However, methods and properties are different concepts.

Like all programming languages, VBA enables you to work with variables. In VBA (unlike in some languages), you don’t need to declare variables explicitly before you use them in your code (although doing so is definitely a good practice).

In the following example, the value in cell A1 on Sheet1 is assigned to a variable named Rate:

rate = Worksheets("Sheet1").Range("A1").Value

You then can work with the variable Rate in other parts of your VBA code. Note that the variable Rate is not a named range, which means that you can’t use it as such in a worksheet formula.

VBA uses many constructs that are found in most other programming languages. These constructs are used to control the flow of execution. This section introduces a few of the more common programming constructs.

If condition Then statements [Else elsestatements]

Sub CheckCell()
  If ActiveCell.Value < 0 Then ActiveCell.Font.ColorIndex = 3
End Sub

Sub SumSquared()
  Total = 0
  For Num = 1 To 10
    Total = Total + (Num ^ 2)
  Next Num
  MsgBox Total
End Sub

Sub AlignCells()
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = xlHorizontal
  End With
End Sub

Sub AlignCells()
  Selection.HorizontalAlignment = xlCenter
  Selection.VerticalAlignment = xlCenter
  Selection.WrapText = False
  Selection.Orientation = xlHorizontal
End Sub

Sub CheckCell()
  Select Case ActiveCell.Value
    Case Is < 0
      ActiveCell.Font.Color = vbRed
    Case 0
      ActiveCell.Font.Color = vbBlue
    Case Is > 0
      ActiveCell.Font.Color = vbBlack
  End Select

End Sub

The following is a VBA macro that can’t be recorded because it uses programming concepts that must be entered manually. This macro creates a list of all formulas on the active sheet. The list is stored on a new worksheet.

Sub ListFormulas()
' Create a range object
  Set InputRange = ActiveSheet.UsedRange
' Add a new sheet
  Set OutputSheet = Worksheets.Add
' Variable for the output row
  OutputRow = 1
' Loop through the range
  For Each cell In InputRange
    If cell.HasFormula Then
      OutputSheet.Cells(OutputRow, 1) = "'" & cell.Address
      OutputSheet.Cells(OutputRow, 2) = "'" & cell.Formula
      OutputRow = OutputRow + 1
    End If
  Next cell
End Sub

Although this macro may look complicated, it’s fairly simple when you break it down.

First, the macro creates an object variable named InputRange. This variable corresponds to the used range on the active sheet (avoiding the need to check every cell). It then adds a new worksheet and assigns the worksheet to an object variable named OutputSheet. The OutputRow variable is set to 1. This variable is incremented later on.

The For-Next loop examines each cell in the InputRange. If the cell has a formula, then the cell’s address and formula are written to the OutputSheet. The OutputRow variable is also incremented.

Figure 39.12 shows the result of running this macro—a handy list of all formulas in the worksheet.

Figure 39.12. The ListFormulas macro creates a list of all formulas in a worksheet.

As macros go, this example is okay, but it’s certainly not perfect. It’s not very flexible, and it doesn’t include any error handling. For example, if the workbook structure is protected, trying to add a new sheet will cause an error.