XL: Macro to Delete Defined Names with Links

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

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

   ='C:\EXCEL\[BOOK1.XLS]Sheet1'!$C$2

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 engineers 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/default.asp

Visual Basic Code Example

Option Explicit

Sub delete_external_names()

'variable declarations

   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

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

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

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".