How to convert the actual excel formula (not the result) to a text string

Question

I have been searching all over for a solution and I couldn't find one.

Let's say I type into cell A1: =if(A2>1,1,0)

Excel will interpret that and either return 1 or 0.

However, what I would like to do is literally convert that formula into the string: '=if(A2>1,1,0)'

If you press Ctrl+~ into Excel, you will see all the formulas. Basically what I want to do, in conclusion, is take all those formulas and make them strings.

(The purpose, if you care, is to compare these strings with another workbook to make sure I didn't delete anything)

Answer 1

You can make a user defined function for this.

Put this code into a Module (not a worksheet code sheet, class, or form)

Function GetFormula(rng As range, Optional asR1C1 As Boolean = False) As String
    If asR1C1 Then
        getFormula = rng.FormulaR1C1
    Else
        getFormula = rng.Formula
    End If
End Function

and call it in your worksheet like so

=GetFormula(A1)

or if you want the results as R1C1

=GetFormula(A1,TRUE)