How to enable and disable Macros in Excel

What are called Macros in Excel?

In Excel, macros are written in VBA called Visual Basic Applications, a programming language built in Excel. It is a set of instructions that is used to automate repetitive tasks. The work done by macros includes,

1. Formatting data,
2. Creating charts
3. Automating calculations
4. Copies the data from one worksheet to another worksheet.

If the Macro is recorded once, it is played back many times if needed, as it takes significant time and effort. Either the user can run the Macro manually, or it is set to run automatically based on specific events or conditions. One advantage of using Macro is that it reduces the workload and time and gives error-free data.

A detailed view of Macros

Before enabling Macros, there needs to know about the security risk involved in it. Though it is written in VBA, sometimes it executes harmful code in the computer. When a macro-enabled document, such as an Excel spreadsheet, is opened, its macros are executed automatically. If it contains malicious code, it potentially harms the user's computer by installing malware, stealing personal information, or performing malicious actions, including removing the data present in the hard drive and corrupting the Microsoft Office Installation.

How to enable secure Macros?

To enable secure macros, Microsoft Excel usually disables macros by default and enables the macros if needed. Users can enable macros from trusted sources digitally signed by a trusted publisher. The macro-enabled documents should be scanned for any viruses before opening. A sandbox or restricted environment should be created to avoid a malicious attack. The code should be thoroughly reviewed, enabling the macros recorded by the user, etc., are the methods to protect the document from the attack. In addition, the antivirus or Microsoft Software should be maintained in an up-to-date format with necessary security patches that restrict the vulnerabilities accessing the data.

How does Macro work?

The working process of Macro is as follows,

Recording: Actions are recorded and saved as Macro and are repeated later.

Editing: The user is allowed to edit the actions which are recorded by editing the code or adding functionality

Running: It either run manually by clicking a menu or button option or is triggered by an event such as opening a document or clicking a button.

To access the macros quickly, it is either assigned a button or keyboard shortcut, or ribbon to run them.

Pros of Macros

Pros of Macros are listed below as follows,

Increased Productivity- It performs repetitive tasks, including formatting text, creating a chart, copying or pasting the data, and saving the user's time.

Consistency and Accuracy- It ensures that the work performed every time is the same as before, which increases consistency and error is reduced in the work.

Customization- Based on the job and requirement, the Macro is customized, which helps automate the task-specific to the job or workflow.

Easy usage: One can be an expert in programming to create macros, as macro recording tools in the office programs record and save the actions as Macros.

Compatibility: Macros support most Microsoft Office versions. Hence one can share the macros with others who work on different computers.

Save Time: Macros perform repetitive tasks, reduce manual user work, and save time.

Accuracy: It increases the accuracy of the data by reducing errors.

Better collaboration: It facilitates better collaboration with the team, as the same format can be used by all team members.

Cons of Macros

Security Risks: Security Risk arises if the Macro-enabled document is opened from untrusted sources, as the code may contain malicious virus or software.

Limited Compatibility: Macros are familiar with Microsoft Office programs, so it has limited compatibility with other software or programs.

Complexity: Macros are difficult to manage if the user has no prior knowledge of scripting or programming language.

Dependence: As macros perform repetitive tasks, the manual work is reduced. Hence increases the chance of dependency on macros.

Incompatibility: If the macros are created in the older version, they may not work in a newer version of the program

Chance of Data Loss: The data may be lost if macros are not written or used correctly.

Data corruption: Similar to data loss, data corruption occurs if the code is not written or used correctly.

Software Crash: Software crashes occur if the macros are not written or used correctly, leading to the loss of important information.

How to enable macros in Excel?

To enable macros in Excel, the steps to be followed are,

Step 1: Open the Excel workbook containing the Macro that needs to be enabled.

Step 2: Click the Office button in Excel, and choose the "Excel options" in the right corner.

Step 3: Choose the "Trust Centre" option in the Excel options. Another page will display in that choice, "Trust Centre Settings".

Step 4: Choose the option "Macro Settings" in the Trust Centre.

There display four options as follows,

Disable all macros without notification, stating that the user doesn't get a notification regarding the disabling of macros.

Disable all macros with a notification state where the user gets a notification regarding the disabling of macros in the current file. The security alert appears if the Macro is present.

Disable all macros except digitally signed macros, which states that it disables all macros except the digitally signed ones. The security alert will arise if the unsigned Macro is present. If the Macro is digitally signed by a publisher, but not trusted yet, the user is provided with the option to enable the signed Macro and to trust the publisher.

Enable all macros states that it gives full permission to run all macros. The macros run without permission.

Step 5: Choose the option based on the requirement and Click Ok. Here the option "Enable all the macro" is selected. If the source is trusted, enable all Macro should be selected.

Step 6: In the Trust Centre, choose the ActiveX Settings option. Click the option "Enable all controls without restrictions and without prompting". This option is selected to enable the macros properly, as it is the object of the macros. Click Ok.

The macros are enabled by following the necessary steps.

How to disable macros in Excel?

A workbook that contains Macro sometimes needs to be disabled. To disable macros, the steps to be followed,

Step 1: Open the Excel workbook containing the Macro that needs to be disabled.

Step 2: Click the Office button in Excel, and choose "Excel options" in the right corner.

Step 3: Choose the "Trust Centre" option in the Excel options. Another page will display in that choice, "Trust Centre Settings".

Step 4: Choose the option "Macro Settings" in the Trust Centre.

Step 5: Choose the option based on the requirement and Click Ok. Here the option "Disable all macro without notification" is selected. The user will get notified regarding disabling macros.

Here, the first three options are based on the disabling macros; therefore, the option is selected based on the requirement

The programming language used in Macros

The programming language used in Macros is as follows,

Visual Basic Application is built in Microsoft Office and is used to develop applications such as Word, PowerPoint, Excel, etc. VBA is a version of Visual Basic Programming that is similar in terms of syntax and structure to Visual Basic.

VBA adds user-interface elements, interacts with other external sources, and creates custom functions.

Macros written in VBA are saved as a part of the document or separate file, either used manually or triggered by an event, including opening a document or clicking a button.

VBA code is used to automate the tasks which take more time and do manually is difficult. Hence it extends the functionality of the Office Applications.

Including VBA, other languages used to create macros are JavaScript, Python, and C# with the help of the Excel-DNA library.

Summary

Macros are a powerful tool enabled based on the options given and the user's requirement. Ensure using macros from a trusted publisher as it contains security risks. This tutorial briefly explains the functions and features of macros and the step-by-step process of enabling and disabling macros in Excel.