Excel VBA to Break Links (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Whenever working with Excel you may face a situation where you will need to link data from other workbooks to get a large amount of data. But this data changes when a small change is made in the source workbook. Breaking links can be helpful in these types of situations. The focus of this article is to explain how to use Excel VBA to break links.

In the following gif, you can see how easily you can break links using Excel VBA.

excel vba break links


Excel VBA to Break Links: 4 Suitable Examples

Here, I have taken the following dataset to explain this article. It contains the sales overview for two months. You can see that cell range B5:C11 is linked with a different workbook named Sales January.

Dataset for Excel VBA to Break Links

In the following image, you can see that cell range D5:E11 is linked with another workbook named Sales February. Now, I will show you how you can use Excel VBA to break links using this dataset. I will explain 4 suitable examples.

Cell Range Linked to Another Workbook


1. Use VBA If Statement with For Next Loop to Break Links in Whole Workbook

In this first example, I will use an If Statement along with a For Next Loop in the VBA code to break links in Excel. You can use this method to check every non empty cell in a workbook and then break links. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

Opening Visual Basic Editor Window for Writing VBA Code to Break Links in Excel

  • Thirdly, the Visual Basic Editor window will open.
  • Select Insert tab.
  • Then, select Module.

Opening Module in Visual Basic Editor Window

  • Consequently, a Module will open.
  • Write the following code in that 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

VBA Code with If Statement And For Next Loop to Break Links in Excel

🔎 How Does the Code Work?

  • Here, I created a Sub Procedure named Break_links.
  • Then, I declared a variable named act_wb as Workbook.
  • Next, I used the Application.ActiveWorkbook Property to set act_wb.
  • Afterward, I used an If Statement, and in the statement, I used the IsEmpty function to check if the variable has been initialized.
  • Then, I used a For Next Loop to go through each link of the workbook.
  • Next, I used the BreakLink Method to convert the formulas that are linked to other sources to values.
  • After that, I ended the If Statement.
  • Finally, I ended the Sub Procedure.
  • After that, Save the code and go back to your worksheet.

Saving VBA Code to Break Links in Excel

  • Next, go to the Developer tab.
  • Then, select Macros.

Selecting Macros Command in Excel

  • Afterward, the Macro dialog box will appear.
  • Select Break_Links as the Macro name.
  • Then, select Run.

Running VBA Code to Break Links in Excel

  • Now, you will see that you have broken the links.
  • To check that, select a cell and you will see only the value remaining.

Links are Removed from Values

  • In the following image, you can see that I have also selected a cell from the second link and the link is removed.

External Links Are Broken and Only Values Remain

Read More: How to Break Links in Excel When Source Is Not Found


2. Apply 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. Here, I will use the On Error Resume Next form of the statement for writing a VBA code to break links in Excel. You can use this method to ignore error warnings. Let me show you the steps.

Steps:

  • In the beginning, open a VBA Module by following the steps from Example-01.
  • Then, write the following code in that 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

VBA Code with On Error Statement to Break Links in Excel

🔎 How Does the Code Work?

  • Here, I created a Sub Procedure named remove_links.
  • Then, I declared a variable named ext_links as a Variant and another variable named j as an Integer.
  • Next, I used the ActiveWorkbook.LinkSources method to return an array of links present in the active workbook.
  • After that, I used On Error Resume Next to enable an error-handling routine.
  • Then, I used a For Next Loop to go through all the links.
  • In the For Next Loop, I used the BreakLink Method to convert the formulas that are linked to other sources to values.
  • Finally, I ended the Sub Procedure.
  • After that, Save the code and go back to your worksheet.
  • Then, go to the Developer tab.
  • Next, select Macros.

Using Macros Command to Run VBA Code in Excel

  • Consequently, the Macro dialog box will appear.
  • Select remove_links as the Macro name.
  • Then, select Run.

Running Macros to Break Links in Excel VBA

  • After that, select a cell to see if the link is broken. Here, I selected Cell C5 and you can only see the value which means I have broken the link.

Link Has Been Broken and Cell Only Contains Value

Read More: How to Break Links in Excel and Keep Values


3. Employ LBound and UBound Functions for Finding Size of Array 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 the upper limit. You can use these two functions together to find the size of an array and then break links from it. This is what I am going to do in this example. Let’s see how you can use LBound and UBound functions to break links in Excel VBA.

Steps:

  • In the beginning, open a VBA Module by following the steps from Example-01.
  • Next, write the following code in that 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

VBA Code with LBound and UBound Functions to Break Links in Excel

🔎 How Does the Code Work?

  • Here, I created a Sub Procedure named brk_links.
  • Then, I declared a variable named ext_links as a Variant and another variable named p as an Integer.
  • Next, I used the ActiveWorkbook.LinkSources method to return an array of links present in the active workbook.
  • Then, I used a For Next Loop to go through all the links.
  • In the For Next Loop, I used the LBound and UBound functions to get the size of the array.
  • Next, I used the BreakLink Method to convert the formulas that are linked to other sources to values.
  • Finally, I ended the Sub Procedure.
  • Afterward, Save the code and go back to your worksheet.
  • Go to the Developer tab.
  • Then, select Macros.

Employing Macros Feature

  • Consequently, the Macro dialog box will appear.
  • Select brk_links as the Macro name.
  • Then, select Run.

Using Macro Dialog Box to Break Links in Excel VBA

  • Finally, select any cell containing links and you will see that you have broken the links.

Only Values Remain in Cells


4. Display Count of Broken Links in MsgBox in Excel

You can also display the number of broken links in a MsgBox. Here, I will show you how you can break links in Excel VBA and display the number of links you have broken in a MsgBox. Let’s see the steps.

Steps:

  • Firstly, press Alt + F11 on your keyboard to open the Visual Basic Editor window.

Using Keyboard Shortcut to Open VBA Editor

  • Consequently, the Visual Basic Editor window will open.
  • Select the Insert tab.
  • Then, select Module.

Opening Module for Writing VBA Code to Break Links in Excel

  • Afterward, a Module will open.
  • Next, write the following code in that 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

VBA Code for Displaying Number of Broken Links in MsgBox

🔎 How Does the Code Work?

  • Here, I created a Sub Procedure named break_ext_links.
  • Then, I declared variables named ext_links as a Variant, y as a Long, and brk_count as Long.
  • Next, I used the ActiveWorkbook.LinkSources method to return an array of links present in the active workbook.
  • After that, I used an If Statement, and in the statement, I used the IsEmpty function to check if the variable has been initialized.
  • Then, I used a For Next Loop to go through each link in that workbook.
  • Next, I used the BreakLink Method to convert the formulas that are linked to other sources to values.
  • Afterward, I used the MsgBox function to display the number of broken links.
  • Lastly, I ended the Sub Procedure.
  • Next, Save the code and go back to your worksheet.
  • Then, press Alt + F8 to open the Macro dialog box.

Applying Keyboard Shortcut to Open Macro Dialog Box

  • Now, the Macro dialog box will open.
  • Select break_ext_links as the Macro name.
  • Then, select Run.

Employing Macro Dialog Box to Run Code

  • Consequently, you will see a MsgBox will appear with the No of Broken Links.
  • Select OK.

Output MsgBox from VBA Code

  • Lastly, select any cell that contained links before and you will see that you have broken the links.

Final Output with Broken Links


Things to Remember

  • Whenever working with VBA, you must save the Excel file as Excel Macro-Enabled Workbook.

Practice Section

Here, I have provided a practice sheet for you to practice how to use Excel VBA to Break Links.

Practice Sheet to Break Links in Excel VBA


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

To conclude, I tried to explain how to break links in Excel VBA with 4 suitable examples. I hope this article was helpful to you. If you have any questions, feel free to let me know in the comment section below.


Related Articles

<< Go Back To Excel Break Links | Linking in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo