Mashhura Jahan
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.
Download Practice Workbook
You can download the practice workbook from here.
4 Suitable Examples of Using Excel VBA to Break Links
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.
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.
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.
- Thirdly, the Visual Basic Editor window will open.
- Select Insert tab.
- Then, select Module.
- 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
🔎 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.
- Next, go to the Developer tab.
- Then, select Macros.
- Afterward, the Macro dialog box will appear.
- Select Break_Links as the Macro name.
- Then, select Run.
- Now, you will see that you have broken the links.
- To check that, select a cell and you will see only the value remaining.
- In the following image, you can see that I have also selected a cell from the second link and the link is removed.
Read More: How to Break Links in Excel When Source Is Not Found (4 Ways)
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
🔎 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.
- Consequently, the Macro dialog box will appear.
- Select remove_links as the Macro name.
- Then, select Run.
- 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.
Read More: How to Break Links in Excel and Keep Values (3 Easy Ways)
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
🔎 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.
- Consequently, the Macro dialog box will appear.
- Select brk_links as the Macro name.
- Then, select Run.
- Finally, select any cell containing links and you will see that you have broken the links.
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.
- Consequently, the Visual Basic Editor window will open.
- Select the Insert tab.
- Then, select Module.
- 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
🔎 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.
- Now, the Macro dialog box will open.
- Select break_ext_links as the Macro name.
- Then, select Run.
- Consequently, you will see a MsgBox will appear with the No of Broken Links.
- Select OK.
- Lastly, select any cell that contained links before and you will see that you have broken the links.
Read More: How to Remove Broken Links in Excel (3 Simple Methods)
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.
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. For more articles, visit ExcelDemy. If you have any questions, feel free to let me know in the comment section below.
