Кто вызвал функцию или процедуру?

Предположим вы написали два макроса - один скрывает строки, другой отображает.

'скрываем строки
Sub HideRows()
    Range("A3:A14").EntireRow.Hidden = True
End Sub
'показываем строки
Sub UnhideRows()
    Range("A3:A14").EntireRow.Hidden = False
End Sub

И, конечно - создали кнопки для вызова этих двух кодов (подробнее про создание кнопок - Как создать кнопку для вызова макроса на листе). Но потом захотелось большего - чтобы была всего одна кнопка и первым нажатием строки скрывались, а вторым отображались. Сделать это не проблема, если применить такой финт:

Sub HideUnhideRows()
    Range("A3:A14").EntireRow.Hidden = Not Range("A3:A14").EntireRow.Hidden
End Sub

Но если код делается для пользователей, то лучше как-то дать понять им, в каком состоянии сейчас строки - скрыты или отображены и к какому действию приведет нажатие на кнопку. И лучше всего это сделать надписью на самой кнопке. Если кнопка одна на одном листе, то проблем быть не должно:

Sub HideUnhideRows()
    If Range("A3:A14").EntireRow.Hidden Then
        Range("A3:A14").EntireRow.Hidden = False
        ActiveSheet.Shapes(1).TextFrame2.TextRange.Text = "Скрыть строки"
    Else
        Range("A3:A14").EntireRow.Hidden = True
        ActiveSheet.Shapes(1).TextFrame2.TextRange.Text = "Показать строки"
    End If
End Sub

Но если в книге несколько листов и на каждом по несколько кнопок, то не очень удобно будет в коде макроса указывать нужную кнопку. Да, можно указать по имени фигуры: ActiveSheet.Shapes("Скругленный прямоугольник 1"). Но опять же - если кнопок много придется давать той единственной свое уникальное имя, совпадающее на всех листах. Но можно сделать проще - использовать свойство Caller:

Sub HideUnhideRows()
    Dim sShName As String
    sShName = Application.Caller
    If ActiveSheet.Shapes(sShName).TextFrame2.TextRange.Text = "Показать строки" Then
        Range("A3:A14").EntireRow.Hidden = False
        ActiveSheet.Shapes(sShName).TextFrame2.TextRange.Text = "Скрыть строки"
    Else
        Range("A3:A14").EntireRow.Hidden = True
        ActiveSheet.Shapes(sShName).TextFrame2.TextRange.Text = "Показать строки"
    End If
End Sub

Скачать пример:

  Автоопределение нажатой кнопки.xls (57,5 KiB, 504 скачиваний)

Function СуммаЯчеекВсехЛистов(Ячейка As Range)
    Dim ws As Worksheet 'объявляем переменную для обращения к листам в цикле
    Dim dblSum As Double 'переменная для хранения суммы
    'цикл по листам книги
    For Each ws In ActiveWorkbook.Worksheets
        If Not ws Is ActiveSheet Then 'исключаем активный лист из суммирования
            dblSum = dblSum + ws.Range(Ячейка.Address).Value
        End If
    Next ws
    'присваиваем значение суммы функции
    СуммаЯчеекВсехЛистов = dblSum
End Function

Но это очень неправильно. Во-первых, цикл идет по листам активной книги. А это значит, что если с этой книги перейти в другую - то функция будет вычислять сумму на листах именно этой книги, а не той, в которой записана функция. Во-вторых, строка If Not ws Is ActiveSheet Then исключает из суммирования лист активной книги, а не той книги, в которой записана функция. Это может привести к ошибочным расчетам, что весьма критично, если на расчеты функции опираются функции других книг и листов. Поэтому надо определять не активную книгу, а именно ту, в которой функция. Здесь опять поможет свойство Caller:

Function СуммаЯчеекВсехЛистов(Ячейка As Range)
    Dim ws As Worksheet     'объявляем переменную для обращения к листам в цикле
    Dim dblSum As Double    'переменная для хранения суммы
    Dim rFuncCell As Range  'переменная для хранения ссылки на ячейку с функцией
    Dim wsFunc As Worksheet 'переменная для хранения ссылки на лист с функцией
    Dim wbFunc As Workbook  'переменная для хранения ссылки на книгу с функцией
 
    Set rFuncCell = Application.Caller 'ячейка с функцией
    Set wsFunc = rFuncCell.Parent      'лист с функцией
    Set wbFunc = wsFunc.Parent         'книга с функцией
    'для листа и книги можно записать одной строкой:
    'Set wsFunc = Application.Caller.Parent        'лист с функцией
    'Set wbFunc = Application.Caller.Parent.Parent 'книга с функцией
 
    'цикл по листам книги с функцией
    For Each ws In wbFunc.Worksheets
        If Not ws Is wsFunc Then 'исключаем лист с функцией из суммирования
            dblSum = dblSum + ws.Range(Ячейка.Address).Value
        End If
    Next ws
    'присваиваем значение суммы функции
    СуммаЯчеекВсехЛистов = dblSum
End Function

А теперь попробуем разобраться, что же за зверь такой, этот Caller.
Caller - свойство объекта Application, которое возвращает информацию о том, как(чем) был вызван код. Есть несколько вариантов вызова и в зависимости от них значение, возвращаемое Caller меняется:

