|

How to Record a Macro in Excel (or Microsoft Word)

ByRick

The easiest way to create a macro is:

  1. Turn on the macro recorder,
  2. Perform the Excel commands to include in the macro, then
  3. Turn off the macro recorder.

The instructions below are for Excel. Word is similar, but has its own quirks (and no “Absolute or Relative”).

If you want to look at programming basics first, see this previous post.

1.     Recording a Macro Absolute or Relative

What does that mean?  When you record Absolute, Excel will record exactly the cell addresses that you used.  Whenever you play back the recording, it will use those same cell addresses.

The alternative is to record Relative.  In that case, Excel does not record cell references, but relative movements.  If you move the cell selector two columns to the right and five rows down, that is what the macro will do when you run it.  This can have unfortunate results if you start running the macro in the wrong cell!

The good news is that you can change Absolute to Relative, or vice versa, while you are recording.  You might, for example, start Absolute and go to cell A1.  You could press Ctrl+Down to slide down to the bottom of the contiguous data in the column. Then you could continue recording relative from there.

2.     Before Recording Your Macro

Before you start recording:

  1. Ask yourself, “Do I want to record Absolute, or Relative?”  If you don’t know what that means, see the preceding section.
  2. Set the recording mode to Absolute or Relative, as required.
  3. Make sure you know what you want the macro to do.  If necessary, practice by going through the actions first!  If you make a mistake when recording, Excel will record all your mistakes as evidence against you.  But if you do achieve your aim, the macro will work. –It may just have unnecessary code that will make it difficult to understand and debug.
  4. Save your file.  If you mess it up. it will be easier to recover.
  5. Select the correct cell, particularly if you are recording Relative.

3.     How Do You Start Recording Your Macro?

There are three ways to record a macro:

  • Use the icon on the Status Bar, bottom left of the Excel window.  However, you can’t set Absolute or Relative there.
  • Use the View ribbon (Macros group, on the right).  This should always be available. You have to pull down the list to see or change the Absolute or Relative mode.
  • Use the Developer ribbon (Code group, on the left).  This ribbon is not visible by default, but we can change that.  You can see on the ribbon if you are in Absolute or Relative mode.

4.     Start Recording your Macro from the Status Bar

To record a macro, use the button on the Status bar at the bottom left of the Excel window.  If you can’t see the status bar, you might be in full-screen view: To return to normal view, press Ctrl+Shift+F1.

Click the button shown on the right of the Num Lock message in the picture above.  The icon will change to a filled square (like the “Stop” button on a tape recorder or video player).  The tooltip will change to suit:

5.     Start Recording your Macro from the View Ribbon

Instead, you can use the View ribbon(Macros group) > Macros dropdown arrow > Record Macro.  Note the “Use relative References” button, which you may want to choose first.

6.     Start Recording your Macro from the Developer Ribbon

Alternatively (picture below), you can use the Developer ribbon.  In the Code (first) group, use Record Macro to start recording.  This button then becomes Stop Recording, which you use to stop recording.

The Developer ribbon is hidden by default.  If you do not see it on the right of the View ribbon, click File > Options > Customize Ribbon.  In the right-hand list, tick the Main Tab, Developer.  Click OK twice.

Note the button Use Relative References on the Developer ribbon.  If it is off when you record, the cells you select will be recorded using absolute references.  That macro will always run in those same cells.  If it is on (highlighted), cell references will be recorded relative to the cell where you started recording.  If, later, you run the macro starting in a different cell, the macro will use cells relative to the new starting point.

7.     Settings for Recording Your Macro

No matter which method you use to start recording, you should get this dialog box:

Name the macro using a concise name starting with a letter.  The macro name can be as short as one character and there is no maximum.  You can use letters, underscores and numbers, but the name may not start with a number. You cannot use spaces: Use underscores instead.

You can assign a shortcut key to the macro.  Although a shortcut key is faster than using a name, you must remember which key combinations are used to represent that macro.  When creating a number of different macros this can often get confusing, and should be used for only your most commonly used macros.

