Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Creative Thinking

The key to improving your application's performance is to remove the bottlenecks by trying to find a different (faster) way to do the same task. Either by rethinking the entire approachso the task that took so long is no longer neededor by thinking how to do the task quicker. The trick is to tap in to the creative side of your brain, so instead of "analyzing" the problem and "identifying" a solution, use your imagination to conjure up sentences that start with "I wonder what would happen if I …." Although this may seem like an alien concept to those of us who are normally analytical instead of artistic, there are a few exercises that can help.

Do a Jigsaw

On the rare occasions that we teach a class on performance optimization, we split the class into pairs and give each pair a child's six-piece jigsaw to build and time how long they take to complete it. The rules of the game are as follows:

All the pieces must start face down, arranged randomly on the table.

The jigsaw must be completed and finish face up.

You are not allowed to be touching any piece when the timer starts.

The first attempt usually takes about 30 seconds. Applying the rule that an optimized routine should be an order of magnitude faster than the first attempt gives us a target of three seconds to do the six-piece jigsaw.

Identify the Steps

The bigger the task, the harder it is to invent a completely new way of performing the task so it will still work. So break it down into smaller steps and try to think how each step could be (a) avoided entirely or (b) speeded up. In VBA terms, the temptation is to focus on each existing procedure, but doing so just locks in the existing design. Instead, look at the process as a whole and identify the transformations, checks and processing that occur. Looking at our jigsaw example, the processing could be broken down into the following steps for each piece:

Pick it up Turn it over Identify it Put it down Join it up

With six pieces and five things to do for each piece, 30 seconds is a reasonable time, but can any of those steps be removed?

Think Outside the Box

"Think outside the box" is probably the most-used consultant-speak phrase of all time, urging us to come up with some new ideas. But what does it really mean? The origin we most like comes from being asked to join up nine dots, drawing as few connected straight lines as possibleanalogous to making a procedure run as fast as possible:

Using five lines is easy (as is our first attempt at coding the VBA routine):

But can you connect all the dots using four straight lines? Visually, the nine dots appear to our brains as a box, which is a visual metaphor for the many rules, regulations and norms that we work (and code) within, usually as a result of our upbringing and education. Connecting the dots using four lines requires us to break through the boundaries of the box and start to consider the area outside itliterally thinking outside the box (but only a little bit!):

Now you're thinking outside the box, can you do it using three straight lines? You'll have to think further outside the box and also smash through a constraint that was never stated, but has been assumedyou don't have to go through the centre of each dot:

