How to Record a macro

With Excel automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them.

Step 1: Enable Developer tab – Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it.

  1. On the File tab, go to Options Customize Ribbon.
  2. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Step 2: Record a macro

Follow these steps to record a macro.

  1. On the Developer tab, in the Code group, click Record Macro.-OR-Press Alt+T+M+R .Record Macro
  2. In the Macro name box, enter a name for the macro. Make the name as descriptive as possible so you can quickly find it if you create more than one macro.Note: The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.
  3. To assign a keyboard shortcut to run the macro, in the Shortcut key box, type any letter (both uppercase or lowercase will work) that you want to use. It is best to use Ctrl + Shift (uppercase) key combinations, because the macro shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For instance, if you use Ctrl+Z (Undo), you will lose the ability to Undo in that Excel instance.
  4. In the Store macro in list, select where you want to store the macro.In general, you’ll save your macro in the This Workbook location, but if you want a macro to be available whenever you use Excel, select Personal Macro Workbook . When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook.
  5. In the Description box, optionally type a brief description of what the macro does.Although the description field is optional, it is recommended you enter one. Also, try to enter a meaningful description with any information that may be useful to you or other users who will be running the macro. If you create a lot of macros, the description can help you quickly identify which macro does what, otherwise you might have to guess.
  6. Click OK to start recording.
  7. Perform the actions that you want to record.
  8. On the Developer tab, in the Code group, click Stop Recording Button image.-OR-Press Alt+T+M+R .

Working with recorded macros in Excel

In the Developer tab, click Macros to view macros associated to a workbook. Or press Alt+ F8. This opens the Macro dialog box.Macro dialog box

Caution: Macros cannot be undone. Before you run a recorded macro for the first time, make sure that you’ve either saved the workbook where you want to run the macro, or better yet work on a copy of the workbook to prevent unwanted changes. If you run a macro and it doesn’t do what you want, you can close the workbook without saving it.

Here’s further information on working with macros in Excel.

TaskDescription
Change macro security settings in ExcelRead specific information on available security settings for macros, and their meaning.
Run a macroThere are several different ways you can run a macro, such as using a shortcut key, graphic object, Quick Access Toolbar, a button, or even when opening a workbook.
Edit a macroYou use the Visual Basic Editor to edit the macros attached to a workbook.
Copy a macro module to another workbookIf a workbook contains a VBA macro that you would like to use elsewhere, you can copy the module that contains that macro to another open workbook by using the Microsoft Visual Basic Editor.
Assign a macro to an object, shape or graphicOn a worksheet, right-click the object, graphic, shape, or the item to which you want to assign an existing macro, and then click Assign Macro.In the Assign Macro box, click the macro that you want to assign.
Assign a macro to a buttonYou can assign a macro to a graphic icon, and add it to Quick Access Toolbar, or the ribbon.
Assign a macro to a control on a worksheetYou can assign macros to forms and ActiveX controls in a worksheet.
Enable or disable macros in Office filesLearn how to enable or disable macros in Office files.
Open the Visual Basic EditorPress Alt+F11
Find Help on using the Visual Basic EditorLearn how to find help on Visual Basic elements.

Working with recorded code in the Visual Basic Editor (VBE)

You can work with recorded code in the Visual Basic Editor (VBE) and add your own variables, control structures, and other code that the Macro Recorder cannot record. Since the macro recorder captures almost every step you take while recording, you can also clean up any unnecessary recorded code that serves no purpose in the macro. Reviewing your recorded code is a great way to learn or sharpen your VBA programming skills.

Published by

Bob Lin

Bob Lin, Chicagotech-MVP, MCSE & CNE Data recovery, Windows OS Recovery, Networking, and Computer Troubleshooting on http://www.ChicagoTech.net How to Install and Configure Windows, VMware, Virtualization and Cisco on http://www.HowToNetworking.com