This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA.
Search formula of a Cell
Assume that you have the below spreadsheet and you don’t know whether any cells contain a formula.
Press CTRL + F to open Find and Replace dialog
In the Find what text box, enter equal sign = , then press Find Next button
Note that if a cell contains equal sign in Cell Text, it will also be searched.
Show formula of a Cell
Navigate to Formulas tab > Show Formulas
Cells contain formulas will display the formulas
Click the Show Formulas button again to hide the formulas
Get formula using FormulaText Function
FormulaText Function was introduced Since Excel 2013. FormulaText Function contains only 1 parameter, which is the Range of the Cell you want to show the formula.
Syntax of FormulaText Function
FORMULATEXT(reference)
Example
Using the above example, using FormulaText Function to show the formulas of Cell C1 and C2.
Get formula of a Cell using VBA
For Excel versions before Excel 2013, as FormulaText is not supported, it is necessary to use VBA to get the Cell formula.
VBA has a Range Property called Formula, which displays the Formula of a Range. We can directly create a custom Function using the Formula Property.
VBA Code
Press ALT+F11 > create a new module > insert the below code
Public Function wGetFormula(rng As Range) As String wGetFormula = rng.Formula End Function
Example
Now go back to worksheet and try the custom function
Outbound References
https://support.office.com/en-us/article/FORMULATEXT-function-0A786771-54FD-4AE2-96EE-09CDA35439C8
