How to translate Excel formula function name to another language?

[Ming Zhao]

I am developing an Excel application in VS2005 for a multi-language
environment. The application writes a formula to an Excel cell from VB code
compiled as a dll, such as MyCell.Formula = “=POWER(A1,B2)â€

It works for English but when I select Spanish from the Windows’ Regional
and Language Options it will show #NAME? error in MyCell. I notice if I enter
the formula directly into Excel, save and reopen the workbook after selecting
Spanish, then POWER will be automatically translated to POTENCIA.

How to use this automatic translation capability of Excel in VB code? My
code obviously does not work because it uses absolute text string.

 

[Ming Zhao]

I found the answer myself. As there is no follow up to this question, I post
it here in case it may help others.

The answer is to use Excel internal translation function. I inserted a
formula to an Excel workbook under English settings and saved it. When it was
opened in another language settings (after changing language from Regional
Options or running in another computer), the formula spelling was changed
automatically. Then I could filter the function name text string and use it
to generate another formula in VB.

This is inspired by relevant discussions on the Internet.