Функции или подпрограммы Excel VBA в массиве. Vba excel функции и процедуры

Работа с процедурами VBA

Процедура — это последовательность операторов VBA, расположенная в модуле VBA, доступ к которому можно получить с помощью VBE. Модуль может включать любое количество процедур.[1] Некоторые процедуры получают аргументы. Аргумент — это информация, используемая процедурой в процессе выполнения. Аргументы процедуры во многом подобны аргументам, используемым функциями Excel.

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

[Private | Public][Static] Sub имя([список_аргументов]) [инструкции] [Exit Sub] [инструкции]End Sub

Рис. 1. Запуск процедуры из Visual Basic Editor

Скачать заметку в формате Word или pdf

Private (необязательное ключевое слово). Указывает на то, что процедура доступна только для других процедур в том же модуле.

Public (необязательное ключевое слово). Указывает на то, что процедура доступна для всех остальных процедур во всех модулях рабочей книги. При использовании в модуле, содержащем оператор Option Private Module, процедура будет недоступна за пределами проекта.

Static (необязательное ключевое слово). Указывает на то, что переменные процедуры сохраняются после окончания процедуры.

Sub (обязательное ключевое слово). Обозначает начало процедуры.

Имя. Любое корректное название процедуры.

Список_аргументов. Представляет заключенный в скобки список переменных, содержащих аргументы, которые передаются в процедуру. Для разделения аргументов используется запятая. Если процедура не использует аргументы, то необходимо включить в объявление процедуры пустые скобки.

Инструкции (необязательные). Корректные инструкции VBA.

Exit Sub (необязательный оператор). Вызывает немедленный выход из процедуры до ее формального завершения.

End Sub (обязательный оператор). Указывает на завершение процедуры.

Выполнение процедуры

Основные способы выполнения, или вызова, процедуры VBA.

1-й способ. С помощью команды Run–>Run Sub/UserForm (Выполнить –> Выполнить процедуру/ пользовательскую форму, рис. 1) в VBE. Альтернатива — нажать <F5> либо воспользоваться кнопкой Run Sub/UserForm панели инструментов Standard (Стандартная, рис. 2).

Рис. 2. Кнопка Run Sub/UserForm на панели Standard VBE

2-й способ. Из диалогового окна Макрос в Excel (рис. 3). Чтобы вызвать окно пройдите по меню Разработчик –> Макрос или нажмите Alt+F8.

Рис. 3. Диалоговое окно Макрос в Excel

3-й способ. С помощью комбинации клавиши <Ctrl> и присвоенной процедуре клавиши (если процедуре присвоена комбинация клавиш). Если в момент создания процедуры ей не была присвоена клавиша, сделать это никогда не поздно. Откройте окно Макрос, как описано выше, выделите процедуру в окне Имя макроса, кликните Параметры, и введите букву в окне Сочетание клавиш (рис. 4).

Рис. 4. Присвоение процедуре комбинации клавиш

4-й способ. Щелкнув на кнопке или любой фигуре рабочего листа. Для этого кнопке или фигуре должна быть присвоена процедура (рис. 5).

Рис. 5. Назначение макроса фигуре

5-й способ. Из другой процедуры. Процедуры Sub и Function могут вызывать другие процедуры.

6-й способ. С помощью пользовательского элемента управления, находящегося на ленте. Кроме того, встроенные элементы управления ленты могут быть «перенастроены» для вызова макроса на выполнение.

7-й способ. Из пользовательского контекстного меню.

8-й способ. После выполнения определенного события. Такими событиями могут выступать открытие рабочей книги, сохранение рабочей книги, закрытие рабочей книги, изменение ячейки, переход на другой рабочий лист и многие другие.

9-й способ. Из окна отладки (Immediate) в VBE. Просто введите название процедуры, укажите все необходимые аргументы и нажмите клавишу <Enter>.

Передача аргументов процедурам

Аргументы обеспечивают процедуру данными, использующимися в ее инструкциях. Аргумент может передавать следующие данные: переменная, константа, массив, объект.

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

По ссылке. При передаче аргумента по ссылке (метод, применяемый по умолчанию) процедуре передается адрес ячейки памяти, в которой хранится переменная. Поэтому изменение аргумента в процедуре приводит к изменению исходной переменной.

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

