This article will provide an overview of how to record a basic macro in Microsoft Excel.
What are Macros?
Macros are the commands or codes that are written in Visual Basic for Applications (VBA) and are entered into Microsoft Excel, where they are stored in workbooks. These instructions allow the Excel user to run this code each time a macro is executed. Oftentimes, VBA code is referred to as a macro, no matter if it was created with the help of a macro recorder or manually written. However, there is a difference between these two; a macro is a piece of code, and VBA is the language used to create this code.
When a user records a macro, Excel studies the steps that are being executed and records these actions in VBA, a language it understands. Once a macro is created, it can be reused as many times as the user wishes. Macros provide Excel users with a time-saving way to automate routine or repetitive tasks.
Steps for Recording a Macro in Excel
Step 1: Add a Developer Tab
Those who regularly work with macros should start by adding a Developer tab to their Excel spreadsheet. This will provide additional options for working with macros that can be used each time Excel is being used.
In order to create a Developer tab, right-click on “View” and then select “Customize ribbon.”
Once it is selected, a tab called “Developer” appears, which is not yet checked. To add that to the ribbon, click the check box for “Developer,” then “OK.” This provides a Developer ribbon tab so that the Excel user does not need to use the “Macros” dropdown group on the righthand side of the screen to access “Macros,” “Recording Macros,” or “Use Relative References.” In addition, the Visual Basic Editor can now be accessed from the Developer tab, as well as basic security.
Step 2: Recording a Macro
Select the “Record” button from the Developer tab. The “Record Macro” dialogue box appears.
First, a name must be chosen for the macro, one that doesn’t begin with a number. For example, “WordMacro.”
This box also contains a keyboard shortcut option, “Ctrl + “key.” While adding a shortcut here can save time when running macros, it’s important to keep in mind that if a shortcut such as “Ctrl + C” is chosen, any time it is selected, Microsoft will override the default copy command and instead run this macro. Because most every letter is already assigned a Microsoft shortcut, the “Shift” key, along with a letter, can be used here to designate a unique keyboard shortcut, if desired. So, “Ctrl + Shift + W” would be a viable option for a WordMacro shortcut.
This box also asks that the Excel user decide where they wish to store the macro. Several dropdown options are listed, such as the existing Workbook, new Workbook, or Personal Macro Workbook, which is an invisible workbook that’s always open in the background when Excel is running.
There is also an option to a description for the macro. This is a helpful feature for collaborative purposes, or for projects that may not be revisited for some time and therefore may require a reminder to help the user recall the specifics of the project.
Once “OK” is selected, the “Record Macro” button in the command bar changes to “Stop Recording.” This means that actions are now being recorded.
Then, select the A1 null cell. In it, type “Word,” then “Ctrl + enter,” which provides a way to stay in the current cell.
Click on “Stop Recording” in the command bar. At this point, the macro has been recorded.
Step 3: Testing the Macro
Once a macro is recorded, it’s important to rest it to ensure that it’s saved. To test the keyboard shortcut that was designated in Step 2, “Ctrl + Shift + W,” select a new empty cell, enter “Ctrl + Shift + W,” and wait for the word “Word” to appear. When it does, this signals that the macro has been successfully recorded into Excel.
Step 5: Sharing Macros
Once created, macros can be shared with other Excel users. If the other person has a Developer tab on their ribbon or goes to the “View” dialogue box, they can select “Macros,” then see the “WordMacro” that had been previously recorded. Then, they simply click on it and select “Run.”
Step 6: Alternative Way to Run a Macro
For those who do not wish to run the macro they created with a shortcut, there is another option for running the macro. Simply click on the dropdown and select “More Commands.”
Once the dropdown for “Popular Commands” is selected, “Macros” is an option that appears. When this option is chosen, all created macros will be shown in this list. From here, the desired macro can be added to the Quick Access Toolbar by selecting “Add >>.”
If the selected icon isn’t one that best conveys the macro’s intended use, simply choose “Modify,” which will provide an assortment of viable icons. From here, any icon can be selected, such as “A.” To apply this icon, click on “OK.”
To run this macro, select any null cell in the Excel spreadsheet, then click on the Quick Access Toolbar, and the macro will run.
While recording macros in Excel may seem tricky at first, with practice it becomes second nature. Each macro that’s recorded provides a time-saving tool for automating repetitive Excel tasks.
Get Started Learning Excel with Hands-On Classes
A great way to learn more about how to record macros is to enroll in one of Noble Desktop’s
Excel courses in NYC and live online. Courses are available for those who are new to Excel, as well as learners who regularly work with this spreadsheet application and wish to brush up on their skills. In addition, there are also in-person and live online Excel courses available through Noble Desktop or one of its affiliate schools. A variety of course options are offered, ranging in duration from three hours to two days and costing between $229 and $1,099.
Noble Desktop’s Excel Bootcamp provides a great option for those who want to master core Excel concepts, such as working with VLOOKUP and PivotTables, as well as perform What-If Analysis. Students who enroll in this rigorous, 21-hour course can elect to study in person in Manhattan or learn in the live online environment. This small class comes with the option of a free retake and covers a variety of Excel concepts applicable to the business world.
Those interested in studying close to home can also browse over 400 in-person Microsoft Office classes in a city near you to find local Excel study options.