A Little I18n “Fun” in Excel

(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?

Advertisements