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.
Number separators in EVALUATE()
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number separators in EVALUATE()
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.
It appears to be caused by the thousands separator: Evaluate(FormatNumber(5000/2,2,,,vbFalse) works correctly on my computer.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 141
- Joined: 21 Oct 2011, 10:08
Re: Number separators in EVALUATE()
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.
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.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number separators in EVALUATE()
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
Hans
-
- 2StarLounger
- Posts: 141
- Joined: 21 Oct 2011, 10:08
Re: Number separators in EVALUATE()
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!
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!
-
- 2StarLounger
- Posts: 141
- Joined: 21 Oct 2011, 10:08
Re: Number separators in EVALUATE()
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.
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.