As part of my preparation for presenting on VSTO (download slides here) at SBTUG this week I was asked a number of questions about how VBA and VSTO fit together. Things like: Is VSTO the VBA replacement? How do VSTO and VBA fit together? What is the benefit of learning VSTO?
10 million VBA developers?
Theyâ€™re important questions, after all there are reportedly 10 million VBA developers still active world wide (thatâ€™s the staggering number I heard at the MVP Summit earlier this year â€“ Iâ€™ve tried to find some online citations for this but havenâ€™t so far â€“ please advise/correct me if you know better).
With so many VBA developers (and many of them passionate VBA coders) there needs to be some compelling reasons why theyâ€™d want to switch to VSTOâ€¦
But hereâ€™s the thing. They donâ€™t have to switch. Whether Microsoft initially started with the aim of â€˜convertingâ€™ the VBA crowd to VSTO Iâ€™m not sure. If so, they soon realised that having a bridge between VSTO and VBA was a far more beneficial offering. So theyâ€™ve worked hard (and successfully) to provide extremely simple VBA/VSTO interop.
This video by Daniel Moth is a good overview, as is this short demo by Eric Schnepp which walks through an Excel example calling VSTO code from VBA. (Both are from last year, recorded before Visual Studio 2008 was officially released, but still very relevant.)
As the videos show, it is dead simple (enabling it is literally a single option getting set true) to call VBA code from VSTO and VSTO code from VBA functions. VBA isnâ€™t going anywhere soon â€“ its around for the long haul.
If youâ€™re a VBA developer then you donâ€™t need to throw out your VBA, you can still use it all and simply add new stuff in VSTO as you get up to speed. The advantages of VSTO are immense (huge library of functions to use, application level code as well as document level, new designers, custom task panes, full access to the framework, working with XML, security, error handling, etc) but it isnâ€™t without its downside: youâ€™ll need to ensure the client has all the pre-requisites installed (.NET framework, PIAs, VSTO runtime).
If youâ€™re after a fuller article on VSTO and VBA then this â€˜ancientâ€™ one from Paul Stubbs and Kathleen McGrath is highly recommended. Incidentally, their book VSTO for Mere Mortals was the first book I read on VSTO, and was an excellent introduction (note: itâ€™s based on a previous version of VSTO).