В следующем примере аргумент процедуры Process передается по ссылке (по умолчанию). После того как процедура Main присваивает переменной MyValue значение 10, она вызывает процедуру Process и передает MyValue в качестве аргумента. Процедура Process умножает значение своего аргумента (с названием YourValue) на 10. По окончании процедуры Process возобновляется выполнение процедуры Main, а функция MsgBox отображает строку MyValue: 100.

Sub Main () Dim MyValue As Integer MyValue = 10 Call Process(MyValue) MsgBox MyValueEnd Sub

Sub Process (YourValue) YourValue = YourValue * 10End Sub

Если требуется, чтобы вызываемая процедура не изменяла переменные, полученные как аргументы, измените список аргументов вызываемой процедуры так, чтобы аргументы передавались по значению, а не по ссылке. Для этого добавьте перед аргументом ключевое слово ByVal. Тогда вызываемая процедура будет управлять копией переданных данных, а не самими данными. В следующей процедуре, например, изменения, которые происходят с YourValue в процедуре Process, не влияют на значение переменной MyValue в процедуре Main. В результате функция MsgBox отображает 10, а не 100.

Sub Process(ByVal YourValue) YourValue = YourValue * 10End Sub

Обработка ошибок

Чтобы указать. программе, что должно произойти при возникновении ошибки, используется оператор On Error. Вы вправе выбрать один из двух вариантов.

Проигнорировать ошибку и позволить VBA продолжить выполнение программы. После этого можно проанализировать объект Err, чтобы узнать, какая ошибка произошла, и при необходимости принять меры для ее предотвращения.
Перейти к специальному разделу кода для обработки ошибок, чтобы выполнить необходимые действия. Этот раздел вводится в конце процедуры и обозначается специальной меткой.

Чтобы программа продолжала выполняться после возникновения ошибки, необходимо вставить в начало процедуры оператор On Error Resume Next. При возникновении ошибки можно использовать объект Err для определения ее номера. Например, на рис. 6 представлена процедура, присваивающая Листу2 имя Исходные данные. Однако, в книге может не быть Листа2. В этом случае появится сообщение об ошибке.

Рис. 6. Процедура присвоения имени Листу Excel, обрабатывающая ошибку

Ссылка на Err эквивалентна обращению к свойству Number объекта Err. Следовательно, два приведенных ниже оператора идентичны:

MsgBox ErrMsgBox Err.Number

Оператор On Error также применяется для определения места в процедуре, к которому должна перейти программа в случае ошибки. Чтобы обозначить это место, используется метка.

On Error GoTo ErrorHandler

Следующая процедура выделяет все ячейки в текущем диапазоне, содержащие формулы, возвращающие число. Процедура также использует оператор If для определения результата: произошла ли ошибка. Оператор On Error GoTo 0 восстанавливает нормальную обработку ошибок перед выходом из процедуры.

Sub SelectFormulas2() On Error Resume Next Selection.SpecialCells(xlFormulas, xlNumbers).Select If Err.Number = 1004 Then MsgBox "He найдены ячейки с формулами." On Error GoTo 0 ' …[код]End Sub

Если свойство Number объекта Err не равно 0, происходит ошибка. С помощью оператора If проверяется, не равно ли свойство Err.Number 1004, и, если это так, отображается окно сообщения. В рассмотренном примере осуществляется проверка кода на предмет обнаружения ошибки с указанным номером.

В следующем примере кода демонстрируется обработка ошибок путем перехода по метке.

Sub ErrorDemo() On Error GoTo Handler Selection.Value = 123Exit SubHandler: MsgBox "Невозможно присвоить значение выделенному диапазону."End Sub

В процедуре предпринимается попытка присвоить значение текущему выделенному объекту. Если происходит ошибка (например, не выделен диапазон ячеек или лист защищен), то оператор присваивания выдает ошибку. Оператор On Error задает переход к метке Handler в случае ошибки. Обратите внимание, что перед меткой используется оператор Exit Sub. Программа обработки не выполняется, если ошибок не было.

[1] По материалам книги Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М: Диалектика, 2013. – С. 253–273.

baguzin.ru

Создание и использование функций и функций-процедур VBA

Вопросы: l l l Понятие функции-процедуры VBA Использование функций типа UDF в рабочих листах Excel Управление передачей аргументов (по ссылке и по значению). 2

