2
votes
      | 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 .

2
You do realize that x, y and z are just labels in math. They can be renamed to anything and mean the same thing. So z=x+y is the same as a=b+c etc. So like z=x+y IS z=B1+B2. The excel formula IS the real formula. - slebetman
You want to get the formula via VBA??? - Elbert Villarreal
You could name the ranges: B1 = "x",B2 = "y"... Then your formula would be =x*y+z - Scott Craner
You can either select the cell and press F2 or simply look in the editor window at the top of Excel. If you are using this formula in multiple places though, you may be better off using a name. - rhughes
@ScottCraner Your method would work, but you would have to go re-write all of the formulas with the names instead of cell references. But this could be a solution - Wyatt Shipman

2 Answers

4
votes

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
2
votes

@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.