So, you want to learn macros in Excel…
You have heard that creating macros is an exciting feature in Excel and you are up to the challenge of learning something great. Or perhaps you are using macros developed by somebody else and you would like to understand how it works or how to improve it. These are common reasons why some would like to dive into the sea of possibilities opened up by learning how to create Excel macros.
So what are macros?
The word macro is a short name for macro command and macro means large. So a macro is a series of instructions that Excel follows to accomplish a large, more or less complex task. Here the term complex refer to all the steps repeated one at a time by a user to achieve an outcome.
Give me an example!
Suppose that every month you are provided with a workbook containing data from a number of branches located in the same worksheet. You are required to sort the data so the branches are grouped together. You then have to manually copy the data for each branch into a new worksheet using the name of the branch as the worksheet name.
Only some columns from your list need to be copied into their corresponding worksheet. Finally, formulas are required for each row as well for a summary at the bottom of the worksheet for each branch.
This is a really tedious job which takes you an average of 40 minutes to complete. However for some months, the amount of data in the original list means that it will take even longer.
To see an example of an interesting project using the Macro Recorder and some custom VBA code, download the project Append2Database using the image on the right.
So, what’s the deal?
Some clever cookie tells you that with a macro you could accomplish this task in, not 30, not 25, not 10 seconds but in 4 seconds!
On top of that, regardless of the size (height) of the original list of data or the number of rows for each branch, the same macro will be used to do the job! Of course if there are thousands of rows for each branch it may take a tad longer … 14 seconds, maybe 16!
I’m in!
A couple months (part time) of effort and you’ll be able to do just that. Good news is that the project described above is an easy one. The example described above took ComboProjects less than 3 hours to come up with a solid prototype for a customer. The material available on this site will allow you to create many useful utilities to get you up and running.
Learning with PFD projects
The best way to learn VBA for those new to coding is to start with many small projects. By small project I mean an exercise that can take between 1.5 to 3 hours depending on your skills. It is not just about typing VBA code but about completing a project from A to Z. Understanding how to accomplish a task and why it needs to be done that way.
Finally you would want to redo a project yourself with a minimum use of the documentation. Then you will be able to create your own VBA routine.
