Excel VBA Tip: translate formulas between local language and English

noreply@blogger.com (Tatu Vanhanen) — Wed, 06 Oct 2010 18:55:00 +0000

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?

VanTed Bits

Excel VBA Tip: translate formulas between local language and English