This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

EXCELence

Exceptions to the Rules

By John Green and Stephen Bullen

The first installment of this series provided a guideline on how to ensure that your application can be used internationally. We discussed the WRS and the Office UI language and how an application can work with different settings, depending on the locale of the user. We also discussed interacting with Excel and interacting with your users. Finally, we came up with a set of rules for working with your users.

In this installment, we'll begin by looking at features that don't play by the rules. We'll also talk about responding to different Office UI language settings.

For reference, here are the rules for working with your users that we outlined in Part I:

1)       When converting a number or date to a text string for displaying to your users, or setting it as the.Caption or .Text properties of controls, explicitly convert numbers and dates to text according to the WRS, using Format(myNum), or CStr (MyNum).

2)       When converting dates to strings, Excel does not rearrange the date part order, so Format(MyDate, "dd/mm/yyyy")will always give a DMY date order (but will show the correct date separator). Use Application.International(xlDateOrder) to determine the correct date order, as used in the sFormatDate function shown later, or use one of the standard date formats (e.g. ShortDate)

3)       If possible, use locale-independent date formats, such as Format(MyDate, "mmm dd, yyyy"). Excel will display month names according to the user's WRS language.

4)       When evaluating date or number strings that have been entered by the user, use CDate or CDbl to convert the string to a date/number. These will use the WRS to interpret the string. Note that CDbl does not handle the % character if the user has put one at the end of the number.

5)       Always validate numbers and dates entered by the user before trying to convert them. See bWinToNum and bWinToDate functions in the second part of this series for an example.

6)       When displaying information about Excel objects, use the xxxLocal properties (where they exist) to display it in your user's language and formats.

7)       Use the xxxLocal properties when setting the properties of Excel objects with text provided by the user (which we must assume is in their native language and format).

Features That Don't Play by the Rules

The xxxLocal functions discussed in the first installment were all introduced during the original move from XLM functions to VBA in Excel 5.0. They cover most of the more common functions that a developer is likely to use. There were, however a number of significant omissions in the original conversion, and new features have been added to Excel since then with almost complete disregard for international issues.

This section guides you through the maze of inconsistency, poor design, and omission that you'll find hidden within the Excel 2000's features found in FIGURE 1. The table in FIGURE 1 shows the methods, properties, and functions in Excel that are sensitive to the user's locale, but which do not behave according to the Rules we have stated previously:

Applies to	US Version	Local Version
Opening a text file	OpenText	OpenText
Application	.ShowDataForm	.ShowDataForm
Worksheet/Range		.Paste/.PasteSpecial
Pivot Table calculated fields and items		.Formula
Conditional formats		.Formula
QueryTables		.Refresh
(Web Queries)
Worksheet functions		=TEXT
Range	.Value
Range	.FormulaArray
Range	.AutoFilter	.AutoFilter
Range		.AdvancedFilter
Application	.Evaluate
Application	.ConvertFormula
Application	.ExecuteExcel4Macro

FIGURE 1: These methods, properties, and functions are sensitive to the user's locale, but do not behave according to the rules we stated previously.

Fortunately, workarounds are available for most of these issues. There are a few, however, that should be completely avoided.

OpenText

Workbooks.OpenText is the VBA equivalent of opening a text file in Excel by using File | Open. It opens the text file, parses it to identify numbers, dates, Booleans, and strings and stores the results in worksheet cells. Of relevance to this article series is the method Excel uses to parse the data file (and how it has changed over the past few versions). In Excel 5, the text file was parsed according to your Windows Regional Settings (WRS) when opened from the user interface, but according to US formats when opened in code. In Excel 97, this was changed to always use these settings from both the UI and code. Unfortunately, this meant that there was no way to open a US-formatted text file with any confidence that the resulting numbers were correct. Since Excel 5, we have been able to specify the date order to be recognized on a column-by-column basis, which works very well for numeric dates (e.g. 01/02/1999).

New to Excel 2000 is the Advanced button on the Text Import Wizard, and the associated DecimalSeparator and ThousandSeparator parameters of the OpenText method. These allow us to specify the separators that Excel should use to identify numbers and are welcome additions. It is slightly disappointing to see that we can not specify the general date order in the same way.

