Excel 2010 Bible

Chapter 39: Introducing Visual Basic for Applications

807

FIGURE 39.9

The TimeStamp procedure was generated by the Excel macro recorder.

The third statement copies the cell. The fourth statement, which is displayed on two 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.

The problem is that the macro is hard-coded to select cell C4. If you execute the macro when a dif-

ferent cell is active, the code always selects cell C4. This is not what you intended, and it causes the

macro to fail.

Note
You’ll also notice that the macro recorded some actions that you didn’t make. For example, it specified several
options for the PasteSpecial operation. Recording actions that you don’t specifically make is just a by-
product of the method that Excel uses to translate actions into code. n

Re-recording the macro

You can fix the macro in several ways. If you understand VBA, you can edit the code so it works

properly. Or, you can re-record the macro using relative references.

Delete the existing TimeStamp procedure, and re-record it. Before you start recording, click the

Use Relative References command in the Code group of the Developer tab. This control is a toggle,

and it’s turned off by default.

Figure 39.10 shows the new macro, recorded with relative references in effect.