Функция-процедура – это особый вид процедуры VBA, возвращающей результат. l Функции-процедуры с некоторыми ограничениями на их действия называются определенными пользователем функциями (или сокращенно UDF – user-defined functions), и только их может использовать Excel в формуле ячейки рабочего листа. 3

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

l l Например, нельзя использовать функциюпроцедуру как определенную пользователем функцию в Excel, если она выделяет ячейки или изменяет каким-то образом текущий рабочий лист. Кроме того, UDF не может устанавливать свойства объекта или использовать методы объекта: в большинстве случаев установка свойств объекта или использование его методов приводят к изменениям в среде Excel. 5

Функция пользователя: l l l не может изменять значения других ячеек; не может изменять форматы ячеек либо присваивать форматы; может возвращать результат только в ту ячейку, в которой записана сама функция. 6

Общий синтаксис для процедурыфункции выглядит следующим образом: Function ([]) [As ] [ = ] End Function 7

Где: Function - ключевое слово, объявляющее начало функции; - имя создаваемой функции, придуманное пользователем При написании имен функций необходимо соблюдать те же правила, что и при написании имен других идентификаторов в VBA: они должны начинаться с буквы, не могут содержать пробелов или каких-либо символов арифметических, логических операторов или операторов отношения и не могут дублировать ключевые слова VBA; - список аргументов данной функции, необязательный элемент. 8

– любой тип возвращаемого значения функции. Если только не определяется иначе, результат, который возвращает функция-процедура, имеет тип Variant; - это последовательность команд, выполняемых при нахождении значения функции. В совокупности они образуют тело функции. Необязательный элемент синтаксиса = представляет присваивание функции, которое указывает VBA, какое значение должна возвращать функция; End Function - ключевые слова, заканчивающие функцию. 9

Особенности применения: 1. В списке аргументов, название каждого аргумента отделяется запятой. Список аргументов используется для передачи информации. Правила написания имен аргументов те же, что и для переменных, точно так же можно указывать их тип данных. Имена аргументов имеют такую же область определения, как и переменные, объявленные в функции локально, т. е. переменные-аргументы недоступны за пределами процедуры-функции. Если список аргументов пуст, тем не менее, круглые скобки следует сохранять; 10

Особенности применения: 2. 3. Необязательный оператор = предназначен для возврата значения функции в точку вызова. Сам оператор носит название присвоение результата функции. Если он опущен, то функция не будет возвращать результат в точку вызова. Впрочем, это не совсем так: числовые функции возвращают значение нуль, строковые – строку длины нуль, в типе Variant возвращается значение Empty; инструкция As определяет тип возвращаемого результата. VBA следит за тем, чтобы тип результата функции был соответствующий указанному. Если она опущена, то процедура-функция возвратит результат работы в типе Variant. 11

Совместимость типов l VBA запрещает присваивание несовместимого типа результату функции в любой функции-процедуре, которая имеет объявленный тип данных для ее результата. l Если, например, вы ошибочно написали оператор присваивания функции так, что присваивается, допустим, тип Integer результату функции с объявленным типом String, то VBA отображает ошибку несовпадения типа. 12

Совместимость типов l l В случае присваивания типа данных, который не является тем же, что объявленный возвращаемый тип для функции-процедуры, но является совместимым, VBA преобразует значение типа, определенного для функции при возврате результата функции. Например, если присваивается тип Double функции, результат которой был объявлен как Long, то VBA не выдает никакой ошибки, а просто преобразует Double в тип Long (при возвращении результата функции). 13

Пример 1 Написать пользовательскую функцию для вычисления дискриминанта 14

Вызов пользовательской функции в MS EXCEL l ВставкаФункция, Категория –Определенные пользователем 15

Вызов пользовательской функции в MS EXCEL 16

Вызов пользовательской функции в MS EXCEL 17

Передача аргументов l l Существуют два способа для передачи информации в функцию-процедуру: по ссылке и по значению. По умолчанию VBA передает функции все аргументы по ссылке. При передаче данных функции по ссылке на самом деле передается только адрес памяти, который ссылается на исходные данные, определенные в списке аргументов функции во время ее вызова. Это означает, что если функция изменит значение в любом из аргументов, то исходные данные также изменяются. При передаче аргумента по значению VBA делает копию исходных данных и передает эту копию функции. Если функция изменяет значение в аргументе, передаваемом по значению, изменяется только копия данных, а исходные данные не изменяются. 18

