International Excel Issues

Hi all

Together with Kirill Lapin and Hector Miguel Orozco Diaz I start with a webpage with International Excel problems. If you have suggestions/comments about the content that is on this page now or have problems with workbooks in different language versions let me know. We try to make this a collection of International Excel issues so feedback and problems/solutions are welcome.

International Excel Issues
http://www.rondebruin.nl/international.htm

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Posted in Uncategorized

15 thoughts on “International Excel Issues

  1. Great reference.
    I didn’t think that #VALUE! looked different in different languages, though it makes sense!

  2. Not sure if this is an international issue, 2007 issue, or what.

    I’ve got a pivottable that has one field (month) that contains UK format dates (dd/mm/yyyy). The table’s generated on the fly, so there’s no cached data or the like. There’s an option for a user to use a ‘filter’ form on the table – mainly to quickly select groups of our products, customers, etc, but also to filter by date. When the form activates, it adds each pivotitem in the month pivotfield to a combobox. All well and good so far.

    Problem is when you try to alter the visible property of the pivotitems in the pivotfield, it all goes a bit Error 1004 if the pivotitem is a UK formatted date.

  3. This is a question that I have posted at Peach.east.lsoft.com back in June 2009. When we ran the following codes in Excel XP vs. Excel 2007, we get different outcomes. Range(“B2:B3) under Excel 2007 returns mm/dd/yyyy format while under Excel XP it returns dd/mm/yyyy format.

    Initially we thought it was an issue with our original build and had decided to recreate our image from scratch. The following were the steps we have taken to isolate the problem.

    This is a critical error especially for finance and banking environment if application.transpose is used. An array of six monthly roll will now transpose into six daily roll. This implies that interest charge could change from monthly compounding of 6 months interest to just daily compounding of 6 days of interest.

    Just imagine if you have code tested thoroughly in one version (ie Office XP) and approve for production. The moment IT upgrade to the new version, your entire coding stops working with no notification. By the time someone finds out, it was all too late because it has already affected too many of your clients.

    Can someone please test this again because I think it is important to get Microsoft to fix this.

    *****************************************************************************************************
    Steps taken with various permutation:
    *****************************************************************************************************
    1.) Get a standalone notebook
    2.) Restore Windows from recovery CD (under either scenario)
    a.) Windows XP Professional SP3,
    b.) Windows 2003 Server Citrix,
    c.) Windows 7
    3.) Install Office (under either scenario)
    a.) 2007 SP3,
    b.) Office 2003
    4.) Set the locale to English (under either scenario)
    a.) (New Zealand)
    b.) (Australia)
    5.) Open Excel
    6.) Run the following codes on a new workbook

    Sub test()
    Range(“A2:C3?).ClearContents
    Range(“A2:A3?).Value2 = Return_Date_AsDouble
    Range(“B2:B3?).Value2 = Return_Date_AsDate End Sub

    Function Return_Date_AsDouble()
    ‘ dimension variable my_date array to
    ‘ contain double date type values
    Dim My_Date(1) As Double
    My_Date(0) = DateSerial(1994, 1, 1)
    My_Date(1) = DateSerial(1994, 1, 2)
    Return_Date_AsDouble = Application.Transpose(My_Date) End Function

    Function Return_Date_AsDate()
    ‘ dimension variable my_date array to
    ‘ contain date type values
    Dim My_Date(1) As Date
    My_Date(0) = DateSerial(1994, 1, 1)
    My_Date(1) = DateSerial(1994, 1, 2)
    Return_Date_AsDate = Application.Transpose(My_Date) End Function

  4. Hi Ron,

    Excellent piece of work.
    Would be good to add some stuff on VBA, as that complicates things even further.
    Examples:

    – Trying to set an autofilter on a date column using VBA which works in all locales is a challenge.
    – FormulaLocal property of Name object returns local formula, but does not accept a local formula
    – Formula1 property of validation object has same issue: it returns a locale formula but only accepts US syntax.

  5. JKP:

    This is from “Excel 2002 VBA Programmer’s Reference”
    Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

    http://www.oaltd.co.uk:80/ExcelProgRef/Ch22/ProgRefCh22.htm
    Search for “Range.AutoFilter” and you’ll see this note:

    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 behaviour. 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/2001? will find all dates
    on or after 1st Feb, 2001, 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, 2001 and will work in any
    locale:

    Range(“A1:D200?).AutoFilter 2, “>=02/01/2001?, xlAnd, “<=02/01/2001?

  6. JKP,

    Ron has a link to that section of the book on his site (in case you forget again ).

    I search my sent folder for “curious beast” when I need to refer to it.

    (Before Ron gets mad at us for not noticing that it’s on his page .)

  7. >Before Ron gets mad at us for not noticing that it’s on his page

    LOL

    I hope I have more time next week to add more to the page
    Today I add “Command bars and controls in Excel 97-2003?

  8. Hi Ron,

    Have you had a chance to look at the PivotTable/international dates one? I’m curious if it’s just something I’m missing, or if it is appearing for other people.

    Cheers,

    Matt

  9. Hi Matt

    Because it is a area that I not use a lot I ask Debra to look
    at your problem so you have a good answer.

    This is it :

    They’re probably getting that message because the code is trying to hide the
    pivot items that don’t match the dates set in the filter. The VBA dates are
    in USA format, so none of them will match the UK dates in the pivot table.

    Because all of the pivot items can’t be hidden, the Error 1004 will appear
    when trying to hide the last pivot item.

    In the code, they’d have to convert the UK dates to US format, so they can
    be checked for a correct match.

    Have a great weekend

  10. I have a question.

    There are a bunch of constants used against Application.International.

    I get why xlLowerCaseColumnLetter, xlLowerCaseRowLetter, xlUpperCaseColumnLetter, and xlUpperCaseRowLetter are constants, because the letter changes depending on your country.
    But why constants for xlLeftBrace, xlRightBrace, xlLeftBracket, xlRightBracket?

    I am not aware of any country that uses a different symbol for the square bracket in R1C1 notation, or the curly braces in an array.
    Does anyone know any different? If you do, please reply?

    Cheers,
    Rob


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.