October 6, 2010

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.

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?

10 comments:

Hi,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.RegardsMujeeb Ahmed.

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.

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.Regards Mujeeb Ahmed

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.

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.)