Number separators in EVALUATE()

Zauberkind
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Number separators in EVALUATE()

Post by 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". :scratch:

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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number separators in EVALUATE()

Post by 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.
Best wishes,
Hans

Zauberkind
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Re: Number separators in EVALUATE()

Post by 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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number separators in EVALUATE()

Post by 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
Best wishes,
Hans

Zauberkind
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Re: Number separators in EVALUATE()

Post by 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
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Re: Number separators in EVALUATE()

Post by 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.