Remember Your Math

Think back to your school days. Remember algebra? Trying hard to forget? One of the concepts taught in my algebra classes was a concept called factoring. Factoring is the process of taking a long equation and rearranging it so that it can't be rearranged in a more concise way. For example, the polynomial 2xy + 2xy can be factored to 4xy.

You should approach your VBA modules with the same mindset. How can my procedures be rearranged so that there is no duplication of code? The best way to achieve this is to create small, focused procedures that each perform only one dedicated task.

There are many benefits to factoring your code. In my opinion, the three most important benefits of creating factored code are increased reuse opportunity, reduced complexity, and improved tuning ability.

There are two different levels of reuse: the first level is reuse within the same module or project and the second level is reuse within other projects. An exciting thing happens with factored code— you experience a wonderful snowball effect. You'll find that as soon as you get a critical mass of small, focused procedures, lights will go on that wouldn't have been activated if your code consisted of long, complicated routines. You'll see new ways to combine these small procedures to provide new levels of functionality, without incurring a significant development burden.

The second level of reuse is significant because you can dramatically reduce the development time of future projects by collecting all of those useful, common routines and using them together as a springboard to jumpstart your next project. This book has presented a decent number of common Excel routines that can seed your collection. For example, the WorksheetExists function that I've mentioned numerous times in this book is something that I need to use in nearly every project.

Another benefit of factored routines is that they reduce complexity and are therefore much easier to maintain. The benefit of this is that it's easier for you or someone else to figure out how a procedure works six months or a year later when updates or modifications need to be made. I've seen my share of programs using huge everything-but-the-kitchen-sink procedures that were basically scrapped rather than updated because it was easier to rewrite than it was to figure out what was going on. This is an avoidable shame.

Finally, by factoring your code, you have an increased ability to improve performance. For example, you may have an application that calls a routine to find a particular item in a range. In an unfac-tored application, different procedures may implement their own search algorithm to find an item in a range. It may not be an easy matter to modify each procedure to use a more efficient search algorithm. A factored application has one search method that's used by any procedure that needs to search a range. By modifying the search algorithm in the search method, all of the procedures using the method will benefit from the increased performance.

0 0

Post a comment