VBA Excel Как получить имя диапазона по любой ячейке из него

[ger_kar]

На листе Excel имеются несколько объединенных ячеек. Каждой такой объединенной ячейке (диапазону ячеек входящих в объединенную) присвоено Имя (на рисунке Имя1, Имя2, Имя3 и т.д.). Колличество ячеек входящих в объединенную может быть разным. Как получить имя объединенной ячейки (диапазона) по любой ячейке которая входит в этот диапазон.
Более конкретный пример: Объединенная ячейка с именем "Имя1" это диапазон из 3-х ячеек "B2" "B3" "B4" или Range ("B2:B4"). В цикле идет перебор ячеек по столбцам. Как при выборе любой из ячеек диапазона (например Range ("B2") или Cells (2,2) получить имя диапазона т.е. "Имя1" ?

Screen.jpg (22.91 Кб) Просмотров: 9174

[ger_kar]

Можно конечно использовать алгоритм перебора всей коллекции имен, для каждого имени создавать свой Range и проверять входит ли проверяемая ячейка в диапазон.
Примерно так:

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

For Each objName In ActiveSheet.Names
      If Not Application.Intersect(Cells(Проверяемая ячейка), objName.RefersToRange) Is Nothing Then
          strName = objName.Name
      End If
Next

Такой способ получения имени вполне работоспособен, но что-то он мне не очень нравиться, так как постоянно заниматься перебором не очень эффективно с точки зрения расхода ресурсов.
Может у кого есть идеи получше?

[dormouse]

как-то так:

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

On Error GoTo err_handler
MsgBox ActiveCell.MergeArea.CurrentRegion.Name.Name
Exit Sub
err_handler:
Select Case Err.Number
    Case 1004: MsgBox "выбранная ячейка не имеет имени"
    Case Else: MsgBox Err.Description, vbCritical, Err.Number
End Select


[alibek]

Я думаю, что MergeArea тут лишний.

[dormouse]

Да.. Но .Name.Name умиляет своей лаконичностью

[ger_kar]

Спору нет! Способ предложенный dormouse очень лаконичный. Создал новую тестовую книгу, протестил - все работает. Сейчас сижу и чешу репу, как я мог пропустить свойство MergeArea? Вроде исследовал все что только было можно и от безысходности (как мне показалось) начал искать обходные пути и "изобретать велосипед". В процессе исследования отдельно заострял внимание на CurrentArray и CurrentRegion. CurrentRegion оказалось почти... , но не совсем ТО!, а CurrentArray и вовсе совсем не то . А MergeArea почему-то ускользнуло от моего внимания. Спасибо dormouse за снятие пелены, которая застилала глаза .
Зато по ходу исследования проблемы выяснил некоторые интересные детали: Одному и тому-же диапазону можно присвоить несколько имен (я пробовал три имени) и при выборе диапазона по имени можно использовать любое из них. Если получать имя через MergeArea, то этот способ возвращает только первое имя (первое присвоенное - по хронологии). Сам Excel почему то в соответствующем окне показывает второе присвоенное имя. А если воспользоваться моим способом перебора, то можно получить все три имени + имя другого диапазона в случае пересечения + еще имя печатаемой области

[ger_kar]

Однако засада! Рано я обрадовался. Перенес способ в свой проект, но он там не работает! Ошибка 1004 и все тут! Теперь я начинаю припоминать, что и MergeArea пробовал, но так как получал ошибку, то от его использования отказался, как и от CurrentRegion (таже ошибка). Однако странно все это, потому, что еще раз все проверил, но причин для возникновения ошибки в своем проекте так и не нашел, но ошибка есть! Попытался смоделировать ошибку на другой книге - присваивал одной области несколько имен, делал пересекаемые именованные области, делал одну область которая включает в себя другую - все работает без сучка и задоринки. А в реальном проекте не хочет и все тут.

[ger_kar]

Может у меня уже бред, нужен свежий взгляд .
Вот тестовая копия. Я расставил стопы и комментарии в нужном месте, чтобы сразу найти искомое. Для запуска теста нужно кликнуть на "ИДЕНТИФИКАЦИОННЫЙ НОМЕР НАЛОГОПЛАТЕЛЬЩИКА".

[dormouse]

Если конкретно ту строчку, где появляется ошибка, исправить на Debug.Print Cells(lngRow, lngColumn).Name.Name, то выдаёт результат Новый!ИНН1
Может быть, потому, что обращение к свойству ячейку происходит из другой ячейки. Т.е. не ActiveCell, а Cell(x, y)
Ну так доступные свойства выясняются в окне Watch или Locals. Я вон даже скриншот показал, как это сделать Вообще, поиски нужных свойств или параметров в VBA (и тем более в прогах микрософта) - это нетривиальная задача. Останавливаешь так процедуру на отладке и копаааешься.. Вроде и имена свойств знаешь и примерный путь к ним как-то представляешь себе. Ан нет - надо выискивать именно тот путь, который возвращает нужное значение. Чем тут и занимаются все, кроме тех, кто задаёт на форуме подобные вопросы

[ger_kar]

Может быть, потому, что обращение к свойству ячейку происходит из другой ячейки. Т.е. не ActiveCell, а Cell(x, y)

Так в том то и весь прикол, что тестируя MergeArea в отдельной книге я использовал Cells(x, y), а не ActiveCell, так как ActiveCell это выделенная ячейка, и тестируя я это учитывал, и в то время как я получал имя объединенной ячейки, активная ячейка была совершенно в другом месте .

Ну так доступные свойства выясняются в окне Watch или Locals. Я вон даже скриншот показал, как это сделать

Ну так про это я тоже знаю, и применяю на практике , как же без них. Но после того как я протестировал пример в отдельной книге, и перенес его в "рабочую обстановку" получив глюк, я зациклился на этом. Посмотреть другие свойства я уже и не помышлял . Но свежий взгляд и не менее свежий ответ разорвал этот вечный цикл . Меня кстати так и мучает этот вопрос, почему в одном месте работает, а в другом глюк. Надеюсь что это когда-нибудь разрешиться.

[dormouse]

Меня кстати так и мучает этот вопрос, почему в одном месте работает, а в другом глюк.

Это не глюк. В VBA классы порождают новые классы, передают в них свои свойства или эти свойства вдруг приобретаются.. И в зависимости от контекста они то доступны, то нет. Но в онке Watch они почти всегда упоминаются
Получается, что для разных ситуаций надо предварительно выяснять, является ли ячейка объединённой, текущая она или удалённая, есть ли у неё имя.. Может быть, надо записать все варианты обращения к этому Name и игнорируя ошибку добиваться того, чтоб всё таки получить его значение

[ger_kar]

Ну для теста, я смоделировал ситуацию аналогичную проектной. Через Cells выбирал координаты верхней левой ячейки входящей в объединенную, которая имеет имя. Cells возвращает екземпляр Range у которого есть свойство MergeArea, которое в свою очередь возвращает Range всей объединенной ячейки, которая в свою очередь имеет свойством объект Name из которого получаем уже собственно имя все логично и понятно и все работает! Переносим эту ситуацию в рабочий проект. Также выбираем, тем-же способом верхнюю левую ячейку из объединенной, которая гарантированно имеет имя, также получаем Range этой ячейки, дальше ... В общем при попытке получения свойства MergeArea получаем ошибку 1004, которая как правило бывает при попытке получить имя области, у которой его нет. Но в моем случае имя то есть! И где же логика?
Далее усложняю ситуацию, в тестовом проекте делаю вложенные области, вложенные с пересечением, одной области присваиваю несколько имен. Вобщем делаю этакую мешанину, тестирую - все работает четко! Но в рабочем проекте ни в какую. Поэтому, кроме как глюком это назвать трудно.

[Sam777e]

Добрый день !

Такой способ получения имени вполне работоспособен, но что-то он мне не очень нравиться

Если Вам нужна скорость, то, как часто бывает, меняем память [ пространство ] на время [ ; или, выражаясь высокопарно - устраиваем предварительную компиляцию ] и становится возможным другой алгоритм, более быстрый.

1. Добавляем в Книгу ещё один Лист, СКРЫТЫЙ;
судя по Вашему файлу [ : Visible : xlSheetVeryHidden : XlSheetVisibility ], Вы знакомы с этим.

2. На этом Листе в нужные ячейки вносим Имя диапазона.

3. И простой алгоритм :
ИмяДиапазона := ЗначениеСоответствующейЯчейки_в_СкрытомЛисте

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


Private Function CellDiapazonName(lngRow As Long, lngColumn As Long) As String
  With Worksheets("HiddenSheet")
    If IsEmpty(.Cells(lngRow, lngColumn)) Then
      CellDiapazonName = ""
    Else
        ' >>> ПРОПУЩЕНО: Проверить, что там String !! . . .
      CellDiapazonName = CStr(.Cells(lngRow, lngColumn))
    End If
  End With
End Function


Public Sub Test()
  MsgBox CellDiapazonName(ActiveCell.Row, ActiveCell.Column)
End Sub



[ger_kar]

Такой способ получения имени вполне работоспособен, но что-то он мне не очень нравиться

Классый форум . Надо в девиз форума "Весь вкус программирования" добавить + Бонус "Улучшим ваш Русский"

[K0GG]

В Тестовая копия.rar
дублируется имена диапазонов, имеющие разную область видимости, либо лист, либо книга.
Проблема может быть вызвана этим.