Microsoft KB Archive/185387

Knowledge Base

XL98: Macro to Delete Defined Names with Links

Article ID: 185387

Article Last Modified on 6/17/2005

---

APPLIES TO

• Microsoft Excel 98 for Macintosh

---

This article was previously published under Q185387

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 might 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 might appear on your sheet:

='<hard drive>:documents:[WORKBOOK1]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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.

To Create a Macro to Delete Defined Names with Links

1. Press OPTION+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module.

3. Type the following code:

       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
                           

4. On the Tools menu, click Macros. Select "delete_external_names" and click Run.

REFERENCES

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

136314 Copying Sheets Between Workbooks Can Create Unexpected Links

Additional query words: XL98

Keywords: kbhowto kbmacro kbprogramming KB185387

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.