I agree with most of this. I work in marketing and knowing Excel better than most (hardly coding I know) and a little bit of VBA and I get a lot of efficiencies where others can't. I'm now leaning some basic web programming at the moment. It helps open the mind to what can be done which in turn should make me a better marketeer when it comes to making plans. Plus it is good to know if engineers are being asked to do too much or if someone is just work shy... when invariably we ask people to do too much.
On the flip side I think it would be great for the engineers to spend some time in marketing and close up that divide to work better together. Often I see both sides try to keep each other out other their business when much value can be had when marketing gels with engineering.
I wouldn't regard Excel as "hardly coding". It depends on the difficulty of what you're doing. If you're using it as a data structure to organize numbers, then that's obviously not coding. But complex Excel data modeling feels like coding.
I did this a lot at my last job. Some portions of the spreadsheet function like tables and calculations on data. Others control input variables. Yet others would just display the data in charts and graphs.
Excel is considered 'functional programming', which is different than traditional programming but nevertheless incredibly valuable. I took a 'Computer Skills for Business' class during undergrad and I can't thank it enough for turning ten-minute tedium into snippets of code.
Not sure about my greatest triumph, but my biggest problem is definitely how certain tasks are either hilariously easy (linear analyses, data modeling) or prohibitively frustrating (lack of conditional loops, version control.)
Generally speaking, I'll use Excel when I want to go quickly from nothing to displayable content (it also helps that it's easier to explain away an Excel formula than show someone a mess of Haskell). And, on a tangential note, it certainly helps that pretty much every school or office computer will have some version of Excel.
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.
On the flip side I think it would be great for the engineers to spend some time in marketing and close up that divide to work better together. Often I see both sides try to keep each other out other their business when much value can be had when marketing gels with engineering.