删除 定义名称的 vba 总结

'一、删除本工作簿内所有工作表的定义名称

Sub 删除定义名称()

Dim wb As Workbook, MyF$, MyS

Application.ScreenUpdating = False

Application.DisplayAlerts = False

MyF = Dir(ThisWorkbook.Path & "\*.xls*")

Do

If MyF <> ThisWorkbook.Name Then

Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)

For Each MyS In ActiveWorkbook.Names

MyS.Delete

Next MyS

wb.Close

End If

MyF = Dir

Loop While MyF <> ""

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

Dim wb As Workbook, MyF$, MyS

Application.ScreenUpdating = False

Application.DisplayAlerts = False

MyF = Dir(ThisWorkbook.Path & "\*.xls*")

Do

If MyF <> ThisWorkbook.Name Then

Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)

For Each MyS In ActiveWorkbook.Names

MyS.Delete

Next MyS

wb.Close 1

End If

MyF = Dir

Loop While MyF <> ""

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

'二、删除不某文件夹下非在本工作簿定义的定义名称及错误引用遗留的定义名称

Sub 删除某工作簿中所有excel表的非法遗留的定义名称()

Dim wb As Workbook, MyF$, MyS

Application.ScreenUpdating = False

Application.DisplayAlerts = False

MyF = Dir(ThisWorkbook.Path & "\下料单\*.xls*")

Do

'        If MyF <> ThisWorkbook.Name Then

Set wb = Workbooks.Open(ThisWorkbook.Path & "\下料单\" & MyF)

Debug.Print wb.Name

For Each MyS In ActiveWorkbook.Names

Debug.Print MyS.Name & " " & MyS

If Left(MyS, 5) = "='E:\" Or Right(MyS, 6) = "!#REF!" Then

MyS.Delete

End If

Next MyS

wb.Close (True)

'        End If

MyF = Dir

Loop While MyF <> ""

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

’三、'在打开表的情况下执行

Sub 删除不在本工作簿定义的定义名称()

For Each d In ActiveWorkbook.Names

Debug.Print d.Name & " " & d

If Left(d, 5) = "='E:\" Or Right(d, 6) = "!#REF!" Then

d.Delete

End If

Next

End Sub