October 6, 2010

Excel VBA Tip: translate formulas between local language and English

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?


mujeeb ahmed said...
This comment has been removed by a blog administrator.
mujeeb ahmed said...
This comment has been removed by a blog administrator.
mujeeb ahmed said...

I need a help regrading this formula.
I have created VBA by your formula I want to know how to work on the sheet.
Can u guide me by your instructions from starting plz.
How to start with it.
I want to translate from Arabic language to English..
Thank you for your help.
Mujeeb Ahmed.

Tatu Vanhanen said...

Thanks for your comment! If you use the provided workbook, just put your formula in the "Local version" column, and the english version will appear in the "English version" column. If you want to translate from English to local language, put the English version in the "English version" column (with a single quote in the beginning), and you can see the local version in the formula bar

If, instead, you want to embed the VB code to your own workbook, you need to attach the provided code to the Worksheet_Change event handler. Follow the link that I provided (above the VB code), it tells you how to do that.

mujeeb ahmed said...

Hi, thank you for all your help, but I'm not able to understand how to work with local version I need to translation in Arabic language to English & English to Arabic..
I have downloaded the excel sheet from your link.
Could you please guide me please.
Mujeeb Ahmed

mujeeb ahmed said...
This comment has been removed by a blog administrator.
Tatu Vanhanen said...

I'm afraid I don't understand what is the problem. Do I understand correctly here, that you need to translate the normal Excel formulas you are using to English? Perhaps to search help for some formulas. If that is the case, you just put your Excel formula to the "Local version" cell. The workbook should show the English version in the "English version" cell.

In Excel, the formulas are also localized (personally, I would prefer them to be English despite of the Excel app language). For example, since I am using Finnish version of Excel, the formulas are Finnish. Now, if I put the formula (needs to be valid Excel formula) in the "Local version" cell, the English version should appear to the "English version" cell.

Unknown said...

Hi Tatu,
In the event that you need to adjust the conditional formatting formula on a worksheet using VBA, but you have multiple localized non-english environments, do you need to convert the English conditional formatting formula to the localized format and then insert it into the FormatCondtions of a selected range?

In addition, if I am inserting a formula into a cell in the non-english environment is it valid to use the cell.formula property to insert an english version of the formula? Would it run into evaluation errors based on the fact that it was not inserted in the local format? (Again i think this was pretty clear just want to double check.)

Thank You in Advance!
Matt G

Tatu Vanhanen said...

Matt, thanks for your comment. To my best knowledge, whenever we are dealing with VBA it is the english version we are working with.

So in VBA code there is no need to convert to local version and should cause no evaluation errors.

Leslie Lim said...

I have a great fun reading your blogs. Thank you to the blogger. Have a great day.


Anonymous said...

Thank you for your post, in an international community, this kind of information is very useful. I have another, possibly related problem. I developed a macro in Swedish Excel but with everything in English then passed it on to a French Excel (in France). It all converts very well and works... except the first two sheets (of 25). In the first sheet, it converts to Swedish "=LETAUP()" and on the second it keeps the English "=LOOKUP()" and neither compute (shows the formula as text). On the other 23 sheets it translates to French "=RECHERCHE" and all is well.
Is there a command that can set the language of a workbook or sheet? Thank you very much in advance. /Jorge

Post a Comment