Using Excel to create maintainable applications

It's occasionally easier to write an application using an Excel spreadsheet than using a conventional language. A spreadsheet is easy to send in an email and you don’t have to worry about installing things.

However, it's very easy to let normal good practices go out the window, and develop something that's impossible to maintain. Often a few macros are added to a spreadsheet that eventually becomes a full-blown application. Here's a few ways I've found to try and make something that is maintainable.

• Treat each worksheet as an "object". As with any other object oriented design, there should be minimal coupling between objects. If there’s too much coupling, things get difficult to maintain very quickly.


• It's easy to use copy and paste for code reuse. If worksheets have common functionality, this can be extracted into separate modules.


• Try and create a 2 or 3 tier structure, with worksheets that contain data, worksheets that perform calculations on the data and worksheets that display the data to the user. Have some worksheets that contain "data", and others that perform calculations and apply presentation to the data. Eventually, you may decide to use a conventional language and migrate to this, and this will make things a lot easier.


• Develop unit tests to call functions, and a separate worksheet to run these. It's possible to develop these first.

Patrick R. O'Beirne has some other thoughts on this at: http://www.exceluser.com/tools/agile1.htm

Do any readers have any other good practices for using Excel to create maintainable applications?