Running text as code in VBA

Question

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

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 spreadsheet, she has the formula the way it would be if it was typed in the cell itself

The variable "Upshot" can be used for other calculations as it has stored the resulting value that is in the cell

PROBLEM

I’ve had other tests and not everyone has succeeded

For example, for:

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

There will be an error in the cell and the code will be error too (in this case it is not because of the ARRED function, use it in a smaller equation and it will work)

SURPRISE

If you are in the error cell ("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?

Answer 1

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

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

So try writing in your code the following:

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

Note that I changed the function ARRED for ROUND and changed the semicolon (;) comma (,) to separate the parameter 1/7 of the parameter 4. (In English the comma is the thousand separator and the point is the decimal separator, so there is no way to confuse in the separation of parameters; as in Portuguese confuses, we use the semicolon to separate parameters).

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