Visual Studio Tools for Office (VSTO) is a set of development tools available in the form of a Visual Studio add-in (project templates) and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime (CLR) to expose their functionality via .NET.
Quote: https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office
As we have seen in my previous blog post Office Development – The Ugly the first option to build your Office extensions using Visual Basic for Applications (VBA) did not win the beauty contest. A very powerful toolset to create beautiful and especially productive customizations. I’m sure that VBA, the tools to set Office to your hand, allowing you to tailor Office in a way that it perfectly fits the needs of your company, is one of the main reasons that made Office such a popular box of applications.
It was however risky – a potential security risk and code management is a pain. In the real world it happened that a ‘new’ version was rolled into production where all the sudden existing features ‘disappeared’ as a result of the developer picking the ‘wrong file’ containing an older version of the solution and continued developing features using the incomplete version.
This needed to change … in the meantime managed code -.NET- appeared at the horizon and the next option at least had to be a) secure and b) needed an improved source control option.
The solution Microsoft put on the table were the:
Microsoft Visual Studio Tools for the Microsoft Office System.
The product name was most likely the longest name available at that time. The Visual Studio Tools for the Microsoft Office System (VSTO) was architected as a bridge between Visual Studio and The Microsoft Office System. On one side the languages team with Visual Studio, on the other side the Office team and in the middle the VSTO team – code named Trinity …
So how is VSTO different compared to VBA?
Applications, add-ins or document customizations, are built using Visual Studio. Initially in a separate Visual Studio .NET 2003 VSTO SKU and after a few version inserted in the Visual Studio 2008 box. The code no longer lived in the documents or other Office files, but from now on lived in an external assembly that would be triggered by the Office host application if the right properties were available in the document.
Loading the assembly was done in a relative complex manner and it was made sure that security wise the pain seen with VBA would not exist with VSTO add-ins.
Here is a schematic overview of the load pattern:
As you can see the Office Application is looking at the Registry to see what add-ins are installed, if found any the Deployment manifest (pointed to by the Registry) is read and following that the Application manifest is read and the assembly loaded.
A real extension to the Office Application … to make this all secure the security is enforced at installation time. There was a whole slew of prereqs that must be covered before the add-in would be installed:
This part made VSTO the “Bad” in The Good, The Bad and The Ugly … it appeared to be a real pain for the IT Pros at the time. By default, VSTO used Click-Once to install the extension on the machine and a lot went wrong … certificates expired, not added to the Trusted Publisher lists, etc. etc. There was another option, to install an add-in using an .msi installer but the average VBA developer had a hard time getting around all of this, jumping all the hoops to make it work.
Was it all Bad?
Not at all! By using managed code, C# or VB.NET – whatever your preference was, your world of Office completely opened. Whatever you could do with .NET (and that is basically everything) you could add to your solution. Calling third party libraries, UI components, later even XAML UI interfaces, sky is the limit.
For me personally this is still my fav option to build Office customizations. I wasn’t awarded nine-year Visual Studio Tools for the Microsoft Office System MVP – I guess the longest MVP title as well, without reason. VSTO controlled my life for the large part of these years (hopefully my wife is not reading this ) did a lot of forum support in this area and built VSTO applications for large enterprise companies. At the time, we still had on Microsoft campus Software Design Review meetings to share feedback and we gave the team a hard time to make sure they released the features that we really needed.
Where to go next
But … the world changed, Windows is no longer the only platform that needs to be supported. Because of that a new mechanism was proposed. By using the common web standards and JavaScript a platform independent system was developed by providing OfficeJS APIs to access the Office object model from web based add-ins.
And this is where we will dive in soon. It’s a relative new world, although development already started years ago, so there is a lot to learn there. Let’s see if we can do the same with OfficeJS as we can do with VSTO – but now on multiple platforms and several clients.
Maybe I will return to VBA and VSTO in separate blog posts just for fun. There is just a ton of information to share on all off these areas.
Book
Oh, and if you really want to know the ins and outs of VSTO I really recommend you reading the VSTO bibles by Eric Carter and Eric Lippert: Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook. It is a huge and heavy piece of reference with over a thousand pages of inside information.
