Many people tend to believe that “coding” is beyond their reach. I am proof that it is not. No matter what your aptitude, you will be able to tweak your spreadsheets using the embedded VBA (Visual Basic for Applications) code.
Do you need to go out and buy one of those huge books that claim to demystify VBA? Eventually, but not in the beginning. The best way to begin, is to record simple macros and then view the code that underlies them.
Macros? What are Macros? I hear you say. Well, this is exciting stuff. Everything you do on Excel can be recorded. This is called a Macro. You move your mouse around and click on the things you usually click on and Excel will record everything you do. You can then play it back later and have it repeat what you did. This is really useful for tasks that you have to repeat. Especially those tedious, boring ones.
Now – where can you find these magical recordings? You know those ribbon menus across the top of the program? Well there is one missing. Take your mouse up to the top of the screen and press the right mouse button on one of the tabs. Left click on Customize the Ribbon. And wait a second or two. The Customize the ribbon window will open. Look at the ribbons available along the right side of the screen and you will notice that the Developer ribbon is missing a tick. Click on it to add the tick and click ok. You will now notice a tab with the words Developer on it. Click on it.
Click on Record Macro. Make sure the name you choose doesn’t contain spaces or strange characters – letters and numbers only. Also make sure that This Workbook is selected, otherwise you will not be able to view the code. Click on Ok.
Now do things. Open a file, type some text, made something a different colour or make it bold. Anything. When you are done, click on Stop Recording.
Now you are ready to look at it. Click on the Macros button and click on the macro you just recorded. On the right hand side you will see an Edit option. Select it. You are now in the code window and looking at the code behind the Macro. While reading it, think about what you were doing. After a few times, you will begin to see what the computer does when you move the mouse around the screen and click.
Remember to save the document if you wish to view the code later. It will not allow you to save it as a normal document (xlsx). You will have to select Save As and select a macro enabled spreadsheet (xlsm)
Enjoy playing around. If you liked this experience, ask LGIT about their Microsoft Excel 2010 Macros and VBA course.
Blog post by Cathy