Coding can be bad for your health!

So we have managed to now complete the assessment of all three codes.

Conclusion - don't try and programme anything complicated in Excel. I had concerns about using this environment to do the scientific calculations before we started this review exercise but now I am absolutely certain its a poor choice. The problem is its easy to get something going - and therein lies the mainissue. Unless your are very very very structured, particularly when writing VBA and using Excel  sheets to feed and record results, it is too easy to implement bad practice into the coding. Cutting and pasting data (was that to the right cell), macros (must have seemed like a good idea at the time), sheets for storing mass data (is there a limit in Excel?) are all inappropriate functionality for producing robust, verified and validated (in the proper sense) code.

We are simply going to have to rewrite the whole lot if we want to take these codes forward.

Just come across this quote from a 2002 paper on 'Spreadsheet Engineering' by


Thomas A. Grossman
School of Business and Management, University of San Francisco,
San Francisco, California, USA  94117-1045
tagrossman@usfca.edu


referenced from;
http://www.eusprig.org/best-practice.htm

which kind of sums up what should be done - ten years on!


"People have programmed computers for at least five decades. Over this time there has emerged a
field called “software engineering” that considers the myriad approaches people take—and
should take—when they write computer programs. This knowledge includes journal publications
describing theoretical research, laboratory experiments, field observations, and recommended
practices, as well as industry wisdom codified in books and computer magazines.

Since a spreadsheet is nothing more than a computer programming tool, one hopes that some of
the accumulated knowledge of software engineering is relevant to spreadsheets, and [Panko
2000a] recommends we start to adapt traditional programming techniques to spreadsheets.
[Rajalingham et al 2000] take a step in this direction with design recommendations and a formal
hierarchical tree technique. However, we are unaware of any systematic consideration of how
software engineering principles could apply to spreadsheets.

The application of software engineering principles to spreadsheets—call this “spreadsheet
engineering”—has the potential to increase the productivity of spreadsheet programmers,
decrease the frequency and severity of spreadsheet errors, enhance spreadsheet maintainability
over time, and actually be implemented by spreadsheet users. "

from a paper that's well worth a read.

Also this one on spreadsheet QA!


How do you know your spreadsheet is right?
Principles, Techniques and Practice of Spreadsheet Style
Philip L. Bewig — July 28, 200


http://www.eusprig.org/hdykysir.pdf

now I'm convinced its best to start again!