With that said, having been a quant type guy and spreadsheet user since the days of Quattro Pro, here is my take.
I think VBA will probably die at some point and be replaced with a more widely used and flexible language like Python or Julia. I'm not super read up on what Microsoft is thinking these days, but I imagine at some point, it makes sense for them to just go ahead and replace VBA with an option to use Python. They have a fairly large legacy user base for this stuff, so I expect they'll continue to support it in one way or another for quite a while to come. In any case, here is an example of what Python in Excel looks like: Python in Excel - DataNitro . Admittedly, I'm curious as to why Microsoft doesn't just buy these guys.
Finally, I don't want to say anything hyperbolic like "spreadsheets are dead," but the recent scientific computing movement is beginning to create serious cracks in what was once the impenetrable fortress that was spreadsheets. To someone who was once a true "spreadsheet junkie", the The IPython Notebook feels like it is to spreadsheets what the internet was newspapers. For anyone using spreadsheets for a long time, the classic problems with them are this:
I guess I would sum things up this way: If you already know VBA, are good at it and have people asking you to do projects, then so long as someone is willing to pay, then why not? However, I do think the world is changing (Python, R, Julia etc) and I'd start to develop expertise in those areas. If I didn't know VBA, I'd probably bypass it (unless my job absolutely required it) and opt to learn Python, R, Julia and their various libraries.
One final note: Like I said above, I was a real spreadsheet jockey. For probably 20 years, Excel was NEVER not open my PC. Then, I started to run into problems processing data sets I needed to understand (think 100s of 1000s of lines of ad server data). At this point, I knew I needed to change my ways and my approach. Excel could not (at least not easily and in reasonable time frame) allow me to do the kinds of analysis I needed. So, I bit the bullet and learned to code Python. It literally changed my life and I am thankful I did it. Now, I rarely open Excel and mainly just dump data and analysis into spreadsheets when I need to share them with an audience who can't understand anything else (which is often).