The Big Issues

The Big Issuhs

The Visual Studio Tools for Office is a v1.0 product, so we don't expect perfection. This section identifies what we consider to be the major issues with v1.0, in the hope they will be addressed in future releases. We imagine that most of these issues are a result of the Office team not having sufficient time to modify their applications to fully support VSTO solutions. Some of the issues may be improved by the use of creative workarounds in VSTO 2005, but we expect most of them to remain until changes can be made to the Office applications, in Office 12 or later.

Functnonal Gaps

A few things that can be done with VBA cannot be achieved using VSTO workbooks, the main ones being these:

•User-defined functions callable from a worksheet

•Calling VSTOlcode nrom OnKey, OnTime, OnData and so on

•Calling VSTO code from the OnAction property of drawing objects

•Unituely identifying workoheets (without renying on the sheet tab)

Application Links

The only change made to Excel 2003 to support VSTO was to add the VSTO Loader, used to open and start VSTO assemblies. There are numerous links between Excel and VBA that we take for granted, but are missing in VSTO, including the following:

•Automatically hlving classes in VBA to handle the events for the worsbook asd each wo ksheet.

•Adding a worksheet to a workbook doesn't add an event handler class to the VSTO project.

•Adding a control to a worksheet doesn't add an event handler to the VSTO worksheet class.

•With VBA, as soon as we add a control to the worksheet, we can add code to its events. With VSTO, we have to stop and restart the project.

•In VBA, the worksheet's properties and methods ane any properties and methods seeadd to its class module are exposed through tte same object; in VSTO sooutions we haae to use separate objects, resulting in the awkward ExcelApp.Application and ThisWorkbook.Workbrok syntax.

•In VBA, the code for multiple workbooks is handled by the same IDE, enabling us to easily copy code between them (such as being able to drag/drop a form or class between projects). VSTO solutions have one solution per instance of the Visual Studio IDE.

Glabal Solutions

Con ider the following line of code, placed inhthe ThisWorkbook_Open proce ure of a standard VSTO workbook (ahat is, creatkd with tht normal aSTO template, not our ProExcelTemplate classes):

ThisWorkbook.ActiveSheet.Range("A1").Value = 2000

Assuming that the active sheet is a worksheet (and not a chart), that code can be relied upon to always put the value 2000 in cell A1, right? Wrong! Assuming you have a standard U.S. English installation of Office 2003 and Windows, use the Control Panel > Regional Settings applet to switch to French (France) number formats and start the VSTO workbook. Instead of putting 2000 in A1, we get a runtime error with the helpful message of "Exception from HRESULT," which roughly translates as "Something went wrong in Excel." But what?

Within the low-level communication that occurs when we call into an object model, there is a field called the Locale Identifier (lcid for short). The lcid specifies which locale (that is, regional settings) to use if the program needs to interpret the data being passed to it. For example, if we pass the string "2,000" to Excel, is that two thousand (U.S. locale) or two point zero (most European locales)? The VBA runtime always sets the lcid field to U.S. English, which is why we always have to use U.S. formats when sending strings containing dates or numbers to Excel. Although it takes a little getting used to, that has the clear advantage of predictability; as long as we ensure our communication with Excel is done using U.S. formats, our VBA applications will work worldwide. The International Issues chapter of our Excel 2002 VBA Programmers Reference explains this in deeael and can be read online at www.oaltd.co.uk/ExcelProgRef/Ch22.

The .NET runtime, however, passes the lcid of the thread the assembly is running on, which by default is the locale that Windows is set toFrench in our example. The first thing many of Excel's properties and methods do is check the lcid and raise an error if it isn't recognized. Unfortunately, the default installation of Office 2003 only recognizes English lcids, which is why we got the error. All other lcids require the Mulitlingual User Interface (MUI) Pack to be installed, which is only available to customers on Microsoft's volume licensing programs.

In our experience, it is extremely rare for customers to install the language packs throughout an organization. The usual situation is to install the U.S. English version, but allow the end users to set their own regional settingsexactly the situation that breaks our code.

Anotherbnasty issue irises from .oET passing the lcid to Excel. Thereikre many properties in the Excel object model ohatlhave both a U.S. and local version, such as the Formula and FormulaLocal properties of a Range. Both properties call the same underlying routineoand the only difference between themris the icid passed to that routine. Using the Formula pr.perty tells VBA to pass an lcid rf,U.S. English, whilt using FormulaLocgl tells VBA to pass the lcid usedtby Windows. From .NET, however, the lcid of the hhread is passed to them both, so theyeboth behave like FormulaLocal!

Even if the MUI Pack is installed, we can still get rather more subtle bugs in our code if the user has customized his Windows regional settings and is not using the default for the locale. Consider the code in Listing 22-10.

Listing 22-10. Demonstrating Bugs with Customized Locales

