0

I have a lot of simple formulas in named ranges that I reference in VBA.

For example one cell contains

=TEXT(TODAY(),"tt.MM.jjjj")

and outputs 17.11.2022

I stumbled upon the correct version through trial and error, and after failing with formulas like

=TEXT(TODAY(),"dd.mm.yyyy")

that for some reason produced 00.mm.yyyy as output.

What is even weirder I have a desktop (1) and a laptop (2) and a computer (3) where the sheet is supposed to run. The desktop and laptop have both English versions of Excel and the working computer has a German version. The first formula produces the correct output on machines 1 and 3, but not on 2. Total randomness.

Additionally I stumbled upon a weird bug in my VBA (English version). Namely if you use

ConditionalFormatting AND =MOD(ROW()/2)

and port this file to a computer with german Excel you get an error.

For some reason, and only in ConditionalFormatting in VBA you have to use

=REST(ZEILE()/2)

if you want your code to work?

Who came up with such an unworkable, error-inducing, time sucking, nerve wrecking scheme? Why can't one have universals like dd spit out 2-digit day of a month regardless of the language?

What is more important is there a way to make such sensible formulas free form EXCELS LOCAL QUIRKINESS?

rioZg
  • 530
  • 1
  • 6
  • 17
  • Use `FormulaLocal` then you can use your own language, but if you port it to someone who uses a different language it will fail. – Scott Craner Nov 17 '22 at 18:02
  • Moreover formulas used by conditional formatting always have to be in the local language - read this one, there are some solutions: https://stackoverflow.com/q/13247771/16578424 – Ike Nov 17 '22 at 18:21
  • Thanks for the quick replies. I am aware of the "tweaks" and "runarounds". It is exactly what pisses me off - the need for such solutions. I was asking if there are some overarching principles that would get rid of all this, like ApplicationLanguge = FORCE ENGLISH? – rioZg Nov 17 '22 at 18:39
  • Bottom lines here: in Excel, always use your local language. In VBA there are English and international versions available eg .Formula and .FormulaLocal etc. To force English, set your regional settings to an English speaking location. – chris neilsen Nov 17 '22 at 18:57

0 Answers0