Прежде чем читать далее необходимо знать что такое функция пользователя(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) и многие другие. Т.е. получить текущее значение этих параметров можно, но вот изменить уже не получится.
Application.OnTime(применяемый для вызова макроса в указанное время) тоже не будет работать при вызове функции с листа.

Не баг, но все же - бяка в каком-то смысле.
В 2010 Excel у объекта Range появился новый метод: DisplayFormat. У него есть такие свойства как Interior(заливка ячейки), Font(шрифт ячейки), Borders(границы) и т.д. В общем с его помощью можно определить цвет заливки или шрифта ячейки, даже если она окрашена при помощи условного форматирования.

Sub GetTrueCellColor()
    MsgBox ActiveCell.DisplayFormat.Interior.Color
End Sub

Стандартно, не применяя DisplayFormat, получить заливку или шрифт ячеек, окрашенных при помощи условного форматирования нельзя без танцев с бубном.
Так вот этот самый DisplayFormat вообще не работает при вызове из UDF листа. Мы просто получим в итоге ошибку #ЗНАЧ!(#Value!):

Function GetTrueCellColor(rcell As Range)
GetTrueCellColor = rcell.DisplayFormat.Interior.Color
End Function

Но здесь нет смысла обижаться - это документированная особенность метода DisplayFormat.

Зато функцией 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 - делитесь в комментариях, соберем коллекцию :)

Loading

Добавить комментарий

This site uses Akismet to reduce spam. Learn how your comment data is processed.