Let Microsoft Excel translate formulas between local language and English
Localized loveliness
Whether you like it or not, all functions are localized in Microsoft Excel. This may sometimes lead to situations where you would like to know the English equivalent of a localized function - or vice versa. Fortunately there are web sites, such as this, that contain function translations between certain languages, but you can also harness Excel itself to do the translation.
Translator spreadsheet
 |
| Local version of the formula is shown in the formula bar |
You can download the above translator spreadsheet and use it as is. If you want to know how it works, read on.
How does it work?
Internally, Excel understands only English. Before evaluating formulas, Excel "generalizes" them to the English version it understands. This affects not only functions, but also parameter separators (semicolon vs comma).
"Under the hood" in VBA, you are working in the internal English environment, too. This means that even if a formula is local language in the worksheet, it will be English when you get it from a cell (with Range.Formula-propety) in VBA.
Likewise, even if a formula will be local language in the worksheet, it must be English when you set it to a cell in VBA.
The VBA code
Knowing these facts, you can easily craft a simple VBA Worksheet_Change event handler that translates any formula between the local version and English version:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Variant
' Prevent recursive change event because of this code
Application.EnableEvents = False
' Column B (up to row 100) is the local version,
' column C (up to row 100) is the English version.
If Not Intersect(Target, Range("B3:B100")) Is Nothing Then
For Each cell In Target
' Take the formula, and put it in the English cell as text
cell.Offset(0, 1).Value = "'" & cell.Formula
Next cell
ElseIf Not Intersect(Target, Range("C3:C100")) Is Nothing Then
For Each cell In Target
' Take the text from the English cell and
' put it as a formula to the equivalent local cell
cell.Offset(0, -1).Formula = cell.Value
Next cell
End If
' Re-enable events
Application.EnableEvents = True
End Sub
The event handler reacts to changes in column B (local formula) and C (English formula). The "trick" here is to let a formula do a roundtrip from column B through VBA code into column C, where it's forced to be just plain text (line 14). So you are just revealing the internal version of the formula that Excel itself uses.
Going in the other way is just as easy. You get the text (that represents the English formula) from column C and set it as a formula to the adjacent cell in column B. Because the formula is now a real formula, you have to select the cell and look in the formula bar to see the local version.
That's it this time. What do you think; is this useful to you or maby just old information?
