Use VBA to build values from a formula

[Rudi]

Hi,

I need help if this is possible in Excel???

I need VBA to build a value using the "value" of a cell that the formula is refering to.

For example:
In A1 is the value 10
In A2 is the value 20
In A3 is the value 30
In A4 is the value 60 (The formula =A1+A2+A3)

In B4 I need VBA macro to insert: 10+20+30. (This is the value of the cell reference in the formula!)

The reason for the request: Someone wants a visual reference of the formula in the cell next to the formula.

PS: The code should work by looping through the selection of formulas, and insert the visual formula in the cell to the right.

Mnay thanks!

[HansV]

Try this:

Code: Select all

Sub ListFormulas()
  Dim rng As Range
  For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
    rng.Offset(0, 1).Value = Chr(39) & rng.Formula
  Next rng
End Sub

If you prefer to omit the = from the formula:

Code: Select all

Sub ListFormulas()
  Dim rng As Range
  For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
    rng.Offset(0, 1).Value = Chr(39) & Mid(rng.Formula, 2)
  Next rng
End Sub

[Rudi]

Hi Hans,

Wow...that was quick! :)

It is putting a value next to the formula, but its adding the actual formula =A1+A2+A3. I need 10+20+30. So the code must insert the value from the reference, not the reference itself.

TX

[HansV]

But what if the formula is =SUM(A1:A1000)? Taking all kinds of formulas into account is an impossible task.

[Rudi]

I must agree... I did not think about these scenarios!

Just out of interest (assuming the above sample formula)...Is it possible to pull the value from a reference in a formula? If it is,how would one do it?

Thanks

[HansV]

If you only have formulas of the type in your example:

Code: Select all

Sub ListFormulaValues()
  Dim rng As Range
  Dim strFormula As String
  Dim strReturn As String
  Dim strCells() As String
  Dim i As Integer
  For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
    strFormula = Mid(rng.Formula, 2)
    strCells = Split(strFormula, "+")
    strReturn = ""
    For i = LBound(strCells) To UBound(strCells)
      strReturn = strReturn & "+" & Range(strCells(i)).Value
    Next i
    rng.Offset(0, 1).Value = "'" & Mid(strReturn, 2)
  Next rng
End Sub

I'm not going to expand it to handle -, *, / or ^. You'll have to do that yourself, or wait for someone else to do that for you.

[Don Wells]

Hi Hans,
... the code must insert the value from the reference, not the reference itself.
TX

Hi Rudi
    See if the attached approach will satisfy the needs of your user.

[Rudi]

Hans,

Thanks for the code. All I was interested in was how one could get the value from the reference. Your code is a perfect explanation for this. If I need it expanded, (which I doubt I will), I am sure I can take it from here! Many TX!!!

Don,

Thanks for this interesting inset! This is a very interesting way of "visualising" the formula. I could present this as a possible solution for the user. Great idea and thanks again. I appreciate the effort again for both of you!

Eileens lounge rocks!!!!!!!!
Cheers