Macros are a tool in Excel to record your steps that you do in repetitive Excel tasks and can repeat them on request. While a macro essentially writes a Visual basic program to do these steps, you don’t need to be a programmer to create one. In this article, I want to show how to create a macro, edit it and assign it to a button.
Create a Macro
Let’s say we have the following spreadsheet.
Each week you get this file and have to bold the column headings, remove the dashes in the phone number and insert a column after state.
You could do this manually each time you get it. Or you could create macro to do it. This way, when you get the file next time, you just run the macro and it will do the tasks you tell it.
Do create the macro, go to View on the ribbon at the top , then over to macro and then down to Record macro.
Then a wizard will come up.
Here you should name it something descriptive but you can’t use spaces. You can create a shortcut key here too if you wanted or write a description of the macro for future reference.
Then press ok and the macro will start recording. It will record all your actions, so do the steps you want.
Once done, go back up to View at the top, then over to macros and then down to Stop Recording.
Congrats! You just created a macro!
Now the next time you get this file, you can run it and it will do the steps.
**Tip – when you save the file, if it has a macro, you will need to save it as a macro enable Excel file.
EDIT THE MACRO – Now that you recorded the macro, you can edit it. To do this, you need to be able to read and understand visual basic, though. Im not going to go into depth in this here, but a good reference for that is http://www.excel-easy.com/vba.html
Let’s check the macro first of all. Go to View at the top then over to Macros and then down to view macros. It will show all the macros you made.
Here is the macro we just made. I called it monthly_report. From here you could run it if you wanted. You could also step into it. That means you go one piece of code at a time. This is used for error finding, when the macro doesn’t work right.
you can also edit or delete it from here. Click on edit. This will come up:
Here is the code and you can edit it. A lot of VBA code is self explanatory and you don’t need to be a programmer to understand it. However editing it might be a little more difficult.
For example, the first line : Rows(“1:1”).Select means that it is selecting the first row. Which is what I did in the macro. The next part is Selection.Font.Bold = True which is the code to bold something. I bolded everything in row 1.
So this is how you can edit it. If you want to add more code into this and don’t know how to, I suggest going to Google and searching for what you need. If you have that question, many others have before you and posted it on sites like http://www.excelforum.com
ADDING MACRO TO A BUTTON
When you create a macro, if you want to use it frequently, you might want to add it to a button on your spreadsheet for easy access. This way you don’t have to go through the ribbon and make 3 or 4 steps to run it.
To add a button ,first we need to insert the image.
Go to Insert at the top ribbon. Then over to Shapes and drop down to the list of shapes. Then pick one and add it to your spreadsheet.
Once you have the shape, you can edit it how you want.
To add the macro to the button, right click on it and go down to Assign macro. Then pick the macro from the list and click ok.
There you have it! You just assigned the macro to the button.
Now whenever you press it, the macro will run!
And that is it for macros. Well really there’s a ton more if you want to master them. However this article is enough to get you to create a basic macro and teach you how to edit and assign it.
If you are interested in checking out our program on Excel functions, go to Master Excel Function product page, It has 4.5 hours of videos and 114 page ebook!