Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Глюк работы в UDF методов SpecialCells и FindNext

Прежде чем читать далее необходимо знать что такое функция пользователя(UDF) и как её создать. Узнать про это можно из статьи: Что такое функция пользователя(UDF)?

Если кратко, то UDF это Ваша собственная функция для вызова её с листа(как и остальные функции Excel). Пишется UDF на встроенном в Excel языке программирования Visual Basic for Applications. UDF способны дополнить и расширить и без того немалый перечень встроенных функций Excel, но есть у UDF и ограничения. Например, они не могут изменять значения других ячеек, форматы ячеек(с некоторыми недокументированными отступлениями), а так же выделять ячейки(методами Select, Application.GoTo и т.п.). Если с изменением значений и форматов ячеек и выделением все более-менее понятно, то некоторые ограничения кажутся больше невменяемыми, чем интуитивно понятными. О них и пойдет речь в статье.
И для детального разбора мы возьмем указанные в заголовке методы, как наиболее часто используемые и многим понятные.


SpecialCells
Для определения последней заполненной ячейки на листе часто используется метод SpecialCells(читать подробнее про определение последней строки). Но он может быть использован и для определения только тех ячеек, которые содержат примечания, проверки данных, только пустые ячейки или только видимые и т.д. И это предоставляет разработчику VBA очень неплохой инструмент для быстрого отбора нужных ячеек. Но у этого метода есть свои недостатки. Например, он не работает на защищенных листах(если конечно, мы не применили трюк с защитой только от пользователя, но не от макроса). Хотя в случае защищенного листа VBA честно скажет сообщением об ошибке в момент выполнения. Однако, при использовании метода SpecialCells именно из UDF - VBA не выдаст никакой ошибки, а вернет результат. Правда, не тот, который ожидался. Возьмем код ниже:

Function UDF_SpecCells_LastCell()
    Dim rr As Range
    Set rr = Cells.SpecialCells(xlCellTypeLastCell)
    UDF_SpecCells_LastCell = rr.Address
End Function

Если выполнить эту функцию напрямую из VBA, то UDF_SpecCells_LastCell вернет корректный адрес одной конкретной ячейки - последней(путь это будет $X$34). Но если выполнить эту функцию, записав в любую ячейку листа =UDF_SpecCells_LastCell(), то функция вернет адрес ячеек всего листа - $1:$1048576. При этом даже защита листа в этом случае не будет помехой. Все потому, что сам метод SpecialCells по факту даже не выполняется, а просто игнорируется и итогом будет адрес родительского объекта - Cells.
Как же из UDF получить адрес последней ячейки?
Адрес последней ячейки можно узнать и другим способом, который точно не даст осечек - можно использовать объект UsedRange:

Function UDF_LastCell()
    Dim lr As Long, lc As Long
    With Application.Caller.Parent 'обращаемся к листу, с которого вызвана функция
        'номер последней строки
        lr = .UsedRange.Row + .UsedRange.Rows.Count - 1
        'номер последнего столбца
        lc = .UsedRange.Column + .UsedRange.Columns.Count - 1
    End With
    'собираем из номера строки и столбца адрес
    UDF_LastCell = Cells(lr, lc).Address
End Function

Однако, чтобы заменить другие возможности метода SpecialCells для работы в UDF, придется подойти индивидуально к каждой задаче. Например, для получения диапазона ячеек с примечаниями, можно использовать такой код:

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: UDF_GetCommentCells
'          Функция возвращает адрес ячеек, содержащих комментарии
'          rr - необязательный. Ссылка на диапазон, в котором надо найти примечания
'               если не указан - берутся все ячейки листа
'---------------------------------------------------------------------------------------
Function UDF_GetCommentCells(Optional rr As Range)
    Dim rAll As Range, rc As Range, rCmnts As Range
    If rr Is Nothing Then
        Set rAll = Application.Caller.Parent.UsedRange
    Else
        Set rAll = rr
    End If
    For Each rc In rAll
        'если в ячейке есть примечание
        If Not rc.Comment Is Nothing Then
            'собираем все ячейки в один диапазон
            If rCmnts Is Nothing Then
                Set rCmnts = rc
            Else
                Set rCmnts = Union(rCmnts, rc)
            End If
        End If
    Next
    UDF_GetCommentCells = rCmnts.Address
