Excel function that evaluates a string as if it were a formula?
EVALUATE
is available in VBA in all current versions
You can include it in you VBA code, or wrap it into a simple UDF to make it available as a worksheet function
Function ev(r As Range) As Variant
ev = Evaluate(r.Value)
End Function
It basically treats the value of the passed parameter as an Excel formula, same as if it were entered in a cell
"11+5"
and "=11+5"
will produce the same result
=evaluate(put_reference[s]_here)
This is a semifunction - it can only be used in Name Manager.
This is how you can use it:
Point to a cell and you open Name Manager (From the FORMULAS tab or by clicking CTRL+F3)
Write
=evaluate(
and click on the cell you want (best to keep relative reference).Finish the formula with
)
Give it a NAME - (in this example I'll just call it
eva
).Click OK.
Now, let's suppose that you've selected B1 and made all this refer to A1.
In A1 you can put "1+1" and in B1 you write =eva
- once you've hit ENTER, the B1 value will be 2
.
As the reference in Name Manager was relative, you can use =eva
to get the evaluation of any cell one cell left from where you want it. (eg. in B2, =eva
will return the result of cell A2)
There is an important caveat with the great answer from @karel and @Laurentiu Mirica: the evaluate function won't recalculate unless the referenced cell changes. For example, cell C1 contains the text "A1+B1"
and D1 contains the function =eval
. If the values in A1 or B1 change, the cell D1 does not get recalculated.
This can be corrected by introducing a volatile function into either the string or the eval cell. This will force a recalculation every time the worksheet is recalculated. For example, cell C1 could be replaced with =if(today(),"A1+B1",)
. Or, D1 could be replaced with =if(today(),eval,)
. Any volatile function should do.
A third and perhaps the simplest solution is to change semi-function in the name manager to =if(today(),evaluate(c1),)