none
language used in formulas and conditional formulas RRS feed

  • Question

  • Hello

    My Excel is in french. All formulas use the french functions, example =SOMME(....)

    However if in VBA I use the Evaluate function this function works with english formulas like =SUM(...)

    I can modify the most obvious functions like SOMME, ET, etc.. into SUM, AND,... but there should be a better way to decide in which language evaluate() operates.

    Can somebody clarify this ?

    I looked into but did not understand the language settings parameters if this is part of the answer

    Thanks in advance

    Saturday, February 6, 2016 6:43 PM

Answers

All replies

  • VBA, including the Evaluate function, is strictly US English-oriented. So you have to use the English names of worksheet functions, use point as decimal separator and comma as list separator, and provide dates in mm/dd/yyyy format.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, February 6, 2016 9:06 PM
  • Thank you for this clarification.

    Since I have to translate the french names into the english names where can I find a table or correspondance between the 2 sets of function names ? (i.e. SOMME -> SUM, etc... some of the correspondances are not obvious and there are tons of functions in Excel)

    Sunday, February 7, 2016 7:45 AM
  • We can use worksheet functions in VBA.
    for example:
        With Worksheets(1)
            Cells(1, 1).Value = Application.WorksheetFunction.SUM(Cells(2, 1), Cells(11, 1))
        End With
    In your French environment, does "Sum" appear "SOMME"?

    P.S.
      Very sorry, I've changed the langeage of Excel 2013 from Japanese to French,
      and tried "Application.WorksheetFunction.SOMME(), in vain.
      I could not use "SOMME" in VBA and in worksheet.
    • Edited by Ashidacchi Sunday, February 7, 2016 8:08 AM
    Sunday, February 7, 2016 7:52 AM
  • I want to evaluate with VBA (Worksheet.Evaluate) an expression written in Excel either in a cell or in a conditional format, both of which are in French
    Sunday, February 7, 2016 8:05 AM
  • See http://dolf.trieschnigg.nl/excel/index.php

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JMB1502 Monday, February 8, 2016 8:52 AM
    Sunday, February 7, 2016 10:49 AM
  • beautiful  !!!

    thanks many times

    • Marked as answer by JMB1502 Monday, February 8, 2016 8:52 AM
    • Unmarked as answer by JMB1502 Monday, February 8, 2016 8:52 AM
    Sunday, February 7, 2016 4:23 PM