Is there an "Execute" worksheet function?

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

LurfysMa

Is there a way I can get Excel to "execute" an expression that is
stored in a cell as a test string?

Say C4 contains "3+4". Can I put some expression in C5 that will
execute that expression and result in "7"?

I tried =calculate(C4) and a few other things, but no joy.
 
in the absence of putting = in front of c4, try this
=LEFT(C4,FIND("+",C4)-1)+RIGHT(C4,LEN(C4)-FIND("+",C4))
 
LurfysMa said:
Is there a way I can get Excel to "execute" an expression that is
stored in a cell as a test string?

Say C4 contains "3+4". Can I put some expression in C5 that will
execute that expression and result in "7"?

I tried =calculate(C4) and a few other things, but no joy.
Click to expand...

Hi,

May be string functions could sometimes help.

In your particular case

=LEFT(C4,1) + RIGHT(C4,1)

gives you the correct answer

regards

Jean
 
Hi

Create a named formula Insert>Name>Define>
Name Calc
Refers to =EVALUATE(A$4)
In C5 enter =Calc
 
in the absence of putting = in front of c4,
Click to expand...

Do you mean putting "=3+4" in C4 rather than just "3+4"?
try this
=LEFT(C4,FIND("+",C4)-1)+RIGHT(C4,LEN(C4)-FIND("+",C4))
Click to expand...

I should have been more clear. The contents of C4 contain an arbitrary
expression, not just "3+4". The actual expressions are much more
complicated and contain function calls.

I want to build a table with the expressions in column 1 and the
values in column 2:


Col1 Col2
3+4 7
3.44*exp(3.44) 107.2831
10^.5 3.162278

etc...
 
Hey Roger,

How come you can use the EVALUATE function in the named range, when there isn't such a
function you can use in a cell? Is it a worksheet function or ain't it??? ):
--
Confused in Virginia Beach,

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
 
LurfysMa,

Here's a user-defined function (put it in a regular module):

Function Eval(arg As Range)
Eval = Evaluate(arg.Value)
End Function

Now call it in C5, thus:

=Eval(C4)

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
You could use a UDF like:

Option Explicit
Function Eval(myStr As String) As Variant

Application.Volatile True

Dim Res As Variant

Res = Application.Caller.Parent.Evaluate(myStr)

If IsError(Res) Then
Eval = "Error!"
Else
Eval = Res
End If

End Function

But be aware that the function won't evaluate when you just change the cells
that are used in the formula.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=eval(A1)
 
Hi Earl

It is using the old EXCEL 4.0 function EVALUATE.
It cannot be used directly in a cell, but as a Named formula, it will
invoke the Excel 4.0 function. Still supported in XL2007 as well.
 
Roger Govier said:
It is using the old EXCEL 4.0 function EVALUATE.
Click to expand...

It's an XLM function. Still available in macro sheets too.
It cannot be used directly in a cell, but as a Named formula, it will
invoke the Excel 4.0 function. Still supported in XL2007 as well.
Click to expand...
....

Note that in Excel 2000 and prior, if you copy a range containing cells with
formulas that refer to such names and then try to paste into other
worksheets, Excel will CRASH with complete data loss.
 
LurfysMa said:
Is there a way I can get Excel to "execute" an expression that is
stored in a cell as a test string?

Say C4 contains "3+4". Can I put some expression in C5 that will
execute that expression and result in "7"?

I tried =calculate(C4) and a few other things, but no joy.
Click to expand...

Another alternative - download and install Laurent Longre's MOREFUNC.XLL
add-in, freely available from

http://xcell05.free.fr/english/

It provides a function named EVAL which would work for this. However, it's
limited to 255 characters each way: it can only accept arguments up to 255
chars in length, and it can only return text strings (not the issue here,
but FYI) up to 255 chars.
 
Hi Harlan

Thank you for that. I was unaware of the problem in XL2000 versions and
earlier.
 
It's a deprecated Excel 4 function.


Hey Roger,

How come you can use the EVALUATE function in the named range, when there isn't such a
function you can use in a cell? Is it a worksheet function or ain't it??? ):
--
Confused in Virginia Beach,

Earl Kiosterudwww.smokeylake.com







- Show quoted text -
Click to expand...
 
ilia said:
It's a deprecated Excel 4 function.
Click to expand...
....

Deprecated in what sense? That VBA should have supplanted XLM by now?

There are no articles on Microsoft's web site (including the
KnowledgeBase, MSDN and TechNet) that include either of the words
'deprecated' or 'discontinued' and the word 'XLM'. Maybe you could
argue that as a PRACTICAL matter XLM is effectively deprecated since
it's no longer being developed, but the same could be said for VBA.
Microsoft hasn't updated VBA since Excel 2002. VSTA and VSTO are
what's in active development.
 
Back
Top