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
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.
Moved byCindy Meister MVPMVPFriday, February 03, 2012 3:18 PMExcel-specific question (From:Visual Studio Tools for Office)