Unfortunately, while Microsoft is to be congratulated for fixing the number format problems, they did not take the opportunity to fix the language issues. We have stated that the best way to handle date strings is to use unambiguous formats that include the month name (e.g. Feb 1, 1999). The OpenText method is the one place in Excel development where that advice should not be followed. This is because in OpenText, Excel only recognizes the month names according to your selected WRS language - English month names are not recognized if you have WRS set to Norwegian. The advice for text files is to store them as numeric dates (e.g. 01/02/1999) and use the OpenText parameters to tell Excel which data order to recognize.

The other language-related issue is in the recognition, or otherwise, of Boolean values. You'll recall that all the VBA string/Boolean conversion functions worked with the English text "True" and "False" and were not dependent on the WRS language or the Office UI language. When opening text files, Excel uses the Office UI language translations of "True" and "False" to identify Booleans. Hence, while "True" and "False" will be recognized when using an English UI, only "Sann" and "Usann" will be recognized as Booleans when using a Norwegian UI.

To summarize, OpenText recognizes:

• Numbers according to the separators we specify (defaulting to the WRS).
• Numeric date formats according to the date order we specify (by column).
• Textual dates according to the WRS language.
• Boolean strings according to the Office UI language.

While OpenText is a vast improvement over earlier versions of Excel, it can still only be used reliably if the text file does not contain textual dates (use 01/02/1999 instead of Feb 1, 1999) or Boolean texts (use 1/0 instead of True/False).

ShowDataForm

Using ActiveSheet.ShowDataForm is exposing yourself to one of the most dangerous of Excel's international issues. ShowDataForm is the VBA equivalent of the Data | Form menu item. It displays a standard dialog box that allows the user to enter and change data in an Excel list/database. When run by clicking the Data | Form menu, the dates and numbers are displayed according to the WRS and changes made by the user are interpreted according to the WRS, which fully complies with the user-interaction rules.

When run from code using ActiveSheet.ShowDataForm, Excel displays dates and numbers according to US formats but interprets them according to WRS. Hence, if you have a date of Feb 10, 1999 shown in the worksheet in the dd/mm/yyyy order of 10/02/1999, Excel will display it on the data form as 2/10/1999. If you change this to the 11th (2/11/1999), Excel will store Nov 2, 1999 in the sheet! Similarly, if you are using Norwegian number formats, a number of 1-decimal-234 will be displayed on the form as 1.234. Change that to read 1.235 and Excel stores 1235, one thousand times too big!

Fortunately, there is an easy workaround for this if your routine only has to work with Excel 2000 or Excel 97. Instead of using ShowDataForm, you can select the first cell in the range, then execute the Data | Form menu item itself:

Sub ShowForm()
         ActiveSheet.Range("A1"). Select
         RunMenu 860  ' 860 is the CommandBarControl ID of the Data, Form menu item. 
      End Sub

The following RunMenu routine executes a given menu item, as if it had been clicked by the user. In this case, the data form behaves correctly.

RunMenu. The routine in FIGURE 2 will run a menu item, given its CommandBar.Control ID (e.g. 860 is the ID for the Data | Form menu item).

Sub RunMenu(iMenuID As Long)
      ' *****************************************************
      ' * Function Name:   RunMenu
      ' *
      ' * Input/Output:    iMenuID - The control ID of the menu item to be run. 
      ' *
      ' *  Purpose:         Runs a specified menu item, simulating clicking on it. 
      ' *
      ' *****************************************************
          Dim oCtrl As CommandBarButton
          'Ignore any errors (such as the menu ID not valid). 
         On Error Resume Next
          'Create our own temporary commandbar to hold the control. 
         With Application.CommandBars.Add
             ' Add the control and execute it. 
            .Controls.Add(ID:=iMenuID).Execute
         
            ' Then delete our temporary menu bar. 
            .Delete
         End With
       End Sub

FIGURE 2: The RunMenu routine.

Pasting Text

When pasting text from other applications into Excel, it is parsed according to the WRS. We have no way to tell Excel the number and date formats and language to recognize. The only workaround is to use a DataObject to retrieve the text from the Clipboard, parse it yourself in VBA, then write the result to the sheet.

PivotTable Calculated Fields and Items and Conditional Format Formulas

If you are used to using the.Formula property of a range or chart series, you'll know that it returns and accepts formula strings that use English functions and US number formats. There is an equivalent.FormulaLocal property, which returns and accepts formula strings as they appear on the sheet (i.e. using the Office UI language and WRS number formats).

