how to get real formula from EXCEL formula

Question

      | A   |  B
------|-----|--------
    1 |  x  |   1
    2 |  y  |   2
    3 |  z  |   3
    4 |  a  |   =B1*B2+B3

Imagine the above stuff is part of an excel sheet, and B4 has a formula =B1*B2+B3.

Is it possible to extract the real formula from excel formula? i.e. in the above example, the real formula is a=x*y+z.

The reason why I want to do the "transformation" is that I have many excel sheets filled with data and formulas, and now I want to quit Excel and use javascript for calculation, so I want to have the real formulas.

Answer 1

After defining what it is that you are after, I think a VBA solution is the way to go. And since you noted that it was acceptable, I have provided one for you.

The following Function will return the string a=x*y+z and will work so long as all your formulas are set up exactly in the manner given in your example.

Function ListRealFormula(rng As Range, ws As Worksheet) As String

Dim sFormula As String

sFormula = rng.Offset(, -1) & rng.Formula

With rng

    Dim d As Range
    For Each d In .DirectPrecedents

        Dim sValue As String, sAdd As String
        sAdd = d.Address(0, 0)
        sValue = ws.Range(sAdd).Offset(, -1).Value2
        sFormula = Replace(sFormula, sAdd, sValue)

    Next

End With

ListRealFormula = sFormula

End Function

You can call the function like so:

Sub GrabFormula()

Dim s As String

s = ListRealFormula(Sheet1.Range("B4"), Sheet1)
Debug.Print s

End Sub

Answer 2

@Wyatt, you have the first part right, but it's not sufficient: once you have clicked the "Show Formulas", you also need to to "Goto Special, Formulas" (Ctrl+G, Special, Formulas). As you have clicked the "Show Formulas", you now do copy/paste into some text editor, and you have all the formulas, used in your Excel sheet.