In the previous session, we just began to feel the power of loops in Excel VBA.  Though the mechanism we created is powerful, it’s not yet doing anything useful for us.  So, in this session, let’s harness the loop to generate some heatmap-type analysis.

To do so, we’ll have to replicate the basic heatmap layout in each of the heatmaps we generate.  This is fairly easily done by creating a named range for a layout ‘storage’ area at the top of the sheet.  Perhaps we’ll move this to another sheet later – using a named a range allows us to do this easily without having to change the code in the VBA editor.

With the basic layout replicated each time we go through the loop, we look at actually creating the calculations!  This is the point, of course.  We do this by integrating the instruction to create a data table.  Carefully ‘stepping through’ the VBA code allows us to understand it is working, and checking the calculations against the DSUM formula allows us to build confidence in the outputs.  Remember, testing at every step is key, and it’s best to check each result at least two different ways.

This gives us the same data table duplicated many times, which still isn’t very useful!  Let’s look at creating analyses for different teams at the click of a button, one data table for each team.  This sounds cool, but requires the integration of a simple but powerful mechanism in the VBA code, based around the loop variable and our old favourite, the offset method.

All that remains to do is to ensure each data table is converted to values when it is created.  Chris manages to do this at the end of the session.  Now, can you improve / scale up this routine to get exactly the analysis you need?

Tools And Techniques

For Next Loop

Integer Variable

Offset Method

Recycling Code

Organic Code

PasteSpecial Values In VBA