Pivot table calculated fields and items and conditional formats also have a .Formula property, but for these objects, it returns and accepts formula strings as they appear to the user, i.e. it behaves in the same way as the.FormulaLocal property of a Range object. This means that to set the formula for one of these objects, we need to construct it in the Office UI language, and according to the WRS.

A workaround for this is to use the cell's own.Formula and.FormulaLocal properties to convert between the formats, as shown in the ConvertFormulaLocale function.

ConvertFormulaLocale. This function, shown in FIGURE 3, converts a formula string between US and local formats and languages.

Function ConvertFormulaLocale(sFormula As String, bUSToLocal As Boolean) _
                  As String
      ' *****************************************************
      ' * Function Name:   ConvertFormulaLocale
      ' *
      ' * Input/Output:    sFormula   - The text of the formula to convert from. 
      ' *                  bUSToLocal - True to convert US to Local. 
      ' 
      *                               False to convert Local to US. 
      ' *
      ' *                  Returns the text of the formula according to local
      ' *                  settings. 
      
      ' *
      ' * Purpose:         Converts a formula string between US and local formats. 
      ' *
      ' *****************************************************
          On Error GoTo ERR_BAD_FORMULA
          'Use a cell that is likely to be empty! 
         'This should be changed to suit your own situation. 
         With ThisWorkbook.Worksheets(1).Range("IU1") 
            If bUSToLocal 
      Then
               .Formula = sFormula
               ConvertFormulaLocale = .FormulaLocal
            Else
               .FormulaLocal = sFormula
               ConvertFormulaLocale = .Formula
            End If
            .ClearContents
         End With
          ERR_BAD_FORMULA: 
      End Function

FIGURE 3: The ConvertFormulaLocale function.

Web Queries

While the concept behind Web Queries is an excellent one, they have been implemented with complete disregard to international issues. When the text of the Web page is parsed by Excel, all the numbers and dates are interpreted according to your WRS. This means that if a European Web page is opened in the US, or a US page is opened in Europe, it is likely that the numbers will be wrong. For example, if the Web page contains the text 1.1, it will appear as 1st Jan on a computer running Norwegian Windows. Web queries cannot be used reliably in a multi-national application.

=TEXT Worksheet Function

The TEXT worksheet function converts a number to a string according to a specified format. The format string has to use formatting characters defined by the WRS. Hence, if you use =TEXT(NOW,"dd/mm/yyyy"), you will get "01/02/yyyy" on Norwegian Windows, since Excel will only recognize 'å' as the Norwegian number-format character used for years. Excel does not translate the number-format characters when it opens the file on a different platform. A workaround for this is to create a defined name that reads the number format from a specific cell, then use that definition within the TEXT function. For example, if you format cell A1 with the date format to use throughout your sheet, you can click on Insert | Name | Define and define a name as:

  Name:    DateFormat
        Refers To:  =GET.CELL(7,$A$1)

Then use =TEXT(Now()DateFormat) elsewhere in the sheet. The GET.CELL function is an Excel 4 macro function - which Excel 2000 lets us use within define names, though not on the worksheet. This is equivalent to, but much more powerful than, the =CELL worksheet function. The 7 in the example tells GET.CELL to return the number-format string for the cell.

The XLM functions are documented in the MACROFUN.HLP and XLMACR8.HLP files, available from Microsoft's Web site.

Range.Value and Range.FormulaArray

