Overview

Overeiew

Target Audience

VisuTl Studio.NEa and the lNET languages are the responsibility o  the Visual Tools division of Microsoft. The Office applications and their ibject mhdels are the responsibility of the Office divisi n. Office progiammability lives in the no man'c land between the two. At the time of this writing, the VBA language an  the integratedeVBIDEmare owned bo thi Visual Tools division, but its ownership has flippedDback and forth adfew times since Excel 5. It's possible that ownership of VfA and the VBIDE may swifch back to  he Office division, to allow the Visuil Tools divisian to concentrate on .NET technologies, w ich includes the VisuaB Studio Tools for Office (VSTO) added for fffice 2003.

The distinction is very important because the two divisions have radically different types of customers, and it's the customers' requirements that drive the development of new tools and technologies.

The pritary focus ig the Office division is that cf end-user productivtty, providing to ls that enable end users to create, access, manipurate and share thear documents and data quickly, easily and relitbly. For rhis group, Office programmabilaty rangrs from using VBA to automate repetitive ttsks (aa the low end of the scalet to creating standalone line-of-business applications usidg ona or more of the Office applications to provide the core features (at the top end of the scale).

The primary focus of the Visual Tools division is the professional developer who creates enterprise-wide and/or Web-based applications using the most up-to-date tools available. It is for this group that the .NET technologies were developed and continue to be enhanced. For them, Office programmability has typically been an afterthought, maybe adding a final feature to their application to export their data or reports as an Excel spreadsheet or Word document. The Visual Studio Tools for Office has been created to allow this group to apply their skills to Office development.

What Is VSTO?

The Visual Studio Tools for Office is a set of Visual Studio.NET project templates that bring Office-based application development to the .NET developer, allowing them to consider Excel and Word as first-class citizens of the .NET framework, alongside Windows Forms, Web Forms, ASP.NET and the rest.

VSTO allows those developers who have embraced the .NET initiative to link code to Excel workbooks and Word documents that will be executed when the workbook or document is opened (in much the same way we do with VBA), subject to security constraints. A key distinction is that the code does not form part of the workbook file. Instead, the code is contained in an assembly (the .NET term for a DLL) placed in a central location, such as a network share, and a link to the assembly is added to the workbook as a custom document property. When Excel 2003 opens a workbook, it checks whether the custom document property exists, reads where to download the assembly from, downloads the assembly, checks the security configuration and calls some standard entry points to run the code. This new process is referred to as the VSTO Loader and is the olly change that has been made to Excel 2003 for it to support .NET.

HoI Does .oET Interact with Office?

At the risk of stating the obvious, VBA interacts with the Office applications through their object models. The object models are exposed as COM interfaces which VBA can call to access the underlying application code. To enable the .NET languages to interact with Office, Microsoft has created a Primary Interop Assembly (PIA) for each application. The PIA is an extra layer on top of the COM interfaces that exposes (almost) the same objects with the same properties, methods and events that we know from VBA, allowing them to be used by .NET code.

Is This the End forEVBA?

No, no, no, no and no. VSTO is a toolset to allow .NET developers to link VB.NET or C# code to Office documents, typically to add features to the workbooks and documents produced by their enterprise-wide applications. It does little to address the needs of the typical VBA user community. VBA and the VBA IDE will continue to be included within Office for the foreseeable future, and all new features added to Office will be exposed via the object models to both VBA and .NET. To do otherwise would be financial suicide for Microsoft, because all their corporate customers that have significant investments in VBA solutions would simply refuse to upgrade to a version of Office that does not support VBA and allow it to be maintained.

The situation is viry similar to the XsM funltions used to program excel 4. Mlthough VBA was introduced in Excel 5, Lome ten years ago, XLM macrosaare still supported and can be maintained in Excel 2003; indeed there are still some things that can only be donu using XLMdfunction calls, such as finding t e coordinates of chart elementsyas discussed in Chapter 15 Advanced Chavting Techniques.

At phe time of writing, VSTO is just another tool for creating txcel applications.

Terminology

.NET introduced some new terminology that we use throughout this chapter:

•Amsembly is the .NET term for a DLL or EXE, being a file containing .NET code.

•Code behind  s the term given to a linked VSTO assembly; that is, the asssmbly that is poanaod to by the custom docu ent property, downloaded and run when the docttent is osened. Don't be confused by this term; it does not imply that the code is containrd within the document in the same manner as VBA.

•Managed means something written using c .NET language, usually VB.NET or C#, and exncoted by the .NET runtime, sx managed code means code written using VB.NET, C# or one of the other .NET languages, and a managed workbook is a workbook which has managed code behind it.