Number separators in EVALUATE()

[Zauberkind]

Greetings,
I have a problem with internationalisation:
I am running Excel over Win 10.

The localisation is set appropriately with ThousandsSeparator = "." and DecimalSeparator = ",".

If I FormatNumber( 5000/2, 2, , , vbTrue ), I get the expected string "2.500,00".
If I Evaluate(FormatNumber(5000/2,2,,,vbTrue)), I get "Error 2015".

I deduce from that that Evaluate gets its Localisation from somewhere else.
I fear that it is fixed by the installation language (in this case, English), and immutable.

Does anyone know how to find the values, other than by trial-and-error?

Thanks in advance for any help or advice.
Z.

[HansV]

It's not the language - on my system, everything is set to US English, yet Evaluate(FormatNumber(5000/2,2,,,vbTrue)) also returns Error 2015.
It appears to be caused by the thousands separator: Evaluate(FormatNumber(5000/2,2,,,vbFalse) works correctly on my computer.

[Zauberkind]

Hi Hans,
I think you might be onto something there:
On my system,
sTestString = "2,500.05" ' English number format
Debug.Print sTestString, Evaluate(sTestString)
sTestString = "2.500,05" ' European number format
Debug.Print sTestString, Evaluate(sTestString)
yields:
2,500.05 Error 2015
2.500,05 Error 2015
as you predicted.
BUT:
sTestString = "2500.05" ' English number format
Debug.Print sTestString, Evaluate(sTestString)
sTestString = "2500,05" ' European number format
Debug.Print sTestString, Evaluate(sTestString)
yields:
2500.05 2500,05
2500,05 Error 2015
The first takes a string in English number format and yields a number displayed in Locale-specific format.
The second takes a number in Locale-specific format and generates an error.
That's the real problematic behaviour.

Thanks for the effort.
Z.

[HansV]

The moral of the story appears to be: don't try to convert numbers to localized text strings in VBA if you want to use them in further calculations/manipulations

[Zauberkind]

That would be nice in a perfect world!
The problem here is parsing strings originating in another system.
The solution is to find a way to edit the separators to generate acceptable Evaluate arguments from them.
I foresee some very ugly code in the future!
Cheers,
Z!

[Zauberkind]

Greetings,
In case anyone comes here to look for solutions to similar issues, like parsing formulae in cells, the formula found in Range.Formula|Formula2|FormulaRC are in "the language of the macro" and can be passed to Evaluate(), while Range.FormulaLocal|Formula2Local|FormulaRCLocal are in "the language of the user" (ie: localised) and will give problems if the separators differ.
Regards,
Z.