These two properties of a range only break the rules by not having local equivalents. The strings passed to (and returned by) them are in US format. Use the ConvertFormulaLocale function shown previously to convert between US and local versions of formulas.

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behavior. The criteria string consists of an operator (=, >, <, >=, etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of ">=02/01/1999" will find all dates on or after 1st Feb, 1999, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 1999 and will work in any locale:

Range("A1:D200").AutoFilter 2, ">=02/01/1999", xlAnd, "<=02/01/1999" 

Range.AdvancedFilter

The AdvancedFilter method does play by the rules, but in a way that may be undesirable. The criteria used for filtering are entered on the worksheet in the criteria range. In a similar way to AutoFilter, the criteria string includes an operator and a value. Note that when using the "=" operator, AdvancedFilter correctly matches by value and hence differs from AutoFilter in this respect. As this is entirely within the Excel domain, the string must be formatted according to the WRS to work, which gives us a problem when matching on dates and numbers. An advanced filter search criterion of ">1.234" will find all numbers greater then 1.234 in the US, but all numbers greater than 1234 when run in Norway. A criterion of ">02/03/1999" will find all dates after the 3rd Feb in the US, but after the 2nd March in Europe. The only workarounds are to populate the criteria strings from code before running the AdvancedFilter method, or to use a calculated criteria string, using the =TEXT trick mentioned previously. Instead of a criterion of ">=02/03/1999", to find all dates on or after the 3rd Feb, 1999, we could use the formula:

=">="&TEXT(DATE(1999,2,3),DateFormat) 

Here DateFormat is the defined name introduced above that returns a local date format. If the date is an integer (i.e. does not contain a time component), we could also just use the criteria string ">=36194" and hope that the user realizes that 36194 is actually 3rd Feb, 1999.

Application.Evaluate, Application.ConvertFormula, and Application.ExecuteExcel4Macro

These functions all play by the rules in that we must use US-formatted strings. They do not, however, have local equivalents. To evaluate a formula that the user may have typed into a UserForm (or convert it between using relative to absolute cell ranges), we need to convert it to US before passing it to Application.Evaluate or Application.ConvertFormula.

The Application.ExecuteExcel4Macro function is used to execute XLM-style functions. One of the most common uses of it is to call the XLM PAGE.SETUP function, which is much faster than the VBA equivalent. This takes many parameters, including strings, numbers, and Booleans. Be very careful to explicitly convert all these parameters to US-formatted strings and avoid the temptation to shorten the code by omitting the Str around each one.

Responding to Office 2000 Language Settings

One of the major advances in Office 2000 is that there is a single set of executables with a set of plug-in language packs (whereas in previous versions, each language was a different executable with its own set of bugs). This makes it very easy for a user of Office to have their own choice of language for the user interface, help files, etc. In fact, if a number of people share the same computer, each person can run the Office applications in a different language.

As developers of Excel applications, we must respect the user's language selection and do as much as we can to present our own user interface in their choice of language.

Where Does the Text Come From?

There are three factors that together determine the text seen by the Office user: the Regional Settings location, the Office UI Language Settings, and the language version of Windows.

Regional Settings location. The Regional Settings location is chosen on the first tab (called Regional Settings) of the Control Panel Regional Settings applet and defines:

• The day and month names shown in Excel cells for long date formats.
• The day and month names returned by the VBA Format function.
• The month names recognized by the VBA CDate function and when typing dates into Excel directly.
• The month names recognized by the Text Import Wizard and the VBA OpenText method.
• The number format characters used in the =TEXT worksheet function.
• The text resulting from the implicit conversion of Boolean values to strings; that is:

 "I am " &True

Office UI Language Settings. The Office User Interface language can be selected by using the "Microsoft Office Language Settings" applet, installed with Office 2000 and defines:

• The text displayed on Excel's menus and dialog boxes.
• The text for the standard buttons on Excel's message boxes.
• The text displayed in Excel's cells for Boolean values.
• The text for Boolean values recognized by the Text Import Wizard, the VBA OpenText method, and when typing directly into Excel.
• The default names for worksheets in a new workbook.
• The local names for command bars.

Language version of Windows. By this, I mean the basic language version of Windows itself. This choice defines:

The text for the standard buttons in the VBA MsgBox function (i.e. when using the vbMsgBoxStyles constants). Hence, while the text of the buttons on Excel's built-in messages respond to the Office UI language, the text of the buttons on our own messages respond to the Windows language. Note that the only way to discover the Windows language is with a Windows API call.

There are some things in Office 2000 that are 100-percent (US) English, and don't respond to any changes in Windows language, regional settings, or Office UI language, namely:

• The text resulting from the explicit conversion of Boolean values to strings, i.e. all of Str(True), CStr(True) and Format(True) result in "True". Hence, the only way to convert a Boolean variable to the same string that Excel displays for it, is to enter it into a cell, then read the cell's.FormulaLocal property.
• The text of Boolean strings recognized by CBool.

Identifying the Office UI Language Settings

The first step in creating a multilingual application is to identify the user's settings. We can identify the language chosen in WRS by using Application.International(xlCountrySetting), which returns a number that corresponds approximately to the country codes used by the telephone system (e.g. 1 is the USA, 44 is the UK, 47 is Norway, etc.). We can also use Application.International(xlCountryCode) to retrieve the user interface language using the same numbering system. This method has worked well in previous versions of Excel, where there were only 30 or so languages from which to choose your copy of Office.

Office 2000 has changed things a little. By moving all the language configuration into separate language packs, Microsoft can support many more languages with relative ease. If you use the Object Browser to look at the msoLanguageID constants defined in the Office object library, you'll see that there are over 180 languages and dialects listed.

To find out the exact Office UI language, we can use:

Application.LanguageSettings.LanguageID(msoLanguageIDUI)

Creating a Multilingual Application

How far to go?

When developing a multilingual application, you have to balance a number of factors, including:

• The time and cost spent developing the application.
• The time and cost spent translating the application.
• The time and cost spent testing the translated application.
• The increased sales from having a translated version.
• Improved ease-of-use, and hence reduced support costs.
• The requirement for multi-lingual support.
• Should you create language-specific versions, or use add-on language packs?

You also have to decide how much of the application to translate, and which languages to support:

• Translate nothing.
• Translate only the packaging and promotional documentation.
• Enable the code to work in a multilingual environment (e.g. month names, etc.).
• Translate the user interface (menus, dialog boxes, screens, and messages).
• Translate the help files, examples, and tutorials.
• Customize the application for each location (e.g. to use local data feeds).
• Support left-to-right languages only.
• Support right-to-left languages (and hence redesign your UserForms).
• Support Double-Byte-Character-Set languages (e.g. Japanese).

The decision on how far to go will depend to a large extent on your users, your budget, and the availability of translators.

A Suggested Approach

It is doubtful that creating a single Excel application to support all 180+ Office languages will make economic sense, but the time spent in making your application support a few of the more common languages will often be a wise investment. This will, of course, depend on your users and whether support for a new language is preferable to new features.

The approach that I take is to write the application to support multiple languages and provide the user with the ability to switch between the installed languages or conform to their choice of Office UI language. I develop the application in English, then have it translated into one or two other languages depending on my target users. I will only translate it into other languages if there is sufficient demand.

How to Store String Resources

When creating multilingual applications, we cannot hard-code any text strings that will be displayed to the user; we must look them up in a string resource. The easiest form of string resource is a simple worksheet table. Give all your text items a unique identifier and store them in a worksheet, one row per identifier and one column for each supported language. You can then look up the ID and return the string in the appropriate language using a simple VLOOKUP function. You will need to do the same for all your menu items, worksheet contents, and UserForm controls. The following code is a simple example, which assumes you have a worksheet called shLanguage that contains a lookup table that has been given a name of rgTranslation. It also assumes you have a public variable to identify which column to read the text from. The variable would typically be set in an Options type screen. Note that the code shown in FIGURE 4 is not particularly fast and is shown as an example. A faster (and more complex) routine would read the entire column of IDs and selected language texts into two static VBA arrays, then work from those; only reading in a new array when the language selection was changed.

Public iLanguageCol As Integer
       Sub Test()
         iLanguageCol = 2
         MsgBox GetText(1001) 
      End Sub
       Function GetText(lTextID As Long) As String
         Dim vaTest As ariant
         Static rgLangTable As Range
          'Set an object to point to the string resource table (once). 
         If rgLangTable Is Nothing Then
            .Set rgLangTable = ThisWorkbook.Worksheets("shLanguage") _
               .Range("rgTranslation") 
         End If
          ' If the language choice is not set, assume the first language in our table
         If iLanguageCol < 2 Then iLanguageCol = 2
          'Try to locate and read off the required text
         vaTest = Application.VLookup(lTextID, rgLangTable, iLanguageCol) 
          ' If we got some text, return it
         If Not IsError(vaTest) Then GetText = vaTest
      End Function

FIGURE 4: A faster routine would read the entire column of IDs and selected language texts into two static VBA arrays, then work from those.

Many of your messages will be constructed at run time. For example, you may have code to check that a number is within certain boundaries:

If iValue <= iMin Or iValue >= iMax Then
         MsgBox "The number must be greater than " & CStr(iMin) &_
                " and less than " & CStr(iMax) & "." 
      End If

This would mean that we have to store two text strings with different IDs in our resource sheet, which is both inefficient and much harder to translate. In the example given, we would probably not have a separate translation string for the full stop. Hence, the maximum value would always come at the end of the sentence, which may not be appropriate for many languages. A better approach is to store the combined string with placeholders for the two numbers, and substitute the numbers at run time:

If iValue < iMin Or iValue > iMax Then
         MsgBox ReplaceHolders( _
                          "The number must be greater than %0 and less than %1.", _
                                                        CStr(iMin), CStr(iMax)) 
      End If

The translator (who may not understand your program) can construct a correct sentence, inserting the values at the appropriate points. The ReplaceHolders function is shown later.

Working in a Multilingual Environment

Allow extra space. It is a fact that most other languages use longer words than the English equivalents. When designing our UserForms and worksheets, we must allow extra room for the non-English text to fit in the controls and cells. A good rule-of-thumb is to make your controls 1.5 times the width of the English text.

Using Excel's Objects

The names that Excel gives to its objects when they are created often depend on the user's choice of Office UI Language. For example, when creating a blank workbook using Workbooks.Add, it will not always be called "Bookn," and the first worksheet in it will not always be called "Sheet1." With the German UI, for example, they are called "Mappen" and "Tabelle1," respectively. Instead of referring to these objects by name, you should create an object reference as they are created, then use that object elsewhere in your code:

Dim Wkb As Workbook, Wks As Worksheet
       Set Wbk = Workbooks.AddSet Wks = Wkb.Worksheets(1)

Working with CommandBar.Controls can also be problematic. For example, you may want to add a custom menu item to the bottom of the Tools menu of the worksheet menu bar. In an English-only environment, you may write something like:

SubAddHelloButton()
         DimcbTools AsCommandBarPopup
         DimcbCtl As CommandBarButton
          SetcbTools = Application.CommandBars("Worksheet Menu Bar") _
            .Controls("Tools") 
          SetcbCtl = cbTools.CommandBar.Controls.Add(msoControlButton) 
          cbCtl.Caption = "Hello"
         cbCtl.OnAction = "MyRoutine" 
      End Sub

This code will fail if your user has a UI language other than English. While Excel recognizes English names for command bars themselves, it does not recognize English names for the controls placed on them. In this example, the Tools dropdown menu is not recognized. The solution is to identify CommandBar.Controls by their ID and use FindControl to locate them. 30007 is the ID of the Tools popup menu:

SubAddHelloButton()
         DimcbTools AsCommandBarPopup
         DimcbCtl AsCommandBarButtonBar").FindControl(ID:=30007) 
          SetcbCtl = cbTools.CommandBar.Controls.Add(msoControlButton) 
          cbCtl.Caption = "Hello" 
         cbCtl.OnAction = "MyRoutine" 
      End Sub

Using SendKeys

In the best of cases, the use of SendKeys should be avoided if at all possible. It is most often used to send key combinations to Excel in order to activate a menu item or navigate a dialog box. It works by matching the menu item or dialog control accelerator keys, in the same way that you can use [Alt]+key combinations to navigate Excel using the keyboard. When used in a non-English version of Excel, it is highly unlikely that the key combinations in the SendKeys string will match up with the menus and dialog boxes, having potentially disastrous results.

For example, SendKeys "%DB" will bring up the Subtotals dialog box in English Excel, but will quit Excel when run with the German UI. Instead of using SendKeys to trigger menu items, you should use the RunMenu routine presented earlier to execute a menu item by its CommandBarControl ID.

The Rules for Developing a Multilingual Application

1)       Decide early in the analysis phase the level of multilingual support that you are going to provide, then stick to it.

