When you're working with Microsoft Excel spreadsheets, there's no need to keep carrying out the same monotonous steps over and over. Save time by creating macros that automate repetitious tasks. Most Excel macros can be created with no knowledge of Microsoft's Visual Basic Application (VBA), simply by recording the steps you take in performing the task.
These steps can include any keystrokes and mouse clicks that you make, such as typing numbers or text, clicking cells on commands in the ribbon, and formatting cells,columns or rows. If you do know how to work with VBA, you can edit your recorded code or create more complex macros in the VB Editor (VBE), accessible from Excel and other Microsoft Office applications.
Video of the Day
Without even using the VBE, though, you might record a simple macro, for example, in order to apply a single format--such as MM/DD/YYYY--to all dates in a spreadsheet. You might build another macro for formatting the names of all customers with overdue accounts in bold, red lettering.
You can also record macros in Excel that extend to other Office applications. For instance, you might create a macro which first updates a table in Excel and then opens Outlook to send the table to a list of specified email addresses.
Before recording a macro, you first need to enable the Developer tab. To do that, choose File, Options, Customize Ribbon. Then, in the Customize Ribbon category, in the Main Tabs list, select the Developer check box and click OK.
After enabling the Developer tab, you must enable macros to run. You can do so in the Developer tab by clicking Macro Security. Then, under Macro settings, click Enable all macros, and then OK.
Recording the macro
You can open the Record Macro box in either of two ways. On the Developer tab, in the Code group, click Record Macro. Alternatively, press Alt+T+M+R.
In the Record Macro box, enter a name for your new macro. The first character must be a letter, but subsequent characters can be numbers, letters, or underscore symbols.
It's a good idea to make the name of the macro as descriptive as possible, so you can find the macro quickly, if needed. However, you'll also see an optional description box that you can fill in if you wish.
If you want to assign a shortcut key, type the upper case or lower case letter that you'd like to use into the shortcut box.
Click OK, and Excel will begin to actually record your actions into the new macro.
While recording, the Record Macro button will change to a Stop Recording square. Click that button when you are done to save your macro.
Generally, you will want to save the macro in the This Workbook location, but if you want it to be available each time you launch Excel, should should save it in the Personal Macro Workbook.
Sharing a macro
To share the macro in your spreadsheet with others, you must save it as an Excel Macro-Enabled Workbook file.
Distribute the Excel workbook master file to those co-workers with whom you want to share the macro. When a user bases a workbook on this master file, the macro will remain embedded in the master file.
Using the VBE
Click the View tab on the Ribbon. Click on Macros, and from that drop-down menu, choose View Macros. You may either view and edit an existing macro that you recorded or click Create.
The VBE will open, allowing you to create more powerful macros if you know the essentials of VBA. You'll then be able add variables, control structures, and other code that Macro Recorder isn't able to record.