How to "Evaluate Formula" in cell in Excel using VBA - e.g. Show working

user_15439029 0
Viewed 285

I need to be able to "show my working" in excel. So when I print the sheet for clients all the work can be checked without having the original spreadsheet. I'm sort of trying to replicate the "Evaluate Formula" feature of excel, but within the cell. I can do it manually, but it's really time consuming as I have a lot of formula.

So if I have this in a cell

=(C2*C3)+C4

I want to be able to use VBA magic to turn it into this

="("&TEXT(C2,"0.00")&" x "&TEXT(C3,"0.00")&" ) + "&TEXT(C4,"0.00")

so the result looks like this

(50.22 x 2.11 ) + 3.22

I can convert the math's signs (brackets, multiply, etc) in VBA. But I don't know how to recognize the cell references, so that I can do the text conversion part (I can't leave the formula without the text function - the decimal places are too long). Any help would be greatly appreciated.

vba excel
1 Answers

This will reformat the selected cells and put the result in the cell 1 column to the right.

Sub ReformatForumla()
    Dim prec As Range
    Dim rng As Range
    Dim frm As String
    Dim cellRef As String
    Dim loopRng As Range
    Dim i As Long
    Dim stored() As Variant
    Dim sorted As Variant
    
    For Each loopRng In Selection
        frm = Replace(loopRng.Formula, "$", "")
        frm = "=""" & frm & """"
        Set prec = loopRng.Precedents
        i = 1
        ReDim stored(prec.Cells.Count, 2)
        For Each rng In prec
            stored(i, 1) = rng.Address
            stored(i, 2) = Len(rng.Address)
            i = i + 1
        Next rng
        sorted = Application.WorksheetFunction.Sort(stored, 2, -1)
        For i = 1 To prec.Cells.Count
            cellRef = Replace(sorted(i, 2), "$", "")
            frm = Replace(frm, cellRef, """ & TEXT(" & Application.ConvertFormula(cellRef, xlA1, xlA1, xlAbsolute) & ",""0.00"") & """)
        Next i
        frm = Replace(frm, "+", " + ")
        frm = Replace(frm, "-", " - ")
        frm = Replace(frm, "*", " x ")
        frm = Replace(frm, "/", " / ")
        loopRng.Offset(0, 1).Value = frm
    Next loopRng
End Sub
user_143854190
Related