These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

List, change or delete external formula references (links)

With the macros below you can find and delete formulas in cells that refers to other workbooks. The macros doesn't find all external references since they only look in the worksheet formulas.

Sub DeleteOrListLinks()
Dim i As Integer
    If ActiveWorkbook Is Nothing Then Exit Sub
    i = MsgBox("YES: Delete external formula references" & Chr(13) & _
        "NO: List external formula references", _
        vbQuestion + vbYesNoCancel, "Delete or list external formula references")
    Select Case i
        Case vbYes
            DeleteExternalFormulaReferences
        Case vbNo
            ListExternalFormulaReferences
    End Select
End Sub

Sub DeleteExternalFormulaReferences()
Dim ws As Worksheet, AWS As String, ConfirmReplace As Boolean
Dim i As Integer, OK As Boolean
    If ActiveWorkbook Is Nothing Then Exit Sub
    i = MsgBox("Confirm all replacements of external formula references with values?", _ 
	vbQuestion + vbYesNoCancel, "Convert external formula references")
    ConfirmReplace = False
    If i = vbCancel Then Exit Sub
    If i = vbYes Then ConfirmReplace = True
    AWS = ActiveSheet.Name
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        OK = DeleteLinksInWS(ConfirmReplace, ws)
        If Not OK Then Exit For
    Next ws
    Set ws = Nothing
    Sheets(AWS).Select
    Application.ScreenUpdating = True
End Sub

Private Function DeleteLinksInWS(ConfirmReplace As Boolean, _
    ws As Worksheet) As Boolean
Dim cl As Range, cFormula As String, i As Integer
    DeleteLinksInWS = True
    If ws Is Nothing Then Exit Function
    Application.StatusBar = "Deleting external formula references in " & _
        ws.Name & "..."
    ws.Activate
    For Each cl In ws.UsedRange
        cFormula = cl.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 1) = "=" Then
                If InStr(cFormula, "[") > 1 Then
                    If Not ConfirmReplace Then
                        cl.Formula = cl.Value
                    Else
                        Application.ScreenUpdating = True
                        cl.Select
                        i = MsgBox("Replace the formula with the value?", _
                            vbQuestion + vbYesNoCancel, _
                            "Replace external formula reference in " & _
                             cl.Address(False, False, xlA1) & _
                             " with the cell value?")
                        Application.ScreenUpdating = False
                        If i = vbCancel Then
                            DeleteLinksInWS = False
                            Exit Function
                        End If
                        If i = vbYes Then
                            On Error Resume Next 
                            ' in case the worksheet is protected
                            cl.Formula = cl.Value
                            On Error GoTo 0
                        End If
                    End If
                End If
            End If
        End If
    Next cl
    Set cl = Nothing
    Application.StatusBar = False
End Function

Sub ListExternalFormulaReferences()
Dim ws As Worksheet, TargetWS As Worksheet, SourceWB As Workbook
    If ActiveWorkbook Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    With ActiveWorkbook
        On Error Resume Next
        Set TargetWS = .Worksheets.Add(Before:=.Worksheets(1))
        If TargetWS Is Nothing Then ' the workbook is protected
            Set SourceWB = ActiveWorkbook
            Set TargetWS = Workbooks.Add.Worksheets(1)
            SourceWB.Activate
            Set SourceWB = Nothing
        End If
        With TargetWS
            .Range("A1").Formula = "Sequence"
            .Range("B1").Formula = "Cell"
            .Range("C1").Formula = "Formula"
            .Range("A1:C1").Font.Bold = True
        End With
        For Each ws In .Worksheets
            If Not ws Is TargetWS Then
                ListLinksInWS ws, TargetWS
            End If
        Next ws
        Set ws = Nothing
    End With
    With TargetWS
        .Parent.Activate
        .Activate
        .Columns("A:C").AutoFit
        On Error Resume Next
        .Name = "Link List"
        On Error GoTo 0
    End With
    Set TargetWS = Nothing
    Application.ScreenUpdating = True
End Sub

Private Sub ListLinksInWS(ws As Worksheet, TargetWS As Worksheet)
Dim cl As Range, cFormula As String, tRow As Long
    If ws Is Nothing Then Exit Sub
    If TargetWS Is Nothing Then Exit Sub
    Application.StatusBar = "Finding external formula references in " & _
        ws.Name & "..."
    For Each cl In ws.UsedRange
        cFormula = cl.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 1) = "=" Then
                If InStr(cFormula, "[") > 1 Then
                    With TargetWS
                        tRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & tRow).Formula = tRow - 1
                        .Range("B" & tRow).Formula = ws.Name & "!" & _
                            cl.Address(False, False, xlA1)
                        .Range("C" & tRow).Formula = "'" & cFormula
                    End With
                End If
            End If
        End If
    Next cl
    Set cl = Nothing
    Application.StatusBar = False
End Sub

Document last updated 2002-05-20 12:51:27