[Solved] Evaluate a string

Discuss the spreadsheet application
Post Reply
nicgiard
Posts: 7
Joined: Mon Feb 25, 2008 12:09 am

[Solved] Evaluate a string

Post 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:
Last edited by Hagar Delest on Fri Oct 12, 2018 10:02 pm, edited 1 time in total.
Reason: tagged solved
bigbossSNK
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Post by bigbossSNK »

Seconded!
OO has python, Javascript etc integration.
Can't someone help a non programmer?
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: evaluate a string

Post 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 ... light=eval
LibreOffice 4.2.3.3. on Ubuntu 14.04
bigbossSNK
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Post 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.
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: evaluate a string

Post 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.
bigbossSNK
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Post 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)
nicgiard
Posts: 7
Joined: Mon Feb 25, 2008 12:09 am

Re: evaluate a string

Post 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:
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: evaluate a string

Post 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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: evaluate a string

Post by Villeroy »

You can use scenarios as interchangeable calculation models. No need for ugly hacks in stupid Basic.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bigbossSNK
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Post 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)
Last edited by bigbossSNK on Thu Mar 20, 2008 12:26 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: evaluate a string

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bigbossSNK
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Post by bigbossSNK »

http://www.ozgrid.com/News/excel-evalua ... la-VBA.htm
Easiest method to evaluate strings in Excel.
reikred
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am

Re: evaluate a string

Post 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!
Libreoffice 5.2.7.2 on Fedora 25
User avatar
lader
Posts: 46
Joined: Mon Jul 02, 2018 6:10 pm

Re: evaluate a string

Post by lader »

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
8-)
LibreOffice 7.6.4.1 on Ubuntu 20.04.4 LTS
reikred
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am

Re: evaluate a string

Post by reikred »

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
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
Libreoffice 5.2.7.2 on Fedora 25
Post Reply