CODEWITHSUNDEEP
excelvbaexcel-formula
D.Yang
D.Yang

Reputation: 51

how to get real formula from EXCEL formula

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

Upvotes: 3

Views: 157

Answers (3)

tnavidi
tnavidi

Reputation: 1418

You can use FORMULATEXT and SUBSTITUTE formulas, so you don't need to deal with VBA.

There's probably a fancier way of doing this with some of the new recursive formula, i don't quite know how to use those yet, but this will work:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B4),"B1",A1),"B2",A2),"B3",A3)

enter image description here

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

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

Upvotes: 4

Dominique
Dominique

Reputation: 17491

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

Upvotes: 2

Related Questions