[Solved] Evaluate a string

[Hagar Delest]

Hi,
it might be simple, but I don't know !!!
How I can calculate the string "3 + 5" ??
Excel has Application.Evaluate ("3 + 5")
MSScriptControl has ScriptControl.Eval ("3 + 5")

And OOo Calc ????

Thank you

[bigbossSNK]

Seconded!
OO has python, Javascript etc integration.
Can't someone help a non programmer?

[squenson]

You can try the solution I posted in the other OOo forum last year (last post of the thread):
http://www.oooforum.org/forum/viewtopic.phtml?t=58809&highlight=eval

[bigbossSNK]

Thanks. That pretty much solves it for mathematical operators and numbers.
Two caveats:
1.The current version doesn't allow formulas to be passed on for evaluation (This would be a very efficient tool)
2. Interoperability with Excel.

[Dave]

1. Just curious. Under what circumstances do you use this functionality where there is no other, or better alternative? It is not commonly used, I think.

2. I've said this for the past three decades: "No two spreadsheet programs are compatible." That is why I get goosebumps when people in other queries add, "If it wasn't for just this ONE little thing..." And how concerned is MS about this problem?

David.

[bigbossSNK]

1. Suppose I want to create a formula based on the selections of a list box.
Like: |SQRT| |25 | |+ | |=INDIRECT(A2)|
This way the end user doesn't need to learn how to program Calc to do an operation, just select that operation from a drop down list.
That, and it wouldn't be hard for the programmer to add new terms to the equation with a simple CONCATENATE.
If there's an alternative to this, I'd be interested in it.
2. It's true that no two spreadsheets are fully compatible. Yet, for the cross-section of prominently used functions they strive to be.
(Granted, this isn't the case of my request)
3. I wasn't shooting for a show down between Calc and Excel. My point was, maybe there is some common ground between the two programs (as in maybe they both support Javascript ? Excuse my ignorance)

[nicgiard]

Hi
the text "5+3" in one cell, and then put the soluction in other cell the result "8", is very important in documents like "book - keeping" in the construction for architect engineering land-surveyor. They often use spreadsheets for owner works.
I hope in a simple soluction.
Thank you

[Dave]

Have 5+3 [or whatever expressed sum] in A1. In B1 put this:

=VALUE(LEFT(A1;SEARCH("+";A1)-1))+VALUE(RIGHT(A1;LEN(A1)-SEARCH("+";A1)))

I doubt I'd have time or inclination to consider all possible variations of arithmetic though.

David.

[Villeroy]

You can use scenarios as interchangeable calculation models. No need for ugly hacks in stupid Basic.

[bigbossSNK]

Well, in Excel, you don't need to do even that. You can just insert a name definition (let's say TEXTFORMULA) for a cell (let's say A1). You then set the name to refer to =EVALUATE($A1).
Now type =TEXTFORMULA in any other cell, and you can evaluate the string formula in A1 (even if the string is the output of CONCATENATE).

This is a make or break feature for me. I know I speak for a tiny percentage of the user base, but this functionality isn't
difficult to implement. (From what little I know of spreadsheets)

[Villeroy]

http://www.excelforum.com/archive/index ... 32007.html

[bigbossSNK]

http://www.ozgrid.com/News/excel-evalua ... la-VBA.htm
Easiest method to evaluate strings in Excel.

[reikred]

I would very much like to see the solution referred to by by user squenson above in the thread, but it refers to another website that is no longer accessible, namely oooforum.org. Here is what squenson wrote:

Re: evaluate a string
Postby squenson » Sat Mar 15, 2008 11:49 pm
You can try the solution I posted in the other OOo forum last year (last post of the thread):
http://www.oooforum.org/forum/viewtopic ... light=eval

I have not been able to access this solution, looking for help from squenson or others that may know what the solution was and are willing to post the solution explicitly here!

[lader]

Code: Select all   Expand viewCollapse view

Function Eval(X As String) As Variant   
   Dim oCell As Object
   oCell = ThisComponent.getSheets().getByIndex(0).getCellByPosition(10, 0)
   oCell.formula = "=" & X
   Eval =   oCell.value
End Function


[reikred]

Code: Select all   Expand viewCollapse view

Function Eval(X As String) As Variant   
   Dim oCell As Object
   oCell = ThisComponent.getSheets().getByIndex(0).getCellByPosition(10, 0)
   oCell.formula = "=" & X
   Eval =   oCell.value
End Function


Thanks user lader, that worked!

Notes to self about how to install the function code:

Menu select Tools>Macros>OrganizeMacros>OpenOffice.orgBasic, then opened MyMacros.Standard.Module1.New and replaced the little module template that popped up with the function code above. Rename the function AND the return variable to something else as needed, e.g. MyEval.

Test in some file blah.ods
Cell B3: =MYEVAL(C3)
Cell C3: 25.71+4.29
Cell B3: evaluates and shows value 30.00 as it should

RESULT: it WORKED!! I think it will work for all my spreadsheets because I did not store the function in the spreadsheet itself, but rather in MyMacros which is stored globally in the file ~/.config/libreoffice/4/user/basic/Standard/Module1.xba