Передача аргументов l Передача данных по ссылке позволяет изменить исходные данные, переданные функции через аргумент; а передача с помощью значения не позволяет изменять значение исходных данных. l Для того, чтобы явно определить метод передачи, используются ключевые слова: By. Val – (By value) метод по значению; By. Ref – (By reference) метод по ссылке. l l 19

Пример 2 20

Передача аргументов l l l Т. к. передача по ссылке позволяет функции изменять значение исходных данных ее аргументов, аргументы, передаваемые по ссылке, могут получить нежелательные побочные значения. В листинге, представленном выше, показано, что до применения функции Argument строковая переменная s 1 содержала текстовую строку в нижнем регистре, а после применения функции она уже содержит строку в верхнем регистре. Для предупреждения таких побочных результатов необходимо, чтобы функция работала с копией значения аргумента, а не с исходными данными. 21

Пример 3 22

Использование оператора Exit Для того чтобы функция прекратила выполнение, используется форма VBAоператора Exit. Оператор Exit имеет следующий синтаксис: Exit Function используется для окончания функции. 23

Пример 4 Напишите процедуру на языке VBA, которая вычисляет значение функции y(x) = cos(sin 2 x) + cos(sin 5 x). Вычисление cos(sin kx) оформить в виде функции. 24

Результат выполнения: 25

СПАСИБО ЗА ВНИМАНИЕ!

present5.com

Что такое в Excel функция Function и процедура Sub — Трюки и приемы в Microsoft Excel

В Excel VBA-макросы существуют в двух различных вариантах: процедуры Sub и процедуры-функции Function. В этой статье вы узнаете, чем они отличаются.

Процедуры Sub

Вы можете думать о процедуре Sub как о новой команде. В вашей книге Excel может быть любое количество процедур Sub. На рис. 206.1 показана простая процедура Sub. Если выполнить этот код, он переключит режим отображения разрывов страниц для активного листа.

Рис. 206.1. Простая, но полезная процедура VBA

Процедуры Sub всегда начинаются с ключевого слова Sub, имени макроса и пары круглых скобок. Выражение End Sub означает конец процедуры. Строки между этим ключевым словом и выражением содержат код процедуры.

Вы выполняете процедуры Sub несколькими способами. Например, можно нажать Alt+F8, а затем выбрать макрос в окне Macros. Другие способы выполнения процедур Sub описаны в статье «Как выполняются макросы в Excel«.

Если вы записываете макрос, это всегда процедура Sub.

Функции VBA

Вторым типом процедур VBA являются процедуры-функции Function, которые всегда возвращают одно значение (так же, как и функция листа всегда возвращает одно значение). Процедура-функция VBA может быть выполнена другими процедурами VBA или использована в формулах листа, как и любая другая встроенная функция Excel.

Рис. 206.2. Процедура-функция Function, возвращающая имя пользователя

На рис. 206.2 вы можете видеть пользовательскую функцию, названную User. Она просто возвращает имя пользователя (которое указано в окне Параметры Excel). Процедура-функция выглядит так же, как и процедура Sub. Только обратите внимание, что процедура-функция начинается с ключевого слова Function, а заканчивается оператором End Function.

Вот пример формулы листа, которая использует эту функцию: =PERSONAL.XLSB!User(). Эта формула предполагает, что функция User определена в вашей личной книге макросов. Если функция определена в книге, содержащей формулу, можно опустить ссылку на файл и использовать следующую формулу: =User()

excelexpert.ru

Процедуры и функции VBA

Все макросы сохраняются в документах или шаблонах программ Office 2000 в виде процедур на языке VBA. Процедурой называется фрагмент текста на языке VBA (программный код), заключенный между операторами Sub и End Sub. В отличие от процедуры при вызове функции имя последней выступает в роли переменной, содержащей вычисленное функцией значение (точно так же, как при вызове функции рабочего листа Excel).

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

Sub имя_процедуры (аргумент_1, аргумент_2, ... аргумент_n)

<инструкция VBA>

<Инструкция VBA>

…

<инструкция VBA>

End Sub

Function имя_функции (аргумент_1, аргумент_2, ... аргумент_n) As тип_данных

<инструкция VBA>

