# Excel show formulas WITH NUMBERS

[Closed]
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have a problem with making Excel write the formula with actual numbers.
This is the text I want to get:
A=1
B=3
C=A+B=1+3=4
In the third line, I'd like to write the formula only once and see the formula in one cell, the formula with actual numbers in second and the result in the third cell. I can write out the formula (C=A+B=4), but can't figure out how to show the numbers used.

Thanks for any tips.

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
For that you have to write a custom function first. Then use that function on your sheet

1. Open workbook
2. Press Alt + F11
3. Click on insert and add a new module
4. paste the code below the instructions
5. Call the function on sheet as given in the sample call

```'This functions returns the formula written in a cell
'if more than once cell is passed as parameter, it will return **Error**
'formulaCell : cell which as the formula
' Sample Call : =displayFormula(c1)
Public Function displayFormula(formulaCell As Range) As String

Dim result As String
If formulaCell.Count <> 1 _
Then
result = "**Error**"
Else
result = formulaCell.Formula
End If
displayFormula = result
End Function```
Thanks, but this is not exactly what I need... Your function shows the general formula, but I would like to see the numbers used in calculation (Instead of "=A4+B4" to show "=28+45", where A4=28 and B4=45).
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
For that you can try using evaluate function. However it will be not be easy to write value. Basically in that you have to write "=28+45" this part yourself, evaluate can solve and tell u what this value is

```'This functions returns the value by evaluating a formula written in a cell
'if more than once cell is passed as parameter, it will return **Error**
'formulaCell : cell which has the formula
' Sample Call : =displayFormulaValue(c1) where C1 would have something like this
'                 =A1 & " + " & b1
Public Function displayFormulaValue(formulaCell As Range) As String

Dim result           As Variant
Dim vPosEqual        As Variant
Dim vPosAmpersand    As Variant

If formulaCell.Count <> 1 _
Then
displayFormulaValue = "**Error**"
Else

result = formulaCell.Formula
Do
result = LTrim(result)
vPosEqual = InStr(1, result, "=")
If (vPosEqual > 0) _
Then
result = Mid(result, vPosEqual + 1)
End If
Loop While (vPosEqual > 0)
displayFormulaValue = Evaluate("=" & result)
End If
End Function```