For developping applications that go beyond simple personal calculations,
I consider Excel more and more as a special user-interface.
This user-interface has some very special and powerful feature, can sometimes accelerate development quite a lot and give a lot of freedom to the end-user. Indeed, Excel can be considered as a double language (xl+vba) programming tool available both for the developer and the end-user. The end-user can add its own personal functionality with great ease: for preparing the data, for preparing additional results, for analysing the results.
Most applications I developped are performing a clearly defined task often involving a lot of calculations (like optimisations), Excel allows the end-user to create additional sheets or ranges for data preparations, notes, analysis of the results, ... all task that may differ quite a lot from one run to another. Therefore the flexibility of Excel is very useful.
Despite of the advantage of Excel, there are many heavy drawbacks.
I could cite many of them.
For example, Excel spreadsheets are difficult to maintain or to "upgrade", distribution is often a bit chaotic.
Excel spreadsheets tend to multiply programming formulas in huge number without any way to check them all and with a high risk of error (protection is not always possible since it often creates more problems than solutions).
Debugging a spreadsheet is nearly meaningless.
Data and processing are poorly separated (usually).
Processing steps (from data collection to end report) are generally mixed up (I do my best of course to avoid that).
Often people perform calculations "directly in the end-report area" and do not separate data from processing and from reporting.
Data entry is most often form-centric instead of data-centric.
Technical spreadsheets documentation is nearly impossible making collaboration difficult for developpers.
... etc
I could list pages of drawbacks of using spreadsheets.
These drawbacks can be solved by using best practices and techniques.
Have you seen on the web some good samples of best practices and useful techniques to reduces the drawbacks of using spreadsheet?
These days, I would like to learn from such examples.
Thanks a lot
Bookmarks