# Evaluate math expressions

In Excel there is a Eval function for evaluating expressions given as String. Is there a way for achieve a goal similar to that one in LO Calc? For example, an user is able to evaluate expressions like tan(50)+sin(90)/4

In Excel, the evaluation of arbitrary expressions is implemented through the Application.Evaluate method.

In LibreOffice you can use the corresponding techniques from the Python language.
LO Basic language function can call a function written in Python.

As far as I remember, VBA Eval can also evaluate formula expressions. IMHO, there is no way to do this with the UNO API.

Only for Microsoft Access.

In Excel it is Evaluate Application.Evaluate-Methode (Excel) | Microsoft Learn

F9 partially implements this functionality for you.

Additionally, as @Villeroy pointed out in the same discussion, the Function Wizard allows you to see all the steps in a formulaâ€™s calculation.

By the way, your formula `tan(50)+sin(90)/4` will not return the result you expect. You mean that 50 and 90 are degrees? Calcâ€™s trigonometric functions expect arguments in radians. So, `=TAN(RADIANS(50))+SIN(RADIANS(90))/4`

So, users are relegated to Python scripts.

The central point is if the â€śexpressionsâ€ť shall be allowed to contain references. If so I cannot imagine a solution.
Otherwise there is a (highly inefficient) way to do it with the help of an additional (hidden?) Calc model or -accepting a few restrictions- a temporary or dedicated additional sheet.

Could somebody tell me if Python actually knows how to handle Calc expressions or which Calc services it uses in what way for the purpose?

The expression can have variables. So an expression like `x+7*y` would be accepted.

I have to leave the thread. No clue.

1 Like
``````def evaluate(s):
try:
r = eval(s)
except:
r = None
return r
``````

Go to your profile folder, subfolder Scripts, create a folder python with subfolder pyCalc and save the above text as SheetFunctions.py.
Create a Basic macro in the Standard library of your document or under My Macros.

``````REM  *****  BASIC  *****

REM Keep a global reference to the ScriptProvider, since this stuff may be called many times:
Global g_MasterScriptProvider

REM Specify location of Python script providing the cell functions:
Const URL_Main = "vnd.sun.star.script:pyCalc|SheetFunctions.py\$"
Const URL_Args = "?language=Python&location=user"

Function getMasterScriptProvider()
if NOT isObject(g_MasterScriptProvider) then
oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
endif
getMasterScriptProvider = g_MasterScriptProvider
End Function

Function SOUNDEX(s\$, optional iLen%)
sURL = URL_Main & "soundex" & URL_Args
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
if isMissing(iLen) then
i = 4
else
i = cInt(iLen)
endif
x = oScript.invoke(Array(s,i),Array(),Array())
SOUNDEX = x
End Function

Function EVAL(s)
sURL = URL_Main & "evaluate" & URL_Args
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
x = oScript.invoke(Array(s),Array(),Array())
EVAL = x
End Function
``````

Enter some expression in a cell and try =EVAL(A1).

Disclaimer: This is not the regular way to integrate cell functions written in Python. It is just a simple demo calling a Python function through the simplified StarBasic interface which allows to call Basic functions in Standard lib from sheet cells.

2 Likes

With this approach, can we use variables in a string expression?

You can introduce parameters on sheet cell level.
pyEval.ods (17.1 KB)

Apache OpenOffice Community Forum - [Calc][oxt] A function for all python string methods - (View topic) introduces an extension package with a sheet function providing the string functions of Pythons standard library. This the way how Eval should be implemented.

1 Like

Understood. In short, variables must be provided in cells and stored in a spreadsheet. The solution involves:

1. Parsing the input string
2. Put variables in corresponding cells
3. Invoke Python function of choise

Ok, so if I put a equal sign in front of the expression I can evaluate `x + 2y - cos(60)` using LO Basic. I donâ€™t know how to do that, please, show the working solution.