This session shows how code can be recorded with the macro recorder which does not rely on lots of fixed, hard-coded ranges nor formatting being recorded. It demonstrates how a knowledgeable user of Excel can take a first step in creating useful and effective automation, by building on effective end-user techniques.

Unfortunately, when using the macro recorder, as its title suggests, it records macros that repeat a user’s actions, recorded while they carry out a sample task. If the actions that are recorded do not anticipate the changes in data and the reports or models, the attempt to reduce the effort of using the workbook can be defeated by introducing a constant series of changes and updates that must be made in the code. Worse still, the recorded results may cause more problems than they solve.

We strongly believe that the calculations and results derived in a workbook should be solely dependent upon its inputs and formulas, and so we do not encourage the wanton use of macros to derive the results. However, there are many simple tasks which might enhance a workbook’s usefulness by making it easier to print reports, place data into presentations, filter data, update PivotTables or import data. Provided that a good approach is used in recording and designing these macros (which is demonstrated in this session), many effective enhancements can be made to existing workbooks by the judicious addition of some helpful macros.