Page 1 of 1

[Solved] Evaluate a string

PostPosted: Fri Mar 14, 2008 12:59 am
by nicgiard
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 :shock:

Re: evaluate a string

PostPosted: Sat Mar 15, 2008 9:01 pm
by bigbossSNK
Seconded!
OO has python, Javascript etc integration.
Can't someone help a non programmer?

Re: evaluate a string

PostPosted: Sat Mar 15, 2008 11:49 pm
by 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

Re: evaluate a string

PostPosted: Sun Mar 16, 2008 12:48 am
by 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.

Re: evaluate a string

PostPosted: Sun Mar 16, 2008 4:16 am
by 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.

Re: evaluate a string

PostPosted: Sun Mar 16, 2008 10:46 am
by 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)

Re: evaluate a string

PostPosted: Wed Mar 19, 2008 12:07 am
by nicgiard
Hi :shock:
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 :lol:

Re: evaluate a string

PostPosted: Wed Mar 19, 2008 4:51 am
by 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.

Re: evaluate a string

PostPosted: Wed Mar 19, 2008 5:32 am
by Villeroy
You can use scenarios as interchangeable calculation models. No need for ugly hacks in stupid Basic.

Re: evaluate a string

PostPosted: Wed Mar 19, 2008 9:25 pm
by 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)

Re: evaluate a string

PostPosted: Wed Mar 19, 2008 9:43 pm
by Villeroy

Re: evaluate a string

PostPosted: Thu Mar 20, 2008 12:18 am
by bigbossSNK
http://www.ozgrid.com/News/excel-evalua ... la-VBA.htm
Easiest method to evaluate strings in Excel.

Re: evaluate a string

PostPosted: Thu Oct 11, 2018 6:11 am
by 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!

Re: evaluate a string

PostPosted: Thu Oct 11, 2018 8:24 pm
by 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

8-)

Re: evaluate a string

PostPosted: Fri Oct 12, 2018 12:49 am
by reikred
lader wrote:
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

8-)


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