Executing text as code in VBA

Question

Executing text as code in VBA

Navigation

#1 by (4 votes)

3

The code below calculates the formula contained in the Text (String) variable:

Private Sub CalculeFormulaNoTexto()

Dim Texto As String

Texto = "= 90 / 3 + 7 + COS(0) + 1/7"

' A variável Texto recebe a fórmula que quero calcular
' Observe que o primeiro caractere é o sinal de igual "='
' Sem ele o cálculo não ocorre

' Coloco o conteúdo de Texto na célula A1
Cells(1, 1) = Texto

'Pego o resultado do cálculo obtido na célula A1
Resultado = Cells(1, 1)

'Apresento o resultado
MsgBox Texto & " = " & Resultado

End Sub

Some remarks:

Check the contents of cell A1 on the worksheet , it has the formula the way it would look if it were typed in the cell itself

The variable " Result " can be used for other calculations because it stored the resulting value in the cell

PROBLEM

I did other tests and not all were successful

For example, to:

Texto = "= 9 + 2 * COS(3*PI()/2) + ARRED(1/7;4)"

An error will appear in the cell and the code will display an error as well (in this case it is not because of the ARRED function, use it in a smaller equation and it will work)

SURPRISE

If you go in the cell with error ("A1") and der F2 to edit and ENTER , the calculation will be done correctly

DOUBT

What is the reason for the error and how to solve this problem of executing a text within the VBA code?

string excel visual-basic-6 vba

asked by anonymous 29.11.2015 / 20:19

1 answer

I would like to understand the use of a DTO with an Entity How to save files on separate servers by extension

score 4 · Accepted Answer

Your Excel is obviously in Portuguese, since you are using the function ARRED to arred and a semicolon ( ; ) as parameter separator . So, of course, it works if you type the formula directly into an Excel cell.

This ARRED function is called ROUND in the original Excel in English. This function translation feature is something that may be cool for inexperienced users (because they write functions in the native language). But it's a problem for the kind of thing you want to do, because VBA only accepts functions written in English .

So, try writing the following code in your code:

Texto = "= 9 + 2 * COS(3*PI()/2) + ROUND(1/7,4)"

Note that I changed the function ARRED by ROUND and changed the semicolon ( ; ) to comma ( , ) to separate the 1/7 parameter from the 4 parameter. (In English the comma is the separator of thousands and the point is the separator of decimal, so you can not confuse in the separation of parameters, as in Portuguese confuses, the semicolon is used to separate parameters).

Of course, Excel will do the "translation" automatically for you, so if you go to the cell and press F2 you will see it translated. Unfortunately, it only has this way to make it work, and you will have to consult the English documentation to find out the original names of the functions you use.