At the start of this session, we take some time away from the heat map sheet to get acquainted with two powerful VBA techniques - offset and range(range,range).  These two drive so many Excel implementations and are a must for any serious VBA analyst.

Why are they so important?  Coding in Excel is really about one thing: control of position.  Excel gives us an infrastructure (or a canvas?) to work with, comprising files, sheets and cells.  How well we navigate this infrastructure is the main determinant of our impact as VBA programmers.

We start with Offset.  As with many Excel tools, it is not well-named!  Offset can be understood as 'to move away from'.  We choose an anchor point and then 'move away' from there by a number of rows, then a number of columns.  It sounds pretty ordinary but, crucially, it replaces static worksheet references such as Range("A1:G4").  This makes all the magic happen later.

Range(range, range) allows selection of multiple cells.  Using the technique, we determine a start cell and an end cell, then Excel selects all the cells in-between.  We start with some 'simple' examples - make sure you build on these and do your own experimentation.  Finally, we bring the two techniques together to achieve powerful position control.

How this supports the heat map analysis is not yet absolutely clear - watch this space, it will be worth it!  In the mean time, make sure you develop confidence with these two powerful VBA techniques.

Topics And Techniques

- Offset
- Range(Range,Range)