What is the use of macros in Excel with examples
|
This hands-on Excel Macros tutorial explains what is a Macro, how to create and use VBA Macros with plenty of examples: Show
Most of us in the industry will surely have certain tasks that are to be performed repeatedly almost every day. Now imagine if those tasks are done with just a single click. Sounds exciting? Excel Macros is the answer to that. In this tutorial, we will learn what a Macro is? How to record a Macro using absolute and relative reference along with some practical examples. => Take A Look At The VBA Beginners Guide Here  What You Will Learn:
What Are Excel MacrosA macro is a set of actions that you can run to perform the desired task. Suppose every month you create a report that needs to mark the user accounts with the overdue amount in bold and red. Then you can create and run a macro that applies these formatting changes every time you wish to. How To Enable Macros In ExcelThe Developer tab gives us the ability to work with features like Macros, Add-ins, and also allows us to write our own VBA code that will help us to automate anything we wish. This tab is hidden by default. Follow the below steps to unhide the Developer tab. This works on all versions of Excel for Windows (Excel 2007,2010, 2013, 2016, 2019). Note: This is a one-time process. Once you enable the Developer Tab, it will always be shown in a custom ribbon for every Excel instance you open, unless you go ahead and disable it explicitly. Enabling The Developer Tab#1) Click the File tab #2) Click Options #3) Click on Customize Ribbon. #4) Under Customize Ribbon enable Developer. Once you enable the Developer tab, it will be displayed on the ribbon list. Developer Tab’s OptionsEnlisted below are the options that are present under the Developer tab.
How To Record A MacroConsider an example, that your company has a certain tool which generates timesheets for various departments in Excel. You as a manager have the responsibility of reviewing and sending the sheet to the finance team every week. But before sending you are asked to make some formatting like:
Instead of doing this manually every week, you can just create a macro and perform all these actions in just a click. Recording macro is fairly easy. Navigate to the Developer tab, and hit on Record Macro. This will open up a window where you need to enter. #1) Macro name: Name should not have spaces in between words. This has to start with an alphabet or underscore. #2) Shortcut key: This is useful when you are running a macro. If you press the shortcut key, it will be executed. Make sure to give a key which is not already taken, else macro will override that. For example, if you mention Ctrl+S as the shortcut, then every time you press Ctrl+S, your macro will be executed and thereby ignoring the save file option. Hence it is recommended to add Shift, like Ctrl+Shift+D #3) Store macro in: This has 3 options as given below.
#4) Description: This will describe the purpose of the macro. It is recommended to give a detailed description so that anyone using that will know what exactly it is used for. Once you fill in the details for the fields mentioned above, you can go ahead and perform the actions required in the Excel Workbook and everything will be recorded. Once done, go back to the Developer tab and hit on Stop Recording. Saving An Excel Workbook With MacroSelecting Store macro in as “This Workbook”: Consider that you have selected the Store macro in as “This Workbook” while recording. Once done go ahead and save the file. While saving you need to select Excel Macro-Enabled Workbook. You don’t have to explicitly save the macro. It gets saved automatically. Selecting Store macro in as “Personal Macro workbook”: Now consider selecting the Store macro in as “Personal Macro workbook” while recording. You need to save the macro explicitly. If you just save the Excel file and then try to close the file. Then you will receive a pop-up dialog as shown below. Note: If you don’t save this then the macro will be deleted. Executing A MacroNow that we are done with recording and saving the file, let’s try to run it and achieve the desired results. We have gone ahead and recorded a macro with all the steps required to achieve in the attendance timesheet example and saved it as a This Workbook with the shortcut key as Ctrl+Shift+B. So every week when you receive a new Excel from the software tool, you just have to open that Excel file and hit the shortcut key (Ctrl+Shift+B) and all the changes will be incorporated as expected. The resulting Excel is given below. Attached the Excel-Macro-Workbook Note:
Cell ReferencingThere are 2 ways to record a Macro as shown below.
Absolute Cell Referencing: Absolute references will always point to the particular cell where it was recorded. For example: if you record a text in A10 cell then next time when you use that macro in another workbook, it will place that text in A10. Consider our Attendance timesheet example. We always want the title to be in the first row of every sheet. We don’t want to cell reference to change when it’s copied to other sheets or workbooks. In that case, Absolute Cell Referencing comes handy. Relative Cell Referencing: Suppose you have to repeat the steps at various places in the worksheet. Relative references is convenient whenever you need to repeat the same calculation or steps across multiple rows or columns. Example: Say suppose you have an Excel sheet with the full names, phone numbers, and the DOBs of 1000 employees. (Format is as shown below) Emp IDEmp FullNamePhone NumberDOB1John Jeson111111111110-01-19872Tom Matis222222222201-02-19883Jesper Cluster333333333322-02-19894Tim Joseph444444444416-03-19905Vijay abc555555555507-04-1991 Your manager expects you to:
Since there are 1000 records, doing it manually would take time. So you decide to create a Macro. But using absolute reference will not solve your problem as you want it to work across multiple rows and columns. In this case, Relative reference comes handy. Record Excel Macro Using Relative ReferenceTo record using a relative reference, first select the cell you want to start the recording. Go to Developer -> click on Use Relative Reference -> Record Macro. Record anything you wish and hit stop recording. For the above example follow these steps.
Emp IDEmp FirstNameEmp LastNamePhone NumberDOB1JohnJeson(+91) 111111111110-Jan-872TomMatis(+91) 222222222201-Feb-883JesperCluster(+91) 333333333322-Feb-894TimJoseph(+91) 444444444416-Mar-905Vijayabc(+91) 555555555507-Apr-91 Attached File for reference Frequently Asked QuestionsQ #1) What is an example of macros in Excel? Answer: A macro is a set of actions that you can run to perform the desired task. Suppose you create a report every month that needs to mark the user accounts with the overdue amount in bold and red. You can create and run a macro that applies these formatting changes every time you wish to with just a single click. Q #2) Where are macros in Excel? Answer: All the recorded Macros will be available under Developer tab -> Macros If you can’t find a Personal Macro, then go to View -> Unhide. Q #3) What are the types of Cell References in Excel? Answer:
Q #4) How do I save a macro in Excel to all workbooks? Answer: While recording a macro select Personal Macro workbook under store macro in, this will make your macro to be available for all the workbooks. If you still don’t see the option, go to View -> Unhide. ConclusionIn this tutorial, we have learned Excel Macros that help us in automating the routine tasks in Excel. We have seen what a macro is? How to enable the macro to be shown in Excel. We also explored how to record a macro using absolute and relative cell referencing with examples. What are examples of macros in Excel?Excel Macro Examples. Unhide All Worksheets at One Go.. Hide All Worksheets Except the Active Sheet.. Sort Worksheets Alphabetically Using VBA.. Protect All Worksheets At One Go.. Unprotect All Worksheets At One Go.. Unhide All Rows and Columns.. Unmerge All Merged Cells.. Save Workbook With TimeStamp in Its Name.. What is the main usage of macro?Macros are used to make a sequence of computing instructions available to the programmer as a single program statement, making the programming task less tedious and less error-prone. (Thus, they are called "macros" because a "big" block of code can be expanded from a "small" sequence of characters.)
What is the use of macros in Excel with examples PDF?An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently.
What is the use of VBA in Excel with examples?VBA is used to automate tasks and perform several other functions beyond creating and organizing spreadsheets. For example, users need to automate some aspects of Excel, such as repetitive tasks, frequent tasks, generating reports, etc.
|
