et all the power available from this enormously flexible application because they do not venture beyond basic spreadsheets or simple macros.A recent discussion with a banker at ANZ mortgage services revealed that bank data is being converted from active data (spreadsheets with in-built commands) to raw data to be actioned by macros. The reasons for the change are several. When the active sheets have data changed, this has implications for the rest of the spreadsheet. All the linked cells will need to recalculate their cell values according to their formula. This takes time to execute. It may seem like a speed of light adjustment on a state of the art computer with a simple spreadsheet, however some of the Bank's files were as large as fifty megabytes. Another reason; the change in the file size when storing only data and not active spreadsheets. Commands such as "vlookup" take up large amounts of memory. When the files are converted to data only the files reduced in size from fifty megabytes to only five. The final reason is that the data only files improve the integrity of the data. If one cell reference is interfered with or some code is inadvertently changes it can throw out the entire spreadsheet. If a macro is incorrect is can be corrected and applied to the data.Macros are written in Visual Basic for Applications (VBA code). This is a powerful language that can be difficult to learn. For the beginner, Excel has a record Macro Function. This function records the commands that an individual makes as they are working on a spreadsheet. The sequence of commands can then be stored and replayed with new data.Below are five years results for Evans Timber Pty Ltd. My objective is to record a macro that provides the average growth figures over the five-year period. These figures will be represented as percentages in column “J”..The record macro function is selected, Tools, Macro, Record new Macro. Every keystr...