2)       Do not include any text strings within your code. Always look them up in a table.

3)       Never construct sentences by concatenating separate text strings, as the foreign language version is unlikely to use the same word order. Instead use place holders in your text and replace the place-holder at run time.

4)       When constructing UserForms, always make the controls bigger than you need for the English text; most other languages use longer words.

5)       Do not try to guess the name that Excel gives to objects that you create in code. For example, when creating a new workbook, the first sheet will not always be "Sheet1."

6)       Do not refer to command bar controls by their caption. While you can refer to command bars themselves by their English name, you must refer to the menu items by their ID (for built-in items) or tag (for custom items).

7)       Do not use SendKeys.

Some Helpful Functions

In addition to some of the custom functions already presented, such as RunMenu and IsDateUS, here are some more functions that I have found to be very useful when creating multinational applications. Note that the code has been written to be compatible with all versions of Excel from 5.0 to 2000 and hence avoids the use of newer VBA constructs (such as giving optional parameters specific data types).

bWinToNum. This method, shown in FIGURE 5, checks if a string contains a number formatted according to the WRS and converts it to a Double. The function returns True or False to indicate the success of the validation, and optionally displays an error message to the user. It is best used as a wrapper function when validating numbers entered by a user, as shown in the "Interacting with Your Users" section in Part I of this series.

