Listing Formulas

This code will list all the formulas in the selection in the Immediate Window.

Sub ListFormulas()
   
    Dim rCell As Range
   
    If TypeName(Selection) = “Range” Then
        For Each rCell In Selection.Cells
            Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
        Next rCell
    End If
   
End Sub

If you have a range selected, it loops through each cell. Then it prints four spaces, the cell’s address ((0,0) means relative, e.g. A1 not $A$1), and the cell’s formula. Note that if a cell contains a value instead of a formula, the Formula property returns the value. I wonder why that is.

Why the four spaces? So I can paste into SuperUser and it thinks it’s code.

Posted in Uncategorized

3 thoughts on “Listing Formulas

  1. Why not avoid those cells that do not have formulas:

    Sub ListFormulas()
       
        Dim rCell As Range
       
        If TypeName(Selection) = “Range” Then
            For Each rCell In Selection.Cells
                If rCell.HasFormula Then
                    Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
                End If
            Next rCell
        End If
       
    End Sub
  2. @Nick and Dick…

    Here is another way to avoid cells that do not have formulas in them. This method has the advantage of not looping through, and testing, every cell in the selection as the SpecialCells method will only deliver a range of cells with formulas for the “For Each” statement to iterate through. Also note that I replaced the String function call with, in my opinion, the more apt Space function call.

    Sub ListFormulas()
       
        Dim rCell As Range
       
        If TypeName(Selection) = “Range” Then
            For Each rCell In Selection.SpecialCells(xlCellTypeFormulas)
                Debug.Print Space(4) & rCell.Address(0, 0), rCell.Formula
            Next rCell
        End If
       
    End Sub
  3. @Nick and Dick…

    I guess I should have included an “On Error” statement just to protect against the Selection having no formulas in any of its cells…

    Sub ListFormulas()
       
        Dim rCell As Range
       
        On Error Resume Next
        If TypeName(Selection) = “Range” Then
            For Each rCell In Selection.SpecialCells(xlCellTypeFormulas)
                Debug.Print Space(4) & rCell.Address(0, 0), rCell.Formula
            Next rCell
        End If
       
    End Sub

Leave a Reply

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