Connecting to a Managed Component from Excel 2002 Visual Basic for Applications

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Dennis Angeline, Misha Shneerson, and Siew-Moi Khor
Microsoft Corporation

October 2002

Applies to:
   Microsoft® Office XP

Summary: Learn how to create a managed component that acts as code behind an Office XP document. This technique is illustrated through the presentation of an Excel 2002 worksheet containing a visual design control. Some security considerations pertaining to such implementation are also discussed. (14 printed pages)

Download or browse odc_xlnet.exe

Contents

Introduction
System Requirements
Running an Unsigned VBA Macro
Try It Out
   How to Install the Sample
   How to Use the Segment Viewer
How Does All of This Work?
Security Considerations. Potential Security Attack
Conclusion

Introduction

You can use the Component Object Model (COM) interop services provided by the common language runtime to enable managed code to be used from COM and conversely, COM code be used from managed applications.

The sample Segment Viewer download included with this article is an example of how this can be done. The Segment Viewer is a managed component that can be consumed by COM clients through its COM-like interface. It demonstrates how to use the COM interop services to enable managed code to be used from COM, and conversely, how to use COM components from within a managed application.

In this article we will also discuss security considerations related to implementing a solution using the approach shown in the Segment Viewer sample. It is assumed that the reader is familiar with Microsoft® Office XP Visual Basic® for Applications (VBA) and Microsoft .NET technologies.

Systems Requirements

To run the sample, you will need the following software installed on your computer:

  • Microsoft Windows® XP or Microsoft Windows 2000 and related service packs (SPs)
  • Microsoft Office XP and related SPs
  • Microsoft .NET Framework and related SPs

Additionally, if you want to view the sample code, you will also need Microsoft Visual Studio® .NET installed. If you want to compile the solution, you will also need the Microsoft Office XP Primary Interop Assemblies (PIAs) installed on your machine.

Running an Unsigned VBA Macro

The Microsoft Excel 2002 macro and components in the sample download are unsigned. To run an unsigned Excel 2002 VBA macro, the security settings in the Excel 2002 Security dialog box (on the Tools menu, point to Macro, and click Security) must be set to Medium, with the Trust all installed add-ins and templates check box cleared. It is strongly recommended that you do this only in a testing environment. After you are done testing, set the security level back to High.

Caution   By setting the security level to Medium, with the Trust all installed add-ins and templatescheck box cleared, users will have the choice to either enable or disable unsigned COM add-ins and VBA macros when they are prompted. If your security level is set to High,****with the Trust all installed add-ins and templates check box cleared, all unsigned COM add-ins and VBA macros will be disabled automatically. Therefore, it is strongly recommended that all users keep their security levels set to High with the Trust all installed add-ins and templates check box cleared.

Try It Out

To try out the Segment Viewer sample, first download the sample file (odc_xlnet.exe) associated with this article, and then run this self-extracting package to extract the Segment Viewer sample solution onto your local machine.

How to Install the Sample

To install the Segment Viewer sample, run SegmentInExcel.msi in the <Installation Path>\Setup\Release directory.

Note   The .NET Framework needs to be already installed on the target machine in order for the setup to succeed.

The setup program will install the necessary files on the user's machine. It will also register the necessary assemblies in the registry so that COM-clients will be able to access those using the usual COM loading application programming interfaces (APIs).

As shown in Figure 1, the default installation location is C:\Program Files\WidgetWare\SegmentViewer. You can alternatively choose to install the components in a directory of your choice.

Aa140056.odc_xlnet01(en-us,office.10).gif

Figure 1. Installation directory selection dialog box

How to Use the Segment Viewer

Before running the Excel macro included in the sample, make sure your Excel 2002 security settings are set correctly as discussed in Running an Unsigned VBA Macro section.

Once the component is installed, from the installation directory, open the SampleSeg.xls Excel file. The SampleSeg.xls file contains the specification for a petroleum line system that a fictitious company is trying to provide a cost estimate for as shown in Figure 2 below.

Aa140056.odc_xlnet02(en-us,office.10).gif

Figure 2. The SampleSeg.xls spreadsheet

The spreadsheet contains information about the four sections of a petroleum line (each section is shown in green). The first section contains nine segments. Each segment of that section of the petroleum line has a different type of shielding or armor and some segments have none.

Cost of armoring per foot depends on the type of armor used, with Lite being the cheapest and Ultra Heavy the most expensive. The cheapest alternative is not to have any armoring.

Therefore, the cost to build any section of the petroleum line is impacted by the type of armor used and the length of the segment that uses that type of armoring.