FunctionbWinToNum(ByValsWinString  AsString, _
                         ByRefdResult As Double, _
                   OptionalbShowMsg) As Boolean
        ' *****************************************************
      ' * Function Name:   bWinToNum
      ' *
      ' * Input/Output:    sWinString    String to be converted. 
      ' *                  dResult       The converted number, set to zero if
      ' *                                the number is not valid or empty. 
      ' *                  bShowMsg      Optional.  True (or missing) to show
      ' *                                an error message. 
      ' *                                False to suppress the error message. 
      ' * Purpose:            Function to convert a number string in Windows
      ' *                     format to a number. 
      ' *****************************************************
          DimdFrac As Double
          'Take a copy of the string to play with. 
         sWinString = Trim(sWinString) 
         dFrac = 1
          IfIsMissing(bShowMsg) ThenbShowMsg = True
         IfsWinString = "-" ThensWinString = "0" 
         IfsWinString = "" ThensWinString =  "0" 
          'Check for percentage, strip it out and remember to divide by 100. 
         IfInStr(1, sWinString, "%") > 0  Then
            dFrac = dFrac / 100
            sWinString = Application.Substitute(sWinString, "%", "") 
         End If
         ' Are we left with a number string in windows format? 
         IfIsNumeric(sWinString)  Then
            ' If so, convert it to a number and return success. 
            dResult = CDbl(sWinString) * dFrac
            bWinToNum =  True
         Else
            ' If not, display a message, return zero and failure. 
             IfbShowMsg ThenMsgBox "This entry was not recognised as a number," _
               & Chr(10) & "according to your Windows Regional Settings.", vbOKOnly
            dResult = 0
            bWinToNum =  False
         EndIf
       End Function

