Excel is a powerful spreadsheet program that makes manipulating, organizing and storing data simple. An Excel macro is an instruction set that lets us work with Excel faster and more productively. Macros are diverse; they can be formulas, recorded user actions or even a complex block of VBA code. In the following section, we'll look at the top five mistakes to avoid when writing them.
1. Redundancy
Excel macros are fast, focused chunks of logic. They can also be slow and boated. A common reason for this is redundancy. In other words, our macro is performing unnecessary steps or it's accomplishing the same task multiple times using different lines of code. Just as we must identify our common Excel tasks and reduce them to Excel macros, we should find the common elements within our macros in order to make those blocks of logic more manageable. A lean macro is a fast and productive macro.
2. Recording
The macro-recording tool built into Excel can be extremely useful. The trick is to use it as a starting point or as a clever way to learn how to write some particular piece of logic. You shouldn't use it to create complete Excel macros that you'll use regularly. That's because the Excel macro recorder is dumb. It can only mimic actions precisely. It can't detect that a series of actions is actually the same action performed multiple times. That's up to you. So once you've finished recording, it's time to dig into that code.
3. Brevity
In addition to being redundant, new macro writers often write long code. Long code makes redundancy more likely, and it makes code more difficult to enhance and maintain. So avoid writing Excel macros that are longer than 10-15 lines. If you've written a new macro that's longer, then it's time to reduce it into two or possibly more smaller procedures.
4. Comments
Avoid uncommented macros and code. Sure, everything makes sense now as you're working your way through the logic and writing the code. But when you come back to read it a week from now or even longer, you'll likely find all the clarity gone. Those comments that you write while it all made sense will help you make sense of it each time you come back to work on it.
5. Hard-coded References
Pointing directly at a cell address is easy, which is why newbies do it. But it's also a recipe for disaster, or at least a lot more work. Excel documents aren't static; they're bound to change. And when they do, those hard-coded references are no longer valid. The user must then go into the code and fix them. Instead, if you write dynamic references from the outset, the macros change with the document auto-magically.
When he's not tinkering with his car, Miles Walker looks where to compare car insurance over at CarinsuranceComparison.Org. His latest article looked at car insurance comparison quotes.
Author: Guest
As we welcome Guest bloggers, all our deepest felicitations are to invite you to contribute your part here. And yes this current post is also a precious contribution of our Guest Blogger.
