The Newton/Galileo posts here will continue with their Italian theme for some time yet, but since I am now back in Australia it’s time to give Excel a look in again.
I recently (well a couple of months ago) had a query about using the Eval User Defined Function (UDF) when the decimal separator was set to a comma, rather than a decimal point. In response to that I added an option to the code for Eval and Evala that would replace all occurrences of “,” with “.” and “;” with “,”. This allows the Excel Evaluate command to operate correctly, regardless of how the values are entered.
The revised spreadsheets using these functions may be downloaded from:
Note that the modification has as yet only been made in the Eval and EvalA functions, and not the integration functions.
After modifying the code I discovered that there is an Excel option that allows the original functions to work, without substituting commas for decimal points. In Excel 2010, under File-Options-Advanced, deselect “Use System Separators” and enter a “,” for Decimal Separator, and a space for Thousands Separator.
System separators options
The spreadsheet will now display decimal numbers with a comma separator, and will only accept a comma as the separator for new numbers, but the Eval functions will work correctly.
However – this (strangely) only seems to work if the region language is set to English. If the region is set to French (or presumably any other region that uses the comma separator) the original Eval functions no longer work. I have only given this limited testing (since I normally use the decimal point), so if anyone else has any comment on how the regional separator options work in Excel, please leave a comment below.
Newton Excel Bach, not (just) an Excel Blog 
Hi Doug,
AFAIK, VBA always inherits its DecSep from “the operating system”, whereas you may change it in XL. Therefore, you may experience that different characters are used as DecSep in XL and VBA, resp. Moreover, even within VBA the CStr and Str functions may use different DecSeps, because CStr is localisation aware and Str is not.
At least, in VBA7 there is an Application.International(someConstant) object that allows to obtain all the local settings being used by XL2010.
So here comes a tiny piece of code:
Function ShowDecSeps()
Dim MsgStr$, VBADecSepCStr$, VBADecSepStr$
VBADecSepCStr = Mid(CStr(Format((3 / 2), "0.0")), 2, 1)
VBADecSepStr = Mid(Str(Format((3 / 2), "0.0")), 3, 1) 'STR always adds a leading sign character
MsgStr = "XL's decimal separator is " & Application.International(xlDecimalSeparator)
MsgStr = MsgStr & "VBA's CStr decimal separator is " & VBADecSepCStr & vbNewLine
MsgStr = MsgStr & "VBA's Str decimal separator is " & VBADecSepStr
MsgBox MsgStr
End Function
All three DecSep characters may be different from each other!
LikeLike
Thanks Georg. Very useful!
LikeLike
Doug – I’ve run into this problem too and had to hack workarounds several times. The root cause seems to be that the Evaluate function treats all expressions in US Standard Format eg ?format([“1/6/12″*1],”d/m/yy”) returns 6/1/12 in my European locale. Since the old XLM Evaluate function behaves the same way I have a hunch this is a legacy issue dating back to when International Macro Sheet were used.
Georg – Interesting, I wasn’t aware of that setting. Multilingual support can be a real headache!
LikeLike
Pingback: Daily Download 15: Evaluation of formulas in text | Newton Excel Bach, not (just) an Excel Blog
Sorry. I have a problem. I can’t use this program. When I open the archive and enable the edition, all the resultis of the functions are wrong and say #value. I don’t understand why. Maybe it’s because I have Excel 2013 and this program is for an older version? Please help me to fix this, please.
LikeLike
Excel 2013 is not the problem, the functions work with the latest version.
Are macros enabled?
Do you get any error message at start-up?
Which region are you in? If your system is set-up to use comma decimal separators the decimal points in the spreadsheet text may be causing the problem.
If you want to send me a copy of your spreadsheet my e-mail address is under “About NewtonExcalBach” at the top of the page.
LikeLike