After running the macro and producing the multiplication table in Figure 6.9, note that Excel still has the copied range from line 2 of the macro as the active clipboard item. If the user of this macro would select a cell and press Enter,the contents of those cells would copy to the new location.This is generally not desirable.To get Excel out of Cut/Copy mode,add this line of code before your programs ends:

Application.CutCopyMode = False

An Interesting Twist

Try this experiment. Move the cell pointer to F6. Turn on macro recording using Tools, Macros, Record New Macro. Click the Relative Recording button on the Record Macro toolbar. Enter the formula =A1 and press Ctrl+Enter to stay in F6. Click the Stop Recording button on the floating toolbar.

You get this single line macro, which enters a formula that points to a cell five rows up and five columns to the left:

Sub Macro1()

ActiveCell.FormulaR1C1 = "=R[-5]C[-5]" End Sub

Now, move the cell pointer to cell A1 and run the macro that you just recorded. You might think that pointing to a cell five rows above A1 would lead to the ubiquitous Run Time Error 1004. But it doesn't! When you run the macro, the formula in Cell A1 is pointing to =IR65532, meaning that R1C1 style formulas actually wrap from the left side of the workbook to the right side. I cannot think of any instance where this would be actually useful, but for those of you who rely on Excel to error out when you ask for something that doesn't make sense, be aware that your macro will happily provide a result, and probably not the one that you expected!

0 0

Post a comment