VSTO stands for Visual Studio Tools for Office and is sometimes pronounced as "Visto." VSTO is an alternative to VBA, and will most likely replace VBA in time. I assume that you already know VBA for Excel.
You probably have been working with VBA for quite a while and like working with this programming tool. Why switch to something new?
You may not need to switch soon, but it looks like Microsoft is going to discontinue VBA in its new releases from 2008 on. At some point in time, you may have to transit to VSTO–unless you want to stay with older versions of Excel and related Office products.
So the question is: Why is Microsoft so excited about VSTO? Is it just because it is a new product? I don't think so.
The answer has something to do with the evolution of another product: Visual Basic, in particular VB 6.0. VB 6.0 is an application on its own that allows you to create your own new applications–simple applications or fancier applications similar to Excel–by using the Visual Basic programming language.
VB 6.0 uses Visual Basic in much the same way that Excel uses Visual Basic in VBA. Visual Basic is a powerful programming language, but there are other languages such as C++ (pronounced "C-plus-plus"), Java, and so forth.
Professional developers are usually specialized in one of these languages, and if they don't speak Visual Basic, they cannot use VB 6.0. To alleviate this problem, Microsoft came up with a new development tool, called Visual Studio .NET (pronounced "Dot-net").
The VS.NET version not only uses the programming language Visual Basic, but also other languages such as C++, C# (pronounced "C-sharp"), and J#. In addition, it has many other advantages.
Can you use VB.NET to program existing applications such as Excel? Can you use the power of Excel, as exemplified in its graphs, in VB.NET? Yes, you can, but the process is involved and not very efficient. So, Microsoft came up with a new engine: Visual Studio Tools for Office (or VSTO).
VSTO is basically a Visual Studio add-in. Sorry if the terminology has become too mystifying and confusing. From now on, I will call the "old" tool VBA, and the "new" tool VSTO. VBA works with the "old" language–VB–whereas VSTO works with the "new" language–VB.NET.
VSTO works within the Visual Studio .NET environment, also called the .NET Framework. VSTO interacts directly with Office applications such as Excel–and that's why you should know about it, and in time may even have to know about it. Let us summarize some of the big advantages VSTO has over VBA:
- Works with your favorite language: VB, C++, etc. Uses more powerful forms with expanded potential.
- Improves access to data residing on a server (SQL and ADO). Enhances communication with Web Servers.
- Protects users with better security.
- Protects code by hiding it from view and preventing inadvertent, inept changes.
- Improves the way you deploy new code and future updates to other users.
All of these issues will receive due attention in the next chapters. Don't feel overwhelmed by the terminology at this point. The key issue remains: How do you create the new code? That will be our main concern. Before we go into code issues, I want to address another point: Where is the new code going to reside?
The code you create for Excel in VSTO is not located inside the document (as it is with VBA), but rather it is a separate DLL file (Dynamic-Link-Library). The Excel document has been given properties that contain "directions" to a certain DLL file at a certain location. The .dll file is called an assembly.
Because VSTO code is built as a .dll file, this code file can be located anywhere. If the code is associated with a particular Workbook used by a single user, you can store it together with the document on that user's hard disk.
But you can also store the file separately on a network where it can then be downloaded by each user the first time the Workbook is opened. Other possible locations are a corporate intranet or a secured internet site.
How is it possible for VSTO to interact with Excel and other Office applications?
Thanks to PIAs (Primary Interop Assemblies). PIAs allow VB.NET code to call Excel code, but they must be explicitly or manually installed with Office 2003+ by including the ".NET programmability support" option for each Office product during or after the installation process.
How does VB.NET as it is used in VSTO differ from VB as used in VBA? We won't go into details yet, but the main difference is that the syntax or grammar of the VB.NET language is much more consistent, strict, and logical than what you are used to in the VBA version.
I just want to mention a few inconsistencies in VBA's VB language that perhaps have baffled you many times:
- Functions require parentheses, but methods reject them.
- Some data types can change type automatically, but others can't.
- Most properties have to be specified, but some don't (they're called default).
- Some indexes start at 0, others at 1.
- Some variables have to be initialized with the Set keyword, but not all.
As we will shortly see in greater detail, VB.NET is a much more streamlined language than VB. This is definitely an advantage for a "born" programmer, but it may be a bit of a problem when you want to transfer or migrate code from VBA (which is based on VB) into VSTO (which is based on VB.NET).
When creating new code from scratch in VSTO, you will have to get used to those stricter rules. One of them is that all type conversions have to be done explicitly for situations where there is no automatic conversion in VB.NET. You may ask yourself whether this is worth the price. We report, you decide.
Given the many advantages that come with VSTO, you may become convinced of its superiority. Given the fact that VSTO will replace VBA some day, you may not have a choice if–for whatever reason–you have to deal with upcoming versions of Excel.