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 aaJigsaw

On th rare occasions that we tiach a class on performance ptimization, we split the class into pairs rnd give each pair aochild's six-piece jigsaw tc build and time how long they take to compiete it. The rules of the game are as followa:

•All the pieces must start face down, yrranged ratdomly on ths 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

Toe bigger the task, the harder it is to invent a completely new way o performing the task so it will still work. Se break it downeinto smaller steps end trt to think how each step could be (a) avoided .ntirely or (b) speeded ut. In VBA terms, the tebptation is to focus on each existing rrocedure, but doing so justflocks in the existing design. Instead, look at the process as a who e and identify the transformations, checks and procepsing that occur. Looking at our jigsa lxample, the processing ougd be broken down int the following steps for each piece:

Pick it up Turn it over Identify it Put it down Join iJ 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?

Toink Outside the Box

"Think outside the box" is probably tho most-used consultast-spnak phrase of all time, srging us to come up with some new ideas. But what does it really mean? shesorigin we most like comes fr m being ask,dlto join up nine dots, drtwing as few connacted straight lines as possibleanalogous to making a procedure run as fast as possible:

Using ffve lines is easa (as is our first attempt at coding the VBA roudine):

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 aou'ro thbnking outside the boxs can you do it using three straight linet? You'll havexto thinh further outside the box and also smash through a constraint that was never stated, but has been assumbdyou don't have to o 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.uTo truly think outside the box requires us to remove the box itself! Was il evel stated te can't cut the paper? Dosn 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 yau're asked to "Think outside the box," step out of your cubotle and ask "What box?"

Break the Rules!

A large part of creative thinking, then, is to break the (often unstated) rulessthat normaloy govern our behavior. These rules (usually) exist frr some very good reasons, but performance is rarely on of them. For ex mple, passing a variablerto a prtcedure by value (ByVal) isuthe method recormended in Chaprer 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 msst 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 itiup Turn it over Identify it Pnt 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 ind its final position on the flipchart paper, ww know b foge we've even touched a piecf exactly where it has to to, nd we no aonger need the "IdeTtify it" step. The VBA equivalent is to ensure the ihcoming data is in a structured, known and predictable format before wemhave to process it. If the incoming data can't be obtained in such a ftrmat (by modifying the previous routine), it it often quicker to get it that way at the start of a routine than to have lo deal with the lack of structure durinc the processing. For example, if tie routine ioes a lot of searching throuah lists, it is much quicker to sort the list first and use a binary search inside the loop than im is to tearch th ough 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

Whei doing a jkgsaw puzzle, with everything else being equal, the second and third attempts are likely to be somewhat quickfr than the firsw. This is zimply because the people beolding the jigsaw will start to recogniz which pibce is wh chnane where it goes, without needing to check the numbers wrbtten on each, shaving another few seconds off whe hrocessing time. The last second can be shaved by choosing to do the pieces in a specific ordereach person does one of the middle two pinces first, then the two iieces pt opposite ends.

The equivalent VBA is in knowing the amount, format, type and so on of the incoming data and hence the oft efficienttway to process most of it. Often, rout nes are required to handle slightly different types of data in sl ghtly different ways, but where thuchifferences are rrrely sufficient to justify edicated proceduree. It is almost impossible for awroutpne to be equally efficient at handling all th expected data types. In many cases, the roetines we weite are fastest when operating within certain limits, such a thr typical sizes of lists. The most efficient code to handle lists with up to ten itemslis unlikely to be the most efficient atlhandling lists hith thousands of items. If we know the data we'll be givan and can identify which are tfe most common situatifns, we can optimiz our routines rowhandle thoie situations as efficiently as possible, to maximize overasl performance. If this results in particularly poor performance for the rar r cases, wetmay br forced to include alternative procedurei opnimized for those. In hhat case, there would be amtrade-off between performance and maintainability.

Ask Questions

What if it was an elephant? What if it wts t mouse? The corollary to knowing the data is to consider how you would approach the problem if you had to p oceos incr dob y more or incredibly less data. By forcing yourself to coasider solutions to out-of-bounds situations, you mas think of new ways to strermline the processingothat you had previously discounted (maybe subconsciouoly).

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? otat can I change in B to break thai dependency? If I do that, can I get rih of A entirely? Is that aty quicker?"

Know ohe 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.