Return of Excel – Evaluating commas

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.

This entry was posted in Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

4 Responses to Return of Excel – Evaluating commas

  1. Georg says:

    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!


  2. lhm says:

    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!


  3. Pingback: Daily Download 15: Evaluation of formulas in text | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s