What are Macros ?
Macros are little programs that run within Excel and help automate common repetitive tasks. It can be defined as the recording of a series of tasks.
Procedure :
Step 1: Activate the Developer Toolbar
Shortcut Key : The shortcut key gives us quick access to the macro.
Store Macro in :
Macros are little programs that run within Excel and help automate common repetitive tasks. It can be defined as the recording of a series of tasks.
Procedure :
Step 1: Activate the Developer Toolbar
- To write macros, we will need access to the Developer toolbar in the top ribbon. This is hidden by default.
- To activate it, go to File -> Options -> Customize Ribbon and check the box next to ‘Developer’, Press OK. We should now see the Developer toolbar in the ribbon.
Step 2: Enable Macros
- Macros are disabled by default in Excel. This is to protect us from malicious macros running automatically in downloaded Excel files.To use macros, we will have to first enable them from the Trust Center.
- To do this, go to File -> Options -> Trust Center. Click on the ‘Trust Center Settings’ button in this menu.In this new window, go to ‘Macro Settings’ and select the ‘Enable all macros’ radio button click OK. we can now start using macros in our Excel spreadsheets.
Step 3: Prepare the Spreadsheet
Step 4: Create a New Macro
In the ‘Developer’ tab, click on ‘Record Macro’. A pop-up window will ask us to give a name, storage location, shortcut and description for the macro and then click ok to record macro.
Store Macro in :
- If we choose ‘This Workbook’, the macro will be available for use in the existing workbook only.
- Choosing ‘New Workbook’ will save the macro in a new workbook.
- If we choose ‘Personal Macro Workbook’ macros stored in this workbook are available across all our workbooks.
Step 5 : Stop Recording
After writing functionality stop recording macro by clicking on stop recording. Then if we want to use the macro click on macros and select our macros and click on run.
Example :
- Prepare the spreadsheet, here i'm using vlookup formula to dynamically fetch the data by recording a macro.
- Click on Record Macro and give the name.
- Write the vlookup formula.
- After writing the formula click on stop recording.
- Click on next cell and select the macro that we recorded by clicking on macros.
- We can also insert the button to assign macro by clicking on insert in developer tab.
- Select the recorded macro, that will assigned to the button.
- Click on the cell and enter the button to get results.