The use of VBA arose purely out of necessity - I was asked to enhance a spreadsheet for underwriters of my reinsurance company. Those guys love their spreadsheets - I can't even describe.
Those guys love their spreadsheets - I can't even describe.
No, describe :)
Seriously, this kind of folklore is valuable (I work on spreadsheet systems and feed on this stuff). What do they do with spreadsheets? How do you know that they love them? What sort of enhancement has to be done in VBA? Does each of them make their own independent spreadsheets? if not, what kind of sharing do they do? What are the most significant limits/problems you've observed in their use of spreadsheets?
I can provide some folklore. I am a professional programmer with a Unix pedigree working as a quant in banking and even I succumbed to the charms of Excel (I still managed to evade VBA though).
I'll start with sharing. The idea of sharing is mostly .xls or .xslx file on mapped network drive and shouting over the floor "Jane, please close quarter results"
Sometimes they are shared on SharePoint which is only marginally better.
So, yeas, sharing is a problem.
But still most of the Excel use (at least with quants) is one off and the end result is a couple of charts that you put into presentation or some insights about numbers and data.
Excel is very good for interactive calculations and what if analysis when you have a complicated chain of formulas and you need results immediately updated when you change inputs
Excel is very good for exploratory data analysis: again charts are immediately updated when you change data and you do not have to remember a lot of API to make then in the first place unlike, say in Matlab or Matplotlib or ggplot.
Old Excel limitation on rows and columns was a problem but not anymore. Lack of column formula (when you would type say =B+C in D's header instead of =B1+D1 and then control-dragging for thousands of rows) (btw Guys from Resolver systems implemented that). Ugly charts.
Also, not a problem with Excel per se, but rather with people who try to use Excel for things it was not designed for: apply it to large datasets and build complex systems on top of it.
I've spent the past week building a system that parses spreadsheets into xml and feeds them into a relational db. Having relational integrity gives insight into relationships in data in ways not possible with a vlookup. Databases also provide concurrency so that many analysts can work in the same data. But because of VBA, you can bind an Excel spreadsheet to a database and get the best of both worlds.
It proves the language choice is irrelevant. It's the solution that counts. Even VBA can be written clearly and works well. It's fun to watch as hipsters flaming echo other for their adopted cool languages though.
And VBA has a surprisingly great development environment.
I think the negativity towards it arises from it being accessible to non-programmers. The VBA in the SO post is beautiful work, however.
