Get Formula Text of a Cell on Another Sheet in Excel 2010

Question

=FORMULATEXT only works in Excel 2013 and after. Is there a way to do that in Excel 2010? Thanks

Answer 1

Can do this with VBA (Macro) code using a UDF (User Defined Function)

To install the VBA code for the UDF:

1. Alt and F11 to open the VBA editor window
2. In the VBA editor select menu item Insert -> Module
3. Copy the VBA code below and paste into the VBA editor.
4. Close the VBA editor (Cross very top right of VBA editor window)
5. Save the workbook using "Save as" and set the "Save as type" field to as Excel Macro-enabled Workbook (*.xlsm).

Ensure macros are enabled as follows.

1. If Developer ribbon is visible then go to step 4.
2. If Developer ribbon not visible, Right click anywhere in one of the ribbons and select "Customize the ribbon".
3. On the right side of the dialog check the box against "Developer" and click OK. (Ensure you click OK and not use cross top right of dialog)
4. Select Developer ribbon.
5. Select Macro Security (In the Code block at left end of Developer ribbon).
6. The dialog that displays should default to Macros in left column.
7. Select required security option button. (Option to "Disable all macros with notification" should be OK.)
8. Click OK to close the dialog.

To use the UDF
Enter the following formula in a cell where A1 is the cell containing the formula

=GetFormula(A1)

or if formula is on a different worksheet then it will be something like as follows with the sheet name in the reference.

=GetFormula(Sheet1!A1)

Following is the UDF Code (and Yes! Only 3 lines of code).

Function GetFormula(rng As Range) As String
GetFormula = rng.Formula
End Function