In this week’s session, we explore the power of computer programming’s (and Excel VBA’s) most powerful technique – the loop.  Loops are useful when we are repeating something: a task, a process, running a model.  What specifically are we looking to repeat?  The creation of heatmaps, one for each team in the league.  It will take a few sessions, but loops are going to make this easy, with just a few lines of code.

Our first challenge is to try to create multiple heatmaps in a more ‘manual’ way.  What’s a good way to get started with a VBA job, and to give you some code to work with?  Yes, the trusty macro recorder!  First, therefore, we record the code for creating row headers for 3 tables.

This gives us something to work with, which is better than a blank canvas.  We can now set about improving the routine – so what needs to be improved?  Two things to avoid in VBA code are ‘hard-coded’, static cell references, and repeated structures.  We use offset to clear up the first problem, and a loop to clear up the second.  The loop being a new concept, we begin with a small-scale example in a ‘safe space’.

As with many powerful techniques in Excel / VBA, setting up a loop takes some time and care.  We first must declare a variable (just a place in the computer’s memory to store some information relevant to the programming,) to control the loop.  Then, we build the loop and tell Excel how many times to work through it using For Counter = 1 to 3.  This allows us to repeat the process of creating the data table headers.  Congratulations – you just harnessed computer programming’s most powerful concept!

Yes, lots remains to be done with this task.  However, having learned position control (.offset), loops, and about the VBA editor more generally, we are now equipped with the basic tools to get it done.  Can you try to do it yourself before next week’s video?  I will see you then!

Tools and Techniques:

-          Use the Macro Recorder

-          Tweaking recorded VBA code

-          Offset method

-          Variable declaration

-          Integer variable

-          For Next Loop

-          Message Box