Visual Studio Tools for the Microsoft Office System
Brian A. Randall
MCW Technologies, LLC
April 2003
Applies to:
Microsoft® Visual Studio® Tools for the Microsoft Office System
Microsoft Office System
Microsoft Visual Studio® .NET 2003
Summary: Provides an overview of how you can use the new Visual Studio .NET 2003 project templates provided by Microsoft Visual Studio Tools for the Microsoft Office System to create solutions that use Microsoft Office Excel 2003 and Microsoft Office Word 2003. It includes steps for installing the product and creating new projects, and discusses issues concerning deployment, security, and project sharing. (20 printed pages)
Contents
Introducing Visual Studio Tools for the Microsoft Office System
Installing Visual Studio Tools for the Microsoft Office System
Creating a Visual Studio Tools for the Microsoft Office System Solution
What Can You Do?
Deployment
Security
What's Going on Inside
Sharing Visual Studio Tools for the Microsoft Office System Projects
Conclusion
Introducing Visual Studio Tools for the Microsoft Office System
Microsoft® Visual Studio Tools for the Microsoft Office System provides a new set of Visual Studio® .NET 2003 project templates you can use to create assemblies that run in process with Microsoft Office Excel 2003 and Microsoft Office Word 2003. This enables you to create document-centric solutions that utilize the power of the hosting application, which, in this first release, includes Excel and Word. Using either Microsoft Visual Basic® .NET or Microsoft Visual C#®, you create assemblies that execute under the control of the common language runtime. This code is often referred to as managed code. In contrast, existing Visual Basic for Applications (VBA) or COM-based code is referred to as unmanaged code.
The common language runtime manages memory and validates your code to make sure it doesn't attempt to perform illegal operations such as access memory that doesn't belong to it. In addition, the runtime provides access to the Microsoft .NET Framework and the Base Class Libraries. With these tools, you could, for example, create a document management system that validates documents for legal correctness. Or you could create a sales forecasting solution that uses Excel for the user interface while automatically updating a central database when projections are changed. The possibilities are only limited by your imagination to leverage the rich object models exposed by Excel and Word coupled with your custom code and rich libraries provided by the .NET Framework.
Although it is possible to automate Excel or Word from managed code, doing so requires an external application manipulating the particular Office application from the outside. Assemblies produced with Visual Studio Tools for the Microsoft Office System enable managed code to work with the hosting application at a more intimate level. Although it's possible to create a COM add-in using managed code, the new framework provided by Visual Studio Tools for the Microsoft Office System enables you to create a solution that is tightly integrated and, more importantly, specifically designed to use managed code.
Installing Visual Studio Tools for the Microsoft Office System
Before you install Visual Studio Tools for the Microsoft Office System, you must install Microsoft Office Excel 2003 and/or Microsoft Office Word 2003. During your Office installation, make sure that you have selected the options that provide .NET support with Word and Excel. Doing so installs the appropriate primary interop assemblies (PIAs) that are required to access Office from Visual Studio .NET. Selecting the full installation of Office automatically includes the necessary components. Once the Visual Studio Tools for the Microsoft Office System developer installation is complete, start Visual Studio .NET and the new project types will be available.
**Tip **Primary interop assemblies allow managed Visual Basic .NET and Visual C# .NET to communicate with the host application's COM-based object model.
Installation Order
The order of product installation is important. On a developer's computer, Visual Studio .NET 2003 Professional or higher must be installed first. As part of its installation, Visual Studio installs version 1.1 of the Microsoft .NET Framework. The .NET Framework must be installed before Office 2003 to ensure that the global assembly cache (GAC) is available. The Office installation program installs the PIAs for the Office applications, including the necessary Word and Excel PIAs, into the GAC as part of the setup. So, the installation order is:
- Microsoft Visual Studio .NET 2003
- Microsoft Office System
- Visual Studio Tools for the Microsoft Office System
While installing Office 2003, you will choose a complete or a custom installation. If you perform a custom installation, select the .NET Programmability Support under Excel and/or Word. In addition, select Microsoft Forms 2.0 .NET Programmability Support under Office Tools and select .NET Programmability Support under the Microsoft Graph component, which you will find under the Office Tools node.
To deploy a Visual Studio Tools for the Microsoft Office System solution to an end-user's computer, you must install the following:
- Microsoft .NET Framework 1.1.
- Microsoft Office Excel 2003 and/or Microsoft Office Word 2003, including the necessary primary interop assemblies.
You can deploy the .NET Framework to end-users through Windows Update, your domain's own Software Update Service, or through a custom setup program you create.
Creating a Visual Studio Tools for the Microsoft Office System Solution
Creating a Visual Studio Tools for the Microsoft Office System solution is easy. From Visual Studio .NET 2003, select New Project from the File menu. Select the Microsoft Office System Projects folder; choose a managed language (either Visual Basic or C#), and then select one of the three possible templates: Excel Workbook, Word Document, or Word Template.
Once you select a project, set the name and location and click OK. At this point, the Microsoft Office Project Wizard will start. The wizard lets you choose whether to create a new document or to attach the new assembly to an existing document. In addition, the wizard assumes that you want it to modify your Code Access Security (CAS) policy settings and allow your account to execute and debug your new solution. You can change this assumption, but you generally won't want to.
Warning If you do not allow the wizard to modify your security settings you will not be able to run or debug your solution until you manually modify your CAS policy. The wizard will warn you if you chose to uncheck the box labeled Update local security policy to enable assembly to execute.
Once you click OK, the wizard creates a new Visual Studio .NET solution containing a single Class Library project. As part of the project creation process, the wizard instantiates an instance of the host application if you are creating a new document instead of using an existing document. The project contains two source files: AssemblyInfo.vb or AssemblyInfo.cs (depending on your language choice), and a file containing a class corresponding to the Office document, such as ThisWorkbook.vb or ThisWorkbook.cs. Also, the project creates references to the appropriate libraries so your code can interact with your chosen host Office application. An additional reference to System.Drawing.dll is created if you add a Windows Form to your project. Naturally, you can add references to additional assemblies if your application requires them.
The basic architecture is simple. Excel 2003 and Word 2003, upon loading a document, check for two custom properties in the document: _AssemblyLocation0 and _AssemblyName0. If these properties are found, the host application loads the Visual Studio Tools for the Microsoft Office System Assembly Loader (Otkloadr.dll). This unmanaged DLL is responsible for loading the assembly that you created using Visual Studio Tools for the Microsoft Office System. The loader uses the document's custom properties to find the document's assembly, loads the assembly into memory, examines some metadata, sets up references between the host and the assembly, and then steps out of the way.
Warning Only a single managed assembly can be directly linked to a host document. Although this document-linked assembly can reference any number of other managed assemblies, the Visual Studio Tools for the Microsoft Office System loader will fail if it encounters more than one set of properties pointing to multiple assemblies.
As you examine your first Visual Studio Tools for the Microsoft Office System solution, you'll notice that the ThisWorkbook.vb file contains a single class named OfficeCodeBehind, which will be examined later in this document. The basics are simple: the Visual Studio Tools for the Microsoft Office System loader creates an instance of the OfficeCodeBehind class after the assembly is loaded. The loader uses an assembly-level attribute named System.ComponentModel.DescriptionAttribute to find the startup class.
Tip If you change the name of your class or your assembly's namespace, make sure you also update the contents of the attribute at the top of the class file to match. Otherwise, the loader won't be able to find your startup class.
What Can You Do?
Although it might sound like a cliché, you really are only limited by your imagination. By using Visual Studio Tools for the Microsoft Office System, you are able to use the full power of Excel's calculation and charting engines, Word's document processing functions, and integrate your custom application logic with a user interface with which your users are accustomed.
Responding to Events
As mentioned earlier, the Visual Studio Tools for the Microsoft Office System loader links an instance of your code-behind class to the host application using a well-known entry point named _Startup. When the procedure is called, it passes two parameters. The first parameter is a by-value reference to the host application, either Excel.Application or Word.Application, typed as System.Object. The second parameter is a by-value reference to the host document, either Excel.Workbook or Word.Document, also typed as System.Object. Both references are assigned to strong-typed module-level variables: ThisApplication for the host, and either ThisWorkbook or ThisDocument for the document
In Visual Basic, the two variables (ThisApplication and ThisWorkbook or ThisDocument) are defined using Friend scope using the WithEvents keyword. The generated code in _Startup assigns the parameters using CType. This stores the application and workbook/document references for later use and hooks the event handlers up. In Visual C#, similar work is done. The object variables are stored away as internal members. In addition, events are hooked up manually using delegates, since C# does not have a function that corresponds to Visual Basic's WithEvents statement.
Regardless of language syntax issues, the overall results are the same. Your code-behind class has a standard set of events in place. You can easily add other events later. By default, an Excel code-behind class defines stubbed-out procedures for the Excel workbook's Open and BeforeClose events. Word document projects have the document's Open and Close events and Word template projects have three document-related events: New, Open, and Close. Each of these objects has additional events you can hook either at design time, or later, dynamically, at run time. Use the Visual Studio object browser to see which events are exposed by a particular object. Then use the Visual Studio Tools for the Microsoft Office System documentation to get more information about the events exposed and their behaviors.
Using Command Bars
Although hooking your code up to host or document events is useful, there are times when you want managed code to execute when the user clicks a toolbar button. Toolbars are known as command bars in Office and are a shared feature of all Office applications. Sometimes you will want to create your own custom command bar and buttons. Other times, you might just want to add a control to an existing command bar or menu. You can do both in your Visual Studio Tools for the Microsoft Office System solutions.
Adding a new menu bar to Excel requires a few simple steps. First, you must alias the Microsoft.Office.Core namespace down to something like Office:
' Visual Basic
' At the top of the ThisWorkbook.vb file.
Imports Office = Microsoft.Office.Core
// C#
// At the top of the ThisWorkbook.vb file.
using Office = Microsoft.Office.Core;
Next, you will need three module-level variables: one to reference Excel's main menu bar, one for the menu bar item, and one to actually represent the menu item whose Click event you will respond to.
' Visual Basic
' In the declarations section of the OfficeCodeBehind class.
Private MainMenuBar As Office.CommandBar
Private MenuBarItem As Office.CommandBarControl
Private WithEvents MenuItem As Office.CommandBarButton
// C#
// In the declarations section of the OfficeCodeBehind class.
private Office.CommandBar MainMenuBar;
private Office.CommandBarControl MenuBarItem;
private Office.CommandBarButton MenuItem;
Once the variables are defined, you will next write two procedures, one to create the custom menu bar item, and one to create the menu item. An example of the first procedure is listed below:
' Visual Basic
Private Sub InitMenuBarItems(ByVal Caption As String)
Try
Me.MainMenuBar = ThisApplication.CommandBars( _
"Worksheet Menu Bar")
Me.MenuBarItem = Me.MainMenuBar.Controls.Add( _
Office.MsoControlType.msoControlPopup, Temporary:=True)
Dim cbc As Office.CommandBarControl
cbc = DirectCast(Me.MenuBarItem, Office.CommandBarControl)
cbc.Caption = Caption
cbc.Visible = True
Catch ex As Exception
MessageBox.Show(ex.Message, _
ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
// C#
private void InitMenuBarItems(string Caption)
{
try
{
this.MainMenuBar = ThisApplication.CommandBars[
"Worksheet Menu Bar"];
this.MenuBarItem = this.MainMenuBar.Controls.Add(
Office.MsoControlType.msoControlPopup, Type.Missing,
Type.Missing ,Type.Missing, true);
Office.CommandBarControl cbc =
(Office.CommandBarControl) this.MenuBarItem;
cbc.Caption = Caption;
cbc.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
Tip This example points out one of many differences between code you write in Visual Basic and C# when working with the Office object models. Many members of the Excel and Word object models expect optional parameters. Although Visual Basic handles these just like VBA, C# cannot. For optional parameters passed by reference, you can pass Type.Missing in C#. For parameters passed by value, you'll need to specify the default value when calling methods with optional parameters from C#.
You will generally call this procedure from the ThisWorkbook_Open event handler that is predefined in your OfficeCodeBehind class. Once you have created your menu bar item, you can use code similar to the following to add a new menu item:
' Visual Basic
Private Function CreateButton( _
ByVal Parent As Office.CommandBarPopup, _
ByVal Caption As String) As Office.CommandBarButton
Dim cbc As Office.CommandBarControl
Try
cbc = Parent.Controls.Add( _
Office.MsoControlType.msoControlButton, Temporary:=True)
cbc.Caption = Caption
cbc.Visible = True
Catch ex As Exception
MessageBox.Show(ex.Message, _
ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return DirectCast(cbc, Office.CommandBarButton)
End Function
// C#
private Office.CommandBarButton CreateButton(
Office.CommandBarPopup Parent, string Caption)
{
Office.CommandBarControl cbc = null;
try
{
cbc = Parent.Controls.
Add(Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, Type.Missing, true);
cbc.Caption = Caption;
cbc.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
return (Office.CommandBarButton)cbc;
}
You can then call the aforementioned procedures from the ThisWorkbook_Open event, as in the following fragment:
' Visual Basic
InitMenuBarItems("&Travel Tools")
Me.MenuItem = Me.CreateButton( _
DirectCast(Me.MenuBarItem, Office.CommandBarPopup), _
"&Create Expense Report")
// C#
InitMenuBarItems("&Travel Tools");
this.MenuItem = this.CreateButton(
(Office.CommandBarPopup)this.MenuBarItem,
"&Create Expense Report");
this.MenuItem.Click +=
new Office._CommandBarButtonEvents_ClickEventHandler(
MenuItem_Click);
Finally, add code that runs when the user selects the new menu item. Below is example that instantiates a Windows Form in dialog mode.
' Visual Basic
Private Sub MenuItem_Click( _
ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
ByRef CancelDefault As Boolean) Handles MenuItem.Click
Dim frm As New frmExpReport
Select Case frm.ShowDialog
Case DialogResult.OK
' Create Report.
Case DialogResult.Cancel
' Do Nothing.
End Select
frm.Close()
frm.Dispose()
End Sub
// C#
private void MenuItem_Click(Office.CommandBarButton Ctrl,
ref bool CancelDefault)
{
frmReport frm = new frmReport();
switch (frm.ShowDialog())
{
case DialogResult.OK:
// Create report.
break;
case DialogResult.Cancel:
// Do nothing.
break;
}
frm.Close();
frm.Dispose();
}
Using VBA Objects on Documents
Command bars provide a way to have user-triggered events without modifying the documents structure. Excel worksheets and Word documents both enable the user (and the developer) to add VBA objects, such as command buttons and list box controls, to the surface of a worksheet or document (see Figure 1). These controls can be manipulated in code and the code can respond to a control's events.
.gif)
Figure 1. VBA objects hosted on an Excel worksheet
To use VBA controls from managed code, you must define an object reference of the object's class type. The definitions for these objects will not be available by default in a Visual Studio Tools for the Microsoft Office System solution. In order to use these objects you must reference the PIA for Microsoft Forms 2.0. You will find the PIA by using the Add Reference dialog box. The Microsoft Forms 2.0 Object Library type library is listed under the COM tab of the Add Reference dialog box. Adding the type library links the PIA from the GAC so Visual Studio .NET doesn't need to generate a standard interoperability assembly. Once the library is referenced, you might want to add a module-level imports/using statement to alias the extra namespaces exposed by the PIA. For example, if you wanted to define a command button variable, you could first add the following alias to avoid typing full namespace references:
' Visual Basic
Imports MSForms = Microsoft.Vbe.Interop.Forms
// C#
using MSForms = Microsoft.Vbe.Interop.Forms;
This alias enables you to type this reference:
' Visual Basic
Dim btn As MSForms.CommandButton
' Instead of:
' Dim btn As Microsoft.Vbe.Interop.Forms
// C#
MSForms.CommandButton btn;
// Instead of:
Microsoft.Vbe.Interop.Forms.CommandButton btn;
Tip When creating Office solutions, avoid using Imports or the using statement without an alias. The Microsoft Forms package has many interfaces that share class names with the controls in the System.Windows.Forms namespace; for example, the ComboBox and ListBox classes appear in both libraries.
Deployment
Visual Studio Tools for the Microsoft Office System enables you to automatically deploy code with a particular document. When you first create a Visual Studio Tools for the Microsoft Office System solution, the document's extended properties point to the compiled assembly using a document relative path that comprises the name of the document with a suffix of _bin. For example, if your project is named ExcelEvents, your assembly will be named ExcelEvents.dll and will be located in a folder named ExcelEvents_bin.
Visual Studio Tools for the Microsoft Office System solutions are comprised of two files: the document file and the assembly. There are three general deployment scenarios, all of which assume that the .NET Framework 1.1 and Office 2003 are already installed on the user's computer. The first scenario that many will find appealing is where the document file is located on the user's local hard drive and where the assembly is stored on a network share, as either UNC or HTTP/S. In the second scenario, both files are located on the user's hard drive. The third scenario is to deploy both files to a network location. All three scenarios have their pros and cons.
Deploying the document directly to the user while keeping the assembly on a remote share enables the user to have a private copy of the data (the document) while having access to the most up-to-date version of the components. In addition, because the .NET runtime supports offline use, traveling users can still run their application remotely. The disadvantage is that the user must remember to open the document once while connected, giving the loader an opportunity to download the assembly to the user's download cache.
Putting both the document and the assembly files on the user's computer provides for guaranteed availability. Once deployed, the user can run the solution regardless of network connectivity. However, this increases deployment headaches because updates to the assembly require some out-of-band redistribution of the assembly so the loader can use the updated version.
Enabling a user to access both the document and the assembly from a central network share is a good solution when the user is always connected to the network. This type of connectivity enables multiple users to take advantage of the collaboration features provided by both Excel and Word. Any updates required to either the document or the assembly will be available to the users immediately.
Regardless of which scenario you choose, you will need to make modifications to the user's local security policy. To protect users from malicious code attached to a document, the Visual Studio Tools for the Microsoft Office System loader requires that an assembly must be trusted by the local security policy. When you first create a Visual Studio Tools for the Microsoft Office System solution, Visual Studio .NET modifies the security policy on your computer to facilitate development and debugging. You will be required to modify the user's settings based upon the deployment model you've chosen.
Security
Before any managed code is loaded, the Visual Studio Tools for the Microsoft Office System loader checks the local security policy to see whether the assembly referenced by the document has been given Full Trust.
This new model is a major boon for developers who want to integrate their solutions with Office 2003. Code must be explicitly trusted before it gets a chance to execute. Each user's computer has a set of rules about which code is allowed to run and what that code can do.
When code is loaded, the common language runtime gathers evidence. Evidence comes in two flavors: host, such as where the code came from, and assembly, whether or not it has a cryptographic signature. Evidence is mapped to a particular policy. There are four policies: Machine, User, Enterprise, and Host. Each policy can have zero or more code groups. Code groups provide a mapping of evidence to a permission set. Permission sets contain one or more permissions. A permission is the right to do something, such as the right to execute or read a file from disk. Using the evidence gathered, the runtime maps the assembly to a code group, which in turn maps it to an intersection of all four policies. This mapping determines whether or not code can execute, and if it can, what it has permission to do.
Assemblies used by a Word 2003 or Excel 2003 document require Full Trust, regardless of which deployment model you follow. Generally, you will trust a specific location with execute permission. Then trust a specific assembly or set of assemblies based upon a strong name.
What's Going on Inside
At this point, you should have a good idea of what a Visual Studio Tools for the Microsoft Office System solution is and what you can do. Digging deeper into the Visual Studio Tools for the Microsoft Office System architecture, it becomes obvious that the solutions you build are document-centric. Although COM add-ins provide host-specific solutions that can act upon documents, Visual Studio Tools for the Microsoft Office System solutions focus on executing custom code in the context of a particular document (or template in the case of Word 2003).
When you first create a Visual Studio Tools for the Microsoft Office System solution, the wizard creates a solution and project using the name you provide. A new feature of Visual Studio .NET 2003 is the ability to provide a different solution name and folder than that of your project. This feature is supported by the Visual Studio Tools for the Microsoft Office System New Project wizard.
When you run the wizard, you'll see an option labeled Security Settings. Generally, you will not want to change the settings available on this page of the wizard. If you clear the check box labeled "Update local security policy to enable assembly to execute", you will receive a warning. Ignoring the warning prevents you from running or debugging your solution.
So what does that little check box do? The wizard doesn't modify the security policy at project creation time. Instead, it waits until you compile and debug a project at least once. If you have more than one Visual Studio Tools for the Microsoft Office System project in a Visual Studio .NET solution, only the projects that are debugged at least once will have a code group entry placed into the computer's security policy. At compile time, Visual Studio Tools for the Microsoft Office System modifies your user policy to include a new code group called Office_Projects (see Figure 2).
.gif)
Figure 2. The .NET Configuration 1.1 tool with the Office_Projects code group
The Office_Projects code group's membership condition is set to All Code. The permission set is set to Nothing (see Figure 3). If you expand the Office_Projects code group, you will find a node using the URL of your project as its name. The membership condition is set to URL. The URL specified is the path from where your assembly will be executed, and the custom bin directory created at compile time is the project name with the added suffix of _bin. This code gives all assemblies in the directory the right to execute.
.gif)
Figure 3. Office_Projects Permission Set
Finally, there is a final child code group whose name is the complete URL of your assembly. This child code group also uses URL as its membership condition. The child code group's membership condition is set to the complete path of the assembly, including the assembly name. Its permission set is set to Full Trust (see Figure 4).
.gif)
Figure 4. A managed assembly with a Permission Set of FullTrust
Once your project is set up, you will be faced with either a ThisDocument or ThisWorkbook code file with an extension appropriate for the language you choose. Regardless of language or project type, you will have a single class named OfficeCodeBehind. This class exposes the _AssemblyLocation0 and _AssemblyName0 methods that the Visual Studio Tools for the Microsoft Office System loader will look for when loading and unloading your code.
By default, when a host application starts, neither the Visual Studio Tools for the Microsoft Office System loader nor the common language runtime are loaded into the process. When the user opens a document that contains the custom document properties, the Visual Studio Tools for the Microsoft Office System loader is brought into memory. The loader brings the runtime into memory and allocates a default AppDomain. At this point, the runtime will only be unloaded if the host process is torn down. The first AppDomain is named WRDomainSetup. This AppDomain is used for setting all future AppDomains used in the host. With the runtime loaded, a second AppDomain is created. It is given a name that matches the name of your document.
An interesting implementation detail is that the AppDomain's SetupInfomation.ShadowCopyFiles property is set to True. An assembly loaded from the local hard drive by the Visual Studio Tools for the Microsoft Office System loader experiences the same behavior as an assembly loaded from a remote URL. The Visual Studio Tools for the Microsoft Office System-loaded assembly is copied into a shadow directory. The shadow directory is part of the current logged-in user's assembly download cache. You can examine the download cache by navigating to your Windows directory using the Windows Explorer. From there, open the Assembly folder and click the plus sign next to it. You will see a sub-folder called Download.
What you should see is an aggregated view of multiple folders located under the Documents and Settings folder, partitioned by user. Before the assembly is loaded, a copy is made and put into the download cache of the user if the version in the cache is older. You can verify where the copy came from by examining the CodeBase property of the assembly where your code is running. The assembly's Location property will tell you where the assembly actually lives.
Tip You may want to take control of your download cache. The first thing to do is change your assembly's build number—adjustable by setting the AssemblyVersion attribute in the AssemblyInfo file—to a static number. If you use the default, you will get a new version number every time you compile and debug. This, in effect, puts a new, versioned copy in your download cache without removing the previous versions. You need to manually remove old versions from the cache using the command-line tool Gacutil.exe with the /CDL switch. Also, the download cache is segmented by user. Clearing your cache does not affect other users on the same computer.
By loading a copied version of the assembly, you can update the original version so that the next time the document is loaded, the most current version is used. In fact, since the copied version is the one in memory, you don't have to stop the user from using the host or the document.
Once the assembly is loaded, the Visual Studio Tools for the Microsoft Office System loader reads the assembly-level attribute, called System.ComponentModel.DescriptionAttribute. The description for this attribute contains the fully qualified type name of the startup class as well as the version number of the Visual Studio Tools for the Microsoft Office System tools the project was built against. In this first release, the version number is ignored. However, the version number must exist or the load process will fail. Using the class name, the Visual Studio Tools for the Microsoft Office System loader creates an instance of the class specified in the attribute.
The OfficeCodeBehind Class
Regardless of language or document type, each Visual Studio Tools for the Microsoft Office System project contains a default constructor and four methods in a collapsed region labeled Generated initialization code. The default constructor is empty and can be deleted if you don't need it. The four methods are _Startup, _Shutdown, and two overloaded versions of FindControl. _Startup is used to hook module-level variables representing the host application and the document that is linked to the assembly. _Shutdown simply nulls out the references to the host and document object variables hooked in _Startup. Both of these methods need to exist for the proper functioning of your Visual Studio Tools for the Microsoft Office System projects.
The FindControl method is used to locate an OLE Control located on the currently referenced host document. A little-known feature of both Excel and Word is that you can place VBA controls onto the surface of a worksheet or document. You can do this using the Control Toolbox toolbar. Once this toolbox is visible, you can use it to place controls used on your document. Figure 5 shows the Control Toolbox in Microsoft Office Word, in design mode, with a new command button placed on the surface of the current document.
.gif)
Figure 5. The Control Toolbox in Word with a new command button on the document surface
Using the FindControl method, you can locate a control, modify its properties, execute methods, and write code to respond to events such as a command button's click event. Regardless of host application, a version of FindControl takes the name of the control you would like to find as a string and returns a System.Object reference, if successful. You should always check to see whether the object returned by FindControl comes back initialized because the method ignores any exceptions that occur while searching for the control.
The first version of FindControl delegates the real work to the second overloaded version, which takes a control name as its first parameter and a reference to a host document as the second parameter. This second FindControl accepts a typed variable specific to the type of project, whether Word or Excel, you are working with. The simpler version of FindControl always passes the document or workbook that is stored in the module level to the ThisDocument or ThisWorkbook variable. If you don't like FindControl ignoring exceptions, you could modify the code; although this is discouraged as you would have to modify the FindControl in every project and it would exhibit a different behavior than expected by other developers.
In addition to the aforementioned methods, there are also event handlers specific to the host document.
Event Ordering
When an instance of the OfficeCodeBehind class is first instantiated, the default constructor is called first. Although you could add additional non-default constructors, there is currently no way to have the Visual Studio Tools for the Microsoft Office System loader execute them. After the class is instantiated, the _Startup procedure is executed. It's important to note that the code will not have access to the host application or document until the _Startup procedure executes. Once _Startup has executed, host and document events that you choose to respond to will fire. The events that are executed depend upon which events you are listening to.
Once the document is closed by the user, additional events occur. First, any host or document events related to the document closing or the host application exiting are raised. After these events have been raised, the _Shutdown method is called, disconnecting the code from the host and document. Any code in the AppDomain's DomainUnload event handler will then execute. After that, the object will be finalized as the garbage collector comes through.
Warning Event execution between VBA code and managed code is non-deterministic. Although empirical evidence suggests that VBA events occur first and are followed by the corresponding managed event handlers, there is no guarantee that will always be the case. Because of this, you should try to use only one type of event handler: either managed handlers or VBA handlers, but not both. In addition, certain events support cancel arguments that are provided as a Boolean value. VBA code can cancel an event, such as a workbook's close process. Although the parameter exists in the managed event handler, modifying the argument has no effect. In addition, even if the VBA code cancels the close event, the managed BeforeClose event still fires, yet the class will not be torn down. In fact, the managed BeforeClose event handler executes as many times as necessary until the workbook is actually closed!
Of Namespaces and Project Names
When you first create a Visual Studio Tools for the Microsoft Office System solution, the name of your project is also used as the name for your root namespace. In Visual Basic projects, this can be seen by accessing the project properties box. Any additional types added to your project become a part of this namespace.
Visual C# projects, on the other hand, simply expose the namespace directly in the ThisWorkbook.cs or ThisDocument.cs source file using the namespace statement. In either case, if you decide to change the namespace in which the OfficeCodeBehind class lives, you need to make sure to change the namespace in the System.ComponentModel.DescriptionAttribute assembly-level attribute. The fully qualified type name for the OfficeCodeBehind class is stored there.
Another item to watch out for is the name of your assembly. When you first debug your project, a Code Group is defined enabling your assembly to execute. If you change the name of your assembly, the Code Group in your security policy is not updated. You will need to manually adjust the Code Group. The good news is that your host document's _AssemblyName0 property will be updated correctly. But, it just won't load until you adjust your security policy. If you do change your assembly's name mid-development, you'll need to remove the existing assemblies using the original name.
Sharing Visual Studio Tools for the Microsoft Office System Projects
Developing solutions is rarely a job done by one person. Often you will want to share your projects with another developer. By now, you're quite aware that they'll need to define a Code Group in their local security policy to trust your project's output. However, one little item that might go unnoticed is the Start external program setting of the Project Properties dialog box in Visual Basic or the Start Application property in Visual C#. Both settings live off the Debugging node under the Configuration Properties folder in their respective dialog boxes. You'll note that this setting is hard-coded to point to the location of the appropriate Microsoft Office host application on the computer where the project was first created. If that path does not exactly match between your computer and another developer's, you will have to manually go in and change the setting when you move a project between computers.
Linking Your Assembly to Your Document
When you first create a Visual Studio Tools for the Microsoft Office System project, the wizard sets the Assembly Link Location property to a relative path below the source code for the project. This information is embedded in the host document as custom properties.
**Tip **If the path to the assembly exceeds 255 characters, there will be additional properties suffixed with an increasing numeric value (1, then 2, then 3, and so on).
In addition, the Office Document property is set to the fully qualified path where the document lives. If you change the location of the host document, you will need to modify the Command line arguments field of the Project Properties dialog box in Visual Basic or the Command Line Arguments field in Visual C#. Once again, both settings live off the Debugging node under the Configuration Properties folder in their respective dialog boxes. If you change either of these settings, you need be prepared to adjust your security policy accordingly.
Conclusion
With Visual Studio Tools for the Microsoft Office System, you can create compelling document-centric solutions for your users that leverage both the power of the Microsoft Office System and the .NET Framework 1.1. Using either Visual Basic or Visual C#, you can develop applications faster and have a wider range of options for automatically deploying your solutions. By using the rich security model exposed by the common language runtime, you can create more secure solutions that only execute code you define as acceptable. All the while, your users will feel comfortable using applications they already know and rely on in their day-to-day tasks.