knowing Excel better than most (hardly coding I know) and a little bit of VBA
There's no question that spreadsheets are programs, albeit not written in a general-purpose language. Question for you: what do you do in VBA? i.e. in what areas does the spreadsheet alone not suffice?
Most of the time it is because the existing functions are inadequate, so people end up adding new ones in VBA. StringConcat, for example, is far more powerful than the regular concatenate function.
When you say "function" you mean something that just does computation, right? As opposed to code that accesses the Excel object model to create interactive effects, like, say, "click this button to change the background color over there"?
How much VBA could be replaced if Excel gave you the ability to make functions just out of spreadsheet cells? That is: build any calculation you like in a spreadsheet, tell Excel "make this calculation be a function called FOO", then use it wherever you want like a normal function: "=FOO(123,A1)" or what have you.
Yes on the definition of function. I did also use VBA to make certain visual effects possible (the one I remember was some advanced conditional formatting).
On VBA being replaced-- I don't know. It depends who the users you're talking about are. Most people I worked with didn't know any VBA because our models weren't that complicated. I in particular was working on a project that was computationally heavy.
Can you describe the computationally heavy spreadsheets you made? How many cells, how many distinct formulas (i.e. distinct except for relative references), how many worksheets, how long it took to recalculate? Were you pushing any limits of Excel? Were there annoyances?
(My email's in my profile if you'd rather discuss it offline.)
I use it for automating repetitive or recurring tasks where formulas can't cut it or they would make the spreadsheet overly heavy to work with. So a lot of data cutting and organising especially when I pull data from systems that don't integrate well with Excel.
There's no question that spreadsheets are programs, albeit not written in a general-purpose language. Question for you: what do you do in VBA? i.e. in what areas does the spreadsheet alone not suffice?