Vba excel функции и процедуры: 2.5.3 VBA. Процедуры и функции

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

'скрываем строки
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, 496 скачиваний)

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

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, 552 скачиваний)

Там записаны две функции — одна с использованием ссылки на активную ячейку, другая с применением Caller:

'возвращает адрес активной ячейки активного листа, даже если активная ячейка в другой книге
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.

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

    Видеоуроки

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

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

Как создать и вызвать пользовательскую подпрограмму или процедуру VBA Excel?

Приложения

0 927 3 минут на чтение

Несмотря на то, что многим это кажется немного сложным. Создание под – программа или процедура, настроенная VBA это очень простой и динамичный способ создать функцию в электронной таблице Excel. Эти функции этой программы основаны на  создание макросов 

Визуальные функции Базовый позволяет вам создавать исполнения, которые одна и та же программа считает невозможными в своих собственных функциях. Visual Basic позволяет создавать собственные функции.

Поскольку это язык, Microsoft Офис содержит для выполнения определенных задач он устанавливает ряд сроков, необходимых для создания процедуры.

Если вас беспокоит то, что вы не можете понять методы, между которыми вы можете код через из Макрос Excel. Здесь вы найдете самое простое объяснение.

Что такое процедура и как ее создать?

То, что определяется в Excel как процедура, есть не что иное, как наименьшая единица кода, выполняемая в Visual Basic.

Их легко распознать, потому что они всегда находятся в начале и в конце всех макрокодировок, которые выполняются в программе для выполнения задачи.

Типы производств классифицируются как » Sous «,» функция «И» свойство «В начале и закрываются сроком» конец » перед ними.

Вы можете создать собственную подпрограмму или процедуру, используя » лист «И» Модули . Использование объектов, закодированных с помощью » Эта рабочая тетрадь «Или» Использование форм .

Создайте модуль в редакторе Visual Basic для создания процедуры или подпрограммы.

Если вы не знаете, как добавить модуль в проекты в VBA, вам просто нужно войти на вкладку » разработчик »И вы увидите кнопка » Визуальный Бейсик ».

Перед вашим экраном откроется новое окно, которое считается редактором, в меню которого есть значок » вставить »Где вы найдете эту опцию» Модули ».

Автоматически в небольшом вертикальном поле слева вы увидите, как создается папка, в которой появится только что созданный вами новый модуль.

Таким образом, вам просто нужно щелкнуть поле справа, чтобы начать писать на языке Visual Basic, чтобы начать создание задачи, которую вы хотите использовать.

Узнайте, как создать настраиваемую подпрограмму или процедуру с помощью Visual Basic

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

А пока вы начнете с самого маленького, которое состоит из определить начало и основные элементы задачи, которую вы хотите выполнить.

Создать подпроцедуру

В VBA есть два метода определения начала процедуры. Либо вручную в окне, либо выполнив серию щелчков мышью через меню » Вносимые ».

Чтобы вставить процедуру вручную, необходимо перейти в окно созданного вами модуля, где вы напишете слово » ниже «Затем вы должны написать имя макроса что вы собираетесь исполнить.

Нажмите кнопку » запись Чтобы Visual Basic автоматически добавлял несколько скобок и ниже инструкция » End Sub ».

Между двумя строками вам нужно написать код задачи, которую вы хотите запустить, а после ее завершения сохранить и запустить.

Создать процедуру функции

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

Поэтому начните с создания » Новый модуль «И в отображаемом справа окне кода напишите» функция »За ним следует имя, которое вы собираетесь дать функции.

Когда вы нажимаете » запись «, VBA добавит круглые скобки и инструкцию для завершения вызываемой функции» End Function .

Все, что тебе нужно сделать, это напишите код между двумя строками задачи, которую вы хотите выполнить, на листе Excel, сохраните ее и запустите функцию.

Если вы планируете работать с Visual Basic, также важно знать, как управлять erreurs другой что Visual Basic может вам представить.

Расширьте свои знания об использовании VBA с помощью использование и создание цикла FOR-NEXT.

Подобные предметы

Создать процедуру (VBA) | Microsoft Узнайте

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронное письмо

