Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Overview

Target Audience

Visual Studio.NET and the .NET languages are the responsibility of the Visual Tools division of Microsoft. The Office applications and their object models are the responsibility of the Office division. Office programmability lives in the no man's land between the two. At the time of this writing, the VBA language and the integrated VBIDE are owned by the Visual Tools division, but its ownership has flipped back and forth a few times since Excel 5. It's possible that ownership of VBA and the VBIDE may switch back to the Office division, to allow the Visual Tools division to concentrate on .NET technologies, which includes the Visual Studio Tools for Office (VSTO) added for Office 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 primary focus in the Office division is that of end-user productivity, providing tools that enable end users to create, access, manipulate and share their documents and data quickly, easily and reliably. For this group, Office programmability ranges from using VBA to automate repetitive tasks (at the low end of the scale) to creating standalone line-of-business applications using one 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 only change that has been made to Excel 2003 for it to support .NET.

How Does .NET 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 for VBA?

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 very similar to the XLM functions used to program Excel 4. Although VBA was introduced in Excel 5, some ten years ago, XLM macros are still supported and can be maintained in Excel 2003; indeed there are still some things that can only be done using XLM function calls, such as finding the coordinates of chart elements as discussed in Chapter 15 Advanced Charting Techniques.

At the time of writing, VSTO is just another tool for creating Excel applications.

Terminology

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

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

Code behind is the term given to a linked VSTO assembly; that is, the assembly that is pointed to by the custom document property, downloaded and run when the document is opened. Don't be confused by this term; it does not imply that the code is contained within the document in the same manner as VBA.

Managed means something written using a .NET language, usually VB.NET or C#, and executed by the .NET runtime, so 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.