Rather than editing the specification in a tabular form, the Segment Viewer allows the user to manipulate the petroleum line sections using a graphical tool. By selecting the petroleum line section and right-clicking the mouse, the Segment Viewer is activated.

For example, select cells B13 to B22 then right-click. When you right-click, the Segment Viewer should pop up. If the Segment Viewer did not pop up, then the Segment Viewer component was probably installed incorrectly.

Aa140056.odc_xlnet03(en-us,office.10).gif

Figure 3. The Segment Viewer component

Once the Segment Viewer is activated, you can drag the segment dividers to change the length of a segment. You can also right-click on the segment to change the type of armor for any part of the segment as shown in Figure 4. You can also have the length of each segment displayed.

As changes are made on the graphical image, the values in the table on the Excel spreadsheet are updated simultaneously.

Aa140056.odc_xlnet04(en-us,office.10).gif

Figure 4. The Segment Viewer pop-up menu options

How Does All of This Work?

The Segment Viewer sample solution consists of three separate projects. The managed components are written in Microsoft C#. To view the sample code in the Visual Studio .NET integrated development environment (IDE), open the SegmentInExcel.sln file located in the <Installation Path>\ folder.

Segment Control Component

The Segment.csproj project (located in the <Installation Path>\Segment\ folder) contains the code for the Segment control itself. The Segment control knows nothing about Excel and is simply a graphical tool for manipulating segments. It could, and has been used with other projects unrelated to Excel.

Windows Form Component

The SegmentInExcel.csproj project (located in the <Installation Path>\SegmentInExcel\ folder) contains a form that hosts the Segment control and interacts with Excel. It is the part of the tool that is registered for COM interop operations during installation.

In this example, we use a small start up VBA code routine to hook up the functionality of SegmentInExcel project to the Excel document. When the SampleSeg.xls file opens, the workbook's Workbook_Open event is fired. Our custom code will then instantiates the SegmentInExcel.WorkbookConnect object as a regular COM component. Next it calls the BindToEvents method, which allows the managed component to bind to the Excel events. The VBA code snippet below shows how this is done:

Private Sub Workbook_Open()
...
    Dim SegmentViewer As New SegmentInExcel.WorkbookConnect
    SegmentViewer.BindToEvents ThisWorkbook
...
End Sub

The BindToEvents method registers all right-click events produced in the Excel worksheet. When a right-click occurs, the WorkbookConnect.OnRightClick method is called. The OnRightClick method captures the current selection, creates the SegmentViewer component, passes the selection into SegmentViewer component and then shows the form. The code snippet below taken from the WorkbookConnect.cs file shows how this is accomplished:

public void BindToEvents(Workbook workbook)
{
    worksheet = (Worksheet) workbook.Sheets[1];
    worksheet.BeforeRightClick += 
        new DocEvents_BeforeRightClickEventHandler(OnRightClick);
}
internal void OnRightClick(Range target, ref bool cancel)
{
    try
    {
        if (target.Rows.Count < 2)
            return;

            cancel = true;
            SegmentViewer sv = new SegmentViewer();
            sv.SetRanges(target);
            sv.ShowDialog();
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message, "Segment Viewer");
    }
}

The SegmentViewer form (see SegmentViewer.cs file) parses the selection and creates an array of Segment objects, which are passed to the control (see the Control.cs and Segment.cs files) to be displayed as shown in the following code snippet taken from Segment.cs file:

...
Segment[] Segs = new Segment[RowCount];
for (int r = 1; r <= RowCount; r++) 
{
    Segs[r - 1] = new Segment(
        (ArmorType)GetInt(ContentRng, GetAddr(3, r)),
        GetInt(ContentRng, GetAddr(1, r)),
        GetInt(ContentRng, GetAddr(2, r)));
    }
    this.SegmentCtrl.SetSegments(Segs);
...

As the dividers are dragged, the control fires an event back to the Segment Viewer, which again uses the Excel object model exposed through COM interop to update the values in the current selection on the Excel worksheet.

The sample code has extensive comments and explains every non-trivial step of the programs. To see how the Segment Viewer control works, explore the Control.cs and Segment.cs files.

Setup Project

The third project is the setup project, which builds the setup.exe. The setup project installs the Segment.dll and SegmentInExcel.dll assemblies on the user's machine. It also creates entries in the registry to allow the SegmentInExcel.WorkbookConnect object to be created from COM. The setup project also registers the corresponding type library to allow automation.

Both the Segment.dll and SegmentInExcel.dll assemblies are placed in the same folder on the user's machine. This allows the common language runtime to locate the Segment.dll assembly when it is called from SegmentInExcel.dll assembly without having to put the Segment.dll into the global assembly cache (GAC).