So what other unstated assumptions can we break in order to join the dots using even fewer lines? Just one line, perhaps? If we take the three-line solution to its extreme, we could have one line that spirals around the globe. (It doesn't have to be physically practical.) Or we could break another assumption and use a thicker pena paintbrush could cover all the dots in one line!

But our thinking is still boxed inliterally. To truly think outside the box requires us to remove the box itself! Was it ever stated we can't cut the paper? Doing so makes a one-line solution trivial:

The final optimization is to realize we can complete the task of joining the dots without drawing any lines at all! If we cut the paper carefully, we can have all the dots touching to begin with. Even better is to break a last assumption and think in terms of a 3D, not 2D, space and stack the dots on top of each other. We've optimized the task of joining the dots to doing nothing!

The next time you're asked to "Think outside the box," step out of your cubicle and ask "What box?"

Break the Rules!

A large part of creative thinking, then, is to break the (often unstated) rules that normally govern our behavior. These rules (usually) exist for some very good reasons, but performance is rarely one of them. For example, passing a variable to a procedure by value (ByVal) is the method recommended in Chapter 3 Excel and VBA Development Best Practices. This is mainly due to defensive programmingknowing the routine being called can't change the value of the variable passed inbut is often (slightly) slower than passing the variable by reference (ByRef), particularly when passing large strings or Variant arrays. The one thing we must do when breaking rules it to fully document the rules we're breaking and why we're breaking them (by commenting the code). It's not uncommon for a maintenance developer to think "We're not allowed to do that" and unwittingly undo your optimizations.

Returning to our jigsaw puzzle, what rules can we break in order to speed up the task? The five steps to completing a piece of the puzzle are as follows:

Pick it up Turn it over Identify it Put it down Join it up

One thing we can do is draw on the table! By tracing around the completed jigsaw pieces (on some flipchart paper), we can tell exactly where each piece goes, enabling us to put it down in its final position. We've combined the "Put it down" and "Join it up" tasks and reduced the time accordingly. In VBA terms, a routine that processes a set of data often has a final step which organizes the results for the next process (such as removing duplicates/blanks, sorting and so on). Try to combine that organization into the processing of the data, so the natural output of the data processing can be passed directly to the next routine.

We can also deface the jigsaw pieces! By numbering the back of each piece and its final position on the flipchart paper, we know before we've even touched a piece exactly where it has to go, and we no longer need the "Identify it" step. The VBA equivalent is to ensure the incoming data is in a structured, known and predictable format before we have to process it. If the incoming data can't be obtained in such a format (by modifying the previous routine), it is often quicker to get it that way at the start of a routine than to have to deal with the lack of structure during the processing. For example, if the routine does a lot of searching through lists, it is much quicker to sort the list first and use a binary search inside the loop than it is to search through an unsorted list each time.

By ensuring we have structured our incoming data (by numbering the jigsaw pieces) and combined the final reorganization into the main processing (by drawing and numbering an outline of each piece of the completed jigsaw), we no longer need to turn the piece over to identify it and so have reduced the processing required for each piece to a simple "Move it." One task for each of our six pieces gives us a processing time of roughly six seconds, which is well on the way to our target of three.

So far we've concentrated on optimizing a routine by changing the way in which things are done, to reduce the amount of processing required. By doing these macro-scale optimizations we've achieved 80 percent of our target 90 percent saving, which is typical for many situations. The last 10 percent comes from making the remaining minimum processing as fast as it can be.

Know the Data

When doing a jigsaw puzzle, with everything else being equal, the second and third attempts are likely to be somewhat quicker than the first. This is simply because the people building the jigsaw will start to recognize which piece is which and where it goes, without needing to check the numbers written on each, shaving another few seconds off the processing time. The last second can be shaved by choosing to do the pieces in a specific ordereach person does one of the middle two pieces first, then the two pieces at opposite ends.

The equivalent VBA is in knowing the amount, format, type and so on of the incoming data and hence the most efficient way to process most of it. Often, routines are required to handle slightly different types of data in slightly different ways, but where the differences are rarely sufficient to justify dedicated procedures. It is almost impossible for a routine to be equally efficient at handling all the expected data types. In many cases, the routines we write are fastest when operating within certain limits, such as the typical sizes of lists. The most efficient code to handle lists with up to ten items is unlikely to be the most efficient at handling lists with thousands of items. If we know the data we'll be given and can identify which are the most common situations, we can optimize our routines to handle those situations as efficiently as possible, to maximize overall performance. If this results in particularly poor performance for the rarer cases, we may be forced to include alternative procedures optimized for those. In that case, there would be a trade-off between performance and maintainability.

Ask Questions

What if it was an elephant? What if it was a mouse? The corollary to knowing the data is to consider how you would approach the problem if you had to process incredibly more or incredibly less data. By forcing yourself to consider solutions to out-of-bounds situations, you may think of new ways to streamline the processing that you had previously discounted (maybe subconsciously).

What if I stood on my head? Instead of looking at the code from top to bottom and accepting that B follows A, look at it from bottom to top and keep asking "Does B have to follow A? What can I change in B to break that dependency? If I do that, can I get rid of A entirely? Is that any quicker?"

Know the Tool

After we've reduced the processing steps to a minimum and organized the rest to be most efficient when handling the most common situations, the last few percentage points can be saved by ensuring we write the most efficient code. Both in pure VBA and when automating Excel, there are usually a number of alternative ways of doing the same thing, some faster than others. These micro-level optimizations require a very good understanding of the tool being used (that is, VBA and/or Excel), are often counterintuitive and are often different for different data types (Longs vs. Strings vs. Variants). Many of these alternatives are explained in the Micro-Optimization section later in this chapter.