Macros In Excel

Macros in Excel are object-oriented programs (Macros can be called  Subroutine, Procedure, Program, or Code). It is mainly a set of actions that you can run as many times as you want. A macro is a piece of code, written in a programming language i.e. VBA, or Visual Basic for Applications, serves as the embedded programming language in Excel and other Microsoft Office applications like Word and PowerPoint. It provides a powerful platform for automating tasks, enhancing functionality, and extending the capabilities of these programs. 

In my experience, macros in Excel have been instrumental in simplifying complex tasks such as data analysis and project management. I generally use macros for repetitive and complex tasks, but, you can also curate new formulas to automate any other task of choice in Excel. 

I’ve created an easy guide addressing questions like “what are macros in Excel”, “how to enable macros in Excel”, “what is the use of macro in Excel”. Keep reading to know more.

How to Enable Macros in Excel

Learn how to enable macros in Excel with the steps I’ve provided below:

• Go to your toolbar, click on ‘File’
• Under the Help section click on ‘Options.’
• Go to ‘Trust Center’, then go to ‘Trust Center Settings.’ 

• Then click on ‘Macro Settings.’ 
• Under this, you click on ‘Enable all macros’. 

Create a Macro in Excel

It takes hardly a minute to create a Macro.

• Here’s a blank sheet called “Record_A_Macro.”‘Excel VBA’ is the workbook given here.

• Go to the Developer tab, where “Use Relative References ” should be selected, then click on “Record  Macro”. When you are recording for the first time, you will get this dialogue box called Record Macro. 

• Here, you can customize the name of your Macro, like "Color". Then, select a Shortcut Key combination, typically Ctrl along with any letter from A to Z. Choose a key not already assigned or commonly used in Excel. For example, pressing Shift + A will assign Ctrl + Shift + A as the Shortcut Key for your Macro. This ensures efficient access to your Macro without conflicting with existing Excel functionalities.

• You have the option to store this Macro in the current workbook. Additionally, you can provide a description, such as "This Macro will Color the Cells," for clarity. Once done, click OK to begin recording. The "Stop Recording" button will then appear, allowing you to finalize your Macro.

• All changes in this sheet will be recorded as a VBA Script (in the form of Macro).

• Go back to the Home tab to execute the coloring part. Leaving one column, keep your cursor on the next cell. These two changes will be recorded as a Macro.

• Then click “Stop Recording,” and the ‘Shortcut Key’ to run your Macro.

How to Run Macros in Excel

Here you learn basically how to use macros in Excel. If you forget the Shortcut Key, you can still go and run the Macro from the Developer tab, here is a button called “Macros,” if you click on it a window will open, where you can see the Macro you had created, here you need to click on this “Run” button to run the Macro.

• If you click on the “Macro” button in the Developer tab, it will give you the list of Macros you have.

How to Write Macros in Excel

Learn how to create macros in Excel:

• In an Excel VBA Workbook, I've added a sheet named "Write_a_Macro". To access the VBA editor, simply go to the Developer tab and click on "Visual Basic". Inside, you'll find "Module 1" under Modules, ready for your macro creations. This streamlined process facilitates easy access to VBA editing, enhancing your Excel workflow.

• To create a Module in Excel VBA, navigate to the "Insert" option in the menu bar and select "Module". By default, it will be named as 'Module 2'. To change this name, access the "View" menu and click on "Properties Window". This will open a window displaying properties related to the selected module, allowing you to rename it as needed. This straightforward process facilitates efficient organization and customization of VBA modules in Excel.

• Here, you can change the name of the Module. I wrote “Write_a_Macro”.

• To do so, remember that coding parts are written in a sub-procedure. For example, “Sub first_Macro ()”, then press Enter key and write “End Sub”.

• Within those steps, you can add actions. To display text as a message box, use "MsgBox" followed by your text in double-quotes. For instance, "MsgBox", “Upgrade tutorials" will show a message box with the text "Upgrade tutorials".

• To execute the code, position your cursor between the "Sub" and "End Sub" lines. Then, simply click the Run button. Alternatively, navigate to the "Run" option in the menu bar and select "Run Macro," or press "F5" on your keyboard. This streamlined process ensures easy execution of your code.

• A dialogue box will prompt you to click OK, after which it will return you to the VBA editor interface.

