Excel VBA Macros : Introduction
If you spend a lot of time working with Office applications such as Excel, Word, PowerPoint, etc. there is a good chance that you do repetitive and monotonous tasks, probably without thinking about it or questioning it.
Over the next few posts I will talk about Excel VBA Macros and the first examples will look at automating repetitive tasks.
I will use the example below to demonstrate my points.
Let’s assume this is a download of sales for a large electronics store, listing the items sold, the prices and also the quantities sold.
The report is run every day, and everyday I have to work on it so that it ends up in the format my manager wants.
The steps involved in formatting are:
- Change the header in column A to say “Item”
- Change the header in column B to say “Price”
- Change the header in column C to say “Quantity”
- Add in a fourth column with the header “Total”
- For each item, in column D, multiply the quantity by the price
- Change the number format of column B to give 2 decimal places and use commas for thousand separator
- Change the number format of column D to give 2 decimal places and use commas for thousand separator
- Make the headers in row 1 bold
- Center the headers in row 1
- Widen the columns
- Add a border around the table
- At the bottom of column D, add in a total of all the totals
- Make this total bold
- Put a border around this total
- Change the fill color for the total to yellow
- Add a border around the headers in row 1
There you have it, our report is ready for our manager.
These 16 steps are quick to complete, but when you do them enough times they become 16 quick but boring and monotonous steps!
Would you rather repeat these 16 steps over and over again until you start wondering why a trained chimp isn’t doing this work? Or would you rather click a button and have a macro run this for you?
Excel VBA Macros
Taking the above report that I started with, I created a macro and when I run the macro (with a grand total of 2 keystrokes) I end up with this formatted report
Do you notice any difference?
You shouldn’t!
The report ends up exactly the same, the only difference is that I wasn’t bored beyond belief when I was formatting the report.
This report could be a thousand columns wide and fifty thousand rows long, it will still run and do the same thing.
On an average day I would use maybe 5 to 7 macros to automate repetitive tasks like this in Excel alone, and it saves me a lot of time (and mind-numbing-boredom)!!
This is just one example of the type of thing I use macros for. It is the most basic and easy to explain, so I’ll start with this type of macro.
Getting Ready For Excel VBA Macros
Visual Basic Editor
The first thing we will need to set up is the Visual Basic Editor.
In Excel, press Alt and F11 together to open the Visual Basic Editor (VBE).
There are 2 windows that we will be using.
- Project Window – The top left window in this image lists all parts of open projects (workbooks). Our macros will mostly be in “module” folders. If there is no module on a file we will insert one.
- Visual Basic Editor – This is where we see, edit and create our code. It is the largest window on the above screen.
The Developer Tab
The next thing we have to get ready is our Developer Tab. This makes up part of our menus (ribbons) running across the top of Excel. It will give us quick access to some of the VBA tools we may need (buttons, forms, etc).
Depending on the version of Excel you are using the steps involved may be different. Below are the steps for some versions of Excel.
Excel 2003
- Click the “View” menu
- From the “Toolbars” pop-out menu, select “Visual Basic”
- Go back into the “View” menu and from the “Toolbars” pop-out menu, select “Forms”
Excel 2007
- Click the “Office” button (top-left)
- Click the “Excel Option” button at the bottom
- Click the “Popular” button at the left
- Under Top Option for Working with Excel, check the “Show Developer tab in the ribbon”
- Click “OK” button to finish
Excel 2010 + 2013
- Click the “File” tab
- Click “Options” at the left
- Click the “Customize Ribbon” at the left
- Towards the right, select the “Main Tabs” from Customize The Ribbon drop down box
- Check the “Developer” item
- Click “OK”
You’re now set up and ready to go!!!
Nervous Yet?
As you get ready to create your first macro, are you worried? nervous? anxious?…… Don’t be!
We will be recording our first macro, Excel will create the VBA code for us and we will then review it.
You may find this surprisingly straight forward!
If you find that code isn’t working for you, don’t worry or blame the computer – it is only doing what you are telling it to do!
Read this post before proceeding.
The next post will get you started with recording a macro and reading the code.
