From VBA to VSTO: Is Excel's New Engine Right for You?
207
From VBA to VSTO: Is Excel's New Engine Right for You?
207eBook
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473175 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 04/12/2006 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 207 |
| File size: | 7 MB |
About the Author
Read an Excerpt
From VBA to VSTO
Is Excel's New Engine for You?
By Gerard M. Verschuuren, Linda DeLonais
Holy Macro! Books
Copyright © 2006 Dr. Gerard M. VerschuurenAll rights reserved.
ISBN: 978-1-932802-60-3
CHAPTER 1
1 Visual Studio Tools for Office
1.1 Why VSTO?
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. If not, study the interactive visual learning CD "Slide Your Way Through Excel VBA" (available from www.mrexcel.com or www.amazon.com).
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 that we will discuss later. In this book, I will just focus on the Visual Basic language of .NET, which is referred to as VB.NET.
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.
1.2 The New Tool: VSTO
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 (see 12.1).
1.3 The New Language: VB.NET
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.
CHAPTER 2Structure of the Tool (VSTO)
2.1 The .NET Framework
The .NET Framework is the backbone of VSTO. What is actually in the .NETFramework? Well, it is loaded with hundreds of classes and interfaces! Here are its main layers:
[ILLUSTRATION OMITTED]
[TABLE OMITTED]
What are CLR and BCL? Perhaps you remember the VB Runtime in VB 6.0. It provided some very powerful services such as the following:
* Automatic memory management:
Destroying objects and variables once they go out of scope
* Safety checking on code:
For example, this prevents you from referencing an array outside its boundaries
* Functions:
For things like string manipulation (InStr), user input (MsgBox), and type conversion (CInt)
Well, VB.NET has split these services into two separate entities, but they are much more comprehensive than before:
* CLR Common Language Runtime):
CLR reads code produced by the VB.NET compiler, scrutinizes it before execution, and cleans up unused variables and objects (so-called garbage collection; see 11.3). Code that runs under the control of the CLR is called "managed" code.
* BCL(Base Class Libraries):
BCL provides many regular functions such as MsgBox(), InStr(), and UCase(). In addition, it contains many other classes and functions to perform data management and file management — and that's what most of this book about.
2.2 The Workspace (IDE)
A fancier name for the workspace is: Integrated Development Environment, or IDE. It has the following components, more or less different from VBA, and definitely more sophisticated:
[ILLUSTRATION OMITTED]
[TABLE OMITTED]
* Solution Explorer:
A Solution can include one or more projects, in many different languages. The window can list general code modules, possibly one or more form modules, one or more class modules, plus a section called References. The last section lists the assemblies or components referenced by this particular Solution, so their classes become accessible for the developer. To add a new Reference, right-click this section (or choose Project [right arrow] Add References).
* Properties Window:
This window is very similar to the one in VBA. Changes to the properties in this window cause changes in the code. This code is usually written for you in files that you will only see if you open them from the Solution Explorer.
* Toolbox:
This window is very similar to the one in VBA for Form controls, but it has many new tools and also stores non-graphical components such as database connections. That's why you see several categories in the toolbox.
* Server Explorer:
This window lists Server components for database connections. The elements displayed can be dragged directly to the Form designer in order to create the proper connection (see 10.3).
* Error List:
Code Errors detected by the automatic syntax checker are listed here. Double-clicking on an error takes you straight to the troublesome spot in your code. You can also use the line number of each error, but then you need all your code lines numbered: Tools [right arrow] Options [right arrow] Text Editor [right arrow] Basic [right arrow][check]Line Numbers.
* Object Browser:
This window shows you all the available classes, just like VBA. But be aware that VSTO is much more class- and object-oriented than VBA. Literally everything is done through objects and the classes of which they are instances (see 3.1).
* Immediate Window:
Debug.Print commands from VBA have been replaced with: System.Diagnostics.Debug.Write("...") statements. Their output is displayed in the Immediate Window. But there is more to this window (see 8.2).
* Class View Window:
This window shows you a listing of all References (plus their sub -classes) and all objects in your project.
There are many more windows than we can discuss here.
You can have several windows available at the same time. If you click a Window's pintag icon that Window will dock on the side or bottom, thus hiding when the mouse is not hovering over it.
You also have the option of keeping several windows open at the same time by clicking their pintag icons again. However, some of these will be combined on the side with a set of tabs at the bottom.
2.3 Connecting to Office Applications
In VBA, you could communicate with Excel directly because VBA was integrated into the application and the VB code was stored inside the document. And if you wanted to interact with another application, say Word, from inside Excel, you could reference the Word Object Library and then create an instance of the Word application:
Dim wdApp As New Word.Application
From there on, you were able to use the new object's Documents. Open method or apply the GetObject() function. So, why would you need VSTO, you may wonder. Well, VSTO shares its environment with .NET — and this combination makes for a much richer and more sophisticated tool (more integration, better data connections, higher speed, improved security, better deployment, and more languages from which to choose). Just keep reading!
A similar story holds for the difference between VB 6.0 and Visual Studio . NET:VB already has the capacity to create instances of Excel and other Office application in order to use their properties, methods, and functions. So the question is again: Why would you go for VSTO? Because VSTO can interact directly with Office Applications! Office 2003+ comes with PIAs (Primary Interop Assemblies) that allow VB.NET code to call Office code directly. These assemblies are tweaked a little to make them perform better and more efficiently. The advantages can go either way: On the one hand, VSTO can leverage Excel as a creator of charts, pivot tables, and so forth. On the other hand, VSTO can create much more powerful assemblies — not located inside the Excel document but linked to the Excel document — making deployment, protection, and updating much easier.
How do you create an MS Excel Solution? The steps are basically simple and just require some clicking on the right menus and buttons. You end up with a direct connection to a new or an existing Excel workbook, so you can create code that lets you communicate directly with Excel.
Now the Solution (or its project) contains at least two source files:
* AssemblyInfo.vb, which stores assembly-level metadata
* This Workbook. vb, containing a single class called This Workbook
How is the connection between document (.xls) and code (.dll) established? The connection is located in a property setting for the document that uses the assembly's name and location. You can find this information when you run the project and check Excel's properties: File [right arrow] Properties [right arrow] Custom.
What is it that VSTO has created for you in the background? One of the ways to view the new Objects that have been created is to check the ClassDiagram1 .cd file. There you will see that two important classes have been made:
* ThisWorkbook class
Holds some important events, such as Startup() and Shutdown()
* Globals class
Has references to ThisWorkbook and all its sheets
What else does VSTO do behind the scenes? It creates a tremendous amount of code that you don't see, and probably don't want to see (see 2.4). In order to make this "hidden" code visible, you may have to click one of the top buttons in the Solution Explorer — the Show All Files button. After doing so, you will see the following code when double-clicking on This Workbook. Designer. vb:
[ILLUSTRATION OMITTED]
Notice how this code actually creates the This Workbook and Globals classes. An ellipsis (...) indicates where I have shortened the code to make it look more palatable:
It is partly due to this code that we can draw on three important objects to help us create all the code we need: Globals, This Workbook, and This Application. By the way, This Workbook refers to the workbook in this specific project and bears its name — in this case, the name "Employees" (or whatever name you have given to your project).
Two of these Workbook events will have already been implemented when you open the window for ThisWorkbook. vb: Notice the This Workbook_Startup() and This Workbook_Shutdown() events.
Whatever code you write in This Workbook_Startup event will kick in when you load the Excel file. By the way, a Startup event kicks in before an Open event.
So what we have to work with now is a set of important objects that allow us to access elements such as Selection on any Excel spreadsheet in ThisWorkbook.
These are all legal statements inside This Workbook:
* var = Application. Selection
* var = Me.Application.Selection
* var = ThisApplication.Selection
* var = Me.ThisApplication.Selection
2.4 References
When you create an Excel project, VSTO automatically adds the References that provide you with some basic classes, including their properties methods, and events:
The Solution Explorer has a section called References.
* When you double-click on one of those References, the Object Browser launches and lists all the available References.
* When you double-click one of those, the middle panel displays all the procedures that come with that specific reference.
The References listed in the Solution Explorer were automatically added, but you can also add your own references manually: Select Project [right arrow] Add Reference.
(Continues...)
Excerpted from From VBA to VSTO by Gerard M. Verschuuren, Linda DeLonais. Copyright © 2006 Dr. Gerard M. Verschuuren. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Contents
Title Page,Copyright Page,
About the Author,
Prologue,
Listing of Code Samples,
1 Visual Studio Tools for Office,
2 Structure of the Tool (VSTO),
3 Structure of the Language (VB.NET),
4 Syntax Changes in VB.NET,
5 Changes in Data Types and Scope,
6 Array Changes,
7 Forms Instead of UserForms,
8 Error vs. Exception Handling,
9 File Management,
10 External Databases,
11 Building Your Own Classes,
12 Deployment and Security,
13 Migrating from VBA to VSTO,
14 What Do You Need to Get Started?,
HOLY MACRO! BOOKS QUICK ORDER FORM,