Excel 2003 Programming: A Developer's Notebook

Kick-Start Security and .NET

If you save and close the workbook you just created, you’ll see the warning in Figure 1-17 the next time you open it:

Figure 1-17. Macro security warning in Excel

You’re probably very familiar with this warning, and you also probably know that if you digitally sign the code from within the Visual Basic editor, you can change the warning to look something like Figure 1-18.

That’s a little less frightening, and if you (or your users) select Always trust macros from this publisher, Excel will automatically enable macros that have your digital signature without warning—at least under the default security settings.

Figure 1-18. Signed macro security warning in Excel

With Excel VBA, digital signatures are the only way to grant or deny the permission to run code on the user’s machine. VBA code is distributed as workbooks, templates, or add-ins and the code in each of those files must be signed before it is trusted.

There are some problems with this approach: users may choose to trust a publisher who isn’t trustworthy—trust is an all-or-nothing proposition—and updating macros means sending out new files to all users. Excel 2003 addresses these problems by allowing you to program workbooks using the .NET Framework and code-behind assemblies. The .NET Framework uses a different security model, based on levels of trust that can be assigned to locations, such as a folder on a public server where assemblies are stored.

When you program Excel 2003 in .NET, no code is stored with the workbook. Instead a special custom property tells Excel where to load the code from (Figure 1-19).

Since the workbook in Figure 1-19 contains no VBA code, the user never sees the macro security warning. And, if there are changes to the .NET code, the new assembly can simply be replaced on the server. Users get the new code the next time they open the workbook.

Figure 1-19. Excel loads .NET assemblies from trusted locations to perform automated tasks

Whether a location is trusted is determined by the user’s .NET security configuration. To view or change these settings, follow these steps:

1. From the Windows Control Panel, choose Administrative Tools → Microsoft .NET Framework 1.1 Configuration, then click the Configure Code Access Security Policy link.

2. Expand the User, Code Groups, All_Code, Office_Projects treeview items, as shown in Figure 1-20.

   

   Figure 1-20. Configure .NET security settings to create a trusted location

3. Click the Edit Code Group Properties link to see the security settings for the group (Figure 1-21).

   

   Figure 1-21. Grant FullTrust to allow all .NET code to run

To see how programming Excel from .NET works in practice, you’ll need Visual Studio Tools for Office (VSTO). VSTO is purchased separately from Office. If you already have VSTO, you can follow these steps to implement our previous example in .NET:

Note

If you don’t have VSTO, browse these steps to get an idea of whether VSTO is something you should buy.

1. Start Visual Studio .NET and choose File → New Project. If VSTO is installed, the New Project dialog box displays Excel Workbook as one of the project templates (Figure 1-22).

2. Create a new project using the Excel Workbook template and base the project on a new workbook. VSTO creates a new folder containing code files for the project.

3. Start Excel and open the workbook created by VSTO. Repeat the first procedure in “Kick-Start Lists and XML” to create a list from http://www.mstrainingkits.com/Excel/ExcelObjects.xml. (Don’t write any VBA code, though.)

4. Save and close the workbook.

5. In VSTO, choose Project → Add a Web Reference to add a reference to the Lists web service at http://server/_vti_bin/lists.asmx?wsdl (Figure 1-23).

   

   Figure 1-22. Project templates

   

   Figure 1-23. Adding a web reference in Excel

6. Open ThisWorkbook.vb in the VSTO code window and create the following event procedure.

   Private Sub ThisWorkbook_AfterXmlImport( _
     ByVal Map As Microsoft.Office.Interop.Excel.XmlMap, _
     ByVal IsRefresh As Boolean, _
     ByVal Result As Microsoft.Office.Interop.Excel.XlXmlImportResult) _
     Handles ThisWorkbook.AfterXmlImport
       Dim cel As Excel.Range, ws As Excel.Worksheet, rng As Excel.Range
       ' Create Web service object.
       Dim lws As New wombat1.Lists, xn As System.XML.XmlNode, rowID As Integer
       ws = ThisWorkbook.Worksheets("Sheet1")
       rng = ws.ListObjects("List1").ListColumns("name").Range
       ' Pass Web service this application's credentials.
       lws.Credentials = System.Net.CredentialCache.DefaultCredentials
       ' Change from For Each because of problems with Excel's Range
         collection.
       For rowID = 1 To rng.Rows.Count - 1
           ' Get the single-cell range object for each row in the name column.
           cel = ws.Cells(rowID + rng.row, rng.Column)
           If Not (cel.Comment Is Nothing) Then cel.Comment.Delete(  )
           cel.AddComment (cel.offset(0, 1).Text)
           ' Get the list of attachments through SharePoint Web service.
           xn = lws.GetAttachmentCollection("Excel Objects", rowID)
           ' If there is an attachment 
           If Not IsNothing(xn.Item("Attachment")) Then
               ' Add a hyperlink for the attachment
               ws.Hyperlinks.Add(cel.Offset(0, 2), _
                 xn.Item("Attachment").InnerText, , _
                 "Click to view sample", _
                 "Code sample")
           End If
       Next
   End Sub

7. Run the project (F5). Visual Studio builds the assembly and then opens the associated workbook in Excel.

8. Refresh the list in Excel (Data → XML → Refresh XML Data). Excel runs the code in the .NET assembly to add comments and hyperlinks.

You’ll notice that the .NET code is only somewhat similar to the VBA code you created earlier. Visual Basic .NET is really a different language, with significant improvements over earlier versions of Basic. If you perform the tutorial, you’ll also notice a number of gotchas that I should warn you about:

• VSTO only works with Excel 2003 and Word 2003. Earlier versions are not supported.

• If a .NET procedure fails, Excel just ignores the problem. To break on errors in VSTO, choose Debug → Exceptions, select Common Language Runtime Exceptions, and then select When an exception is thrown: Break into the debugger.

• You can’t make changes to .NET code while you are debugging. You must rebuild the assembly for changes to take effect. (This changes in the next version.)

• The Excel Range object isn’t always recognized as a collection in .NET. To work around this, replace For Each loops with For...Next loops.

• Web service methods are called somewhat differently than in VBA. The .NET web service tools allow you to specify security credentials for the user and provide slightly different objects as return values.

• The .NET code runs more slowly than the VBA version. Each call between Excel and the .NET assembly crosses a process boundary, which imposes overhead.

These aren’t trivial problems, but if you can deal with them .NET provides a more advanced security and deployment model than VBA. Also, .NET provides a single development platform for working with other applications, which I’ll talk about next.