• If you want to display some numbers instead of text, type “Msgbox” followed by the desired number. In this example, I chose “50”.  

• Then, once you click on the “Run” option, it will tell you which Macro you want to run, here you can choose and run your Macro.

How to Write a Complex Macro in Excel

If you are an Excel user you might be acquainted with the term “Absolute” and “Relative” References. In case you are not, let me explain here!

If you put some random numbers in an Excel sheet and make a sum of those just by using the Sum formula, put Enter, and copy that formula down, you can notice that by default, the initial Cells are using Relative Cell referencing. That means Excel knows that if I drag a formula down, it needs to modify those Sum references and move each one down one.

However, there is something called Absolute Referencing. If I type ‘Tax’ in one cell and ‘15%’ in the next cell, and choose some random numbers to calculate 15% of those, I will do the calculation in a slightly different way, as in SUM ( value 1 * 15% ) here the value is multiplied by the absolute figure (15%) of the cell where ‘15%’ is written, so I’m making that cell absolute by locking the cell. When I’m going to drag this formula down, Excel is going to adjust the cell reference where we have a percentage (15%) because we were effectively locked to that cell.  

When you record a Macro in Excel, it defaults to Absolute Referencing. This means that if you run the Macro in Cell A1, it will always start from Cell A1, which can be limiting. To overcome this, ensure that you activate "Use Relative References" from the Developer tab before recording your Macro. Also, be sure to select a different cell to conclude your Macro. This enables greater flexibility and adaptability when executing your Macros.

What is The Use of Macros in Excel (with examples)?

In the “Macro” window if you click on the “Edit” button, the VBA editor will open in a new window, where, whatever changes I have done, will be recorded as coding.

When you record a Macro in Excel, it defaults to Absolute Referencing. This means that if you run the Macro in Cell A1, it will always start from Cell A1, which can be limiting. To overcome this, ensure that you activate "Use Relative References" from the Developer tab before recording your Macro. 

Also, be sure to select a different cell to conclude your Macro. This enables greater flexibility and adaptability when executing your Macros.

How to Save an Excel Workbook Containing a Macro?

When attempting to save the file, you'll receive a message indicating that you cannot save a file containing Macros in a Macro-free workbook. If you wish to retain these features, select "No" when prompted.

Then choose a Macro Enabled File Type in the File Type list. In the “Save As” window, choose the “Excel Workbook” option from the ‘Save As type’ section. Then choose “Excel Macro Enabled Workbook”.

You'll notice a difference in the icons between workbooks with Macros and those without. Workbooks containing Macros will display a slightly distinct icon. Additionally, the file extension for a workbook with Macros is ".xlsm".

To elevate your tech expertise and go further in your career, you can take a look at these top 15 ways to improve your Excel skills. 

Wrapping Up

Macros are undeniably invaluable tools, streamlining analysis and enhancing efficiency. I hope this Macros in Excel tutorial has equipped you with the knowledge you need.

Speaking of which, if you are looking to advance your career further, take a look at the bonafide certificate courses upGrad provides. Affiliated to prestigious universities and with curriculums curated by industry experts, these courses will not only give you the added expertise, but also a better chance at a better job position.

Frequently Asked Questions

1. What are macros? Explain with examples.

Macros in Excel, also known as subroutines or automated sequences, mimic keystrokes and mouse actions to accomplish repetitive tasks effortlessly. They're invaluable for data analysis in Excel, as well as in MS Word and PowerPoint.

2. How do I get to macros in Excel?

You can easily run your macros by clicking on the ‘Macros’ option under Developer tab.   

3. How to create a macro?

In order to create a macro in Excel, you can record your macro with necessary details, and then run it using your ‘Shortcut Key’. You can additionally write your own macro in the VBA editing window, where you write your code for your macro, and can run them. 

4. What is the macro key for Excel?

To access macros quickly, use the shortcut Alt + F8, or navigate to the 'Macros' option within the Developer tab.

5. How do macros work?

Macros can be recorded or written in Excel, you can run them using the shortcut key you suggest for each macro. Basically used in data analysis to replace time consuming steps.

6. What are macros in MS Office?

Macros in MS Office, much like in Excel, consist of a series of commands or instructions that automate tasks, significantly saving time.