I have a workbook with VBA code that specifies a number format for data labels in a horizontal bar chart.
Chart.SeriesCollection(1).DataLabels.NumberFormat = "0.0"
In my copy of Excel (O365) in English, output is as expected, e.g. 3.7. When the workbook is opened in another copy of Excel (O365) in a language other than English, Excel appears to insert a backwards slash that can be seen in the Format Code -field of the Format Data Labels -sidebar (i.e. 0\.0). Where output should be e.g. 3.7, output becomes 0.4.
The issue persists when I load up the workbook after it has been touched by the non-English Excel - my version doesn't throw away the backward slash. The issue goes away when I delete the backwards slash, and output is as expected again.
It seems to me Excel treats the leading zero and decimal point as plain text and the second zero as the intended numerical character.
How can I make sure Excel maintains the number format specified in VBA when a workbook is opened in a different language version?
It appears the easiest solution is to programmatically disable decimal separators and replace them with your own, as described here.
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
I came across this solution in this thread while searching for how to determine the language version of Excel.
Another solution is to use the decimal separator prescribed by the system when defining the number format. This solution seems to also produce the desired result (one decimal place displayed, even if 0), though with whatever decimal separator the system has defined:
Chart.SeriesCollection(1).DataLabels.NumberFormat = "0" & Application.International(xlDecimalSeparator) & "0"