CODEWITHSUNDEEP
excelvbadatelocale
user2858200
user2858200

Reputation: 123

VBA date arguments\format in another locale\language

I have english office installed. Building a macro that builds a string using dates in one of its steps:

Range("G2").Formula = "=TEXT(TRIM(F2),""dd.MM.yyyy"")"
Range("G2:G" & LastRowNum).FillDown

or say

Range("R2").Formula = "=H2&""-""&TEXT(today(),""ddMMyyyy"")&TEXT(now(),""HHmm"")"
Range("R2:R" & LastRowNum).FillDown

So for me, it works fine and i am getting the string correctly. Then I am sending this macro to colleagues, and they have office installed with different language.

So for them, this i am unable to build a string. I have to convert my vba code to reflect their locale. How do i force excel to automatically adjust the formula\vba to the user's locale, regardless of what that locale is, whether it be portuguese or russian?

I tried putting * (asterisk) - didn't work

Range("R2").Formula = "=H2&""-""&TEXT(today(),""*ddMMyyyy"")&TEXT(now(),""*HHmm"")"
Range("R2:R" & LastRowNum).FillDow

I tried mentioning that formula was built in english, thinking that excel would "translate" it to user's locale, also didn't work

Range("R2").Formula = "=H2&""-""&TEXT(today(),""[$-0409]ddMMyyyy"")&TEXT(now(),""[$-0409]HHmm"")"
Range("R2:R" & LastRowNum).FillDow

The same issue applies to conditional formatting set thru vba, but that's another question, i think i will be able to apply the same approach if you suggest me how to resolve date issue above.

I have many colleagues from different countries and i don't like having different macro versions for each of them.

Thanks

Upvotes: 0

Views: 71

Answers (0)

Related Questions