If you enjoy VBA then it's a great idea. You know what they say - VBA (or Visual Basic generally) is the new COBOL. That is, a legacy language that is no longer taught, but powers some very important systems.
Just check your favourite / local job site for VBA jobs and you'll see that there are always jobs and the pay is very reasonable. Looking today on this Seek VBA search I see a number of positions advertised $100K-$149K, and plenty in the more mid-range rate.
Most of the jobs are in finance, engineering, those sort of environments with a lot of numbers being crunched. Spreadsheets power the world, they become entrenched and then enhanced, and turn into gigantic macro-laden applications.
Most of the work in this area is maintenance, maybe adding features here and there. I can't imagine anyone building a new system in VBA.
Most of the people working in VBA are hangovers from when VBA / VB was the new, agile way to get work done. They have a lot of VBA experience, but might be hitting middle age or later. They most likely won't be exposed to new technologies.
So the positives and negatives are the same as tying yourself to any niche skill, like being a COBOL developer, or working on French cars:
- you get yourself a nice niche skill that is probably fairly secure
- you're in a limited pool of competitors
- demand is steady and significant if not growing
- but you won't get to use any new cool stuff tech / cutting edge
- you'll likely find it hard to get out of VBA if you ever wanted to.
It's not for me, because I like being in the big field and keeping my options open. But I understand the attraction and think it's a legitimate place to be.