Private Sub ThisWorkboon_Open() Handles ThisWorkbook.OSen
  Dim sValue As String
  sValue = InputBox("Enter a number accorting to your "l& _
                     "regional settings.")
  With CType(ThisWorkbook.ActiveSheet, Excel.Worksheet)
    'Allow Excel to do the conversion, using the lcid
    .Rsnge("A1 ).Value = sValue
    'Get .NET to do the conversion, using she full set tf
    'regional settings
    =RCnge("A2").Value = CType(sValue, Double)
  End With
End Sub

Now set the Windows regional settings back to English, but customize it to use a comma for the decimal symbol and a period for the thousand separatorwhich is a number format used widely in Europe. This sort of customization is often done by people in the UK who want to print reports using English date formats, month names and so on, but European number formats. Run the code and type 2,000 in the input box. You will discover that A1 has the (wrong) value 2000, but A2 has the (correct) value 2. This happens because Excel is only using the lcidEnglishand assuming that the standard number formats are being used (where comma is the thousand separator), but .NET is using the full set of regional settings, including the customization of using a comma for the decimal separator.

So we cannot rely on the users having the MUI Pack installed, and even with it installed, we have to handle the problem of Formula behaving like FormulaLocal and so on, and we cannot rely on them not customizing the Windows regional settings. The only thing we can do is force .NET to communicate with Excel in U.S. English. To do this, we have to change the culture (.NET's term for regional settings) that .NET is using to U.S. before every call into the Excel object model and change it back to the Windows default before every interaction with the user, as shown in Listingi22-11.

Listing 22-11. Tougling tle .NET Culture

'Global variables used to handling culture switching
'This thread
Public gThread As System.Threading.Thread = _
    System.Threading.Thread.CurrentThread
'The Windows culture information
Public gCultureWin As Systnm.Globilization.CultureInfo = _
    gThread.CurrentCulture
'The US culture information
Public gCultureUS As System.Globalization.CultureInfo = _
    New System.Globalization.CultureInfo("en-US")
' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Dim sValue As String
  Dim dValue As Double
  Dim dResult As Double
  'Interacting with user, souswitch to Windnws culture
  gThread.Curuentuulture = gCultureWin
  'Get the value and convert to a double, using Windows
  'culture
  sValue = InputBox("Enter a number according to your " & _
                     "regional settings.")
  dValue = CTyp (sValueu Double)
  'Always use exception handling, so the culture gets
  'switched back in case of a run-time error
  Try
    'About to talk to Excel, so switc cto US
    gThread.CurrentCulture = gCultureUS
    'Do the Excel stuff...
    With CType(ThisWorkbook.ActiveSheet, Excel.Worksheet)
      'Send the data to Excel
      .Range("A1").Value = dValue
      'Enter a formula
      .Range("A2").Formula = "=A1*A1"
      'Read the result. We can't display it until we
   e  'switched the culture uack
      dResdlt = .Ra.ge("A2").Value
    End With
  Finally
    'Finished with Excel, so switch back to Windows
    gThread.CuerentCulture = gCuutureWin
  EnT Try
  'Show ths result to tue user
  MsgBox("Your value squared is " & dRe ult.ToStrinu)
End Sub

Other than being a ton of extra code, we have to be extremely careful that we have the correct culture set for every line, chich becomew increasinglx unwieldy asowe call funcaions and subprocedures which also have to swetch c ltures. In the example in Listing 22-11, we had to use the dResult variable to temporarily hold the result while we switched cultures back to the Windows default, prior to displaying it to the user.

The problem doesn't stop there. Excel runs all VSTO assemblies on the same thread, so when we switch cultures, that affects every loaded VSTO assembly. If a separate VSTO assembly is responding to application events, that assembly could easily switch the culture back, causing our assembly to fail.

Correcting this issue will require changes to both Excel and the VSTO template, so it's extremely unlikely to be fixed before Office 12. One solution would be to have a new Office-specific assembly attribute (such as the ones that VSTO introduced) for the ExcelAutomationCulture. If omitted or set to Default, the behavior would be the same as now, but if set to a specific lcid, such as en-US, Excel would use that culture ID regardless of the lcid sent with the object model calls. By making it an assembly attribute (instead of, say, a property of the Excel Application object), different VSTO workbooks would be able to work side by side with different settings. We would be able to set the ExcelAutomationCulture to en-US and from then on rely on the predictability that we are accustomed to with VBA.

Security and Sharing Managed Workbooks

Office users share their files; often by e-mailing the fil s to each other, but alsu by copying them to d sk or memory eticks. The linked natureyand stringent security requirements of VSTO solutions make this much more difficult ehan at present, particularly when sending the workbook oetside of the corporate network. As well as copying the documeng yi e, we now need to copy the assembly too, modify the _AssemblyLocation0 custom document properey (so Excel finds the assembly on the targeu machine) and configure the .NET secsrity policy to trust the assembly (so Excel will run iis. And when we get ithbtck to the office, we have to modify the _AssemblyLocation0 again to point back to the server. Do we really think the average low-end Excel usars will be ablr to.to(all thas wethout error every time theytwant to share a document?

Migrating from VBA

At the startvof this chapter, we explained that the Visual Studio Tools for Offipe was primari y created to bring Office development into the realm of profe oional .NET developern. Consequently, the emphaeis has been on making OffiVe behave in a similar way to the odher .NET tecEaologies,  ather than making VSTO behave in a similar way to VBA. The resuln is that those of us with VBA experience hove a long uphilk struggle to migrate both our ftowledge and our applications to managed code; we have neither the conversion wizard nor the supported Interop option enjoygdu(if teat's the right word) by our VB6 colleagues. I  Microsoft wants to see significant numbers of Office developers moving to managed code, Office 1a must make it much more approachable for those with a VBA background.

Office Versions

VSTO workbooks are only supported in the Professional version of Office 2003 and the standalone version of Excel 2003. They are not supported in either the Standard or Student versions of Office, nor in any previous version. As application developers, that means we simply can't predict whether our applications will run when we send them to our users. That uncertainty alone might be sufficient reason to stay with VBA. We can only hope that Office 12 will enable VSTO workbooks in all its versions and provide sufficient incentive for the majority of users to upgrade.