{"id":96486,"date":"2021-06-17T09:00:30","date_gmt":"2021-06-17T03:30:30","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=96486"},"modified":"2021-06-14T12:36:06","modified_gmt":"2021-06-14T07:06:06","slug":"macros-in-excel","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/macros-in-excel\/","title":{"rendered":"Macros in Excel"},"content":{"rendered":"

Macros helps in automating the tasks that you wanted to do repeatedly by recording a macro. While recording a macro, each and every mouse clicks and keystrokes will be recorded. It can be edited or minor changes can be done as per the convenience. Macros is one of the programming codes that runs in an excel environment and it is widely helpful when the tasks are repetitive.<\/span><\/p>\n

What is the use of Macros in Excel?<\/h3>\n

Macros help you to complete your everyday routine tasks on a single button click. Macros records the routine steps and a single click on the button can replay it again and again.<\/span><\/p>\n

For example:<\/strong> You are an assistant manager and you wanted to design a call sheet template everyday. This task may seem boring if you are doing it everyday. In these types of situations, you can switch to using macros. Macros automates the routine tasks. You can use the macro to record the format of the layout of the call sheet template.\u00a0<\/span><\/p>\n

Let\u2019s now look at how macros are being formed and how they are useful to the user of MS-Excel.<\/span><\/p>\n

How to Enable Macro Recording<\/h3>\n

Method 1: From Developer Tab<\/h4>\n

To enable macro recordings, follow the steps:<\/span><\/p>\n

1: Right click on the ribbon.<\/span><\/p>\n

2: Choose customize the ribbon option.<\/span><\/p>\n

\"customise<\/a><\/p>\n

3: Check the developer box.<\/span><\/p>\n

\"developer<\/a><\/p>\n

4: Click ok.<\/span><\/p>\n

After following the steps, the developer tab appears on the excel sheet.<\/span><\/p>\n

\"Developer<\/a><\/p>\n

After activating, we could also find the macro symbol below the excel sheet name.<\/span><\/p>\n

\"macro<\/a><\/p>\n

This represents that the macro recorder is on.<\/span><\/p>\n

Method 2: From Trust Center Settings<\/h4>\n

1: Go to the file tab and click on the options from the left side of the pane.<\/span><\/p>\n

2: Click on the trust center option from the left side of the dialog box.<\/span><\/p>\n

\"trust<\/a><\/p>\n

3: Click on the trust center settings and a dialog box appears.<\/span><\/p>\n

4: Choose the macro settings from the left side of the window and click on the radio button beside \u2018Enable all macros (not recommended; potentially dangerous code can run) radio button.\u2019<\/span><\/p>\n

\"trust<\/a><\/p>\n

5: Finally, press ok.<\/span><\/p>\n

After following these steps, the macros will run on all excel worksheets without any warning messages.<\/span><\/p>\n

Recording macro in Excel<\/b><\/h3>\n

Now, let’s see how to record a macro. To record a macro, follow the steps:<\/span><\/p>\n

1: Click on the macro record icon.<\/span><\/p>\n

\"Record<\/a><\/p>\n

\"recording<\/a><\/p>\n

2: A box appears like this. Fill it out as you require and click on the \u201cok\u201d button.<\/span><\/p>\n

\"record<\/a><\/p>\n

Note<\/strong>: You can fill the box if required or otherwise you could leave it as such. Once you click on the ok button, the macro recording starts.<\/span><\/p>\n

\"Macro<\/a><\/p>\n

This symbol denotes that the recording has started. After the appearance of this symbol, each and every move will be recorded.<\/span><\/p>\n

Let’s look at what does the macro do with an example:<\/span><\/p>\n

\"Macro<\/a><\/p>\n

In this, we have students and their marks in three different sheets. Here, we have to calculate the percentage of each student and average in each subject.<\/span><\/p>\n

So, in this, we are going to repeat the steps. Hence, to save time and reduce the work, we are going to use macros.<\/span><\/p>\n

How to start a macro?<\/h3>\n

1: Click on the developer tab.<\/span><\/p>\n

2: Choose the record macro option.<\/span><\/p>\n

\"dev<\/a><\/p>\n

Note: A box appears when you click on the record macro.<\/span><\/p>\n

\"filling<\/a><\/p>\n

3: Fill the box and as we are finding averages, we are naming it averages here.<\/span><\/p>\n

Note: The shortcut key, description are optional. You can fill it if you want or else you could leave it as blank.\u00a0 In the store macro in label, choose on this workbook option.<\/span><\/p>\n

4: After filling the box, press ok.<\/span><\/p>\n

This is how we have to start a macro. Once you press ok, the macro recording gets started.<\/span><\/p>\n

From then, each and every move will be recorded. Once, every step is being recorded for a sheet in the spreadsheet, then it can be easy to access in other sheets.<\/span><\/p>\n

\"run<\/a><\/p>\n

In our dataset, we have found the average score in each subject\u00a0 and percentage of each student.<\/span><\/p>\n

How to run a Macro?<\/b><\/h3>\n

Now, we know how to record a macro and save it. Hence, now we will learn how to run a macro. In order to try the same thing to happen in the Language mark sheet using a macro, follow the steps:<\/span><\/p>\n

1: Click on the Developer tab.<\/span><\/p>\n

2: Choose the Macros option.<\/span><\/p>\n

Note: A box appears with the macros we have already recorded.<\/span><\/p>\n

\"Macro<\/a><\/p>\n

3: Click on the macro you wanted to apply.<\/span><\/p>\n

4: Click on the run button.<\/span><\/p>\n

Note: We could either follow the steps or press on the shortcut key we have assigned to that particular macro.<\/span><\/p>\n

Once we apply this to our dataset, then the average and percentage of the subject appears with no time.<\/span><\/p>\n

Let’s look at it.<\/span><\/p>\n

\"Macro<\/a><\/p>\n

Thus, this is how a macro works.<\/span><\/p>\n

Debugging a Macro Code<\/b><\/h3>\n

Sometimes, there are chances of users to commit mistakes in the code and this results in error. So to overcome the error in the code, we can debug the code just as we do to any other code to know about the error and eventually we can correct them.\u00a0<\/span><\/p>\n

Let\u2019s see how to look at the macro recorded code lines. In order to look at it, follow the steps:<\/span><\/p>\n

1: Click on the Developer tab.<\/span><\/p>\n

2: Choose the macros option.<\/span><\/p>\n

3: Choose the \u201c Step Into \u201d option from the box.<\/span><\/p>\n

\"step<\/a><\/p>\n

After following the steps, the code automatically opens up in Microsoft Visual Basics for Applications.<\/span><\/p>\n

\"Macros<\/a><\/p>\n

The steps lead to opening of the code. If you want to edit on the code directly, then click on the edit option in the box. It gives you access to edit the code in the code console itself.<\/span><\/p>\n

\"Edit<\/a><\/p>\n

Once you click on it, the code console opens up and you can start editing in the code itself.<\/span><\/p>\n

1. How to open a macro<\/h4>\n

To open a macro, follow the steps:<\/span><\/p>\n

1: Go to the view tab and click on the macros icon.<\/span><\/p>\n

2: Click on the view macro to view it.<\/span><\/p>\n

\"open<\/a><\/p>\n

2. View Macro<\/h4>\n

When the user clicks on the view macro, a dialog box appears with the existing macros along with run, edit or delete macro options.<\/span><\/p>\n

3. Record Macro<\/h4>\n

When the user clicks on the record macro, a dialog box appears asking for the macros details and the user can record the macros after filling it.<\/span><\/p>\n

4. Absolute References<\/h4>\n

An absolute reference macro would perform the exact steps that have been recorded. Usually, the macros are being recorded in absolute references type. While recording an absolute reference macro, ensure that all the steps are being recorded from the start to end.<\/span><\/p>\n

5. Relative References<\/h4>\n

Relative reference is a type of function in macros. It is being used widely when there is a lot of data to be arranged in a specific format. <\/span><\/p>\n

To enable the relative reference macro, click on the view tab, go to macros and choose \u201cUse Relative References\u201d, then you start the macro recording. A <\/span>macro recorded using relative references can perform the tasks at different parts on the worksheet.<\/span><\/p>\n

\"relative<\/a><\/p>\n

6. Deleting a Macro<\/h4>\n

We can also delete a macro. In order to do that, follow the steps:<\/span><\/p>\n

1: Click on the Developer tab.<\/span><\/p>\n

2: Choose the macros option.<\/span><\/p>\n

3: Choose the \u201c Delete \u201d option from the box.<\/span><\/p>\n

\"Delete<\/a><\/p>\n

Once it\u2019s clicked, the macro gets deleted.<\/span><\/p>\n

7. Options label<\/h4>\n

In case, if you change your mind to change the shortcut key and description of that particular macro, then click on the option label from the box. Then, again a box appears with the edit options.<\/span><\/p>\n

\"Options<\/a><\/p>\n

After editing, click on the ok button.\u00a0<\/span><\/p>\n

8. Assigning a macro to a shape<\/h4>\n

Let\u2019s look at how to assign a macro to a shape. In order to do that, follow the steps:<\/span><\/p>\n

1: Click on the Insert tab.<\/span><\/p>\n

2: Choose the shapes.<\/span><\/p>\n

3: Click on the shape you want.<\/span><\/p>\n

Note: We are choosing a rectangle here. Have a look at the sample below.<\/span><\/p>\n

\"Insert<\/a><\/p>\n

4: Right click on the shape in the worksheet.<\/span><\/p>\n

5: Choose to \u201cAssign macro\u201d.<\/span><\/p>\n

6: Click on the macro you wanted to apply.\u00a0<\/span><\/p>\n

7: Click ok button.<\/span><\/p>\n

\"assigning<\/a><\/p>\n

After the steps are being followed, the macro will be assigned to the shape. Now, whenever you click on the shape, the recorded macro will work on your spreadsheet.\u00a0<\/span><\/p>\n

Let\u2019s look at how to add the macro to the quick access toolbar. In order to do that, follow the steps:<\/span><\/p>\n

1: Click on the File tab<\/span><\/p>\n

2: Choose options.<\/span><\/p>\n

3: A box appears and choose the Quick Access Toolbar.<\/span><\/p>\n

4: Click on the view macros and click on the add button.<\/span><\/p>\n

\"view<\/a><\/p>\n

5: Then, press ok.<\/span><\/p>\n

\"Macros<\/a><\/p>\n

After following the steps, the macro icon is also viewed at the top of the excel worksheet. Once, you click on the icon then you can view all the recorded macro of the worksheet.\u00a0<\/span><\/p>\n

9. How to save a macro-enabled file?<\/h4>\n

To keep the macro enabled in the worksheet, save the excel worksheet type as Excel Macro-Enabled Workbook.<\/span><\/p>\n

\"Macro<\/a><\/p>\n

This is how we have to save a macro enabled worksheet.<\/span><\/p>\n

10. Saving Macros in a Personal Workbook<\/h4>\n

Create a macro and save them in a personal macro workbook.<\/span><\/p>\n

\"personal<\/a><\/p>\n

By creating a macro and storing them in a personal macro workbook would help you hide the information that the worksheet is macro enabled. It saves the spreadsheet as a normal excel workbook rather than saving it as a macro enabled worksheet.<\/span><\/p>\n

11. Hiding \/ Unhiding a personal Macro workbook:<\/b><\/h4>\n

If you want to make changes in the macros which are being stored in a personal macro workbook, then click on the view tab and choose to unhide from the ribbon. <\/span><\/p>\n

Following the above instruction, you can unhide your personal workbook since they will be hidden by default. After unhiding, you can run your macro or edit it as per your convenience.<\/span><\/p>\n

How to view macros in VBA<\/h3>\n

Macros are being stored in excel as VBA code. After recording a macro, we can view, modify, copy the code that is generated. We can also write code in the VBA code editor. There are three ways to view excel macro code. They are as follows:<\/span><\/p>\n

1. Developer Tab on the Ribbon<\/h4>\n

Go to the Developer tab, click on the view code from the ribbon.<\/span><\/p>\n

\"view<\/a><\/p>\n

This leads to the VBA code editor window screen.<\/span><\/p>\n

\"Macros<\/a><\/p>\n

VBA code editor contains the code of already recorded macros.<\/span><\/p>\n

2. Visual Basic Icon<\/h4>\n

Go to the developer tab and click on the visual basic icon. This automatically leads us to the VBA code editor window screen.<\/span><\/p>\n

\"visual<\/a><\/p>\n

\"Macros<\/a><\/p>\n

3. Macro Dialog box<\/h4>\n

You can also open the code editor by clicking on the macros on the view tab. Choose view macros and choose the respective macro and click on the edit button. This leads you to the VBA code editor.<\/span><\/p>\n

\"edit<\/a><\/p>\n

The code editor also holds following few components and they are:<\/span><\/p>\n