<Инструкция VBA>

…

имя_функции = вычисленное_значение

…

End Function

Выполнение программного кода процедуры или функции происходит при передаче на нее управления. Передача управления (вызов) может осуществляться различными способами.

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

Очень важную роль в программировании на VBA играет частный случай процедуры — процедура без параметров. Такие процедуры могут выступать в роли командных макросов и процедур обработки событий. Схема работы с командными макросами не отличается от традиционной схемы работы с макросами. После создания такого макроса с помощью редактора VBA можно назначить ему комбинацию клавиш для вызова, сопоставить команду меню или кнопку на панели инструментов. Процедуры обработки событий представляют собой основу механизма связи событий с программным кодом для их обработки.

Функции VBA, как правило, располагают списком параметров. Они не могут использоваться для обработки событий или в качестве программных макросов. Их можно вызывать как с помощью инструкции Call, так и непосредственно в процессе расчета значения выражения VBA (из кода других процедур или функций) или формулы рабочего листа Excel. На листинге 14.2 приведены описания процедуры pifagor, вычисляющей длину гипотенузы прямоугольного треугольника по длинам его катетов. Значения длин катетов передаются в процедуру с помощью ее первых двух аргументов, вычисленная длина гипотенузы передается в вызывающую процедуру с помощью третьего аргумента. У функции fpifagor — то же самое назначение, только результат вычислений она передает в вызывающую процедуру через имя функции, а не дополнительный аргумент. Процедура Main вызывает процедуру pifagor и функцию fpifagor, и выводит результаты их работы на экран с помощью процедуры MsgBox, которая выводит в диалоговом окне возвращенное значение.

Листинг 14.2. Определения и вызовы процедуры и функции Sub Main()

а = 3 ' b = 4

Call pifagor(a, b, с)

Call MsgBox(c)

Call MsgBox(fpifagor(a, b))

End Sub

Sub pifagor(x, у, z)

z = Sqr(x * x + у * у)

End Sub

Function fpifagor(x, y)

fpifagor = Sqr(x * x + у * у)

End Function

Как правило, программа на языке VBA состоит из нескольких процедур и функций. Описания этих программных единиц хранятся в модулях. В свою очередь, модули как составные части входят в проекты. Проекты сохраняются в файлах документов приложений Office 2000, но работают с ними с помощью отдельного приложения — редактора Visual Basic. При необходимости вызова процедуры или функции, описание которой расположено в другом модуле, ее имя при вызове указывается следующим образом: имяМодуля.имяПроцедуры. Если описание процедуры или функции расположено в другом проекте, при вызове следует использовать синтаксис:

имяПроекта.имяМодуля.имяПроцедуры.

gsmtut.narod.ru

Выполнение процедур в VBA

Ранее, с запуском процедур из меню "Макросы" мы уже сталкивались. Так же, мы уже назначали нашим процедурам горячие клавиши. Теперь поговорим о еще нескольких способах запуска процедур в VBA.

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

Этот способ позволяет нам создать свои элементы в пользовательских меню и назначить этим элементам макросы т.е. процедуры. Ниже приводится первый способ создания элемента меню и привязку к нему макроса. Данный способ использовать не предпочтительно т.к. отсутствует гибкость, при переходе на другую рабочую станцию (ПК) элементы придется создавать и настраивать повторно. Со вторым способом, а именно с программным созданием панелей меню и подключением их как надстройки, мы поговорим в следующих статьях, ну а для создания простенького меню из одного- двух элементов смотрим способ ниже.

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

Sub GetUserDate() Dim msg As String msg = "Пользователь: " & Environ("UserName") & Chr(13) _ & "Текущая дата и время: " & Now 'Environ("UserName") возвращает имя текущего пользователя ' Now - возвращает дату и время ПК MsgBox msgEnd Sub

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

Обратите внимание!!! При открытом окне "Настройка", Вы можете изменить любой пункт меню, как панелей, так и выпадающих меню.

2. Перетащите методом Drag & Drop из списка "Команды", элемент который называется "Настраиваемая команда меню", в нижнюю часть меню "Данные", расположив его после элемента "Обновить данные".

3. Щелкните правой кнопкой мыши на новом пункте меню «Настраиваемая команда меню» и в контекстном меню, в поле Имя введите название пункта меню «&Имя пользователя и дата»

4. Теперь нам остается только связать нашу процедуру (макрос) с этим пунктом. Для этого, в контекстном меню настройки элемента «Настраиваемая команда меню» т.е. уже «Имя пользователя и дата», выбираем пункт «Назначить макрос» и выбираем нашу процедуру из списка:

Все. Жмем ОК и закрываем окно «Настройка». Тестируем, меню Данные - Имя пользователя и дата, получаем сообщение:

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

Выполнение процедуры из другой процедуры

Самый распространенный способ вызова (выполнения) процедуры это вызов процедуры из другой процедуры. Существует три способа вызова:

Ввод названия процедуры и ее аргументы через запятую (если есть аргументы). Самый распространенный способ. В прошлых статьях мы уже вызывали таким способом процедуры, например «Создание расширенного списка выбора».
Ввод ключевого слова Call, после, ввод названия процедуры и аргументов, только в этом случае, аргументы необходимо заключить в скобки.
Вызов с использованием метода Run объекта Application. Этот способ можно применить и для выполнения других процедур VBA. С помощью метода Run можно выполнить процедуру, имя которой присвоено в переменной. В этом случае в метод Run переменная передается как аргумент.

Рассмотрим каждый способ на примерах. Для этого создадим в модуле с созданной ранее процедурой GetUserDate, вызывающую процедуру CallProc.

Способ 1.

Sub CallProc() GetUserDateEnd Sub

Способ 2.

Sub CallProc() Call GetUserDate [арг_1, арг_2]End Sub

Т.к. наша процедура GetUserDate не имеет входных аргументов, то вызываем ее без них. арг_1, арг_2 даны для примера. В правилах хорошего тона, вызов процедур правильнее делать вторым способом т.к. это явно указывает на вызов другой процедуры, но необязательно. Сознаюсь честно, сам ленивый и постоянно использую первый способ :).

Способ 3.

Этот способ требует особого внимания. Создадим процедуру, определяющую сегодняшний день недели и сообщающую нам в MsgBox, выходной сегодня или нет. Для этого создадим две процедуры, содержащие только сообщения:

Sub Работаем() MsgBox "Пашем! Суббота еще не скоро!"End Sub

Sub Отдыхаем() MsgBox "Ура! Выходные!"End Sub

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

Sub GetWeekDay() Dim SubToCall As String 'переменная содержащая имя процедуры

Select Case Weekday(Now, vbMonday) Case 1 To 5: SubToCall = "Работаем" Case 6 To 7: SubToCall = "Отдыхаем" End Select Application.Run SubToCall End Sub

Обратите внимание, имя вызываемой процедуры присваивается в текстовую переменную SubToCall в виде обычной текстовой строки. Функция Weekday определяет по текущей дате (которая возвращена встроенной функцией Now) день недели, который проверяется в Case-селекторе (по работе с Case читаем здесь ) и в соответствии с возращенным номером дня недели присваивает в переменную SubToCall строку, содержащую имя процедуры, которую необходимо вызвать. Далее в Application.Run передаем SubToCall как аргумент. Хочу обратить внимание на функцию Weekday - функция имеет еще второй необязательный параметр, который определяет, с какого дня недели вести счет дней. По умолчанию, счет ведется с воскресенья, поэтому константа vbMonday, в нашем примере, указывает функции, что счет необходимо начать с понедельника.

Вызов процедуры из другого модуля

VBA - язык не привередлив и снисходителен ко многим моментам, например, описание переменных (по этому поводу читайте здесь ). Тоже относится и к вызову процедур, содержащихся в разных модулях. Процедуры могут быть Public или Private (что это значит, можете ознакомиться в статье по области видимости переменных). Так вот, в случае с приватными процедурами, вызов их может происходить только в рамках общего модуля т.е. процедуры (вызываемая и вызывающая) должны находится в одном и том же модуле. Если же вызываемая процедура описана как Public, тогда она становится доступной, для вызывающих процедур, во всех модулях. В случае с Public возможно повторение имен процедур в разных модулях, но вот с вызовом будут проблемы, работа закончится с ошибкой Run-time error 1004: «Не найден макрос…» т.е. интерпретатор, в рамках всего проекта, нашел несколько доступных процедур с одинаковым именем, но не понял какую из них вызвать. Для того чтобы это избежать необходимо явно указать путь к данной процедуре. Это делается довольно просто и привычно для объектно-ориентированных языков, т.е. мы указываем название модуля, содержащего процедуру, ставим точку, и нам предлагается список всех доступных процедур (функций) или глобальных переменных. Например, Module2 содержит процедуру типа Public с именем Test. Для того чтобы ее вызвать из Module1 необходимо написать: Module2.Test.

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

