VBA is a lovely language, that supports object-oriented programming (with composition... no inheritance). It has deep access to and control of Excel. It's mature and stable (Microsoft is no longer significantly changing it). "Real programmers" hate on it largely because of all the amateur spaghetti VBA code written by the business people (that the programmers are occasionally asked to debug).
Counterpoint: VBA is an awful language, other than its access to/control of Excel, Word, etc.
It's full of bizarre quirks, like <i>control characters in code</i> that are localized.[1][2] Want your code to run on non-English installations? Better dynamically build all of the strings that are passed to that type of function using placeholders like Application.International(xlDecimalSeparator), making your code much less readable. When code breaks for this reason, it does so with incredibly unhelpful errors, and it is literally impossible for the developer to reproduce unless they know it's a potential problem with VBA, and then they have to switch their interface language to one they potentially don't even know to reproduce the problem.
In Word, at least, probably half of the most useful functions (insert a paragraph after the current one, etc.) will break if you use them on the last paragraph in a table cell, requiring tons of spaghetti-code workarounds.
Want to pass around a string of text that contains multiple formats, the equivalent of referring to the innerHtml property of a DOM element? Good luck with that, unless you want to do it all using hacky scripted-select and copy/paste.
Someone in a parallel thread compared it to Bash, and I actually agree with that. No one should be writing anything complicated in either language.
There are quirks in JavaScript too right? When I hit a VBA quirk, I write a rectifying function around that quirky functionality. I use the custom function going forward, and never deal with that quirk again. I agree that there are a lot of quirks, and the Excel object model is byzantine. Relying on vanilla VBA / object model isn't a good idea. But, with some investment, one can be very effective in VBA. The syntax is simple / clean.
FWIW, I think JavaScript is an awful language as well, just for different reasons than I dislike VBA.
VBA in my experience has too many quirks that can't be wrapped in a less-quirky general purpose function. For example, I was just working in Word and was reminded that as soon as tables come into the mix, the order of text in the document is no longer linear in terms of numeric range values. E.g. text might have a greater numeric offset value in the document than text that visually appears after it, if the first text is inside a table. I've had Word VBA get confused about this, and extend a search loop outside the range I gave it to search within and start returning content in other parts of the document. Why would I trust a language like that for anything important?
MS should really have just gone forward with a .NET replacement, IMO. C# is one of the best things they've ever invented.
I hear you. Wrapping stuff in less-quirky functions... is a slippery slope to building a framework with a new object model (on top of the current object model). With that said, I love the hell out of VBA.
Options are good. Resuming on error can be just as much a feature or flow control paradigm as using exceptions for flow control.
VBA gives users options. If you want a straitjacketed 1990s predeclared OOP language, you can use Option Strict and Option Explicit and forbid Goto statements and On Error statement. If you can deal with ambiguity, you don't need to.
And of course even a a language with misfeatures is better than the VP of the IT Dev Silo giving you the choice of spending $2m and a year or doing your work by hand.
