Absolute and Relative Recording

When you run MonthNamesl, the macro returns to the same cells you selected while typing in the month names. It doesn't matter which cell is active when you start; if the macro contains the command to select cell B1, that is what it selects. The macro selects B1 because you recorded in absolute record mode. The alternative, relative record mode, remembers the position of the active cell relative to its previous position. If you have cell A10 selected, turn on the recorder, and go on to select B10, the recorder notes that you moved one cell to the right, rather than noting that you selected cell B10.

Record a second macro called MonthNames2. There will be three differences in this macro compared with the previous one:

□ Click the Use Relative References button on the Developer tab of the Ribbon. You can do this before you start recording or while you are recording.

□ Do not select the Jan cell before typing. You want your recorded macro to type Jan into the active cell when you run the macro.

□ Finish by selecting the cell under Jan, rather than A2, just before turning off the recorder.

Start with an empty worksheet and select the B1 cell. Turn on the macro recorder and specify the macro name as MonthNames2. Enter the shortcut as uppercase M—the recorder won't let you use lowercase m again. Click OK and select the Use Relative References button on the Developer tab of the Ribbon.

Type Jan and the other month names, as you did when recording MonthNamesl. Select cells B1:G1 and click the Bold and Italic buttons on the Home tab of the Ribbon.

Make sure you select B1:G1 from left to right, so that B1 is the active cell. There is a small kink in the recording process that can cause errors in the recorded macro if you select cells from right to left or from bottom to top. Always select from the top-left corner when recording relatively. This has been a problem with all versions of Excel VBA.

Finally, select cell B2, the cell under Jan, and turn off the recorder.

Before running MonthNames2, select a starting cell, such as A10. You will find that the macro now types the month names across row 10, starting in column A, and finishes by selecting the cell under the starting cell.

Before you record a macro that selects cells, you need to think about whether to use absolute or relative reference recording. If you are selecting input cells for data entry, or for a print area, you will probably want to record with absolute references. If you want to be able to run your macro in different areas of your worksheet, you will probably want to record with relative references.

If you are trying to reproduce the effect of the Ctrl+arrow keys to select the last cell in a column or row of data, you should record with relative references. You can even switch between relative and absolute reference recording in the middle of a macro, if you want. You might want to select the top of a column with an absolute reference, switch to relative references, and use Ctrl+down arrow to get to the bottom of the column and an extra down arrow to go to the first empty cell.

Excel 2000 was the first version of Excel to let you successfully record selecting a block of cells of variable height and width using the Ctrl key. If you start at the top left-hand corner of a block of data, you can hold down the Shift and Ctrl keys and press the down arrow and then the right arrow to select the whole block (as long as there are no gaps in the data). If you record these operations with relative referencing, you can use the macro to select a block of different dimensions. Previous versions of Excel recorded an absolute selection of the original block size, regardless of recording mode.

0 0

Post a comment