• Статья
• 13.09.2021
• 2 минуты на чтение

Код внутри модуля организован в виде процедур. Процедура сообщает приложению, как выполнить конкретную задачу. Используйте процедуры, чтобы разделить сложные задачи кода на более управляемые единицы.

Чтобы создать процедуру, напишите код

1. Откройте модуль, для которого вы хотите написать процедуру.

2. Вы можете создать процедуру Sub , Function или Property .

3. Тип Sub , Функция или Свойство . Нажмите F1, чтобы получить справку по синтаксису, если это необходимо.

4. Введите код процедуры. Visual Basic завершает процедуру соответствующим End Sub , End Function или End Property оператор.

Чтобы создать процедуру с помощью диалогового окна «Вставить процедуру»

1. Откройте модуль, для которого вы хотите написать процедуру.

2. В меню Вставить выберите Процедура .

3. Введите имя процедуры в поле Имя диалогового окна Вставить процедуру .

4. Выберите тип создаваемой процедуры: Sub , Function или Property .

5. Установите для процедуры значение Public или Private.

6. Можно выбрать Все локальные переменные как статические , чтобы добавить ключевое слово Static в определение процедуры.

7. Нажмите OK .

• Практические руководства по Visual Basic

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы Office VBA, чтобы узнать, как вы можете получить поддержку и оставить отзыв.

Функция Excel VBA против VBA Sub

Написано Томом (AnalystCave) 29 февраля 2016 г. в Excel, MS Office, Outlook, PowerPoint, Word

A Функция VBA может принимать параметры и возвращать результаты. Однако функции не могут выполняться напрямую. С другой стороны, процедура VBA Sub может выполняться напрямую и также может принимать параметры. Однако процедуры не возвращают значений.

Мы часто используем подпрограммы и функции, часто не задумываясь об их истинном потенциале и о том, сколько мы можем выжать из обычной функции VBA. Давайте начнем с напоминания, чем они отличаются друг от друга, а затем углубимся в детали.

Подпрограмма VBA, синтаксис функции VBA

Структура ниже напоминает подпрограмму VBA и функцию VBA:

«Это Подпрограмма (процедура). Подпрограммы могут принимать только аргументы, они не возвращают результаты
Sub SomeSub(...)
  ...
Конец сабвуфера

'Это простая функция
Function SomeFunc(...) as ... 'Функции могут принимать аргументы и возвращать результаты
 ...
Конечная функция
 

Ниже приведены простые примеры сабвуферов и функций:

'Пример Sub
Sub SayHello (имя в виде строки)
  Debug.Print «Привет» и имя
Конец сабвуфера

'Пример функции
Функция Add2Numbers(num1 as Long, num2 as Long) as Long
  Add2Numbers = число1 + число2
Конечная функция

'Ниже подтестирует функцию Add2Numbers
Sub TestAdd2Numbers()
  Отладка.  Печать Add2Numbers (1,2)
Конец сабвуфера
'Результат: 3
 

Синтаксис подпроцедуры VBA

Блок кода подпроцедуры VBA помечен операторами Sub и End Sub .

Синтаксис процедуры функции VBA

Блок кода функции VBA отмечен операторами Function и End Function .

Функция VBA против VBA Sub

Мы часто склонны смешивать процедуры, Subs и Functions в VBA. Итак, давайте поправимся на этот раз. Между процедурами VBA (подпрограммами) и функциями VBA есть 2 основных различия:

• Функции VBA возвращают значения , VBA Subs не возвращают
• Вы можете выполнять VBA Sub, y Вы не можете выполнять функции VBA — они могут выполняться только VBA Subs

Выполнение функций и подпрограмм

Хотя я привел примеры выше, существует несколько способов выполнить функцию VBA и подпрограмму VBA:

'Пример процедуры VBA
Sub TestSub (аргумент как длинный)
. .
Конец сабвуфера

'Пример функции VBA
Функция TestFunction (arg as Long) как строка
...
Конечная функция


