Macros are small, simple programs in Excel that can automate common tasks. A macro is a series of commands, such as VBA codes, designed to automate a complex or repetitive task.
You must be careful when you enable macros, as malicious macros exist. When you run a malicious macro unexpectedly, it might damage or delete your files or corrupt your Microsoft Office installation. In Excel, all the macros are disabled by default with notification due to their potential to inflict harm. To avoid errors, enable only safe macros. Only use VBA codes that you have reviewed and understand the function of. Use macros that you write or record yourself or are from trusted sources.
This article focuses on enabling macros in Excel and the benefits of doing so.
Why Should I Enable Macros in Excel?
Suppose you have a large spreadsheet with details of employees. Imagine your task is to search through the records to get the list of employees with work anniversaries during that particular month and then mail that information to the HR department.
Because this is a repetitive job each month, it is best to automate it as it saves time and reduces errors. You can record a macro that searches your file, lists the employees with work anniversaries in the current month, and then mails the list.
Before creating a macro, users must decide the extent of permissions to give while enabling a macro. You can either give complete, partial, or no permission to the macros according to your preference, and then you may take further steps to enable a macro in Excel.
Excel Macro Basics – Enabling Macros
Before working with macros, you must enable them using either of the methods outlined below.
Through Trust Center
Click the File tab, and you will get the Options dialogue box. Click on Trust Center and then Trust Center Settings. You will see a separate dialogue box, Trust Center. There, select Macro settings and select the extent of permission to grant. Select any of the options from the Macro Settings dialogue box depending on the extent of permission you decide to give.
- Disable VBA macros without notification.
- Disable VBA macros with notification.
- Disable VBA macros except for digitally signed macros.
- Enable VBA macros.
Disable all macros without notification does not send any notifications about the disabled macros.
Disable all macros with a notification sends notifications stating that the macros of the current file are disabled.
Disable all macros except digitally signed macros will stop the usage of all macros except the digitally signed ones.
Enable all macros option gives full permission to run all macros.
Through The Developer Tab
Another method of enabling macros in Excel is through the Developer tab. It is a built-in tab in Excel that provides the necessary features to use in VBA code to perform a macro operation.
To enable the Developer tab, right-click on the Excel ribbon. This will cause a dialogue box to appear; click on the Customize the Ribbon option and then check on the Developer option. You can also follow these options to include the Developer tab: File → Options → Customize Ribbon → Developer.
The Developer tab appears next to the View tab, and when you click on the Developer tab, the ribbon has the following options:
Code – This group has the option Visual Basic which opens the application to write the VBA code. The Macros option is where you name it, run, and edit. You can Record Macro and also set Macro Security. This option is the same as how you enable the macros through the Trust Center.
Add-ins – You can include Microsoft add-ins or Excel add-ins like Euro Currency Tools and Analysis ToolPak, an Excel add-in program to provide data analysis tools for financial, statistical, and engineering data analysis.
Controls – You can insert Form controls and ActiveX Controls. Other options such as Properties, View Code, and Run Dialog are related to the VBA code.
XML – This group enables the import and export of XML files, adding Expansion Packs and Refresh Data.
How to Create a Macro in Excel
Let’s look at the steps to create a macro in Excel.
- Click on the Developer tab and then the Insert option.
- You’ll find Form Controls and ActiveX Controls.
- In the ActiveX controls, click Command Button and you’ll see a + button on your spreadsheet.
- Drag and draw a command button.
- Right-click on the command button and click on View Code.
- A separate window for Visual Basic Application will open up.
- In the code window, you will see two lines of code as
Private Sub CommandButton1_Click ()
End Sub
Place your cursor in between the lines, and in the second line, include the following code:
Range (“A1”). Value = “This is a tutorial on Excel Macros”.
After you enter the code, click on the Run button, and then click on View Object in the Project Explorer window.
You will get to the Excel spreadsheet where you inserted the command button. You’ll see the message in cell A1 when you click on the button.
Excel Macro Examples
Let’s take a look at one more Excel macro example. This example will create a macro in Excel without a code. There’s a table with a list of employees and their joining dates. Assume you are interested in highlighting the new employees. Looking at the table, it is evident that the employee Jean joined in July.
This is a basic example of creating a macro in Excel which would highlight the row with the name of the employee who joined in July. Let’s look at the steps.
Step 1: Go to the Developer tab.
Step 2: In the code group, click on Record Macro.
Step 3: Choose a Macro name and an appropriate Shortcut key.
Step 4: Perform the intended task. Here, the new employee’s name is highlighted using the Fill Colour option.
Step 5: Click on Stop Recording.
Step 6: Go to Insert → Illustrations → Shapes and select a rectangle shape to place a button.
Step 7: Then go to Developer → Insert → Form Controls → Button.
Step 8: Drag and draw a button inside the shape.
Step 9: Right-click the button, select Edit Text and change the button’s name.
Step 10: Again, right-click the button, select Assign Macro, and choose the macro you recorded.
Step 11: Meanwhile, ensure you have enabled the macros using the steps that we discussed in the previous section.
Step 12: Save the worksheet and run it.
Step 13: Click on the button, and you will see the colour change.
Step 14: Also, you can use the shortcut to run the macro.
Frequently Asked Questions
1. What are macros?
Macros are a series of instructions written in VBA code to automate manual tasks in order to save time. Excel executes these as step-by-step instructions on the given data. It automates repetitive tasks such as summation, cell formatting, and customizing the header and footer.
2. How are macros disabled?
By default, Excel’s settings have disabled macros with a notification, allowing users to enable them manually. Follow these steps if you wish to disable all the macros without receiving any notifications.
- Go to File → Options → Trust Center.
- Then, click Trust Center Settings → Macro Settings.
- Select the option Disable VBA macros without notification.
3. What are the security features that you must consider while enabling a macro?
- The VBA code file is saved as a macro-enabled workbook with the .xlsm extension. It won’t get enabled if it is saved with another extension.
- You must use caution when you enable all macros without warning or authorization. If you enable an untrusted source’s macro by mistake, it will corrupt your source files.
- The Disable all macros without notification option will not explain why the macros don’t work. Therefore, always select Disable all macros with notification to understand the warnings.
Closing Thoughts
This article covered enabling macros in Excel and recording a macro to automate an action that you otherwise perform manually in Excel. Using a macro, you can print a particular range of cells using just a shortcut key.
There are a lot of other functions and features in Excel that you can learn which will help you manage data more efficiently. Check our courses in Excel and Microsoft Office Applications to learn what a pivot table is, how to insert slicers, and much more. Also, you can learn what lookup functions are and how to use them using our Lookups in Excel – Beginner’s Guide.