• Если вызов был из функции пользователя - Caller вернет объект Range, представляющий ссылку на ячейку, в которой записана функция пользователя. Если это функция введена как формула массива - то Caller вернет ссылку на все ячейки, в которые записана функция. Соответственно это дает возможность определить и лист с функцией(Application.Caller.Parent) и книгу(Application.Caller.Parent.Parent)
• Если вызов был кнопкой/фигурой на листе - Caller вернет текст, содержащий локальное имя объекта Shape, к которому привязан вызов процедуры. Что значит локальное имя объекта Shape(это очень важно понимать для правильной работы с Application.Caller): если в русской локализации офиса создать скругленный прямоугольник на листе, то он будет отображаться в окне адреса как "Скругленный прямоугольник 1" - это локальное имя, которое и вернет Application.Caller. Однако внутри кодов VBA коллекция Shapes воспринимает только внутренне имя - "Rounded Rectangle 1". И на попытку обратиться к "Скругленный прямоугольник 1" выдаст сообщение, что компонент не найден. Поэтому, при работе с фигурами и Application.Caller лучше сразу переименовать вручную созданную фигуру на любое имя(лучше только англ.буквами, т.е. латиницей). Сразу после переименования Application.Caller и Shapes будут работать с этим новым именем фигуры.
  Отдельно хочу отметить, что это не распространяется на элементы форм(Разработчик -Вставить -Элементы управления формы). Элементы управления формы сразу имеют одинаковое имя и на листе и внутри VBA.
• Если вызов был из событийной процедуры (Workbook_Open и им подобные), либо процедура была вызвана через Alt+F8, либо через отложенные процедуры приложения(типа Application.OnTime) - Caller вернет ошибку REF
• Если процедура вызвана с панели(Ribbon или настраиваемая панель) - Caller будет иметь тип Variant(), но не сможет определить как именно был вызван код и при попытке обращения к нему получим так же ошибку REF
• Если вызов был через процедуры автоматизации (Auto_Open, Auto_Close, Auto_Activate, Auto_Deactivate) - Caller вернет тип String и содержит имя книги и активного листаэто устаревшие процедуры, которые сейчас заменены событийными в классах книг и листов, но тем не менее их можно встретить в некоторых кодах

Т.е. по факту Caller может пригодится исключительно в случаях, когда код вызывался через нажатие кнопки или необходимо определить адрес ячейки с вычисляемой функцией пользователя(UDF). Во всех остальных случаях Caller по сути бесполезен и определить им любой вызывающий объект/процедуру нельзя. Ниже приведены различные ситуации вызова кодов:

'пробуем вызвать из другой процедуры напрямую - получим ошибку
'   т.к. VBA не может определить вызывающий объект
Sub TestCaller_CallFromProc()
    Call IsCaller
End Sub
'пробуем вызвать через OnTime - получим ошибку
'   т.к. VBA не может определить вызывающий объект
Sub TestCaller_CallByAppOnTime()
    Application.OnTime Now, ThisWorkbook.Name & "!IsCaller"
End Sub
'пробуем вызвать через другую процедуру, которая запущена нажатием кнопки
'предварительно надо создать на листе любую фигуру и назначить ей этот макрос:
'   правая кнопка на фигуре -Назначить макрос -TestCaller_CallByShape
Sub TestCaller_CallByShape()
    Call IsCaller
'   однако, если вызвать из этой процедуры другую через Application.OnTime - получим ошибку
    Application.OnTime Now, ThisWorkbook.Name & "!IsCaller"
End Sub
'если назначить эту процедуру напрямую для вызова нажатием на фигуру - MsgBox покажет имя фигуры
Sub IsCaller()
    Dim v
    On Error Resume Next
    v = Application.Caller
 
    Select Case True
    Case IsError(v)
        MsgBox "Ошибка определения вызова", vbInformation, "www.excel-vba.ru"
    Case Else
        MsgBox v, vbInformation, "www.excel-vba.ru"
    End Select
End Sub

Процедуры TestCaller_CallFromProc, TestCaller_CallByAppOnTime и TestCaller_CallByShape(с примененным OnTime) при вызове IsCaller и последующем обращении из неё к Application.Caller вернут ошибку. И только если создать фигуру(или кнопку) на листе и напрямую назначить ей вызов процедуры IsCaller - Application.Caller определит эту фигуру и в сообщении будет выведено имя этой фигуры.

  Caller test.xls (56,0 KiB, 559 скачиваний)

'возвращает адрес активной ячейки активного листа, даже если активная ячейка в другой книге
Function GetActCell()
    Application.Volatile True
    GetActCell = "'GetActCell' return address: " & ActiveCell.Address(0, 0, xlA1, True)
End Function
'возвращает всегда адрес той ячейки, в которой записана
Function GetActCell_Caller()
    Application.Volatile True
    GetActCell_Caller = "'GetActCell_Caller' return address: " & Application.Caller.Address(0, 0, xlA1, True)
End Function

Чтобы во всей мере понять смысл объекта Caller поизменяйте значения любых ячеек на листе с функциями. Потом перейдите на любой другой лист этой же книги - измените там какую-нибудь ячейку. Вернитесь на лист с функциями и посмотрите, что там отображается. Так же можно перейти в другую книгу и изменить что-то там. Вернуться и посмотреть результат. В ячейке с GetActCell будет отображаться адрес той ячейки, которая была изменена последней. В ячейке же с GetActCell_Caller всегда будет адрес именно той ячейки, в которой записана сама функция GetActCell_Caller.

Хотел бы так же отметить, что для определения ячейки с функцией можно использовать объект Application.ThisCell, который возвращает ссылку на ячейку, из которой была вызвана функция. Этот объект внутри функций пользователя можно применять точно так же, как и Caller. Но он не может быть применен для определения других методов вызова функций и процедур, как Caller.

    Видеоуроки

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