In the previous session, we used Excel VBA to automate the creation of a data table.  Though this saves time, the result is sub-optimal for a few reasons.  First, the code creates a data table linked to the model output cells, which means that the data table updates with new model inputs.  This could confuse, so how could we avoid it?  Also, we know that clearing data tables can be problematic, so could we automate the process of clearing an existing table too?  Finally, could we get VBA to add the cool ‘heat map’ style formatting that is the icing on the cake?

In this video, we take on the above challenges with our usual steady and systematic approach.  Chris shows how to use the macro recorder to record code, tweak and move it around in the VBA editor, and finally to get it working for us.  This process is fundamental in practical VBA coding.  So, make sure you practice all three applications in this video!  You will use it many times in the future.  The result is a clean and well-presented output that avoids the file inefficiencies that data tables can create.

To think about before the next session: how might we create multiple heat maps?  Could we work towards, for example, creating a heat map for each team on a single sheet, all at the click of a button?  What coding concepts would be required to do this; or, what coding techniques are you currently lacking?  See you in the next session!

Tools And Techniques
- Data Table
- Macro Recorder
- Tweak Code In VBA Editor
- Move Code Around VBA Editor
- Paste Special Values
- Conditional Formatting