Excel show formulas WITH NUMBERS

[Closed]
Report
-
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.

1 reply

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