Microsoft Knowledge Base

• Microsoft Excel for the Macintosh, version 4.0
• Microsoft Excel for Windows, version 4.0, 4.0a

SUMMARY

This article contains a macro function you can use to turn worksheet formulas into text so that the formulas (rather than their resulting values) can be displayed when you print them.

Although you can display formulas by selecting the Formulas box in the Display Options dialog box (from the Options menu, choose Display), selecting this option will disable most cell formatting (including wrapped text). This option will also affect the entire spreadsheet.

To display formulas and retain control over cell formatting in Microsoft Excel 4.0, you can create a macro that adds an apostrophe to the beginning of each formula in a selection. The following sample macro places an apostrophe at the beginning of cells in a selection.

MORE INFORMATION

Macro to Display Formulas

A1: ApostPut A2: =FOR.CELL("loop") A3: =IF(ISBLANK(loop),GOTO(A4),FORMULA("'"&GET.CELL(6,loop),loop)) A4: =NEXT() A5: =RETURN()

The IF statement in A3 prevents hidden apostrophes from being placed in blank cells in the selected area. The GET.CELL() function, with the argument of 6, returns the formula from each cell, which also returns entered text and values. If FORMULA("'"&loop,loop) was used, the macro would only return the value in the cell, not the formula.

Macro to Remove Apostrophes (if you are using A1 Notation)

To remove these apostrophes and restore functionality to the formulas, use the following macro (note that this macro uses A1 notation in the worksheet).

A1: ApostRemove A2: =FOR.CELL("loop") A3: =IF(ISERROR(FORMULA.CONVERT(loop,TRUE,FALSE,,loop)),GOTO(A4),

     FORMULA(FORMULA.CONVERT(loop,TRUE,FALSE,,loop),loop))

The IF(ISERROR()) is necessary to prevent errors halting the macro on blank cells. The FORMULA.CONVERT is required because the FORMULA function requires use of RC notation and the formula needs to be converted from A1 to RC notation.

Macro to Remove Apostrophes (if you are using R1C1 Reference Style)

If you are using R1C1 reference style, the following macro will remove apostrophes and return the text of formulas to working formulas. In this case, the ISBLANK prevents the macro from placing zeros in blank cells.

A1: ApostRemove A2: =FOR.CELL("loop") A3: =IF(ISBLANK(loop),GOTO(A4),FORMULA(loop,loop)) A4: =NEXT() A5: =RETURN()

Removing an apostrophe does not restore formulas that need to be entered as array formulas. Arrays are entered by pressing CTRL+SHIFT+ENTER (in Microsoft Excel for Windows) or COMMAND+RETURN (in Microsoft Excel for the Macintosh).

For information about how to create a similar Visual Basic, Applications Edition, macro, see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE ID: Q124935
   TITLE: XL5: Procedure to Add and Remove Hidden Apostrophes