Excel get formula of a cell using FormulaText Function and VBA

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.

Excel get formula of a cell 01

 

Press CTRL + F to open Find and Replace dialog

In the Find what text box, enter equal sign = , then press Find Next button

Excel get formula of a cell 06

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

Excel get formula of a cell 02

 

Cells contain formulas will display the formulas

Excel get formula of a cell 03

 

Click the Show Formulas button again to hide the formulas

Excel get formula of a cell 01

 

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.

Excel get formula of a cell 04

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

Excel get formula of a cell 05

Outbound References

https://support.office.com/en-us/article/FORMULATEXT-function-0A786771-54FD-4AE2-96EE-09CDA35439C8

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *