Decipher a formula in excel 2

[Geosubhtech]

In EXCEL when i use a formula, to check one value i mostly check by EVALUATE FORMULA for the correct value is referred or not. but instead of doing that is there any other method or function or VBA available to that ?

For Example:
A1: 10
A2: 20
A3: -1.332 =Sin(A1)/Cos(A2)

But i want to show this in adjacent cell: =Sin 10/Cos 20

Thank you.

 

[JG2828]

Found this on the web:

https://superuser.com/questions/776606/replace-cell-references-with-values-without-f9-using-macro

It seems to do exactly what you want but it replaces the formula instead of showing it in an adjacent cell. Which cell would you want it shown in? A4 or B3? It would be fairly easy to modify. The link gives a few use cases that would break it like named ranges.

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

 

[cowski]

I think that eng-tips user IDS might have you covered. Here is a blog post on the subject, I found the link in this thread:
thread770-287516

 

[JG2828]

Nice find cowski!
I modified that VBA in the blog post a little so it doesn't print the "original" formula with the references.
Just select your cell and run the macro. Works great.

Sub GetRefs()
  Dim MyRange As Range, strFormula As String, strVal As String, FormCell As Range
  Dim NumRows As Long, FormA() As String, i As Long, Outrange As Range
  
  NumRows = Selection.Rows.Count
  ReDim FormA(1 To NumRows, 1 To 1)
  i = 1
  For Each FormCell In Selection.Areas(1).Cells
    With FormCell
      strFormula = .Formula
      For Each MyRange In .Precedents.Cells
        With MyRange
          strVal = " " & Range(.Address).Value & " "
          strFormula = Replace(strFormula, .Address, strVal)
          strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
          strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
          strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
        End With
      Next
      
      strVal = "' " & strFormula
    End With
    FormA(i, 1) = strVal
    i = i + 1
  Next
  
  With Selection
    If .Areas.Count > 1 Then
      Set Outrange = .Areas(2)
    Else
      Set Outrange = Selection.Offset(0, 1)
    End If
  End With
  Outrange.Value = FormA
End Sub

-edit - fixed code indentation

 

[IDS]

There are some updates to the code linked above, which provide better functionality, including user defined functions that will convert the cell references to values, or evaluate a formula entered as text.

https://newtonexcelbach.com/2014/06/28/evaluating-text-update/

Has links to the Eval2.xlsb spreadsheet and also Units4Excel, that also handles unit conversion and evaluation. Note that the Units4Excel includes the Eval and Addr2Val functions, but does not document them.

https://newtonexcelbach.com/2017/08/30/udf-to-replace-cell-references-with-values/

Has more documentation on the Addr2Val functions.

Note to shub_PMT:- You know that the input for the Excel Sin and Cos Functions needs to be in radians? Either change the values to radians, or change the formula to: Sin(Radians(A1))/Cos(Radians(A2))

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Geosubhtech]

Thank you IDS for the references.
it works very fine. saved a lots of time.

many many thanks to all.