Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Big Issues

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.

Functional 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 VSTO code from OnKey, OnTime, OnData and so on

Calling VSTO code from the OnAction property of drawing objects

Uniquely identifying worksheets (without relying 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 having classes in VBA to handle the events for the workbook and each worksheet.

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 and any properties and methods we add to its class module are exposed through the same object; in VSTO solutions we have to use separate objects, resulting in the awkward ExcelApp.Application and ThisWorkbook.Workbook 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.

Global Solutions

Consider the following line of code, placed in the ThisWorkbook_Open procedure of a standard VSTO workbook (that is, created with the normal VSTO 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 detail 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.

Another nasty issue arises from .NET passing the lcid to Excel. There are many properties in the Excel object model that have both a U.S. and local version, such as the Formula and FormulaLocal properties of a Range. Both properties call the same underlying routine and the only difference between them is the lcid passed to that routine. Using the Formula property tells VBA to pass an lcid of U.S. English, while using FormulaLocal tells VBA to pass the lcid used by Windows. From .NET, however, the lcid of the thread is passed to them both, so they both 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 ThisWorkbook_Open() Handles ThisWorkbook.Open
Dim sValue As String
sValue = InputBox("Enter a number according to your " & _
"regional settings.")
With CType(ThisWorkbook.ActiveSheet, Excel.Worksheet)
'Allow Excel to do the conversion, using the lcid
.Range("A1").Value = sValue
'Get .NET to do the conversion, using the full set of
'regional settings
.Range("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 Listing 22-11.

Listing 22-11. Toggling the .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 System.Globalization.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, so switch to Windows culture
gThread.CurrentCulture = gCultureWin
'Get the value and convert to a double, using Windows
'culture
sValue = InputBox("Enter a number according to your " & _
"regional settings.")
dValue = CType(sValue, 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 switch to 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
'switched the culture back
dResult = .Range("A2").Value
End With
Finally
'Finished with Excel, so switch back to Windows
gThread.CurrentCulture = gCultureWin
End Try
'Show the result to the user
MsgBox("Your value squared is " & dResult.ToString)
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, which becomes increasingly unwieldy as we call functions and subprocedures which also have to switch cultures. 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 files to each other, but also by copying them to disk or memory sticks. The linked nature and stringent security requirements of VSTO solutions make this much more difficult than at present, particularly when sending the workbook outside of the corporate network. As well as copying the document file, we now need to copy the assembly too, modify the _AssemblyLocation0 custom document property (so Excel finds the assembly on the target machine) and configure the .NET security policy to trust the assembly (so Excel will run it). And when we get it back 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 users will be able to do all that without error every time they want to share a document?

Migrating from VBA

At the start of this chapter, we explained that the Visual Studio Tools for Office was primarily created to bring Office development into the realm of professional .NET developers. Consequently, the emphasis has been on making Office behave in a similar way to the other .NET technologies, rather than making VSTO behave in a similar way to VBA. The result is that those of us with VBA experience have a long uphill struggle to migrate both our knowledge and our applications to managed code; we have neither the conversion wizard nor the supported Interop option enjoyed (if that's the right word) by our VB6 colleagues. If Microsoft wants to see significant numbers of Office developers moving to managed code, Office 12 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.