CODEWITHSUNDEEP
vbaexcelnumber-formatting
jodoox
jodoox

Reputation: 839

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:

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)

Upvotes: 2

Views: 1945

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

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.

Upvotes: 1

Related Questions