Tips for Taking on VBA & Excel Macros, Part 2

Last week, the feature was for the advanced Excel users who are not programmers and new to VBA.  This post continues that theme. 

The Immediate Window Is Your Friend

The Immediate Window is where you can test code against current data and see the results. Use this to make sure your macro really does what you think it should! The below example shows the results of the very simple “?Activecell.Value” statement.

Don’t Reinvent the Wheel

There is nothing worse than spending hours creating a method for reversing text only to realize that there are a hundred VBA code examples of just that on message boards via free add-ins and for download. Even if a solution you find isn’t exactly what you need, it may be easier to start with one that is close and tweak it rather than start from scratch. Examples of code to work from may be found:

• Online through searches, help pages, and message boards
• Print Reference Guides
• Training Courses, Seminars, and Webinars

Save Frequently, Build in Blocks

Savvy programmers don’t just save frequently; they save changes in discrete versions along the way so that can return to a former working version if things go awry.

Writing and saving VBA Macros in incremental steps can also:

• Make the task easier by breaking it into smaller, more manageable parts
• Define common groups of steps that can be reusable in multiple Macros
• Save time debugging by making it easier to test
• Save time when updating macros to new versions of Excel

With a little deliberation and patience, you can become a confident VBA programmer, unafraid of not just the deep end, but the vast open ocean that is all Excel has to offer.

Until it happens the first time, inexperienced programmers don’t realize how fragile their code is – how easy it is to make the one last little change that ruins everything.