Turbocharge Your Spreadsheets with Excel Macros

In this post our VBA for Excel trainer Mark explains how using macros is like turbocharging your car. It also helps eliminate human error!

If Rolls Royce says it’s “Ok to turbo”, then it’s “Ok to turbo”…

…if the Boss says it’s “Ok to Macro”, then it’s “Ok to macro”!

turbocharged-excel-spreadsheets

There was a time (for those of us old enough to remember) when a new word hit the motorcar market: “Turbo”. Some of us know it’s slang for “turbine” referring to the part that forces air into the engine rather than merely allowing air to be sucked into the engine resulting in a serious increase in engine speed and power.

For the boy (and girl) racers, they thought they’d died and gone to heaven, for manufacturers pushing an engine to the limits of it’s endurance sometimes resulted in lots of smoke pouring from under the bonnet, combined with the dashboard lit up like a Christmas tree.

So the upside, more power, MUCH more power, the downside, more to go wrong, and when it did, VERY wrong!

Now, where IS he going with this I hear you ask? Macros.

Like Turbos to the “old skool”, Macros to the office Excel user is something we all know is there but some are a little vague about what it does and what the pros and cons are. So if you’re a hardened programmer (and you know who you are!) being reminded of the basics you learned long ago might irritate you but I respectfully submit it’s good to return to basics some times.

If you’re a code-virgin (you also will know if you are!), let me take you through the fundamentals. Don’t worry, it’s not illegal or immoral but might be fattening on account of how much time you might spend in front of the computer.

Rule 1: ANYTHING you can do in Excel (and the other office products for that matter) can be undertaken with a MACRO.

Rule2: ANYTHING done by a macro cannot be undone with the undo buttons.

Rule 3: ALWAYS work with a copy, never an original!

Got all that?!

In a nutshell, you can “teach” or program Excel to do a particular task that might be very repetitive or complex, and it will carry out everything you tell it to do at lightening speed.

For example, before the days of Excel 2007, one client turned up on a VBA course (Visual BASIC [Beginners All Symbolic Instruction Code]) and explained she worked for a credit card company. Occasionally, due to the odd bug in their system, some customers might get charged twice for an item. Now, I’m sure you and I would go into orbit without the proverbial apogee motor if this happened to us (I know I would!)

She presented me with a sheet of several hundred rows and around 35 columns and explained they’d been sorted into credit card number order (I should stress the REAL numbers had been removed and randomly generated unique numbers substituted). She would then look through for adjacent pairs and remove one of the duplicate pairs. This would take a horrendous amount of time.

I wrote a Macro to do this and when it ran, it carried out the whole process in just under 2 minutes, turbo style!

Previously, it would take a day for some one to do the same process by hand and inevitably, the sheer boredom would lead to mistakes.

To begin investigating Macros, use Alt+F11 when in Excel.

In the next instalment, we’ll deal with Macro security and the turning on the Developer Tab.

Tags

, , , , , , ,

Leave a Comment