What is a macro in excel?
This is a fine option that can be used to learn Excel VBA Macro. When the “Record Macro” in switched ON:
- Manual operation done to excel are captured and
- Each steps is Converted to corresponding Excel VBA code.
This way we have 2 in 1 benefit.
- Recorded steps can be played any time.
- Generated Code will give us lot of hints on how to write a VBA Macro.
VBA developers use this option to find the syntax of any manual options done in excel sheets (Example: Highlighting a cell with specific color, deleting a row, copy data from a sheet to another sheet etc., )
In Excel 2003 record macro option is easy to find. In 2007 or above, use the below explained options.
[su_note note_color=”#26261c” text_color=”#ffffff” radius=”2″](1). Enable ‘Record Macro’ Button. (2). Switch on & Off Recorder (3). Run, Edit or Reuse Recorded VBA Macro[/su_note]
How to Enable “Macro Record” Button & Create a Macro in Excel?
1. Add Developer Tab:
Go to File Menu -> Options -> Customize Ribbon. In this Pop up options window, search for “Developer” in the right hand side options listed under the title “Customize the Ribbon:”.
Enable this check box and click on Ok. Now, you can see that there is a Option added for “Developer” in the menu. Click this tab and you can find the “Record Macro” on the Left corner.
Also Read: Add Sheets Dynamically to Excel when a Macro is executed
2. Start Recording:
Click the “Record Macro” button & it will display a pop up to enter the Macro options.
Here, “Macro Name”, “Shortcut key” and other options can be assigned to the macro which we are going to create now.(Example to Assign Shortcut key to macro: If you want the recorded macro to be played by using “Ctrl+q”, assign “q” in the short cut key option in below window)
Once options are typed, press Enter to start recording. Now the manual actions done to excel will be converted to corresponding VBA (Visual Basic for Applications) Code.
For example: Click on Sheet 1, Cell A1 and change its background color to “Yellow”.
3. Pause or Stop Recoding:
Now macro is in RECORD mode, when the icon changes to shape a square button.
At this point whatever task done in the excel workbook is recorded. Click again on the icon in screen shot to stop recording.
How to Run Created Macro and Edit it in VB Editor?
Now that we have recorded the manual actions done on excel. But, how are we going to re-execute or edit the code auto generated?
- To run the recorded macro either use Shortcut key if you have assigned one or refer step 1 again and add “Run Macro” Command to Quick Access toolbar.
- To view or edit the auto created macro code, press Alt + F11. Double click on “Modules ” which will list the Modules and recorded Macros.It will have the code corresponding to every task or keystrokes or formatting or any record-able tasks in the excel workbook. (If you are new, learn on how to write Excel Macro Hello World Program for beginner.)
This is how a Macro can be created in Excel by enabling the “Record Macro” option from Developer Tab. In long course of time as a Macro developer, the option explained in this page will be very much helpful, when we don’t know the syntax for any commands.
More Tips: Create Your Own Excel Formulas (User Defined Functions)