If you've ever thought about creating macros in Excel, you've probably heard the term "VBA". In this post, I'm going to give you a little background on VBA so that you can decide if it's something you want to tackle.
Excel is a great tool for analyzing and presenting your data. But if you're like most people, you spend more time than you'd like performing repetitive tasks. Maybe you need to import data from your database once a month, sort it, and format it, for example. Macros can save you time by automating these tasks. Consider the Excel report shown below.
To generate this report, raw data was brought in from another application. Subtotals and grand totals were added, and a report heading was inserted at the top. Date and currency formats were applied, and a date/time stamp was added at the bottom.
The original data didn't look quite as nice. Here's what it looked like after being copied in from another source:
So how did this plain Excel data get transformed? Via that "Generate Report" button that you see to the right. It runs a macro, and the macro performs all of the magic to improve the report. If you've tried macros, you have probably used the macro recorder, which is a great start. The macro recorder takes note of the steps that you are taking to transform the worksheet and saves them for later use. Next time you need to do something similar, you simple run the macro to repeat the series of steps.
So where does VBA play in? Behind every macro is VBA code. VBA stands for Visual Basic for Applications. This is the programming language that Excel macros use. It contains all of the instructions to be performed by a macro. Let's look at a simple example. On the raw data shown above, I'll create a macro to add the title row and format it. The steps will be:
Insert a row at the top
Merge and center cell A1 over columns A through H
Enter the heading text
Make the font bigger
In order to create a macro that does this, I will start the Macro Recorder, and then perform the steps listed above. When I'm done, I'll stop the macro recorder. Detailed instructions on using the Macro Recorder are not listed here, since this is meant to be a high level overview. I'll post a blog later with the details on how to do this.
In the meantime, here's what the raw data sheet looks like after I've recorded my macro:
Now you've got a macro that you can reuse to add that same heading on additional documents. There are ins-and-outs that you need to be aware of as far as storing the macro, and again, that will be covered in another post. For now, I'd like you to understand that the macro you just recorded has VBA code that you can see and edit. In this case, here's what the VBA looks like:
This is a very simple example of an Excel macro. As you can see, quite a few lines of code were generated. I'll dive into the details of the code in another post, but for the time being, I want to draw your attention to the line that says: Selection.FomulaR1C1 = "Sales Order Summary, March 1 through 4" (it's about half way down the page). Notice my heading text is here in the code. If I'd like to tweak the macro to change the dates, for example, I can change this line of code. Using this method, I can make changes to my macros without having to start over. I know the code can be overwhelming when you look at it as a whole, but it doesn't take much effort to learn to make simple changes to your macros once you learn to take it on a line-by-line basis.
As you can imagine, Excel VBA is a powerful tool for automating some of the Excel tasks that are taking up your time. If you're feeling up to the challenge, go ahead and create a macro with the Macro Recorder and take a look at the code. I'll be posting a series of articles here that take you step-by-step through the process of creating and editing Excel VBA. I hope you're feeling adventurous, and are ready to jump in. If you are, remember: the golden rule of programming is BACKUP! Make a copy of your file first, and then confidently get in there and start messing with the code, knowing that you can always restore if (WHEN!) you mess it up.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2016 North Port Solutions, LLC. All right reserved.