Ну, а на этом все. В следующей статье еще захвачу один способ, это вызов процедур из других книг. А пока – пока!

www.programm-school.ru

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

Хитрости » 9 Декабрь 2015 Дмитрий 5008 просмотров

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

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

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

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 вернет ссылку на все ячейки, в которые записана функция
Если вызов был кнопкой на листе - Caller вернет текст, содержащий локальное имя объекта Shape, к которому привязан вызов процедуры
Если вызов был из событийной процедуры(Workbook_Open и им подобные), либо процедура была вызвана через Alt+F11 - Caller вернет ошибку REF
Если процедура вызвана с панели(Ribbon или настраиваемая панель) - Caller будет иметь тип Variant(), но не сможет определить как именно был вызван код и при попытке обращения к нему получим так же ошибку REF
Если вызов был через процедуры автоматизации (Auto_Open, Auto_Close, Auto_Activate, Auto_Deactivate) - Caller вернет тип String и содержит имя книги и активного листаэто устаревшие процедуры, которые сейчас заменены событийными в классах книг и листов, но тем не менее их можно встретить в некоторых кодах

Для примера можно скачать файл по ссылке:

www.excel-vba.ru

vba - Функции или подпрограммы Excel VBA в массиве

Да, но я не рекомендую. VBA на самом деле не построен для этого. Вы отметили этот вопрос в Excel, поэтому я опишу, как это делается для этого продукта Office. Общая концепция применима к большинству Office Suite, но каждый другой продукт имеет различный синтаксис для метода Application.Run.

Во-первых, важно понять два разных метода динамического вызова процедуры (sub/function) и когда использовать их.

Application.Run либо выполнит подпрограмму, либо вызовет функцию, которая хранится в стандартном модуле *.bas.

Первый параметр - это имя процедуры (передано как строка). После этого вы можете передать до 30 аргументов. (Если ваша процедура требует больше, чем рефакторинг для любви к коду.)

В Application.Run есть еще две важные вещи.

Вы не можете использовать именованные аргументы. Арги должны проходить по положению.
Объекты, переданные как аргументы, преобразуются в значения. Это означает, что вы можете столкнуться с непредвиденными проблемами, если попытаетесь запустить процедуру, для которой требуются объекты, свойства по умолчанию в качестве аргументов.
Public Sub Test1() Application.Run "VBAProject.Module1.SomeFunction" End Sub

Вынос:

Используйте Application.Run, когда вы работаете со стандартным модулем.

CallByName выполняет метод объекта или задает/получает свойство объекта.

В экземпляре объекта, который вы хотите вызвать метод, используется как аргумент, а также имя метода (снова как строка).

Public Sub Test2() Dim anObj As SomeObject Dim result As Boolean result = CallByName(anObj, "IsValid") End Sub

Вынос:

Используйте CallByName, если вы хотите вызвать метод класса.

Как вы можете видеть, ни один из этих методов не использует фактические указатели (по крайней мере, не извне). Они берут строки, которые затем используют, чтобы найти указатель на процедуру, которую вы хотите выполнить. Итак, вам нужно знать имя точное, которое вы хотите выполнить. Вам также нужно знать, какой метод вам нужно использовать. CallByName с дополнительным бременем, требующим экземпляра объекта, который вы хотите вызвать. В любом случае вы можете сохранить эти имена в виде строк внутри массива или коллекции. (Хек, даже словарь может иметь смысл.)

Таким образом, вы можете либо жестко закодировать их как строки, либо попытаться извлечь соответствующие имена процедур во время выполнения. Чтобы извлечь имена процедур, вам нужно будет взаимодействовать с самим VBIDE с помощью Microsoft Visual Basic для расширяемости приложений библиотека. Объяснение всего этого здесь потребует слишком большого количества кода и усилий, но я могу указать вам на некоторые хорошие ресурсы.

Статьи и вопросы SE:

Код из некоторых моих Qs и As:

qaru.site