Microsoft Knowledge Base

• Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY

In Microsoft Excel, there is no built-in function to return the formula contained in a cell as text. However, this information can be provided through a custom function macro. You can create this custom function in a Visual Basic, Applications Edition, procedure, or you can create it in a Microsoft Excel 4.0 macro.

MORE INFORMATION

Visual Basic Procedure

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

In a Microsoft Excel 5.0 module sheet, type the following code:

'Name the function "FormulaText" and define the argument "cell_ref"

'Allow formula to be updated if changes are made on the sheet

 'Test for reference style in use
   If Application.ReferenceStyle = xlA1 Then
     'Set the return value of the function to the A1 style formula
     'contained in "cell_ref"
         FormulaText = cell_ref.Formula
    Else
     'Set the return value of the function to the R1C1 style formula
     'contained in "cell_ref"
         FormulaText = cell_ref.FormulaR1C1
   End If

To use this Visual Basic procedure:

1. On a worksheet, select the cell where the result is to be returned.

2. From the Insert menu, choose Function.

3. Find the name of the macro, "FormulaText," in the list of available functions.

4. Provide the "cell_ref" argument, which is the cell containing the formula to be returned.

5. The formula contained in "cell_ref" will be returned as a text string.

Microsoft Excel Macro for Versions 3.0, 4.0, and 4.0a

The following macro code example may be used in Microsoft Excel versions 3.0, 4.0 and 5.0.

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

In a new macro sheet, type the following code in the cells indicated:

A1: FormulaText A2: =ARGUMENT("cell_ref",8) A3: =RETURN(GET.CELL(6,cell_ref))

Explanation of Macro:

A1: Name of the macro.

A2: Defines an argument, "cell_ref", as referring to the formula

     in a cell.

To use this macro, do the following:

1. On a new macro sheet, select cell A1.

2. From the Formula menu, choose Define Name.

3. In the Define Name dialog box, select the Function option and choose OK.

4. On a worksheet, select the cell where the result is to be returned.

5. From the Formula menu, select Paste Function.

6. Find the name of the macro, FormulaText, in the list of available functions.

7. Provide the cell_ref argument, which is the cell containing the formula to be returned.

8. The formula contained in cell_ref will be returned as a text string.