删除 定义名称的 vba 总结

'一、删除本工作簿内所有工作表的定义名称Sub 删除定义名称()Dim wb As Workbook, MyF$, MySApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseMyF = Dir(ThisWorkbook.Path & "\*.xls*")DoIf MyF <> ThisWorkbook.Name ThenSet wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)For Each MyS In ActiveWorkbook.NamesMyS.DeleteNext MySwb.CloseEnd IfMyF = DirLoop While MyF <> ""Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd SubDim wb As Workbook, MyF$, MySApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseMyF = Dir(ThisWorkbook.Path & "\*.xls*")DoIf MyF <> ThisWorkbook.Name ThenSet wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)For Each MyS In ActiveWorkbook.NamesMyS.DeleteNext MySwb.Close 1End IfMyF = DirLoop While MyF <> ""Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd Sub'二、删除不某文件夹下非在本工作簿定义的定义名称及错误引用遗留的定义名称Sub 删除某工作簿中所有excel表的非法遗留的定义名称()Dim wb As Workbook, MyF$, MySApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseMyF = Dir(ThisWorkbook.Path & "\下料单\*.xls*")Do'        If MyF <> ThisWorkbook.Name ThenSet wb = Workbooks.Open(ThisWorkbook.Path & "\下料单\" & MyF)Debug.Print wb.NameFor Each MyS In ActiveWorkbook.NamesDebug.Print MyS.Name & " " & MySIf Left(MyS, 5) = "='E:\" Or Right(MyS, 6) = "!#REF!" ThenMyS.DeleteEnd IfNext MySwb.Close (True)'        End IfMyF = DirLoop While MyF <> ""Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd Sub’三、'在打开表的情况下执行Sub 删除不在本工作簿定义的定义名称()For Each d In ActiveWorkbook.NamesDebug.Print d.Name & " " & dIf Left(d, 5) = "='E:\" Or Right(d, 6) = "!#REF!" Thend.DeleteEnd IfNextEnd Sub