Daily Dose of Excel

Precedents are ranges on which the value of another cell relies. If, in cell B1, you have the formula “=A1″ then A1 is a precedent of B1. The Range object has a Precedents property that returns another Range object that includes all the precedents for that cell.

Take this formula in A8, for example

To list all the precedents for this formula, you can loop through all the cells in the range returned by the Precedents property. However, it may be more useful to loop through the Areas of the range, as in the example below.

Sub ListPrecedents()

    Dim rStart As Range
    Dim rPrecCells As Range
    Dim cell As Range
    Dim sPrecList As String
    
    Set rStart = Sheet1.Range(”A8″)
    
    ‘If there are no precedents, and error will occur
    On Error Resume Next
        Set rPrecCells = rStart.Precedents
    On Error GoTo 0
    
    ‘If there are precedents
    If Not rPrecCells Is Nothing Then
        ‘Loop through the Areas collection and string
        ‘together the addresses
        For Each cell In rStart.Precedents.Areas
            sPrecList = sPrecList & cell.Address(0, 0) & “,”
        Next cell
        
        ‘Remove the last comma
        sPrecList = Left(sPrecList, Len(sPrecList) - 1)
    Else
        sPrecList = “No Precedents Found”
    End If
    
    MsgBox sPrecList
    
End Sub

By looping through the Areas collection, the sub returns a more readable list of range addresses. Here’s the resulting message box: