Why macro is used in excel

You will need to display it via customize report. Excel Macros can be used to compromise your system by attackers. By default, they are disabled in Excel. If you need to run macros, you will need to enable running macros and only run macros that you know come from a trusted source. Always fill in the description of the macro when creating one. This will help you and others to understand what the macro is doing.

We will work with the scenario described in the importance of macros Excel. We will create a macro enabled template that will import the above data and format it to meet our business reporting requirements.

To execute VBA program, you have to have access to developer option in Excel. Enable the developer option as shown in the below Excel macro example and pin it into your main ribbon in Excel. First, we will see how we can create a command button on the spreadsheet and execute the program. Now that we have finished our routine work, we can click on stop recording macro button as shown in the image below. Before we save our work book, we will need to delete the imported data.

We will do this to create a template that we will be copying every time we have new receipts and want to run the ImportBankReceipts macro.

Macros simplify our work lives by automating most of the routine works that we do. Macros Excel are powered by Visual Basic for Applications. You'll still need to type or edit code manually sometimes. But it's still a handy way to get started. Here's how: 1. Go to the "View" tab of the ribbon and click the tiny arrow below the "Macros" button.

Then click "Record Macro" 3. Type in the name of your macro and click "OK" to start the recording. Perform the actions in your spreadsheet you want to be turned into a macro. This is your first code! Amazing, right? Yours probably look different than mine. Can you guess what my code does? Sub Makebold is just the text Sub followed by the name I entered when I started recording. End sub simply tells Excel that the macro stops here. Now, what will happen if I change the True part of the third line to False?

The macro would then remove any bold formatting from the selection instead of making it bold. But the real power of macros comes when you can write it yourself—so let's get started learning to write simple VBA code.

Macros are just bits of code in Excel that do your bidding. Once you write the code in the VBA Editor, you can run it and let the code work its magic on your spreadsheet. But what's even better is to build your macro into your spreadsheet, and the best tool for that is buttons.

So first, before we start coding, let's add a button to run our macro. You can use various Excel objects as buttons for running macros, but I prefer to use a shape from the "Insert" tab. When you have inserted your shape, right click it and select "Assign Macro…" Then select the macro you want to run when the shape is clicked—perhaps the one you just made with a recording and save it by clicking "OK".

Now, when you click the shape which we just turned into a button, Excel will run the macro without having to open the code each time.

There's one other thing to note before we get started: saving your spreadsheet with Macros. By default, Excel spreadsheet files with an. Go ahead and do that to save your spreadsheet before we start coding. Copying and pasting is the simplest way to move data around, but it's still tedious. What if your spreadsheet could do that for you? With a macro, it could. Let's see how to code a macro that will copy data and move it around in a spreadsheet.

Open the project file you downloaded earlier and make sure the "Copy, cut, and paste" sheet is selected. This is a sample employee database with the names, departments, and salaries of some employees. First, let's look at the code we need:. Copying in VBA is quite easy. Here's some examples:. Range "A:C". Range "A1:C".

Remember when you recorded a macro before? The macro had Sub Nameofmacro and End sub at the top and bottom line of the code. These lines must always be included. Excel makes that easy, too: When you type in "Sub" followed by the macro name in the beginning of the code, the End sub is automatically inserted at the bottom line.

Pasting can be done in different ways depending on what you want to paste. If you want to relocate your data instead of copying it, you need to cut it. Cutting is quite easy and follows the exact same logic as copying. Therefore, you need these lines to paste your cells with VBA: Range "Insert where you want to paste". Select ActiveSheet. For example, here's the code you'd need to cut the range A:C and paste it into D1 :. Range "D1". Copying, cutting, and pasting are simple actions that can be done manually without breaking a sweat.

But when you copy and paste the same cells several times a day, a button that does it for you can save a bunch of time. Additionally, you can combine copying and pasting in VBA with some other cool code to do even more in your spreadsheet automatically.

I just showed you how to take a simple action copying and pasting and attach it to a button, so you can do it with a mouse click. That's just one automated action. When you have the code to repeat itself, though, it can do longer and more complex automation tasks in seconds. Take a look at the "Loops" sheet in the project file.

This type of faulty data structure is not unusual when exporting data from older programs. This can take a lot of time to fix manually, especially if the spreadsheet includes thousands of rows instead of the small sample data in this project file. Enter this code in a module, then look at the explanations below the picture:. This line makes sure the loop starts at the top-left cell in the sheet and not accidentally messes the data up by starting somewhere else.

This means that the loop will run times. The number of times the loop should run depends on the actions you want it to do. Use your good sense here. This line recognizes the active cell and tells Excel to move 3 rows down and select that cell, which then becomes the new active cell.

If it was every fourth row that was misplaced in our data, instead of every third, we could just replace the 3 with a 4 in this line. This line tells Excel what to do with this newly selected cell. In this case, we want to delete the cell in such manner that the cells to the right of the cell are moved left. That is achieved with this line.

If we wanted to do something else with the misplaced rows, this is the place to do it. This line tells Excel that there are no more actions within the loop. I click the Record Macro button. Let's name this macro, "FillDays". Macro names can't contain spaces. I click OK to start the recorder. See how the Record Macro button turned to Stop Recording. When I'm done, I'll click that.

We are done with our auto-fill, and since that's all we wanted to record, I'll click Stop Recording. Now I'll clear these filled cells to clean up the worksheet, and select A1 again so we can test our macro. I'll click the Macros button to bring up the Macro dialog box, and click Run. I'll clear these cells again, and this time I'll select a cell in a different column and row C3, just to test the macro a little more.

By using relative references, the macro will build a series by selecting the active cell and 6 cells to its right, instead of building a series using A1 through G1. In the next video, we'll rewrite the "FillDaysRelative" macro manually in an Excel tool called the Visual Basic Editor , so that it'll do something a little bit different.

Table of contents. Work with macros. Table of contents Work with macros. Excel training. Want more? Create or delete a macro Edit a macro Assign a macro to a button Create and save all your macros in a single workbook. The best way to do that? The macro recorder is the easiest way to create a new macro in Excel. That's where all of the macro commands are.

So, let's get started. That's what we want to record.