Managed WorkbooksConceptManaged 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 _AssemblyName0, giving the directory and filename of the assembly 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\share 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 brings a number of benefits:We can update everyone's code by copying a new version of the assembly to the network sharewe no longer need to track who the document has been sent to in order to distribute 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 better protection for our 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 to access the network share in order to download and run the assembly, which makes it much harder to use the document outside the corporate network, such as taking it home to work on, or sharing it with partner companies.Every computer has to have its .NET security permissions set to allow VSTO solutions to run from the network share. In a corporate environment, this could be administered centrally by including the configuration with a login script. A small company or home user would have to configure the settings manually, requiring detailed knowledge of the .NET and VSTO security model (see later). A Hello 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 Excel workbook, as shown in Figure 22-1. Figure 22-1. The Visual Studio.NET NewProject Dialog 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 SolutionWhen you press F5 to run the project, Visual Studio builds the assembly and starts Excel, passing in the workbook to open. Excel opens the workbook, checks the _AssemblyLocation0 and _AssemblyName0 custom properties, loads the assembly, reads the OfficeStartupClass assembly attribute to find the class to start and calls the _Startup procedure in that class. Excel passes in a reference to itself and a reference to the workbook it opened, which the _Startup procedure assigns to two module-level WithEvent variables, ThisApplication and ThisWorkbook. That gives us both application-level and workbook-level events by default. Excel then raises the workbook's Open event, which we handle in the ThisWorkbook_Open procedure to show our "Hello World" message. The Default VSTO 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 practice, the default template really doesn't work well for people used to working in VBA. We're used to having global objects such as Application and ThisWorkbook and being able to refer to worksheets by the code name. It also mixes up the procedures required for the communication with Excel, the event handling code for the workbook and some standard functions. Our best practice recommendation would be to separate out these functional areas 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 comprise the following:VSTOHooks.vb contains the procedures that Excel calls to start up and shut down the VSTO project.CExcelApp.vb contains procedures to set up and handle the Excel Application's eventsCThisWorkbook.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. MGlobalsListing 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-2. The MGlobals Code
VSTOHooksListing 22-3 shows the code contained in the VSTOHooks.vb file. Listing 22-3. The VSTOHooks CodeThe line that 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 CodeThe 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[View full size image]  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.Application. 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 ThisWorkbook.Workbook to refer to the Excel workbook we're linked to and Sheet1.Worksheet to refer to the worksheet handled by the Sheet1 variable.CThisWorkbookThe 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 CodeWithin 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 modules for each of the worksheets follow the same structure as the CExcelApp and CThisWorkbook classes, so won't be repeated here. When setting up the VSTO project, you will need to add a worksheet class for each of the worksheets in your solution, adding global variables to refer to them and adding code to Workbook_Open to initialize them. If the worksheet has any ActiveX controls (including controls from the Control Toolbox), the Sub New() procedure should be used to set up their event hooks, as shown in Listing 22-6, for a worksheet that contains a command button btnShow. Listing 22-6. A Worksheet Class with a CommandButton Event Handler
MStandardCodeThe MStandardCode module shown in Listing 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
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 to do the following:Give all our menu items the same Tag property, to uniquely identify them as belonging to our add-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 our menus, create a new instance of the class for each combination of ID and Tag that we use. If we're not using any built-in menu items, we would only 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 folder. Listing 22-8. Collaborative Use of Command Bars
 |
لطفا منتظر باشید ...
![[View full size image]](/image/library/english/13850_22fig02_alt.jpg;380140){kind=link}