Excel function that evaluates a string as if it were a formula?

Question

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?

This would be helpful for another project where I would like to be able to write 'dynamic' formulas in Excel.

Answer 1

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

Answer 2

=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)

Answer 3

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),)

Answer 4

=indirect()

if you use this in a cell (alongside concatenate) it can be very useful.

For example, this formula will display the value of cell B5 on another worksheet (the name of which is stored in cell A2 on this worksheet):

=INDIRECT(CONCATENATE(A2,"!B5"))

To make INDIRECT work the external worksheet must be open.

Answer 5

Enhanced Function to execute a string as though it is a formula. Function extended from above ev() function.

New Function Name: EvaluateEx()

• Paste function to an Excel VBA Module.
• Place function name into a worksheet cell.

Example Calling Syntax: * =EvaluateEx("=11 + 5") * =EvaluateEx("=g1 + g2")+ evaluateEX("2 + 3") * =EvaluateEX("defined name")

Function tested with:

• Structured Table References

• Defined Names that reference text,
  filter() function, etc.

  Function evaluateEx(r As Variant) As Variant
  'Note: Renaming function requires same change after the ExitFunction label.
  'User Function to evaluate string as formula.
   Dim ev As Variant
  
     Select Case TypeName(r)
  
         Case "Range"
              If r.Value <> vbNullString And Trim(r.Value) <> "=" Then
                 ev = Evaluate(r.Value)
              Else
                 ev = r.Value
              End If
  
         Case "String"
              If r <> vbNullString And Trim(r) <> "=" Then
                 ev = Evaluate(r)
               Else
                 ev = r
               End If
  
         Case "Variant()"
               ev = r
  
         Case "Double"
               ev = r
  
         Case "Error"
               ev = "Defined Name not found in list of Defined Names. Cannot Evaluate"
  
         Case Else
               ev = "Unknown parmeter type. Cannot Evaluate"
      End Select
  
      On Error GoTo ExitFunction 'Handle possible type mismatch
      If ev = CVErr(2029) Then
               'ev = "The parameter passed to the EV function results in a value (i.e. " & r & ") that cannot be evaluated by the EV function."
                ev = r
      End If
  
  ExitFunction:
  
      evaluateEx = ev
  
  End Function