Excel: ошибка при удалении «битых» именованных диапазонов

[Samsonov]

Предыстория. Есть кучка книг Excel, видимо, созданных на базе одного шаблона: у всех них имеется целая куча именованных диапазонов (Вставка • Имя • Присвоить) с покорёженными именами и пустыми ссылками. Эти имена никому не нужны, и их бы вообще никто не заметил, если бы их присутствие не мешало выполнять некоторые операции с книгами, как то изменение стиля ссылок с A1 на R1C1, например. Удаление всех имён решает проблему, но в Excel не предусмотрено удаление всех имён сразу — только поштучно и в неудобной форме; проделывать эту операцию вручную слишком муторно, учитывая количество книг и количество имён в каждой книге.

В общем, задача сводится к написанию макроса, который удалял бы все именованные диапазоны. На первый взгляд, всё элементарно — надо просто очистить коллекцию ActiveWorkbook.Names, но так как у неё нет метода Clear, приходится удалять по одному элементу:

Код: Выделить всё

For Each oName In ActiveWorkbook.Names
  oName.Delete
Next

Этот код успешно работает, но только покуда имена диапазонов корректны. Как только натыкаемся на элемент с некорректным именем — тут же выдаётся ошибка 1004 «Данное имя недопустимо». И всё, ничего с этим не поделаешь.

Пробовал искать в Интернете — ничего стоящего не нашёл, кроме идеи с присвоением нового имени и последующего удаления. Не прокатило: операция переименования как будто происходит успешно, но, похоже, что просто создаётся новое имя, а старое лежит на прежнем месте. Причём народ тоже отмечает, что удаление вручную через пользовательский диалог Excel таки всегда срабатывает, независимо от корректности имени. В итоге предлагалось два выхода: либо сидеть и удалять вручную, либо воспользоваться hex-редактором — но ни один из них мне категорически не подходит.

Пример проблемного файла прилагаю — там же лежит и макрос с различными неудавшимися обходными вариантами.

[Alec]

Интересная заморочка...
Если некритично к данным и оформлению, можно попробовать пересохранить книгу в старом формате "Книга Microsoft Excel 5.0/95". Большинство имен преобразуется в корректные и макросом удалятся. Чтобы не спотыкаться на оставшихся некорректных именах можно включить в модуль

Код: Выделить всё

On Error Resume Next

У меня после пересохранения книги из вложения осталось всего одно некорректное имя.

[Samsonov]

Можно попробовать пересохранить книгу в старом формате «Книга Microsoft Excel 5.0/95» — при этом большинство имён преобразуется в корректные и макросом удалятся.

Воистину, всё гениальное просто. Если изначально некорректными являются 36 имён, то после смены формата — всего 2 штуки; это уже можно и руками удалить. Спасибо за хитрый трюк.

Чтобы не спотыкаться на оставшихся некорректных именах, можно включить в модуль On Error Resume Next.

Теперь макрос выглядит так — после работы в стиле best effort, выводит имена оставшихся диапазонов:

Код: Выделить всё

Sub DeleteAllNamedRanges()

  On Error Resume Next

  Dim oName As Name
  For Each oName In ActiveWorkbook.Names
    oName.Delete
  Next

  DoEvents

  Dim iRemaining As Integer, sRemaining As String
  For Each oName In ActiveWorkbook.Names
    iRemaining = iRemaining + 1
    sRemaining = sRemaining & oName.Name & vbCrLf
  Next
  If sRemaining <> vbNullString Then
    MsgBox "Оставшиеся имена - " & Format(iRemaining) & " всего, по одному на строку:" & vbCrLf & vbCrLf & sRemaining
  End If

End Sub

Автоматическое сохранение во временном формате делать не стал — пользователь с этим и самостоятельно лучше справится.

[KL]

А если так?

Option Explicit

Код: Выделить всё

Sub DeleteAllNamedRanges()
    Dim oName As Name
    On Error Resume Next
    For Each oName In ThisWorkbook.Names
        oName.Delete
    Next
    Application.ReferenceStyle = xlR1C1
    For Each oName In ThisWorkbook.Names
        oName.Delete
    Next
    Application.ReferenceStyle = xlA1
End Sub

[Samsonov]

А если так?

Код: Выделить всё

Application.ReferenceStyle = xlR1C1

Так ведь с этого всё и начиналось — см. вторую фразу в моём первом сообщении:

Эти имена вообще бы никто не заметил, если бы их присутствие не мешало выполнять некоторые операции с книгами, как то изменение стиля ссылок с A1 на R1C1, например.

Тем не менее — чём чёрт ни шутит, — проверил и этот способ: в конце концов, раз уж поведение диалога «Присвоить имя» отличается от способностей метода Name.Delete, то почему бы методу Application.ReferenceStyle не превосходить аналогичное свойство в диалоге параметров Excel? Но, увы, чуда не случилось: при вызове этого метода тоже выскакивает окошко «Имя не должно напоминать ссылку» для каждого некорректного имени — а имена остаются как раз все те, что не смог удалить первый цикл, то есть как раз все некорректные.

В общем, это просто какой-то глюк Экселя — проверять корректность имени даже перед удалением.