End Function

Собственно, такой подход можно применять для поиска и других типов ячеек. Для только видимых надо будет применять проверку каждой на Rows.Hidden и Columns.Hidden, для получения только ячеек с формулами - hasFormula. Для получения ошибочных - IsError(Cell) и т.д. Но в любом случае это будет в разы медленнее, чем SpecialCells.


FindNExt
Еще один неплохой метод, с помощью которого можно искать адреса всех ячеек с определенным значением. Если зайти в справку по методу Find(он используется для поиска ячейки с определенным значением на листе), то там можно найти код поиска всех ячеек. Я его чуть модернизировал под работу внутри функции:

Function FindAllValues()
    Dim s As String, firstAddress As String, c As Range
    With Range("E:E")
        Set c = .Find(2, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                s = s & c.Address & "; "
                Set c = .FindNext(c)
                If Not c Is Nothing Then
                    If firstAddress = c.Address Then
                        Exit Do
                    End If
                End If
            Loop While Not c Is Nothing
        End If
    End With
    FindAllValues = s
End Function

Чтобы проверить работу этой функции запишите в столбец E(начиная с ячейки E1) ряд значений(в каждую ячейку по одной цифре): 1, 2, 3, 2, 5, 4, 7.
И опять - если выполнить эту функцию напрямую из VBA, то FindAllValues вернет адреса всех ячеек с указанным значением(2) - $E$2; $E$4; . Но если выполнить эту функцию, записав в любую ячейку листа =FindAllValues(), то функция вернет адрес только первой найденной ячейки - $E$2; . Проблема в том, что метод FindNext не выполняется и возвращает Nothing.
Так же как и в случае со SpecialCells заменить такой поиск можно только собственными усилиями. Я для примера могу приложить такой вот не оптимальный, но рабочий код:

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: UDF_FindValCells
'          Функция возвращает адрес ячеек, содержащих искомое значение
'          v  - искомое значение
'          rr - необязательный. Ссылка на диапазон, в котором надо найти значения
'               если не указан - берутся все ячейки листа
'---------------------------------------------------------------------------------------
Function UDF_FindValCells(v, Optional rr As Range)
    Dim rAll As Range, rc As Range, rVals As Range
     If rr Is Nothing Then
        Set rAll = Application.Caller.Parent.UsedRange
    Else
        Set rAll = rr
    End If
    For Each rc In rAll
        'если значение в ячейке равно искомому
        If rc.Value = v Then
            'собираем все ячейки в один диапазон
            If rVals Is Nothing Then
                Set rVals = rc
            Else
                Set rVals = Union(rVals, rc)
            End If
        End If
    Next
    UDF_FindValCells = rVals.Address
End Function

Быстрее поиск будет работать на массивах, но это уже другая история.


Еще несколько коварных методов
Так же проблемы возникнут с использованием таких методов как: CurrentRegion(определение всей прилегающей к активной ячейке таблицы. На листе можно вызывать нажатием сочетания клавиш Ctrl+A)), CurrentArray(определение всей области применения формулы массив), ShowPrecedents и ShowDependents(выделение зависимостей ячеек).

  • CurrentRegion и CurrentArray при вызове из UDF всегда будут возвращать адрес активной ячейки
  • ShowPrecedents и ShowDependents при вызове из UDF просто ничего не покажут

Аналогично игнорируются и все так называемые объекты окружения самого Excel. Например, методы изменения способа вычисления формул(Application.Calculation), изменение стиля ссылок(Application.ReferenceStyle), вид курсора(Application.Cursor) и многие другие. Т.е. получить текущее значение этих параметров можно, но вот изменить уже не получится.


Зато функцией UDF можно добавить в ячейку примечание. Функция ниже прекрасно отработает как при вызове непосредственно из VBA, так и при вызове с листа при помощи записи в ячейку функции =UDF_AddNewComment():

Function UDF_AddNewComment()
    Dim rr As Range
    Set rr = ActiveCell 'Application.Caller 'если надо добавить в ячейку с самой UDF
    rr.AddComment ("Привет от excel-vba.ru!")
End Function

Если Вы так же обнаружите методы, которые некорректно работают при вызове из UDF - делитесь в комментариях, соберем коллекцию :)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2019 Excel для всех   Войти