With a blueprint for the macro created in the previous video, now we can approach the coding with confidence.  I find VBA coding much easier when working from pseudo code – it’s simple line-by-line translation.  And that’s a good thing, since low stress levels mean long and productive Excel development sessions!

In the video, I build the macro step-by-step, combining powerful VBA constructs such as loops and position control.  Using the pseudo code as a guide, the process is fairly smooth, and ‘stepping through’ the routine shows it’s doing what’s required.  There’s a sting in the tail, however!  Running the routine in full exposes some major inefficiencies – the macro takes several minutes to complete.  The video concludes with implementation of changes to improve the execution speed, and the efficiency of the file as a whole.  It reminds us of the need to keep our Excel files ‘lean and mean’!

So, the VBA-based solution is complete.  The purpose of this mini-series was to explore alternatives to long formulae in Excel.  The solution is not perfect but, for me, is preferable to the formula-based approach for a number of reasons.  But, what do you think?  Let me know in the comments below the video, I’ll get back to you there.

Topics And Techniques