Reflections on Developing with Excel...

This process may go on for a while. While each challenge has had its challenge – in terms of making it intuitive, efficient, and easy to use, scaffolding accuracy, creating something robust that won’t break, and designing something that can be maintained over the “long” term in excel … I’d say the Creative Curriculum was the most ambitious.

So looking back, what are some of my thoughts. I believe in rapid prototyping, but this didn’t turn out to be so rapid.

1. Don’t neglect the front end, top-down analysis and design process – so the design doesn’t, well, do too much or do things that are unnecessary, like programming for creating the filenames on the fly when it turns out that we knew what they were all along.
2. Recognize that Excel has limitations. Too many linked workbooks / formulas make it a really slow performer. So you have to recognize when an application is going to take that much computing power … and recommend another development platform.
3. Developing a complicated application in Excel does NOT make development go faster, it could actually slow things down. Like trying to dip out a lake with a spoon … it’s a trade-off, a balancing act.
4. Spend some time up front acquiring some really good test data and a test plan – and work it as you go. Keep it small and manageable but try to get as much mileage from it as you can … because you may be re-entering it every time the design changes. Nothing’s worse than making a change in the master and then having to go out and update 30 fully loaded clones. Set a schedule so you cut down on the inbetween times and get the model into the users hands for feedback, but limit the repetitive data entry they have to do too.
5. Versioning is a good thing. So are backups. SharePoint would probably be a good development environment.
6. This sounds odd – but try to give yourself enough room to expand ON ALL SIDES. Maybe start the design in the middle of the work sheet (leave space along sides, top, and bottom) and hide the excess columns/rows until you need them. Because when you have to insert rows and columns, this tends to break links in other worksheets / workbooks. Take advantage of working with names when you can – sometimes it’s not always practical to do that.
7. You can set protection in such a way as to initially allow yourself to go back and make certain changes. This is a good thing to do while you’re still developing.
8. Update the users regularly on progress – what’s been done, what’s left to do, what the challenges and questions are. Do it in such a way that involves them in the progress – because they will have input and help shape how the thing unfolds.

Okay, that’s all for now …