Pulling Formulas from a Worksheet

Excel allows you to display the formulas in a worksheet simply by making sure the Formulas check box is selected on the View tab of the Options dialog box. This can be a handy way to print a worksheet that shows the formulas used to create the worksheet.

However, this approach only works well if the formulas used in the worksheet are rather short. If the formulas are longer, then understanding a worksheet with formulas displayed can quickly become a bothersome chore.

One solution is to pull the formulas from Excel and place them in a program such as Word. Why Word? Because you can easily format text attributes (such as typeface and point size) to best display your formulas. You can also add additional text to explain the formulas, if desired.

The simplest way to get formulas from Excel into Word is to follow these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Advanced.
3. Scroll through the list of options until you see the section entitled Display Options for this Worksheet. (See Figure 1.)



Figure 1. The Advanced options of the Excel Options dialog box.

4. Ensure the Show Formulas In Cells Instead of Their Calculated Result check box is selected.
5. Click on OK. Excel should now be displaying formulas.
6. Select the cells whose formulas you want to copy to Word.
7. Press Ctrl+C to copy the cells to the Clipboard.
8. Switch to Word.
9. Position the insertion point where you want the information added.
10. Display the Home tab of the ribbon.
11. Click the down-arrow under the Paste tool, and then choose Paste Special. Word displays the Paste Special dialog box.
12. Choose the Unformatted Text option.
13. Click on OK.

At this point your formulas are in Word, and you can do with them as you see fit. While this approach works well, it can become bothersome to do this over and over again if you have a large number of formulas to copy. If you are in such a situation, you would benefit from having a macro that actually pulled the formulas and placed them in a Word document for you. The following Excel macro will do just that:

Sub WriteFormulasToWord()
    Dim Wrd As New Word.Application
    Dim CellTxt As String
    Dim CellAddr As String
    Dim SRow As Long
    Dim SCol As Long

    Wrd.Visible = True
    Wrd.Documents.Add

    Wrd.Selection.TypeText Text:="List of the Formulas of Sheet """ _
      & ActiveSheet.Name & """ in Workbook """ _
      & ActiveWorkbook.Name & """."
    Wrd.Selection.TypeText Text:=vbCrLf & vbCrLf

    'Change the following line to pick the number of columns
    For SCol = 1 To 5
        'Change the following line to pick the number of rows
        For SRow = 1 To 10
            If Cells(SRow, SCol).HasFormula Then
                CellAddr = Chr(64 + SCol) & Trim(Str(SRow)) & vbTab
                CellTxt = ActiveSheet.Cells(SRow, SCol).Formula
                Wrd.Selection.TypeText Text:=CellAddr & CellTxt
                Wrd.Selection.TypeText Text:=vbCrLf
            End If
        Next SRow
        Wrd.Selection.TypeText Text:=vbCrLf
    Next SCol
End Sub

There are a couple of things to note in this macro. First of all, you can change the range of rows and columns over which the macro works by changing the noted For statements that use both SCol and SRow. In the example shown above, the macro pulls formulas from columns 1 through 5 (A through E) and rows 1 through 10.

In addition, this macro will not work properly unless you set up Excel macros to handle references to Word objects. You do that by following these steps within the VBA Editor:

1. Choose References from the Tools menu. VBA displays the References dialog box. (See Figure 2.)



Figure 2. The References dialog box.

2. Scroll through the list of references until you see one called Microsoft Word Object Library. (There may be a version number included in the reference name, such as Microsoft Word 12.0 Object Library.)
3. Make sure the check box to the left of the object library is selected.
4. Click on OK.

An advantage to using a macro to actually pull your formulas is that you can customize exactly what is placed in the Word document. In the case of this macro, the address of the cell is inserted, followed by a tab character, and then the actual formula. You could just as easily change the information inserted to be anything you need in your particular instance.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6233) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Pulling Formulas from a Worksheet.