FIGURE 5: The bWinToNum function.

bWinToDate. This provides the same functionality as bWinToNum, but for dates instead of numbers.

 Function bWinToDate(ByValsWinString  AsString, _
                          ByRefdResult As Double, _
                          OptionalbShowMsg) As Boolean
      ' *****************************************************
      ' * Function Name:   bWinToDate
      ' *
      ' * Input/Output:    sWinString   -String to be converted. 
      ' *                  dResult      -The converted number, set to zero if the
      ' *                                number is not valid, or empty. 
      ' *                  bShowMsg     -Optional
      ' *                               True 
      (or missing) to show
      ' *                                   an 
      error  message. 
      ' *                                False to suppress the error message. 
      ' *
      ' * Purpose:                       
      Function to Convert a date string in
      ' *                                   Windows' 
      format to a date. 
      ' *****************************************************
            IfIsMissing(bShowMsg) ThenbShowMsg = True
          IfsWinString = "" Then
            ' An empty string gives a valid date of zero. 
            dResult = 0
            bWinToDate =  True
            ElseIfIsDate(sWinString) Then
            ' We got a proper date, so convert it to a Double
            ' (i.e. the internal date number). 
            dResult = CDbl(CDate(sWinString)) 
            bWinToDate =  True
         Else
            ' If not, display a message, return zero and failure. 
            IfbShowMsg ThenMsgBox "This entry was not recognised as a date," & _
               Chr(10) & "according to your Windows Regional Settings.", vbOKOnly
            dResult = 0
            bWinToDate = False
         End If
       End Function

FIGURE 6: The bWinToDate function.

sFormatDate. This function, shown in FIGURE 7, formats a date according to the WRS, using a four-digit year and optionally including a time string in the result.

