[Solved] Evaluate a string

nicgiard · Post by **nicgiard** » Fri Mar 14, 2008 12:59 am

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 · Post by **bigbossSNK** » Sat Mar 15, 2008 9:01 pm

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

Post by **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

Edit: All links to oooforum.org are dead -- MrProgrammer, forum moderator

bigbossSNK · Post by **bigbossSNK** » Sun Mar 16, 2008 12:48 am

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 · Post by **Dave** » Sun Mar 16, 2008 4:16 am

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 · Post by **bigbossSNK** » Sun Mar 16, 2008 10:46 am

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 · Post by **nicgiard** » Wed Mar 19, 2008 12:07 am

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 · Post by **Dave** » Wed Mar 19, 2008 4:51 am

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.

edit this topic's initial post · Post by **Villeroy** » Wed Mar 19, 2008 5:32 am

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

bigbossSNK · Post by **bigbossSNK** » Wed Mar 19, 2008 9:25 pm

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)

edit this topic's initial post · Post by **Villeroy** » Wed Mar 19, 2008 9:43 pm

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

bigbossSNK · Post by **bigbossSNK** » Thu Mar 20, 2008 12:18 am

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

reikred · Post by **reikred** » Thu Oct 11, 2018 6:11 am

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 · Post by **lader** » Thu Oct 11, 2018 8:24 pm

Code: Select all

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 · Post by **reikred** » Fri Oct 12, 2018 12:49 am

lader wrote:

Code: Select all

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