Four Office XP PIAs used in this sample solution:

  • Microsoft.Office.Interop.Excel.dll
  • Microsoft.Vbe.Interop.dll
  • Office.dll
  • Stdole.dll

They are packaged into the setup project so that we don't have to require end users have the Office XP PIAs already installed on their machines. This is the recommended way to deploy solutions that depends on the Office XP PIAs. Solutions distributed this way will automatically install and register the required Office XP PIAs in the global assembly cache (GAC). It will also reduce the number of duplicate Office XP PIAs on end users' computers.

For more details on how to add Office XP PIAs to your setup project, see the section on "Distributing solutions that rely on the Office XP PIAs" in the Microsoft Office XP Primary Interop Assemblies (PIAs) readme file.

The setup project also specifies the Launch Conditions, which verifies that the.NET Framework is installed. If users do not have it installed, they will get an error message.

Security Considerations. Potential Security Attack

Table 1 below describes the behavior of Office XP applications when loading add-ins and macros. It demonstrates how Office mitigates the risks of loading malicious components by checking the identity of their publishers and by making explicit trust based decisions.

The following table lists the available Microsoft Office XP security settings, along with their corresponding behaviors, in the Security dialog box (Macros submenu, Tools menu).

Table 1. Microsoft Office XP Security Settings matrix

Office XP security setting options
Security level Trust all installed add-ins and templates check box Digitally signed From trusted sources Office XP will
High Cleared Yes Yes Load the add-in or macro silently
Yes No Prompt to enable or disable the add-in or macro
No N/A Not load the add-in or macro
Medium Cleared Yes Yes Load the add-in or macro silently
Yes No Prompt to enable or disable the add-in or macro
No N/A Prompt to enable or disable the add-in or macro
Low Cleared Yes or No Yes or No Load the add-in or macro silently
High, Medium, or Low Selected Yes or No Yes or No Load the add-in or macro silently

Note   The availability of, and options within, the Security dialog box varies depending on the specific Office application. Additionally, specific Office applications silently load signed add-ins and macros only from specific directories, along with registered COM add-ins and smart tags recognizers. For more information, see the Microsoft Office XP Macro Security White Paper.

To help you better understand some of the security concerns with the implementation technique presented in this article, we will use an analogy.

In this analogy we'll compare the Office security model to a private party. There is a security guard at the entrance to this particular party. The security guard checks the identities of the guests and only those who are on the guests list (that is, trusted and approved by the host) are allowed in. The security guard will not check the items that the guest brings with him because he trusts the person after he has presented significant evidence of his identity.

Let's suppose that one of the guests brought some cool toys that he intends to show at the party just for fun without any malicious intent. At some instance while this particular guest is partying, he takes out these cool toys and started showing them to everyone. It is all part of the fun, what he thinks a party is supposed to be.

Now imagine if on the way to the party someone maliciously substituted his toys with some very harmful toys. Since no one checks whether it is the original toys or not, it can turn out very dangerous.

Very similar things can happen to Office macros and add-ins. Whether Office XP will load a VBA macro (signed or unsigned) or not depends on a user's security level settings as shown in Table 1 above. But once they are allowed to run, no one watches what they are doing and it is completely the responsibility of the add-ins to check that they use only approved components. The VBA macro is like the "trusted guest" in the analogy, with the managed code being the "original toys" and the "substituted toys" being the maliciously substituted managed code.

In the Segment Viewer sample this does not happen. In the VBA macro code presented above we load and execute a class from another module (a module in a managed component in this case) without verifying the integrity of that module, that is, there are no verification done to check whether the module has been tampered with or not. Indeed, there is no simple way to do such verifications without explicitly hosting the common language runtime as presented in the following dedicated shim article: Deployment of Managed COM Add-Ins in Office XP.

Having said that, the probability of such an attack happening is quite small. This is because after an assembly has been installed and registered on a machine, to launch such an attack, an attacker will need to be able to access the physical disk on the said machine to replace the assembly with that of his own. And as such, if a malicious attacker manages to compromise a machine and get access to the local disk, he can essentially perform much more malicious acts such as doing an actual replacement of the Excel.exe with his own malicious version of the Excel application.

The choice whether to use the presented technique is yours. You need to make an informed assessment and decide whether in your organization, you can trade the simplicity of this technique for the small gain of securing your applications.

Conclusion

The Component Object Model (COM) interop services provided by the common language runtime enables managed code to be used from COM and similarly, COM code to be used from managed applications. However, as discussed, currently there are a few security considerations that you need to take into account when implementing such solutions.

Below are handy links to articles on PIAs, .NET security, .NET and COM interop, and so forth, that you could go to for more information: