If there is a certain work or command or task that needs to be followed or done large number of times, that repetitive task can be automated, in order to reduce the redundancy of writing or undergoing the same task repeatedly or successively. A macro is an action or a set of actions that is devised to run as many times as you want. When you create a macro in Excel or other programs, you are recording your mouse clicks and keystrokes that will be followed in an automated manner, such that the workload of a particular redundant task is reduced.
Suppose that every month, you create a report for your organization’s employees total working hours. You want to format the names of the employees with less working hours in red, and also apply bold formatting to their names. You can create and run a macro that immediately applies these formatting changes to the cells you select, on the basis of their total working hours. Since this task needs to be done each month, it would obviously require a particular task of comparing the working hours, and eliminating the non-working members (relatively), and in spite of repeating the same task of comparing, a particular macro can be created for carrying out the comparison function of working hours and thus, can help in increasing the efficiency of the task, by making a repetitive task automated.
What is known as a Macro?
A macro is a particular piece of programming code or command that runs in Excel and helps to automate routine and repetitive tasks. In general terms, as well as technical application, a macro is a ‘recording’ of your routine steps in Excel that you can replay using a single button and can be used to carry out a particular function with efficiency.
Uses of macros in Excel
Let’s say you work as an accountant for electricity supplying utility company. Some of the customers pay their bills through the bank or through cash. At the end of the day, you are required to download the data of the all payers from the bank database and format it in a way, considering all transactions, such that the business transactions are important. Carrying out this task manually each day is not only strenuous, but can make it highly erroneous and problematic, wherein Macros come into rescue by automating the boring routine tasks and reducing the workload of performing the ‘ritual repeatedly’.
Before Recording a Macro
Before recording a macro, Developer tab, which is essentially used to provide the feature to ‘develop’ Excel by using features such as Macro, needs to be displayed or enabled onto the Menu Bar to make it easily accessible for further tasks.
To show Developer Tab
- Click on the Microsoft Office Button at the top left corner of MS Excel Screen and then click on Excel Options option at the bottom of the list that appears.
- Click on Popular tab in the Excel Options dialog box, and then ‘Check’ the box against the ‘Show Developer tab in the Ribbon’ under Top options for working with Excel section.
- Click on OK.
Developer Tab is now visible on Menu Bar.
Note: The Ribbon is part of the Microsoft Office Fluent user interface and would become enabled in all associated applications of MS Office. The same could also be applied to MS Word or MS PowerPoint in a similar manner.
How to Record a Macro
You can follow the aforementioned easy steps of recording a macro in Microsoft Excel sequentially:
- Click on the Developer Tab on the Menu Bar, and go to the Code section.
- Click on Record Macro option, such that a dialog box appears.
- Optionally, enter a user-defined name for the macro in the Macro name box, which needs to be distinct and cannot be used beforehand as another Macro. Also, an already defined Microsoft Keyword cannot be used as Macro Name. Enter a shortcut key in the Shortcut key box, location for using Macro, which is on ‘This Workbook’ by default and an appropriate description in the Description box, which will help to explain about what the Macro wants to establish or accomplish and then click OK to start recording.
- Perform the actions you want to automate that need to be a part of the macro, such as entering boilerplate text or filling down a column of data or performing a particular sum. For example, if you want a Macro to display the sum of cells C4 to C23 in a Cell C24, whenever the Macro is run using the same user-defined Macro Name, along with the pressing of the shortcut key within the prescribed location of running Macro, the similar action would be achieved.
- Click on the Developer Tab and click on Stop Recording, for the Macro Recording to stop.
- Save your file in a macro-enabled view. In order to preserve your macros and be available in future, you’ll need to save your workbook in the following manner:
- Click on the File menu on the Menu Bar and select on Save option.
- Click on the File Type menu underneath the file name field.
- Click on Excel Macro-Enabled Workbook.
- Give a user-defined name to the workbook and click on Save to preserve the Macro and the workbook.
Note: In case of 2007 Version of MS Excel, click on Microsoft Office Button on the top left of Excel Program, and click on Save. A dialog box appears, asking to save in ‘Excel Macro-Enabled Workbook’ mode. Click on ‘Yes’ to save the workbook and to preserve the Macro.
Macro is a complex feature to use and understand, and you can even use them to open in other Office application programs. When the macro is recording in Excel, virtually everything you do is added to the macro command, from the hovering of the mouse to the striking of the keyboard!
Using a Macro in Excel
- Open the macro-enabled workbook file in Excel by simply following the steps of ‘Opening a Workbook’. If you have already closed your file before running your macro, you’ll be prompted to enable the content in the dialog box, wherein click on Enable Content that appears at the top of the Excel workbook whenever a macro-enabled workbook is opened.
- Press your macro shortcut, defined at the time of recording macro. In other words, when you want to use your macro, you can quickly run it by pressing the shortcut you defined, on the keyboard.
- Click on the Macros button in the Developer tab, available on the Menu Bar. A dialog box will appear displaying all the macros that are available in your current spreadsheet, or are accessible by the location defined while recording macro.
- Click on the macro you want to use and click on Run button.
The macro will run on the current cell or selection of cells, depending upon the command or the ‘information’ contained in Macro.
Consider the following Spreadsheet titled Society Work, wherein we need to calculate sum of total working hours of all employees of society for different months to check out business goals and requirements and calculate efficiency of employees, individually as well as on a monthly durational basis.
If the Macro used relatively, makes use of sum of cells for different months’ working hours, for different employees, a macro would be created to have sum from 3 to 18 rows for each month and displaying it in 19th row for each month column.
Note: This macro would be created using relative cell references, that is, in terms with the location of starting cell address, and not on the basis of absolute cell locations.
Points to be Considered while creating a Macro
- Name of Macro:
Macro names need to be short, descriptive, unique and distinct. They also provide the feature of containing a description of the Macro, describing its functionality. However, this is rarely obeyed or used by users. Macro names must, mandatorily, begin with an alphabet and cannot contain special characters or numbers. After the first letter, you can other letters, numbers or underscore as special characters, but the length of Macro Name should be short, and cannot exceed 80 characters. It cannot contain any other special character except underscore.
- Cell addresses/locations:
Use relative cells addresses rather than using an absolute cell location. Absolute cell addresses are those cell locations that are specific, and limit macro functionality to a specific cell or range of cells, making it tedious or less flexible to work with. Relative means the macro’s recorded keystrokes are relative or in terms associated to the starting cell’s location, and are not absolute. The default in Excel is Absolute Cell Locations, but this can be changed to Relative by clicking on Developer Tab and choosing Use Relative References, as relative cell locations.
This is how a Macro can be recorded and used in MS Excel, and can help in reducing the redundancy and monotony of routine tasks.