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?

19 comments:

Unknown said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by a blog administrator.
Unknown said...

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

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

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

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

Technical Assistance said...

thanks for sharing a informative article.so keep it up and share some more posts.
Coupondunia
Digital Marketing Institute
best matrimonial site

BİLAL HAN said...

no deposit bonus forex 2021 - takipçi satın al - takipçi satın al - takipçi satın al - takipcialdim.com/tiktok-takipci-satin-al/ - instagram beğeni satın al - instagram beğeni satın al - google haritalara yer ekleme - btcturk - tiktok izlenme satın al - sms onay - youtube izlenme satın al - google haritalara yer ekleme - no deposit bonus forex 2021 - tiktok jeton hilesi - tiktok beğeni satın al - binance - takipçi satın al - uc satın al - finanspedia.com - sms onay - sms onay - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - perde modelleri - instagram takipçi satın al - instagram takipçi satın al - cami avizesi - marsbahis

Anonymous said...

MMORPG OYUNLARI
ınstagram takipçi satın al
Tiktok Jeton Hilesi
TİKTOK JETON HİLESİ
SAÇ EKİMİ ANTALYA
instagram takipçi satın al
İNSTAGRAM TAKİPÇİ
metin2 pvp serverlar
instagram takipçi satın al

Anonymous said...

perde modelleri
sms onay
mobil ödeme bozdurma
nft nasıl alınır
ankara evden eve nakliyat
trafik sigortası
DEDEKTÖR
web sitesi kurma
aşk kitapları

Anonymous said...

kadıköy lg klima servisi
tuzla bosch klima servisi
tuzla arçelik klima servisi
çekmeköy samsung klima servisi
ataşehir samsung klima servisi
çekmeköy mitsubishi klima servisi
maltepe arçelik klima servisi
kadıköy arçelik klima servisi
kartal samsung klima servisi

korsan taksi said...

Good content. You write beautiful things.
vbet
sportsbet
taksi
vbet
hacklink
mrbahis
hacklink
sportsbet
mrbahis

betmatik said...

Good text Write good content success. Thank you
tipobet
kibris bahis siteleri
betpark
poker siteleri
slot siteleri
bonus veren siteler
mobil ödeme bahis
kralbet

Sarp said...

salt likit
salt likit
NHB40C

ceren said...

https://saglamproxy.com
metin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
MK5SU7

Post a Comment