0

I am writing a excel macro with VBA that contains a simple formula:

=NOT(AND($L1>360;$K1<45))

However, this does only work, if the language in excel is set to English. If the applicants language is for instance German, it looks like that:

=NICHT(UND($L1>360;$K1<45))

Is there a way to use a generic one that works for all applicants?

Ernte1893
  • 183
  • 2
  • 11
  • Are you using this formula within a formatcondition or as a formula in a cell? – Ike Sep 17 '21 at 07:25
  • I am using the expression withing a formatcondition. Does that make a difference? – Ernte1893 Sep 17 '21 at 07:30
  • Yes - that's why I am asking: Regardless of VBA or not: the formula of formatcondition is always language specific - so you have to try to build it without "Keywords": something like ($L1>360)*($K1<45)=0 (not sure if this is the correct result - you have to try) – Ike Sep 17 '21 at 07:38
  • [Excel Functions Translator](https://support.microsoft.com/en-us/office/excel-functions-translator-f262d0c0-991c-485b-89b6-32cc8d326889) OR [Excel Function Translations EN-DE](https://www.perfectxl.com/excel-glossary/what-is-excel-function/translations-english-german/) and if you created the function in English, excel will do the translations. So opening on a German version, you will see the formula's in German. – Luuk Sep 17 '21 at 07:39
  • 1
    Propably you should change your question to include the information about format condition - then it wouldn't be a duplicate. – Ike Sep 17 '21 at 07:40
  • Since I cannot post an answer anymore, I'll give it here: Formulas entered via VBA already are language-independent, meaning English. ~~However, it expects R1C1 addressing style, no matter your Excel settings.~~ Your formula would work in this format for example (note that the semicolon became a comma): ```Range("A1").Select ActiveCell.FormulaR1C1 = "=NOT(AND(RC11>360,RC12<45))" ``` **Edit:** My mistake, the R1C1 adressing style is only necessary because I used ```.FormulaR1C1``` - You can just use ```.Formula = "=NOT(AND($L1>360,$K1<45))"``` – Franz Reischl Sep 17 '21 at 07:42
  • @FranzReischl That is wrong. You can enter formulas in either A1 or R1C1 with VBA. That is why there is `Formula` and `FormulaR1C1`. It is about the language, not the notation. – GSerg Sep 17 '21 at 07:46
  • @GSerg Thanks, I noticed immediately after posting. I tried to edit the answer to clarify. – Franz Reischl Sep 17 '21 at 07:49
  • @Ike Is it really the case that formatcondition only accepts localized formulas? That's ridiculous, you have that, and then there is `FormulaArray` that does the opposite and [only accepts](https://stackoverflow.com/a/25251099/11683) non-localized formulas. Talk about coming up with properly international Excel documents. – GSerg Sep 17 '21 at 07:51
  • @FranzReischl That would also be wrong, you can enter formulas in either English or the local language, that is why there is `Formula` and `FormulaLocal`. So there is four in total, `Formula`, `FormulaR1C1`, `FormulaLocal` and `FormulaR1C1Local`. – GSerg Sep 17 '21 at 07:53
  • @GSerg Yes it's definitly like I said - and that's not a problem of VBA - you have the same issues when typing the formula via the GUI – Ike Sep 17 '21 at 07:53

0 Answers0