A Little I18n “Fun” in Excel
10 February 2010 1 Comment
(stackoverflow rep: 9595, Project Euler 91/277 complete)
I work in the London offices of a multinational banking corporation, with the majority of my colleagues located in Germany. Unsurprisingly, most of these colleagues use the German language version of Excel, which is almost transparently compatible with the English version we have here.
One little edge case cropped up this morning, however: date formatting strings in TEXT()
functions. Where I would write =TEXT(A1, "DD/MM/YYYY")
one of my German colleagues would have =TEXT(A1, "TT.MM.JJJJ")
for Tags and Jahres. Excel doesn’t translate this – and I don’t see how it could, at least, not automatically.
After a little thought, we cooked up the code below, presented here (a) in case it might be useful and (b) because I’m curious to know if anyone has a better way to do it:
Option Explicit ' assume for now that there is only one date format used in TEXT() functions Private Const ENGLISH As String = """dd/mm/yyyy""" Private Const GERMAN As String = """TT.MM.JJJJ""" Private Sub Workbook_Open() If ThisIsGermanExcel Then SwitchDateFormats ENGLISH, GERMAN Else SwitchDateFormats GERMAN, ENGLISH End If End Sub Private Function ThisIsGermanExcel() As Boolean Dim dt As Date On Error GoTo Catch ' do we know if there's a "correct" way to identify the language version? ' English Excel doesn't like this, I'm told German Excel does... dt = DateValue("23.02.2010") ThisIsGermanExcel = True Finally: On Error GoTo 0 Exit Function Catch: ThisIsGermanExcel = False Resume Finally End Function Private Sub SwitchDateFormats(ByVal fromFmt As String, ByVal toFmt As String) Dim sht As Worksheet Dim cel As Range For Each sht In ThisWorkbook.Worksheets For Each cel In sht.UsedRange ' lots of ways this may need to be more careful... cel.Formula = Replace(cel.Formula, fromFmt, toFmt) Next Next End Sub
So what do people think? Anyone? Anyone?
What does Application.LanguageSettings.LanguageID do for you?
You might use .SpecialCells(xlCellTypeFormulas) in the last code in case you run into monster spreadsheets.