[Solved] Evaluate a string

Discuss the spreadsheet application

[Solved] Evaluate a string

Postby 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 :shock:
Last edited by Hagar Delest on Fri Oct 12, 2018 10:02 pm, edited 1 time in total.
Reason: tagged solved
nicgiard
 
Posts: 7
Joined: Mon Feb 25, 2008 12:09 am

Re: evaluate a string

Postby bigbossSNK » Sat Mar 15, 2008 9:01 pm

Seconded!
OO has python, Javascript etc integration.
Can't someone help a non programmer?
bigbossSNK
 
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

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.phtml?t=58809&highlight=eval
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: evaluate a string

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

Re: evaluate a string

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

Re: evaluate a string

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

Re: evaluate a string

Postby nicgiard » Wed Mar 19, 2008 12:07 am

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

Re: evaluate a string

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

Re: evaluate a string

Postby Villeroy » Wed Mar 19, 2008 5:32 am

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28344
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: evaluate a string

Postby 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)
Last edited by bigbossSNK on Thu Mar 20, 2008 12:26 am, edited 1 time in total.
bigbossSNK
 
Posts: 5
Joined: Sat Mar 15, 2008 8:58 pm

Re: evaluate a string

Postby Villeroy » Wed Mar 19, 2008 9:43 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28344
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: evaluate a string

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

Re: evaluate a string

Postby 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!
Libreoffice 5.2.7.2 on Fedora 25
reikred
 
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am

Re: evaluate a string

Postby lader » Thu Oct 11, 2018 8:24 pm

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-)
LibreOffice 6.4 on Ubuntu 20.04 LTS
lader
 
Posts: 22
Joined: Mon Jul 02, 2018 6:10 pm

Re: evaluate a string

Postby reikred » Fri Oct 12, 2018 12:49 am

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
Libreoffice 5.2.7.2 on Fedora 25
reikred
 
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests