How To List, change or delete external formula references using VBA?
Tags: tolistchangeordelete, vbamacros
Published: 2021-10-05 11:14:20

---
title: How To List, change or delete external formula references using VBA?
published: true
date: 2021-10-05 09:49:22 UTC
tags: ToListChangeOrDelete,VBAMacros
canonical_url: https://geekexcel.com/how-to-list-change-or-delete-external-formula-references-using-vba/
---

In this article, you will learn how to  **list, change or delete external formula references** using VBA in Microsoft Excel. Let’s see them below!! Get the official version of  **MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Example

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
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

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/10/Save-the-Code-11-1024x905.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Then, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/10/Run-the-Code-11.png)<figcaption>Run the Code</figcaption>

- Finally, you will receive the **list, change or delete external formula references** using VBA.

## Wrap-Up

We hope that this tutorial gives you guidelines on how to  **list, change or delete external formula references** using VBA in Microsoft Excel. ** ** Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Keep Reading:**

- **[Replace or Change Names in Formulas with Cell Reference in Excel 365!!](https://geekexcel.com/replace-or-change-names-in-formulas-with-cell-reference-in-excel-365/)**
- **[How to Copy Exact Formulas without Changing Cell References in Excel?](https://geekexcel.com/how-to-copy-exact-formulas-without-changing-cell-references-in-excel/)**
- **[How to Fill Cell References From Multiple Worksheets in Excel?](https://geekexcel.com/how-to-fill-cell-references-from-multiple-worksheets-in-excel/)**
- **[Convert Cell Reference to Relative/Absolute Reference in Excel 365!!](https://geekexcel.com/convert-cell-reference-to-relative-absolute-reference-in-excel-365/)**
- **[How to Delete Sheet in Microsoft Excel 365?](https://geekexcel.com/how-to-delete-sheet-in-microsoft-excel-365/)**