Function sFormatDate(dDate As Date, Optional bTimeReq) As String
      ' *****************************************************
      ' * Function Name:       sFormatDate
      ' *
      ' * Input:               dDate    - The Excel date number. 
      ' *                      bTimeReq - Optional. True to include the time string
      ' *                                           in the result. 
      ' *
      ' * Output:              Returns the date formatted as a string according to
      ' *                         Windows settings and with a 4-digit year. 
      ' *
      ' * Purpose:             Format a date for display. 
      ' *
      ' *****************************************************
          Dim sDate As String
          If IsMissing(bTimeReq) Then bTimeReq = False
          Select Case Application.International(xlDateOrder) 
            Case 0
            ' month-day-year
               sDate = Format$(dDate, "mm/dd/yyyy") 
            Case 1
            ' day-month-year
               sDate = Format$(dDate, "dd/mm/yyyy") 
            Case 2
            ' year-month-day
               sDate = Format$(dDate, "yyyy/mm/dd") 
         End Select
          If bTimeReq Then sDate = sDate & "" & Format$(dDate, "hh:mm:ss") 
          sFormatDate = sDate
       End Function

FIGURE 7: The sFormatDate function.

ReplaceHolders. This function, shown in FIGURE 8, replaces the placeholders in a string with values provided to it.

Function ReplaceHolders(ByVal sString  As String, ParamArray avReplace())As String
      ' *****************************************************
      ' * Function Name:   ReplaceHolders
      ' *
      ' * Input/Output:    sString   - The text to replace the placeholders in
      ' 
      *                  avReplace - A list of items to replace the placeholders. 
      ' *
      ' *                  Returns 
      the original text, with the placeholders
      ' *                  substituted with the values. 
      ' *
      ' * Purpose:         To substitute the placeholders in a string with
      ' *                  their values. 
      ' *
      ' 
      *****************************************************
       
         
      Dim i As  
      Integer
       
         ' 
      Work backwards, so we don't replace %10 with our %1 text. 
      
         
      For i = UBound(avReplace)  
      To LBound(avReplace) 
      Step -1
            sString 
      = Application.Substitute(sString, "%" & i, _
       
               avReplace(i 
      - LBound(avReplace))) 
         
      Next
       
         ReplaceHolders = 
      sString
       
      End Function

FIGURE 8: The ReplaceHolders function.

Conclusion

It is possible to create an Excel application that will work on every installation of Excel in the world and support all 180-plus Office languages, but it is unlikely to be economically viable.

If you have a limited set of users and you are able to dictate their language and WRS, you can create your application without worrying about international issues. Even if this is the case, you should get into the habit of creating locale-independent code. The requirement for locale-independence should be included in your analysis, design, and coding standards. It is much, much easier and cheaper to write locale-independent code at the onset than to rework an existing application.

At a minimum, your application should work regardless of the user's choice of WRS or Windows or Office UI language. You should be able to achieve this by following the rules listed in this article.

The following Excel features don't play by the rules and have to be treated very carefully:

• OpenText
• ShowDataForm
• Pasting text from other applications
• The .Formula property in all its guises
• <range>.Value
• <range>.FormulaArray
• <range>.AutoFilter
• <range>.AdvancedFilter
• The =TEXT worksheet function
• Application.Evaluate
• Application.ConvertFormula
• Application.ExecuteExcel4Macro

There are also some features in Excel that you may have to avoid completely:

• Web Queries
• SendKeys
• Using True and False in imported text files

Reprinted with permission from Wrox Press Ltd.

Copyright, 1999 by Wrox Press Ltd.

ISBN: 1-861002-54-8

For more information, go to http://www.wrox.com/.

John Green lives and works in Sydney, Australia as an independent computer consultant specializing in Excel and Access. With 30 years of computing experience, a Chemical Engineering degree, and an MBA, he draws from a diverse background. He established his company Execuplan Consulting in 1980, specializing in developing computer-based planning applications and training. He has been accorded the status of MVP (Most Valuable Professional) by Microsoft for his contributions to the CompuServe Excel forum and the MS Internet newsgroups every year since 1995.

Stephen Bullen lives in Milton Keynes, England. After graduating from Oxford University in 1992 with an MA in Engineering, Economics, and Management, Stephen joined Price Waterhouse Management Consultants where he spent five years developing Excel applications for a wide range of multinational corporations. He established Business Modelling Solutions Ltd. in 1997 to specialize in Excel and Access development and consulting. He has received an MVP award every year since 1996.