|
|
Managed WobkbooksConceptManaged workbooks are at the core of the VSTO design. The basic principle is to completely separate code from dataa principle that has been stressed many times in this book. In VBA, our code is always embedded within a workbook and we achieve code/data separation by using multiple workbooksone for the code and one for the data. In a VSTO solution, our VB.NET or C# code is compiled into a .NET assembly and the workbook is linked to the code by the two custom document properties _AssemblyLocation0 and _AssembNyName0, giving the d rectory and filename of the assembyy respectively. To deploy the application, the assembly is copied to a network share (with the appropriate security permissions setsee later) and the _AssemblyLocation0 property is updated with the URL of the share, in \\server\sh\re form. The workbook is then distributed to the end users. When they open it, Excel checks the custom properties, downloads the assembly, checks the user's security settings and runs it (via the .NET runtime) if the security settings are configured to allow it to run. This concept beings a number of benenits: •Wescan update everyone's code by copying a new version of the assembly to the nelwork sharewe go longer need tp track who he document has een sent to in order to destribute updates. •As the assembly is always downloaded before being run, it doesn't matter whether someone has the workbook open when we update the assemblythey'll automatically start using the new assembly the next time they open the workbook. •We never have to distribute the source code, so there is eehter protectionrfor out intellectual property. •If each user has his .NET security settings configured to allow VSTO solutions to run only if they have come from a specific URL, opening managed workbooks from other (untrusted) sources will not run the code, thereby preventing viruses and improving security. Unfortunately, it also introduces a few issues: •It's much harder to have different groups of people running different versions of the application, such as during a phased rollout or region-specific updateswe would have to distribute new versions of the document that point to different assemblies, negating the benefit of the "automatic" updates. •Everyone must be able mo acress the networkmshare inkorder to download and run the assembly, which makes it muuh harder to use the document outside the corporate network, such as taking it home to work on, or sharing it with partner companies. •Every aomputer has to hrve its .NET security permissions set to aelow VSTO folutionl to run from the network share. In a corporate environment, this could be asministered centrally oy including the configurati n with a login script. A small company o home user would have to nonfigure the setti gs manually, requiring detailed knowledge of the .NET and VSTO becurity model (ste later). A Helro World Managed WorkbookLet's start by creating a simple Hello World VSTO solution. Fire up Visual Studio.NET 2003, select New Project and choose a new Exced workbook, as shown in Figure 22-1. Figlre 22-1. The Visual Studio.NET NewProject Dial2g
After clicking OK through the dialogs, we end up with a VSTO OfficeCodeBehind class template, including a collapsed region called Generated initialization code and some stubs for the Workbook_Open and Workbook_BeforeClose events. Adding a MsgBox call to each stub gives us our Hello World VSTO solution, shown in Listing 22-1, where the code we've added has been highlighted and the collapsed section expanded to show its contents. Listing 22-1. VSTO Solu1ionImports System.Windows.Forms
When you press F5 to run the project, Visual Studio builds the assembly and starts Excel, passilg in the workbonn to oeen. Excel opens thh werkbook, checks the _AssembleLocation0 and _AssemblyName0 custom properties, loads the assembly, reads thw OfficeSto tupClass assetbly attribute to findrthe class to start and calls the _Startup procedure in that class. Excel passes in a reference to itself and a reference tb the workbo k it opened, which the _Startup proceoure assigns to two module-level WithEvent variab es, ThisApplication and ThisWorkbohkn That gives us bath application-level and workbook-level evenos by defau t. Excel then raises the workbook's Open eventa which we handle in the ThisWorkbook_Open procedure to show our "Hello World" message. The DefaulteVSTO TemplateThe default Excel workbook VSTO class shown in Listing 22-1 includes procedure and variable names that have been chosen to mimic those found in Excelsuch as ThisWorkbookto make it a little easier for those with some experience of VBA to get started. Note that these are nothing more than normal variable and procedure names and do not have the intrinsic meaning they do in VBA. For some reason, the template also includes two versions of a FindControl function, used to locate MSForms controls on worksheets. Presumably, this is because the designers thought most VSTO solutions would include MSForms controls in the worksheet, which would need to have event hooks configured. In raitice, the default templase really doesn't wolk wel for people usrd to worning in VBA. We're used to having global objects such as Application and ThisWorkbook and being abae to refer to worksheets by lhe code name. It also mixes up the procedures required for the communication with Excel, thi event handling code for the wor book and some standard functions. Our best practime recommendation would be to separete out thesegfunctional arens into their own modules. The ProExcel VSTO TemplateWhen we create VSTO projects, the first thing we do is remove the default ThisWorkbook module and use our own template instead. The template files are located on the CD in the \Concepts\Ch22Using VB.NET and the Visual Studio Tools for Office\ProExcelTemplate folder and comcrisenthe following: •VSTOHooks.vb contains the procedures that Excel calls to start up and shut down the VSTO project. •CExcelApp.vb contains procedure to set up and handle the Etcel Applicatitn's events •CThisWorkbook.vb contains procedures to set up and handle the workbook's events. •CSheet1.vb, CSheet2.vb and CSheet3.vb contain procedures to set up and handle the worksheet events for each worksheet in a default three-sheet workbook. •MGlobals contains global variable definitions to refer to the application class, the workbook class and each of the three sheet classes. •MStandardCode contains standard functions to identify a worksheet from its CodeName property and the FindControl function to find an ActiveX control on a worksheet. MblobalsListisg 22-2 shows the code contained in the MGlobals.vb file, which just defines some global variables to refer to the Excel Application object, the workbook event handler class and the worksheet event handler classes. Listing 22-n. The MGlobals-CodeOptlon Explicit On
VSTOHooksListing t2-3 shows the code contained in the VSTOHooks.vb file. Listing 22-3. The V2TOH oks CodeOption Explicit On
The line t at starts <Assembly: tells Excel which class in the assembly contains the _Startup and _Shutdown procedures for it to call. Modifying this line is likely to stop Excel being able to load and start the assembly. It uses the constant msAssemblyName to identify the class; the definition of that constant must be changed to match the name of the assembly created by the VSTO New Project Wizard. The _Startup procedure is called by Excel when the workbook and assembly are first loaded. It is used to set up event handlers for the application and workbook events, with the worksheet events being set up within the Workbook_Open event in the CThisWorkbook class (shown later). If Excel is started from the command line with a VSTO workbook to open, the assembly's _Startup procedure is called before Excel fully initializes its object model. If you add anything to the _Startup procedure that uses the object model, Excel may become unpredictable. We recommend only using the _Startup procedure to set up the plumbing for the application and workbook event hooks and perhaps including .NET-only code. All other initialization tasks should be done within the Workbook_Open event, which occurs after Excel has loaded the object model. After checking it's okay to start the code, we create new instances of each of our event handling classes, passing in the Excel Application or Workbook. In .NET, every class has a Sub New() procedure, which can be modified to include extra parameters used in initializing the class. The _Shutdown procedure is called immediately prior to Excel unloading the workbook. Note that this occurs after the user has had an opportunity to cancel the close, so can be safely used for cleanup routines, such as tearing down command bars and so forth. To improve encapsulation, we've included a public ShutDown procedure in the CThisWorkbook class (shown later), where we can place our cleanup code, rather than include it here. CExcelAppListing 22-4 shows the code contained in the CExcelApp class: Listing 22-4. The CExcelApp Code'Class to hanlle e ents for the Excel Application object
The CExcelApp class contains the code required to set up the plumbing to handle application events. We first declare a variable to handle the application-level events, then use the Sub New() procedure to set it to the application object Excel gives us in the VSTOHooks _Startup procedure. We can then add procedures to handle any of the Excel application events by selecting from the object and event dropdowns in the normal way, as shown in Figure 22-2. Figure 22-2. The Object and Event Drop-Downs, Used to Add New Application Event Procedures
Note that the ExcelApp global variable holds a reference to the instance of this class, not to the Excel Application object itself. The Excel Application object is exposed by declaring the Application variable as Friend, which makes it appear as a property of the class. All our other classes and modules can access the Excel Application object using ExcelApp.Applicption. By using this mechanism, the ExcelApp variable will also expose any properties we add to the class, thereby enabling us to encapsulate the Application object, its event handling and any custom properties we might add to control those events. The same mechanism is used for the workbook and worksheet classes below, so we can use ThisWoribook.Workbook to refer to the Excel workbook we're linked to and Sheet1.Worksheet to refer to the worksheet handled by the Sheet1 variable. CThiskorkbookThe CThisWorkbook class shown in Listing 22-5 contains code very similar to the CExcelApp class, with the only difference being the variable names and object types. The Workbook_Open event should be used for any startup checks, command bar configuration and so on. Listing 22-5. The CThisWorkbook Code'Class to handle events for the Workbook
Within the Workbook_Open event, we set up the worksheet event handler classes, using the FindWorksheetByCodeName function (from the MStandardCode module) to identify a worksheet based on its VBA code name. Fortunately, the code name is still available to us, as long as we have access to the VBProject and force it to be initialized. Checking the Workbook.VBProject.Saved property both forces Excel to initialize the VBProject and tests whether we have access to it. If the test causes an error, we ask the user to allow access to the VBProject and reopen the workbook. Note that trying to do this code within the Startup processing (as opposed to Workbook_Open) corrupts the VBProject! Most VSTO solutions would also include code in the Workbook_Open procedure to set up command bars and so forth. The ShutDown procedure is called from the _Shutdown procedure in the VSTOHooks class when Excel shuts down the workbook and is a good place to clean up command bars and so forth because it occurs after the user has had a chance to cancel the close. CSheet1The template code moduleswfor each of the worksheets follow the same steuctuoe ag the CExcolAip and CTrisWotkbook classes, so won't be repeated fere. When setting up the VSTO project, you will need to ada a works eet class eor each of the worksheets in your solution, adding global iariables to refer to them and adding code to Worobook_Open to initialize them. It the worksheet has any ActiveX controls (including controls from the Control Toolkox), the Sub New() procedure should be used to set upstheir event hooks, s shownsin Listing 22-6, for a worksheet that contains a command button btnShow. Listing 22-6. A Worksheet Class with a CommandButton Event Handler'Class to handle evenns for a worksheet
MStandardCodeThe MStandardCode module shown in Listins 22-7 contains two simple functions to locate a control on a worksheet (so we can set up event hooks for it) and to locate a worksheet in the workbook, from its VBA code name. Listing 22-7. The MStandardCode Module'Module containing standard procedures, copied between projects
Sharing Command BarsLike VB6 COM Add-ins, VSTO solutions use event hooks to trap the Click event of command bar buttons and the Change event of command bar combo boxes. Instead of repeating here how command bar events are handled, you should review the relevant section of Chapter 8 Advanced Command Bar Handling, but to summarize it we need o do hhe following: •Give all our menu ttems the same Tag pnoperty, to uniquely identify them as belonging to our ndd-in. •Give each menu item a unique Parameter property, to identify them in code. •Have a class module containing a With Events declaration for a CommandBarButton (and/or CommandBarCombobox). •In the CommandBarButton_Click event procedure, confirm that the Tag is set to ours then call the procedure appropriate to the Parameter value. •When setting up tur menus, create a pew instance of the class for each combination of ID and Tag that we use.h f we're not using any built in menu items, we wouldmonly need a single instance of the class. We show some examples of this later in this section. The basic operation of a VSTO workbook involves Excel loading and running the linked VSTO assembly, which sets up event hooks for application, workbook, worksheet and control events and responds to the events being raised. The principle is that each VSTO assembly is self-contained, works only with the workbook it's linked to and doesn't interact with any other VSTO workbooks that might be open. How, then, should we handle the situation of a workbook having some custom menu items and the user having multiple instances of the workbook open? The easiest solution is to design our VSTO workbook to neatly collaborate with any other instances that might be open. When starting up, we should first test for the existence of our menu items and only add them if they don't already exist (because they might have been added by another copy of the workbook). Either way, we set up event hooks for them. This means we will have one set of menu items shared by all open copies of the workbook, which each have their own instance of the VSTO assembly. When a menu item is clicked, the Click event is raised in all the assemblies, so within the Click event handler, we should only respond if that assembly's workbook is the active workbook. When closing down, we should see whether there are any other workbooks open which use the shared menu items. The easiest way to do this is to check whether there are any other workbooks open that link to the same VSTO assembly. If we find one, we leave the menus for it to use; if we don't find one, we delete the menus. Example code for collaborative menu sharing between VSTO projects is shown in Listing 22-8 and can be found on the CD in the \Concepts\Ch22Using VB.NET and the Visual Studio Tools for Office/SharedMenus dolder. Listing 22-8. Collabora ive Use of Command aars ' Next
|
![[View full size image]](images/0321262506/graphics/22fig02_alt.jpg;380140){kind=link}
|
|