'КАК ВЫПОЛНЯТЬ ФУНКЦИИ И ПРОЦЕДУРЫ
Подтест()
  Тусклый результат

  '---ЗАПУСТИТЬ ФУНКЦИЮ---
  
 'Пример 1: Запустите Sub со скобками с оператором Call
  Позвонить в TestSub (10)

  'Пример 2: Запустить Sub без скобок и без оператора Call
  TestSub 10

  '---ЗАПУСТИТЬ ФУНКЦИЮ---
  'Пример: функции предназначены для возврата значений, поэтому их необходимо использовать со скобками.
  результат = Тестовая функция (1)
Конец сабвуфера
 

Передача аргументов ByVal и ByRef

Общеизвестно, что только функции VBA могут возвращать значения. Это действительно в основном правда. Однако вместо значения (результата Функции) мы можем присваивать значения переменным, которые можно передать в процедуру по ссылке. Что это обозначает?

Переменные могут быть переданы в процедуру (функция, подпрограмма и т. д.) по их значению или ссылке.
Передача по значению приводит к созданию копии переменной. Таким образом, любые изменения в копии не будут отражены в исходной переменной.
: Передача по ссылке — это передача адреса переменной в процедуру. Это означает, что любые изменения аргумента будут отражены в исходной переменной.

По умолчанию все аргументы передаются процедурам ByVal, так что никакие изменения не могут быть внесены в исходные переменные

Примеры ByVal и ByRef

Теперь рассмотрим несколько примеров:

Sub SetValueByVal (ByVal, как долго)
    какое-то длинное = 10
Конец сабвуфера
Sub SetValueByRef (ByRef someLong As Long)
    какое-то длинное = 10
Конец сабвуфера

Подтест()
    Dim someLong As Long
    какое-то длинное = 1
    
    SetValueByVal someLong
    Debug.Print someLong 'Результат: 1 (без изменений)
    
    SetValueByRef someLong
    Debug.Print someLong 'Результат: 10
Конец сабвуфера
 

При передаче по значению переменная someLong не модифицируется. Однако, когда мы передаем его по ссылке, его значение изменяется внутри процедуры Sub.

Объекты и массивы всегда передаются по ссылке , потому что объекты на самом деле являются ссылками (например, коллекция). Остерегайтесь в таких случаях не изменять объекты, на которые ссылаются, по ошибке!

Передача массивов в подпрограммы и функции

Вы также можете легко передавать массивы в подпрограммы или функции, даже переопределяя их длину. См. пример ниже:

Sub ChangeLength(arr() As Long)
    ReDim arr(5) As Long
Конец сабвуфера

Подтест()
    Dim arr() As Long
    ReDim arr(2) As Long
    Debug.Print UBound(arr) 'Результат: 2
    ИзменитьДлина обр.
    Debug.Print UBound(arr) 'Результат: 5
Конец сабвуфера
 

Дополнительные параметры

Функции и подпрограммы VBA допускают необязательные параметры, которые не нужно указывать при выполнении функции или подпрограммы. Рекомендуется указывать для таких параметров значение по умолчанию. См. пример ниже:

Sub SayHi (имя в виде строки, необязательная фамилия в виде строки = vbNullString)
    Debug. Print "Привет," & имя & IIf (фамилия = vbNullString, "", " " & фамилия)
Конец сабвуфера

Подтест()
    Call SayHi("John") 'Результат: "Привет, Джон"
    
    Call SayHi("John", "Smith") 'Результат: "Привет, Джон Смит"
Конец сабвуфера
 

Вы можете проверить, не был ли параметр передан подпрограмме или функции, используя функцию IsMissing. Однако функция IsMissing работает только для параметров типа Variant. Смотрите же SayHi описанная выше процедура, на этот раз с функцией IsMissing.

Sub SayHi (имя в виде строки, необязательная фамилия в виде варианта)
    Debug.Print "Привет," & имя & IIf(IsMissing(фамилия), "", " " & CStr(фамилия))
Конец сабвуфера

Подтест()
    Call SayHi("John") 'Результат: "Привет, Джон"
    
    Call SayHi("John", "Smith") 'Результат: "Привет, Джон Смит"
Конец сабвуфера
 

Динамический список параметров

Допустим, вы хотите создать функцию VBA, такую ​​как формулы Excel SUM или AVERAGE, которая может быть снабжена динамическим списком параметров.