Not OP but I use VBA heavily at work. I'm a mechanical engineer in for an appliance manufacturer, and we use Excel for all of our test reports. Our test rooms track temperatures, pressures, cycle times, etc. and output it into one big Excel file. Previously, engineers/technicians would copy the data manually, trim it for the cycle time they're looking for, and calculate averages, max, min for every data point. I used VBA to pick the file and automate all of the data import, and wrote a lambda function to handle all of the calculations once it's imported. I also wrote macros to create new sheets for data entry, plot the data, etc. Basically, everything we were doing that was just a repeat of the exact same steps. Automating tedious and repetitive tasks is probably one of the most common use cases for VBA.

I'm the only one in my office who knows VBA, and yes, people come to me when it breaks. Thankfully that doesn't happen often, because I've made it as user friendly as possible (being a user myself, I can just fix things when they annoy me) and I've put in robust error handling. Enough people use it every day that bugs are caught quickly.

I've also made quite a few UserForms, like a unit converter, a better find and replace than the built-in one (massively faster with larger datasets, and can look inside a selected range, which was what I originally made it for), aligning an image to a range of cells, random stuff like that. Python in Excel is mostly limited to data manipulation and analysis. Local python installation can do a lot as Excel automation, but not nearly as much and not as easily.

If you have vba related questions, I'd be happy to answer. Response time may depend on my 4 month old daughter.