Context

A computer science teacher once told us “a given computation can be achieved with any programming language, even spreadsheet formula”.

At first, as wise at it could have been, including Excel in the comparison sounded definitively stupid …

Thereafter, while studying Turing machine, it then sounded correct, yet not very fulfilling.

Several years of experience with Excel, we will mostly remind Excel formula only are definitively limited with the lack of input/outputs.

But, the set of problems that can be simply solved with formula only remain impressive.

Anyway, this work is not just some kind of performance … There is was a good reason for me to do it.

Spreadsheet are a powerful tool that everyone has to learn to use for almost every business jobs.

Yet, when most people come to solve most complexes problems, they want to use VBA language, without even knowing why.

And once they started learning it, they try to use it for any kind of problem, even simple search or rendering.

Today, as an excel teacher, I’m trying to explain to these people why writing VBA macro for any problem while not being educated on computer programming is not only a real waste of time, but also a serious risk for their spreadsheet quality.

In a business environment, using formula rather than macro is :

Faster to write for anyone but professional analyst programmer

Easier to maintain for anyone but professional analyst programmer. (while macro are mostly unusable once the initial developer is gone)

A guaranteed quality, due to permanent value checking. (a forced Test Driven Development method)

More efficient in the long run, due to the “think before you write” process with formula design

And definitively, much better integrated in the overall spreadsheet tool, following the initial design pattern of the spreadsheet, while macro often appear like specific developments requiring extensive maintenance afterwards.

Nota : these concerns mostly apply to procedure used as macro, while additional function written in VBA can increase the efficiency without lowering the quality.

This is how I came to write this game : an applied demonstration, that macro was not necessary at first, even for some of the most complex problems.

To be more precise, I found only 2 cases when VBA is required :

Adding specifics input or output (as I did here to get key events), while formula is always limited to change on the cell itself

Some complex problems (like optimization, try-and-check problems), those in which the computation time is too long, and/or taking too much space. But theses problems are quite rare in real business life.

This said, now, I’ll only focus on the rest of this article on how the spreadsheet actually works, for its different aspects.