But unfortunately microsoft didn’t invest into VBA in 20 years, other than keeping the light on. And it lacks so many modern features.
There was an attempt at a .net version of VBA (would have worked the same way, with a mini visual studio embedded in Office), called VSTA. But it was killed. So the cattle (business users) is stuck with 1990s technology.
It's a hindrance to -us- but I suspect for code that's designed to be passed from SME to SME that -not- having access to features so you have to write things the ugly stupid way ... may actually be an advantage, since the next SME along only has to have a sufficient tolerance for 'ugly' rather than an understanding of the features you and I would both want.
There was an attempt at a .net version of VBA (would have worked the same way, with a mini visual studio embedded in Office), called VSTA. But it was killed. So the cattle (business users) is stuck with 1990s technology.