Home // Video Tutorials // 6 - 10 (Advanced) // 7 // Excel VBA Macros

Excel VBA Macros

How to use the Excel VBA Macro recorder to automate some repetitive tasks.

Excel VBA Macros

Excel VBA Macros allow you to automate some tedious tasks, using something called the Macro Recorder.

As an example, what we have got here is some information from Years 1-10 and you’ll see it goes from 100 in Year 1 to 1000 in Year 10. What we want to do is, next year, allow the 1000 to fall off at year 10 and the 100 to 900 must move one down- so there is space to enter the new information in Year 1. How we can do this is to use the Macro-Recorder and you will see if you click the “View” Tab and at the end you will find “Macros” – you’ll see the options are – View Macros, Record Macros, and Use Relative References. I am going to Record a Macro and hence click on Record Macro – you get some options, I am going to name it – Rolling_Years- it is important to note that you can’t have spaces in the name. You can give it a shortcut key if you want to. It is important to tell Excel where to store the Macro. It can either be in the Personal Macro Workbook which is a workbook that exists and which will open every time you open Excel, you can store it in a new workbook or else in “this workbook”. I am going to choose this workbook. If you want you can give it a description. When I click ok, you’ll see at the extreme bottom left hand side of the screen there is a little button, which warns you that a Macro is currently recording. When you want to stop, you will stop it on this button. But now any key strokes you type will be recorded so, for example, what I am going to do is cause the number column to roll down, so the first thing I am going to do is click on the 1000 opposite Year 10 and delete it. Then highlight cells 100 to 900 and copy the column, then click in the cell 200 and paste and then click in the top cell (100)- opposite Year 1- delete it and leave the highlighted cell opposite year 1 so that when the macro runs it will capture the data in this top cell. Switch off or stop the Macro. Now let’s see how well the Macro works. Again under View, click Macros and go to View Macros and now because I have told Excel to show me only the Macros in this workbook, I will find “Rolling Years”. Click “Run” and watch what happens. You will see it has deleted the bottom (900) taken the top and copied it down, so now you can insert 50 in year 1, 75 in year 2. The next year we go to View Macros, find Rolling Years, click “Run” and you’ll see its moved down and slowly but surely It rolls all the way down. Now I want to show you what the recorder did while you were recording. If you go to Macros and go to View Macros and then go to Rolling Years and click on Edit – it takes me to the VBA Editor and while you were recording key strokes, Excel was actually writing code.So you’ll see here Range –choose Cell D21: Clear contents; Choose a range (D12:D20) copy it and paste it etc. So what it has done is it has taken your actions and turned it into code that Excel can understand. Also another useful ability in Excel, is once you have set up a Macro, you can actually set up a button or picture or symbol or something that you can use to automatically activate the Macro. For example, I am going to go to “Insert”, and I am going to insert a shape. Place the shape in the worksheet as shown. If you right click on any shape or picture, you will find an option – Assign Macro- click on it. It will give me all the Macros that are available in this workbook and I am going to click on “Rolling Years” and say ok. You can make changes to your shape – make it neat and tidy and put words in it, etc. Click away from it. Now when I want to run the Macro- every time you click in the shape it will run and you will see the rolling down effect on the list of numbers – by merely clicking in your shape you will be able to run your Macro very easily.