Evaluating text as formulae

Is it possible to refer to text in a cell as though it were a formula, ie
execute it instead of displaying it? Eg if the result of your formula was
"A1 + B2", to actually add A1 and B2 and display the result?

Thanks

Nigel

There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

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.

Then use a formula like:

=eval(A24)
if A24 contained the string to evaluate.



Nigel Ramsden wrote:
>
> Is it possible to refer to text in a cell as though it were a formula, ie
> execute it instead of displaying it? Eg if the result of your formula was
> "A1 + B2", to actually add A1 and B2 and display the result?
>
> Thanks
>
> Nigel

--

Dave Peterson

One thought ...
If the result formulas that you expect are fairly limited and
predictable (eg always in the style Ref1 + Ref2), then you could
probably use the Indirect() function.
eg assuming that your result formula was in C1, then
=INDIRECT(LEFT(C1,FIND("+",C1)-1))+INDIRECT(RIGHT(C1,LEN(C1)-FIND("+"
,C1)-1)).
Of course if the result formula is more free-form, then this approach
would become so unwieldy as to be impractical.
hth
ScottO


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:434D4704.527B5054@verizonXSPAM.net...
| There's nothing built into excel that lets you do that.
|
| But you could create a user defined function that did it.
|
| Option Explicit
| Function Eval(myStr As String) As Variant
| Eval = Application.Evaluate(myStr)
| End Function
|
| 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.
|
| Then use a formula like:
|
| =eval(A24)
| if A24 contained the string to evaluate.
|
|
|
| Nigel Ramsden wrote:
| >
| > Is it possible to refer to text in a cell as though it were a
formula, ie
| > execute it instead of displaying it? Eg if the result of your
formula was
| > "A1 + B2", to actually add A1 and B2 and display the result?
| >
| > Thanks
| >
| > Nigel
|
| --
|
| Dave Peterson