Excel VBA to Break Links - 4 Examples

The following gif provides an overview.

The following dataset contains the sales overview for two months. B5:C11 is linked to a different workbook: Sales January.

D5:E11 is linked to another workbook: Sales February.

Example 1 – Use VBA If Statement with For Next Loop to Break Links in the Whole Workbook

Use an If Statement with a For Next Loop in the VBA code.

Steps:

Go to the Developer tab.
Select Visual Basic.

In the Visual Basic Editor window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub Break_Links()
Dim act_wb As Workbook
Set act_wb = Application.ActiveWorkbook
If Not IsEmpty(act_wb.LinkSources(xlExcelLinks)) Then
            For Each ext_link In act_wb.LinkSources(xlExcelLinks)
            act_wb.BreakLink ext_link, xlLinkTypeExcelLinks
            Next ext_link
End If
End Sub

Code Breakdown

a Sub Procedure: Break_links is created.
a variable: act_wb is declared as Workbook.
the Application.ActiveWorkbook Property sets act_wb.
The If Statement and the IsEmpty function check if the variable was initialized.
The For Next Loop goes through each link in the workbook.
the BreakLink Method converts the formulas that are linked to other sources to values.
the If Statement is ended.
the Sub Procedure is ended.

Save the code and go back to your worksheet.

Go to the Developer tab.
Select Macros.

In the Macro dialog box, select Break_Links as the Macro name.
Click Run.

Select a cell and you will see the value only, not the link.

The link is removed.

Example 2 – Applying the On Error Statement to Break Links in Excel Ignoring Error Warnings

On Error Statement is used to enable an error-handling routine within a procedure. Use the On Error Resume Next form of the statement to create a VBA code.

Steps:

Open a VBA Module by following the steps in Example 1.
Enter the following code in the Module.

Sub remove_links()
Dim ext_links As Variant
Dim j As Integer
ext_links = ActiveWorkbook.LinkSources(1)
On Error Resume Next
For j = 1 To UBound(ext_links)
ActiveWorkbook.BreakLink ext_links(j), xlLinkTypeExcelLinks
Next j
On Error GoTo 0
End Sub

Code Breakdown

a Sub Procedure:remove_links is created.
a variable: ext_links is declared as a Variant and another variable: j as an Integer.
the ActiveWorkbook.LinkSources returns an array of links present in the active workbook.
the On Error Resume Next enables an error-handling routine.
a For Next Loop goes through all the links and the BreakLink Method converts the formulas that are linked to other sources to values.
the Sub Procedure is ended.

Save the code and go back to your worksheet.
Go to the Developer tab.
Select Macros.

In the Macro dialog box, select remove_links as the Macro name.
Click Run.

Select a cell, here C5. You can see the value only, not the link.

Example 3 – Use the LBound and UBound Functions to Find the Size of an Array and to Break Links in Excel

The LBound function is used to find the lower limit of an array dimension and the UBound function is used to find its upper limit.

Steps:

Open a VBA Module by following the steps in Example 1.
Enter the following code in the Module.

Sub brk_links()
Dim ext_links As Variant
Dim p As Integer
            ext_links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
            For p = LBound(ext_links) To UBound(ext_links)
            ActiveWorkbook.BreakLink _
            Name:=ext_links(p), _
            Type:=xlLinkTypeExcelLinks
            Next p
End Sub

Code Breakdown

a Sub Procedure: brk_links is created.
a variable: ext_links is declared as a Variant and another variable: p as an Integer.
the ActiveWorkbook.LinkSources returns an array of links present in the active workbook.
in the For Next Loop, the LBound and UBound functions get the size of the array
the BreakLink Method converts the formulas that are linked to other sources to values.
the Sub Procedure is ended.

Save the code and go back to your worksheet.
Go to the Developer tab.
Select Macros.

In the Macro dialog box, select brk_links as the Macro name.
Click Run.

Links were removed.

Example 4 – Display the Count of Broken Links in MsgBox in Excel

Steps:

Press Alt + F11 to open the Visual Basic Editor.

Select the Insert tab.
Choose Module.

Enter the following code in the Module.

Sub break_ext_links()
Dim ext_links As Variant
Dim y As Long
Dim brk_count As Long
ext_links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsEmpty(ext_links) = True Then GoTo ReportResults
For y = 1 To UBound(ext_links)
ActiveWorkbook.BreakLink Name:=ext_links(y), Type:=xlLinkTypeExcelLinks
brk_count = brk_count + 1
Next y
ReportResults:
MsgBox "No of Broken Links: " & brk_count
End Sub

Code Breakdown

a Sub Procedure: break_ext_links is created.
a variable: ext_links is declared as a Variant, y as a Long, and brk_count as Long.
the ActiveWorkbook.LinkSources returns an array of links present in the active workbook.
the If Statement, and the IsEmpty function check if the variable was initialized.
a For Next Loop goes through each link in the workbook.
the BreakLink Method converts the formulas that are linked to other sources to values.
the MsgBox function displays the number of broken links.
the Sub Procedure is ended.