Tradducciones de formulas

[Marcelo Branco]

Amigos,

A menudo tenemos que traducir las fórmulas de nuestro idioma a Inglés y vice-versa.

A veces no es fácil y hacemos errores.

He creado estas dos funciones para facilitar.

Public Sub ENGtoYOURLANGUAGE()
    ActiveCell.Offset(1, 0) = ActiveCell.Formula
End Sub

Sólo tienes que copiar la fórmula del foro en Inglés y pegar en una celda. Con esta celda seleccionada, ejecutar el
procedimiento anterior y la fórmula aparecerá traducida en su idioma en la celda inmediatamente inferior.

Public Sub YOURLANGUAGEtoENG()
    Dim strFormula As String
 
    strFormula = ActiveCell.Formula
    ActiveCell.Offset(1, 0) = " " & strFormula
End Sub

Este procedimiento hace lo contrario. Pasa la fórmula de su idioma para o Inglés.

Espero que esto ayude.

M.

 

[shg]

Marcelo,

I don't speak Spanish, but have you looked at the FormulaLocal property?

 

[shg]

Sorry -- I just figured out it's not a question ...

 

[Marcelo Branco]

An improvement to the second procedure

Public Sub YOURLANGUAGEtoENG()
    Dim strFormula As String
 
    strFormula = ActiveCell.Formula
    ActiveCell.Offset(1, 0) = "'" & strFormula
End Sub

Now can switch English/YourLanguage/English...

shg4421

I had never heard about this property...What is it?

Tks for your comment

M.

 

[shg]

Since I've never used any version of Excel other than English, I can only speculate that Formula returns the English-centric VBA's English version, and FormulaLocal returns the formula in the language of the installed version.

'Tis I who should ask you, Marcelo!

Merry Christmas to you and your family.

 

[Marcelo Branco]

shg4421,

I've taken a look at
http://msdn.microsoft.com/en-us/library/aa205983(office.10).aspx

And have seen this

"Remarks
If the cell contains a constant, this property returns that constant. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar (including the equal sign

Example
Assume that you enter the formula =SUM(A1:A10) in cell A11 on worksheet one, using the American English version of Microsoft Excel. If you then open the workbook on a computer that's running the German version and run the following example, the example displays the formula =SUMME(A1:A10) in a message box.

MsgBox Worksheets(1).Range(A11).FormulaLocal"

The problem is that when you open a workbook originally produced in an english-version (or in any other language version) the formulas are immediately translated to the local version.
(my wife has a computer with the english-version installed so i'm used to use both versions - english and portuguese)

If i try, in my comuputer (portuguese-version), to enter mannualy a formula using the english words and separators i get an error and Excel doesnt allow me to enter the formula.

I have to enter it as a string, and doing so and using the LocalFormula property as written in the Microsoft support page i get the same string in english.

So, or i'm missing something, or the FormulaLocal property is not very useful.

Merry Christmas to you and your family too.

And a very happy 2011 to us!!!

M.

 

[Marcelo Branco]

shg4421,

Maybe it can be useful if the OS = portuguese-version and Excel = english version. (This is precisely the configuration of my wife computer)

I'll try it latter, if my wife allow me to use her computer

M.

 

[mjrofra]

Hola,

por acá hay un ejemplo en el que se usa la propiedad formulalocal: http://excelpatas.blogspot.com/2010/05/mostrar-formula-en-una-celda_19.html

 

[mjrofra]

An improvement to the second procedure

Public Sub YOURLANGUAGEtoENG()
    Dim strFormula As String
 
    strFormula = ActiveCell.Formula
    ActiveCell.Offset(1, 0) = "'" & strFormula
End Sub

Now can switch English/YourLanguage/English...

shg4421

I had never heard about this property...What is it?

Tks for your comment

M.

Interesante el aporte Marcelo.

Si me permites, realmento no veo la necesidad de la variable strFormula:

Public Sub YOURLANGUAGEtoENG()

    ActiveCell.Offset(1, 0) = "'" & ActiveCell.Formula

End Sub

o lo que es lo mismo:

Public Sub YOURLANGUAGEtoENG()

    with ActiveCell
        .Offset(1, 0) = "'" & .Formula
    end with

End Sub

 

[Marcelo Branco]

Interesante el aporte Marcelo.

Si me permites, realmento no veo la necesidad de la variable strFormula:

Public Sub YOURLANGUAGEtoENG()
 
    ActiveCell.Offset(1, 0) = "'" & ActiveCell.Formula
 
End Sub

o lo que es lo mismo:

Public Sub YOURLANGUAGEtoENG()
 
    with ActiveCell
        .Offset(1, 0) = "'" & .Formula
    end with
 
End Sub

mjrofra,

Gracias por el comentario y la mejora de mi procedimiento

Feliz Navidad!

M.