Typically the code is container-bound, meaning that it forms part of the spreadsheet or document on which it is supposed to operate. When you make a copy of, for example, a macro-enabled spreadsheet, the code comes with it, which leads to code and version sprawl that’s difficult to contain. It is possible, through add-ins, to run code in one spreadsheet that resides in another, but in my view that simply makes a messy situation even worse.
To be able to support apps written in the .NET Framework, an entirely new runtime and development environment (VSTO) was introduced, again with different versions needed for different versions of Office.
What is VBA good for?
It’s clear that writing enterprise-grade, maintainable software using a container-bound platform with difficult-to-control dependencies is challenging.
VBA is great for small, often one-off, computational tasks and for organizing data extracted from other sources.
Despite its age, the integrated development environment (IDE) is a pleasure to work with, and so much has been added over the years you can do almost anything you want with VBA. It also comes with a macro recorder; you can record a series of keyboard actions, and the recorder will generate their VBA equivalent. This functionality seems to have been largely abandoned in later versions, where many actions that operate on the newer additions to the object model are not recorded.
I have mixed feelings about the macro recorder. The positive aspect is that a user can get some insight into how to manipulate the object model. On the negative side, I’ve seen many dreadful VBA applications based on macros that began life as a series of recorded macros. It’s not a good model from which to learn.
Apps Script does not have anything like this, and the type of skills needed to create enduring applications probably cannot be learned from such a tool.
Overall, VBA offers graceful automation coupled with easy implementation. It is a hard act to follow.