none
Chart axis custom number format localization in Excel 2010

    Question

  • Hi, 

    I've encountered a very annoying problem while formatting numbers on charts in Excel 2010 using vsto on a french version of Excel (happens on both french and english version of windows).

    I've managed to reproduce the issue with the following steps :

    1) Create a simple chart

    2) Apply a custom format number on the source data for the chart. Here, I used : #,##0.0_);(#,##0.0);"- "_._0_). 

     The picture below describes what happens if you do this manually using the french equivalent of the above code # ##0,0_);(# ##0,0);"- "_._0_) vs with Vba with the english code (VBA does the conversion itself).

    If you look at the number format if the axis in the buggy case, you see #,##0.0_);(#,##0.0);"- "_._0_) . In other words, the code was not converted.

     

    3) The weirdest part now : if you provoke some kind of redraw on the source range, the number format gets converted to french ! (in the example above, the chart looks as in the left part). We managed to force this conversion by :

    • changing the width of any column on the sheet
    • if the source range is on another sheet, change the width of any column on the source sheet
    • change the font of the source range
    • put a border around the source range
    • cut and paste the chart
    • ...

     

    Some more details :

    - You can also do this with any kind of number format, so long as it needs to be converted in french, for example "0.0" will result in the same bug.

    - It does not happen if the number format of the axis is not linked with the source data format (seems logic).

    - You can have the same issue with data-labels linked to source data. 

     

    Is this a known issue? any ideas?

    Any help greatly appreciated.

     

    Julien

     


    • Moved by Cindy Meister MVPMVP Friday, February 03, 2012 3:18 PM Excel-specific question (From:Visual Studio Tools for Office)
    Friday, February 03, 2012 1:48 PM

All replies

  • Hi Julien,

    We are trying to repro the issue right now and we would update as soon as we get some valuable suggestions. Please be patient before that.

    Have good day,


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, February 06, 2012 6:28 AM
    Moderator
  • Hi, Is there any solution to this? The same bug persists in Excel 2016 (Excel version 16.0). 5 years is a bit long to be patient.. The major problem seems to be that Axis.TickLabels.NumberFormat does not seem to use the same formatting engine as cell.NumberFormat. "Linked to Source" even gives different formatting on the axis numbers, compared to the cell numbers. Erik
    Wednesday, May 03, 2017 3:42 PM