XL: Macro to Delete Defined Names with Links

ID: Q138619

The information in this article applies to:

SUMMARY

In Microsoft Excel, workbooks can have two kinds of links: internal or external. Internal links are references to objects within (or internal to) the document (for example, another cell on a given sheet or another cell on another sheet in the same workbook). The following examples demonstrate how an internal link may appear on your sheet: 

   =C2

   -or-

   =Sheet1!C2
External links are references to objects outside the document (for example a cell on a sheet in another file). The following example demonstrates how an external link may appear on your sheet: 

   ='C:\EXCEL\[BOOK1.XLS]Sheet1'!$C$2
The macro in the "More Information" section of this article displays all the defined names in the active workbook that has an external reference to a defined name in a different workbook. External links not referenced by a defined name are unaffected. After a defined name is deleted by the macro, the error value "#NAME" is displayed in the cell(s) that use the defined name.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/

Visual Basic Code Example

 Option Explicit

 Sub delete_external_names()

 'variable declarations
 Dim response As Integer
 Dim msg As String
 Dim flag As Boolean
 Dim defined_name As Object

   flag = True     ' check if external links were found

   ' loop through each defined name in workbook
   For Each defined_name In ActiveWorkbook.Names

      ' if a [ was found, then the name has a link
      If InStr(defined_name.RefersTo, "[") > 0 Then

         flag = False ' set flag to False indicating a link was found

         ' Message displayed to ask if you want to delete name
          msg = "Do you want to delete the defined name " & "'" & _
             defined_name.Name & "'" & Chr(13) & " that refers to '" & _
             defined_name & "' ?"

          ' delete the defined name
          If MsgBox(msg, 292) = vbYes Then defined_name.Delete
       End If
   Next defined_name  ' get the next defined name

   If flag = True Then  ' if flag was not set, display message below

      MsgBox "No defined names with external were links found."
   End If
 End Sub

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base: 

   ARTICLE-ID:  Q136314
   TITLE     :  XL: Copying Sheets Between Workbooks Can Create
                Unexpected Links

   ARTICLE-ID:  Q188449
   TITLE     :  XL97: Delete Links Wizard Available on MSL
For more information about Visual Basic for Applications in Excel 7, select Excel's On-line Help menu, choose "Microsoft Excel Help Topics," click on the Contents tab and choose "Getting Started with Visual Basic".

For more information about Visual Basic for Applications in Excel 5, choose Excel's On-line Help menu, select "Contents" then "Programming with Visual Basic."

Additional query words: 5.00 7.00 

Keywords          : kbmacro kbprg kbdta kbdtacode xlformula KbVBA 
Version           : WINDOWS:5.0,7.0,97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999