Search code examples
vbaexcelnumber-formatting

VBA erases local currency number format

I am trying to apply a number format from a named cell to another one, where the source cell format can be a percentage, a currency (€ in my case) or whatever. My code uses the .NumberFormat range property and looks like the one below:

For newCell in newCellsRange
    Range("newCell").Value = some_calculation()
    Range("newCell").NumberFormat = Range("sourceCell").NumberFormat
Next newCell

It works fine for what concerns the way the range is displayed on the sheet (be it a percentage, a currency or a date).

However, if this very same range is linked to a bar chart and its value used as a label (no fancy formatting, right click on the series > add data labels and that's all), the label format will change when I trigger the macro that updates the chart source range (newCellsRange in my example): the format will change from the Euro currency format to an improper American one: 1 523€ will become 1,523 $, 235 € will become ,235 $.

To give further details, I've found out that the .NumberFormat property of the range is "#,##0 $" (which displays €) while the chart label's one is "# ##0 $". I don't have a clue:

  • why the macro would make the comma disappear as I don't do anything for that
  • why "#,##0 $" would show up as € on the spreadsheet while "# ##0 $" would be $.

What can I do not to get this weird format switch?

In case this helps (which I doubt): Excel 2013 32 bits (English version) on W7 Enterprise (English version)

Solution

  • The number format depends on two things. At first on the locale settings of the Windows operating system and at second on the chosen format in Excel. So the "Currency" format can be different dependent on the locale settings. With comma or point as decimal point or with different currency symbols. But in English Excel versions its name will always be "Currency".

    In VBA the default language is always US English. This is a dilemma with number formats because the default in US English is the decimal point and $ as currency symbol. So the "Currency" format, taken from the Range.NumberFormat property, will be "#,##0.00 $" also in my German Excel.

    If I assign Range.NumberFormat = "#,##0.00 $" in my German Excel then the sheet will map this to the locale "Currency". Why the Chart then has problems with this? I don't know.

    Microsoft tries to solve the dilemma in VBA by having ...Local properties. So 

    For newCell in newCellsRange
    Range("newCell").Value = some_calculation()
    Range("newCell").NumberFormatLocal = Range("sourceCell").NumberFormatLocal
Next newCell

    may solve your problem.