More advanced Excel automation & simplification

In a previous post, I showed a way of automating and/or simplifying Excel by processing many data records through a single sheet using data tables, rather like a merge. If you liked that, you should love this more advanced tool, written in VBA.

It is generic enough to be used on a wide range of projects without modification, so I'll call it an automation "engine" below. If you find it useful, it is free to use and modify, providing you don't claim undue credit or sell it.

It was built to power an actuarial valuation system which can be extremely complex, because each client implementation is unique and has to be configured separately. The complexity and non-standard configurations make checking very difficult, so this has been a good test of the software.

Importantly, the engine allows every single formula to be laid out clearly and simply on worksheets, and checked step by step at every level, from the lowest level to the highest. So if you want, you can see all the calculations for product X, in a specific future year Y, laid out in detail across a whole sheet. It also means non-experts can configure the system entirely in worksheets, because the engine only provides automation and doesn't contain any business logic or client details.

The engine also allows us to nest as many calculation loops as we like, eg we can run 6 scenarios, each involving processing 1,000 products, each of which requires 15 future years of calculations, each of which requires several calculations - all by pressing one button.

All of this made our system simpler, much safer, more accessible and highly flexible, which is exactly what we needed. The only downside is that it isn't as fast as a pure VBA program, but it's still fast enough for us, and a small price to pay.

I'm going to pitch this explanation at the level of experienced business spreadsheet users who, like myself, work with nasty spreadsheets and who are motivated to automate or simplify them. The section below introduces the engine, then I have a workbook which explores its features step by step.

The engine is like a souped up data table. It puts a sequence of numbers in a cell (ie 1, 2, 3,..), calculates, and stores results chosen by the user. However, it can nest calculation loops, so that for example, each time sheet A calculates, sheet B will calculate Y times, and each time sheet B calculates, sheet C calculates Z times. This means that sheet C will calculate Y x Z times as often as sheet A. If you are familiar with programming, we are talking about nested For...Next loops and subroutines, within worksheets, which is something I've always wanted in Excel.

Each calculation loop requires its own worksheet, which has special range names that tell the engine what to do on that sheet. For example, there are range names that tell the engine

• how many times to loop through this sheet (can be a formula)
• the name of the child sheet, if any, which should be run every time this sheet runs (that child sheet will then have its own fields which tell it how many times to run, etc) - this is how the loops are nested
• what results to store

and more. It can also keep running totals of results, and copy hardcoded results to separate sheets.

The code engine comes with a worksheet template with about 10 named cells which are all you need to configure your system. You should not have to touch or even look at the code.

It's hard to explain it in much more detail without looking at it, so I suggest you do that now. I've provided 3 files

• explanation of the features with several examples using a simple financial model (Engine Demo.xls)
• a non-financial example showing how to solve a word puzzle game (WordPuzzle.zip)
• the template for your own use (Template.xls)

The documentation is not as complete as I'd like, but please bear in mind this is all done in my spare time. Comments and suggestions are welcome.