Your macro name and shortcut keys must be unique. If you try to re-use one that is in memory, Excel will not write over it, it will refuse to record.  You have to delete the old macro or shortcut key combination by hand.

Warning: If a macro shortcut key conflicts with a built-in shortcut combination, the macro will replace that built-in shortcut for a long as it is in memory!  Now, Excel uses almost the entire alphabet for Ctrl+ shortcut keys.  Think Ctrl+A, Ctrl+B, Ctrl+C, Ctrl+D, Ctrl+V, Ctrl+X, all the way to Ctrl+Z!  Therefore, we recommend that you always use Ctrl+Shift for your shortcuts.

There is a further pitfall.  In the dialog box above, Excel has already filled in “Ctrl+” for you.  So, here, you only press Shift, for example, Shift+E.  But to run the macro later, you would use Ctrl+Shift+E.  Isn’t life interesting?

You can store a macro in your personal macro workbook, this workbook or a new workbook.  Macros are available while the workbook containing them is open.  The personal macro workbook is always opened (hidden) when Excel loads.  Thus, it is a good place to store macros that must always be available.  It is best not to overload it with shortcut keys you seldom use.

The description should give a meaningful explanation of what the macro does.  Include the date you created it.

8.     At Last!  Recording your Macro

Click the OK button on the dialog box above.

With recording on, perform the tasks you want recorded in the macro.  You might format a table, add text and/or numbers to the worksheet, open a worksheet, save, print, etc.

Remember: Do not do anything you do not want recorded!

Do not forget to stop recording!

9.     Stop Recording your Macro!

When the task is done, click the button Stop Recording on the Status Bar, or the View or Developer ribbons.Your macro is now recorded and you can edit or run it.

10.   Before Testing Your Macro

Before you run your new macro, some precautions are in order:

  1. If the macro needs to start in a particular cell, select that cell. This is important if you recorded Relative.
  2. You cannot Undo (Ctrl+Z) what a macro has done. So, save your file! If your macro messes it up. it will be easier to recover. Unless you recorded the macro in the Personal Macro workbook, save the macro file as an “Excel Macro-Enabled Workbook”. This has a file extension of .XLSM. An ordinary Excel Workbook (extension .XLSX) cannot contain Macros.

11.   Running Your Macro

You can run a macro in many ways:

  • If the macro has a short-cut key, press that key combination and the macro will run.
  • If you assigned the macro to a button on a sheet, show that sheet and click on the macro button.  (We haven’t covered that option yet.)
  • Click the View tab (Macros group) > Macros button.
  • On the Developer ribbon (if you have it visible), in the Code group), click the Macros button.
  • Press Alt+F8.

The last three options will show the Macro dialog box:

Here you can Edit, Delete, Step Into, or Run the macro of your choice.

“Step Into” is a useful choice.  It will take you into the macro code.  There you can step through it one command at a time using F8.  You will be able to watch each command take effect in the spreadsheet.

To run the macro, select the macro and click the Run button.

To stop the macro from running before it is complete, press the Esc key or Ctrl+Break.  The following dialog box will appear.  This lets you edit (Debug) the macro, Continue the macro or End the macro altogether:

If you choose Debug here, or Edit from the Macro dialogue box, you will go into the Visual Basic Editor.  That is the subject of our next post.

Similar Posts

|

How to Store Program Settings in the Registry from Excel Visual Basic

ByRick

Windows stores program settings in the Registry, a Windows internal database.  You can use this to remember previous user choices and use them as defaults the next time.  We typically remember window positions, file paths, and other settings with the Registry. These three commands work with items stored in the Registry: SaveSetting appname, section, key,…

|

How to Write a Visual Basic Function Procedure

ByRick

This is from Microsoft Excel 2000 Visual Basic Help, with changes and additions. A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is like a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as…

|

FORMULATEXT to Display the Formula in a Cell

ByRick

Here is a function that displays the formula in another cell: FORMULATEXT(reference). The argument, “reference”, is the cell address that contains the formula you want to display. This is most useful for documentation purposes.  For example, when you want to print out a spreadsheet with its formulas. If you have spent much time on this…