Microsoft KB Archive/213385

= XL2000: How to Programmatically Delete Defined Names with Links =

Article ID: 213385

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213385





SUMMARY
This article shows you how to create a Microsoft Visual Basic for Applications Sub procedure (macro) that displays all the defined names in the active workbook that have an external reference to a defined name in a different workbook. With the macro, you can also delete the defined name if you want to.



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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

The following sample macro lists all of the defined names in the active workbook that have an external reference to a defined name in a different workbook. The macro asks whether you want to delete each defined name as it is found.

When you use the macro to delete a defined name, the error value "#NAME" is displayed in the cell or cells that use the defined name.

NOTE: External links not referenced by a defined name are not affected.

To create a macro that lists defined names that have an external reference to a defined name in a different workbook, use the steps in the following example:  In Excel, press ALT+F11 to start the Visual Basic editor. On the Insert menu, click Module.  On the module sheet, 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 

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 worksheet:

=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

