Function that converts cell into just the value of its formula

[ashriva1]

I'm trying to create a function that convert cells into just the value that the formula inside the cell outputs. For example this would be like copying a cell and pastespecial value back into the same cell.

So, if A5 is 10 and A6 is 11, and A7 = A5+A6, I want the formula bar for A7 to just show 21 instead of =A5+A6.
I'd want to type something like this in A7: =ConverterFormula(A5+A6)

I can't select specific cells in my range because I'm going to need to call this function on different cells each month. Any help would be appreciated, thanks!

[arnelgp]

say A1=2, B1=3, on C1 =EvalExpr("A1+B1")


Public Function EvalExpr(ByVal strExpr As String) As Double
Const operators As String = "+-*/"
Dim strNew As String
Dim strTmp As String, c As String
Dim ln As Integer, i As Integer
Dim dblValue As Double
ln = Len(strExpr)
For i = 1 To ln
c = Mid$(strExpr, i, 1)
If InStr(1, operators, c) <> 0 Then
dblValue = Val("0" & Range(strTmp))
strTmp = ""
strNew = strNew & dblValue & c
Else
strTmp = strTmp & c
End If
Next
If Len(strTmp) Then
dblValue = Val("0" & Range(strTmp))
strNew = strNew & dblValue
End If
EvalExpr = Evaluate(strNew)

End Function

[ashriva1]

Hi thanks for your reply. While your function does work, I tried to go through it line by line to understand what it was doing but I am confused. Would you be able to explain what is happening at a high level?

[p45cal]

If you select some cells on the sheet, it can be a non-contiguous selection, any size, then run this macro, all the formulae in the selected range(s) will convert to plain values (of course, the formulae will be lost):

Sub ConvertFormulasToValues()
For Each are In Selection.Areas
are.Value = are.Value
Next are
End Sub
You could assign the macro to a keyboard shortcut or to a button.

[ashriva1]

I was trying to use a function instead of running a macro so that every time I insert new data in the sheet, these specific cells would update by themselves instead of me needing to go in and run the macro. I tried your code and it does work though, thank you for that.

[jolivanes]

Is this question also asked on different forums?