While you may know a lot of people who do VBA code in Excel, that is actually far from the norm for the average corporate user. From my non-CS friends who have worked in both large and small companies, I have heard tons of horror stories of the insane spreadsheets that low-to-average users make using just the GUI functionality. Huge spreadsheets with formulas spanning tons of sheets to simulate what could be done in a simple function. These spreadsheets get huge over time as coworkers slowly add/update functionality over time. All using the GUI which makes it a cluster fuck (my friends' words) to understand.
And all of this actually is equivalent to the "uncommon" VBA coding that you hear of. I wouldn't be surprised if all of the VBA code / spreadsheets you hear about evolved from one of these massive GUI created spreadsheets. Why? Anecdote time:
Those VBA sheets tend to come into existence when a non-programmer decides to learn about macros, updates one of these sheets to be simpler (less data entry), and it actually works. One of my friends was one such employee and she ended up converting a few of the inefficient spreadsheets into a single faster (though still slow) one. Due to cutting down the amount of manual data entry and processing time, it made what used to take a few days of work into a single day of work (mostly to have the sheet run calculations). If this creation gets useful enough, it can take a life of its own in the company and eventually some manager might make it the responsibility of an "IT" guy to update the code. Usually because the original employee got promoted (or left for a better job) due to killing their performance reviews. My friend was one such employee who left and actually did this at more than 1 company leading to a pretty damn well paying job at a young age. Last she heard, her original spreadsheet was still being used and semi-maintained by IT. And this it how I believe a lot of those VBA coding projects come into existence.
My dad takes great pride in complex VBA code. Unfortunately, this can backfire. A few years ago he was brought in to help manage a particular support system for a large telecoms company here in the UK, which involved a lot of Excel donkey work. He understandably figured out a way to automate about 50% of the work of the entire system.
This meant that he freed up plenty of time for the entire team he was on. Unfortunately, this meant that they now had surplus staff, and as the newest arrival, he was the first to let go.
Yeah, incredibly backwards internal politics, but I swear it's true.
100% believe you because this was one of the concerns my friend had about sharing her work. Only management didn't go the firing route. They ended up giving the employees even more work because of their newly found free time.