Создаем свою первую функцию в excel

Метод Application.MacroOptions

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

Пример кода с методом Application.MacroOptions:

• ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
• ИмяФункции — имя функции, параметры которой добавляются или изменяются.
• Описание функции — описание функции, которое добавляется или изменяется.
• Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию — «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
• «Описание 1», «Описание 2», «Описание 3», . — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

После однократного запуска этой подпрограммы получаем следующий результат:

Новое описание пользовательской функции и ее второго аргумента

Arguments

VBA procedures can be passed data via arguments, which are declared in the procedure definition. For example, you could have a VBA Sub procedure that adds an Integer to every cell in the current selected range on a worksheet. You could supply the value of the integer to the Sub via an argument, as follows:

Sub AddToCells(i As Integer)      .      .      .End Sub

Note that it is not essential for VBA Functions or Subs to have arguments. Some procedures may not require them.

Optional Arguments

You can also define VBA procedures to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the procedure will assign a default value to them.

To return to the example above, if we wanted to make the supplied integer argument optional, this would be declared as follows:

Sub AddToCells(Optional i As Integer = 0)

You can use multiple Optional arguments in a VBA procedure, as long the Optional arguments are all positioned at the end of the argument list.

Passing Arguments By Value and By Reference

You can specify whether an argument is passed to a VBA procedure by value or by reference by using the ByVal or the ByRef keyword when defining the procedure. This is shown below:

Sub AddToCells(ByVal i As Integer)        .        .        .End Sub	In this case, the integer i is passed by Value. Any changes that are made to i will be lost when you exit the Sub procedure.
Sub AddToCells(ByRef i As Integer)        .        .        .End Sub	In this case, the integer i is passed by Reference. When you exit the Sub, any changes that have been made to i will be remembered by the variable that was passed into the Sub procedure.

It should be noted that, by default, VBA arguments are passed by Reference. Therefore, if you do not use the ByVal or the ByRef keyword, the arguments will be passed by Reference.

Before discussing further properties of VBA Function and Sub procedures, it is useful to look at the two types of procedure individually. The following two sections provide a brief discussion of VBA Function and VBA Sub procedures, along with simple examples.

Calling a Sub or Function

When people are new to VBA they tend to put all the code in one sub. This is not a good way to write your code.

It is better to break your code into multiple procedures. We can run one procedure from another.

Here is an example:

' https://excelmacromastery.com/
Sub Main()
    
    ' call each sub to perform a task
    CopyData
    
    AddFormulas
    
    FormatData

End Sub

Sub CopyData()
    ' Add code here
End Sub

Sub AddFormulas()
    ' Add code here
End Sub

Sub FormatData()
    ' Add code here
End Sub

 
 
You can see that in the Main sub, we have added the name of three subs. When VBA reaches a line containing a procedure name, it will run the code in this procedure.

We refer to this as calling a procedure e.g. We are calling the CopyData sub from the Main sub.

There is actually a Call keyword in VBA. We can use it like this:

' https://excelmacromastery.com/
Sub Main()
    
    ' call each sub to perform a task
    Call CopyData
    
    Call AddFormulas
    
    Call FormatData

End Sub

 
 
Using the Call keyword is optional. There is no real need to use it unless you are new to VBA and you feel it makes your code more readable.

If you are passing arguments using Call then you must use parentheses around them.

For example:

' https://excelmacromastery.com/
Sub Main()
    
    ' If call is not used then no parentheses
    AddValues 2, 4
    
    ' call requires parentheses for arguments
    Call AddValues(2, 4)
End Sub

Sub AddValues(x As Long, y As Long)

End Sub

ByRef – ByVal

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

Наприклад:

Sub macro_test() Dim var_number As Integer var_number = 30 calcul_square var_number MsgBox var_numberEnd SubPrivate Sub calcul_square(ByRef var_value As Integer) ‘ByRef не обязательно указывать ‘(является значением по умолчанию) var_value = var_value * var_valueEnd Sub

Чтобы стало понятнее, ниже есть пример того, что произойдет, если макрос будет запущен на выполнение:

var_number = 30’Начальное значение переменной “var_number” є 30calcul_square var_number’Под-процедура запускается с “var_number” как аргументPrivate Sub calcul_square(ByRef var_value As Integer)’Переменная “var_value” в некоторой степени служит для быстрого доступа к переменной “var_number”,’что означает, что если переменная “var_value” изменена, переменная “var_number” будет также изменена”(и они не должны обязательно иметь одинаковое имя)var_value = var_value * var_value’Значение переменной “var_value” изменено (и поэтому “var_number” также одновременно изменено)End Sub’Конец под-процедурыMsgBox var_number’Переменная “var_number” была изменена, поэтому 900 будет сейчас отображено в диалоговом окнеВторой метод заключается в использовании ByVal .

В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.

Ниже вы можете увидеть как предыдущий код и ByVal работают:

var_number = 30’Начальное значение переменной “var_number” есть 30calcul_square var_number’Под-процедура запускается с “var_number” как аргументPrivate Sub calcul_square (ByVal var_value As Integer)’Переменная “var_value” копирует значение переменной “var_number” (2-е переменные не являются связанными)var_value = var_value * var_value’Значение переменной “var_value” измененоEnd Sub’Конец под-процедуры (в этом примере под-процедура не имеет никакого влияния ни на что)MsgBox var_number’Переменная “var_number” не была изменена, и поэтому 30 будет отображено в диалоговом окне

Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена …

Функции-процедуры VBA

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

Основное различие между функцией-процедурой и другими процедурами, помимо того, что функции возвращают значение, а процедуры — нет, состоит в том, что в функции-процедуре используются ключевые слова Function и End Function.

Function Name() ‘VBA Statements End Function

Function — ключевое слово, объявляющее начало функции.

Name — имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA.

Arglist — список аргументов данной функции, необязательный элемент.

Type — любой тип возвращаемого значения функции. Если тип не определен, результат, который возвращает функция-процедура, имеет тип Variant.

Name = expression — присваивание функции, которое указывает VBA, какое значение должна возвращать функция, необязательный элемент. Тем не менее, всегда следует включать оператор присваивания в функции-процедуры.

End Function — ключевые слова, заканчивающие функцию.

Даже если функция не имеет аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки.

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

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

Без использования функции листинг бы выглядел так:

Из этого простого примера, думаю, понятна основная идея использования функций-процедур — улучшение читабельности программного кода и его сокращение (другими словами, функция-процедура пишется когда в программном коде более 2-3 раз встречается один и тот же «кусок» кода). Действительно, если бы наша функция-процедура состояла не из одной строки, а, скажем, из 10 строк; и программный код использовал бы эту функцию-процедуру 5 раз, то общий листинг программы был бы меньше на 38 строк.

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

Аргументы с определенными типами используются по тем же знакомым причинам, по каким используются типизированные переменные или результаты функции. Определение типов аргументов для функции-процедуры также помогает пользователю при вызове функции вводить аргументы правильного типа в правильном порядке.

В начало страницы

В начало страницы

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

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.

Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

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

• Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
• Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

• Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
• Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
• Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.

После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).

Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.

Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.

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

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.

Этой строкой мы преобразовываем текстовую переменную result, которая содержит все цифры аргумента Текст, в числовое значение. И говорим какой результат должна вывести наша функция Цифры.

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

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

Можно использовать процедуру Sub для упорядочения других процедур с целью упрощения их понимания и отладки. В приведенном ниже примере процедура Sub Main вызывает процедуру Sub MultiBeep , передавая значение 56 для ее аргумента.

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.

Вызов процедур Sub из модулей разных книг

Если вызываемая подпрограмма расположена в другой книге, она должна быть объявлена как Public, а книга открыта. Запустить такую процедуру Sub можно с помощью метода Application.Run (протестировано в Excel 2016).

Синтаксис метода Application.Run

Application.Run «ИмяКниги!ИмяМодуля.ИмяПроцедуры», Арг1, Арг2, …, Арг30

1. ИмяКниги!ИмяМодуля.ИмяПроцедуры — обязательный компонент, полный адрес подпрограммы, заключен в двойные кавычки.
   • ИмяКниги — имя рабочей книги Excel с расширением, в которой находится вызываемая подпрограмма, если имя содержит пробелы, оно заключается в одинарные кавычки — апострофы (‘Имя Книги’).
   • ИмяМодуля — имя модуля для стандартного модуля (для уникальных имен вызываемых подпрограмм может не указываться), имя листа для модуля листа, словосочетание «ЭтаКнига» (без кавычек) для модуля книги.
   • ИмяПроцедуры — имя вызываемой процедуры Sub.
2. Арг1, Арг2, …, Арг30 — необязательные компоненты, аргументы вызываемой подпрограммы, максимальное количество которых ограничено 30 элементами.

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

Полный адрес вызываемой процедуры

Может показаться сложным составить полный адрес вызываемой подпрограммы, но на самом деле все очень просто — Excel уже сделал это за нас.

1. Откройте окно со списком макросов.

Список макросов во всех открытых книгах

2. Найдите в списке вызываемую подпрограмму и кликните по ней. Ее полный адрес отобразится в поле «Имя макроса».

3. Скопируйте полное имя вызываемой процедуры Sub и вставьте ее в метод Application.Run, заключив в двойные кавычки.

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

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

Пример вызова подпрограмм из другой книги

Допустим, у нас есть рабочая книга Excel под именем «Книга1.xlsm» (или «Книга1.xls» в ранних версиях программы). В ней находятся вызываемые из другой книги процедуры Sub, перечисленные ниже.

В стандартном модуле «Module1»:

1 2 3

SubVyzov1() MsgBox»Запущена процедура в стандартном модуле!» EndSub

В модуле листа «Лист1»:

1 2 3

SubVyzov2() MsgBox»Запущена процедура в модуле листа!» EndSub

В модуле книги «ЭтаКнига»:

1 2 3 4

SubVyzov3(aAsVariant,bAsVariant) MsgBox»Запущена процедура в модуле книги!»_ &vbNewLine&»Сумма равна: «&a+b EndSub

Для последовательного запуска этих подпрограмм можно вставить в любой модуль другой книги Excel следующую процедуру:

1 2 3 4 5 6

SubProverkaVyzova() Application.Run»Книга1.xlsm!Vyzov1″ Application.Run»Книга1.xlsm!Module1.Vyzov1″ Application.Run»Книга1.xlsm!Лист1.Vyzov2″ Application.Run»Книга1.xlsm!ЭтаКнига.Vyzov3″,555,445 EndSub

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

И еще раз напомню, что имя книги с пробелами заключается в одинарные кавычки (апострофы):

1	Application.Run»‘Новая Книга 1.xlsm’!Процедура1″

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

Пользовательские процедуры «Function» и «Sub» в VBA

В Excel Visual Basic набор команд, выполняющий определённую задачу, помещается в процедуру Function (Функция) или Sub (Подпрограмма). Главное отличие между процедурами Function и Sub состоит в том, что процедура Function возвращает результат, процедура Sub – нет.

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

При помощи аргументов процедурам VBA могут быть переданы различные данные. Список аргументов указывается при объявлении процедуры. К примеру, процедура Sub в VBA добавляет заданное целое число (Integer) в каждую ячейку в выделенном диапазоне. Передать процедуре это число можно при помощи аргумента, вот так:

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

Необязательные аргументы

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

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

В таком случае целочисленный аргумент i по умолчанию будет равен 0.

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

Аргументы в VBA могут быть переданы процедуре двумя способами:

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

Перед тем как продолжить изучение процедур Function и Sub более подробно, будет полезным ещё раз взглянуть на особенности и отличия этих двух типов процедур. Далее приведены краткие обсуждения процедур VBA Function и Sub и показаны простые примеры.

A Quick Comparison

Sub:

1. Cannot return a value.
2. Can be called from VBA\Button\Event etc.

Function

1. Can return a value but doesn’t have to.
2. Can be called it from VBA\Button\Event etc. but it won’t appear in the list of Macros. You must type it in.
3. If the function is public, will appear in the worksheet function list for the current workbook.

 Note 1: You can use “Option Private Module” to hide subs in the current module. This means that subs won’t be visible to other projects and applications. They also won’t appear in a list of subs when you bring up the Macro window on the developer tab.

Note 2:We can use the word procedure to refer to a function or sub
 
 

Объявление пользовательской функции

Компоненты функции

• Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
• Имя — обязательный компонент, имя пользовательской функции.
• СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
• Операторы — необязательный компонент, блок операторов (инструкций).
• Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
• Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.

Видимость функции

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

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

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.

VBA процедуры типа Sub – подпрограммы

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

Sub ИмяПодпрограмм() Операторы операторы End Sub

После ключевого слова Sub следует имя подпрограммы, в круглых скобках можно указывать или не указывать аргументы. Аргументы – это переменные (параметры), значение которых может обрабатываться, аргументы разделяются запятыми. Конструкция Exit Sub также не является обязательной, она говорит том, что нужно произвести выход из подпрограммы и продолжить выполнение кода, следующего после выражения End Sub.

Вызов VBA процедуры осуществляется с помощью ключевого слова call, например, Call MySub.

Давайте напишем простой пример: добавьте в проект новую форму и новый модуль. На поверхность формы добавьте два текстовых поля (TextBox), одну метку (Label) и одну кнопку (CommandButton). Создайте связь между формой и модулем, прописав в редакторе кода для модуля:

Я назвал форму SubForm, а модуль – SubModule, за имя отвечает свойство Name.

Теперь в редакторе кода для формы пропишите:

Тут все предельно просто, вначале мы объявили процедуру Hipotenuze, которой будут передаваться два аргумента, далее происходит проверка на нулевые значения. Вызов происходит при нажатии на кнопку, находящуюся на форме, параметрами будут значения, хранящиеся в текстовых полях TextBox1 и TextBox2. Результат отображается в метке Label1.

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

Sub MySub (a As Integer, b As String) … End Sub

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

ParamArray – данное ключевое слово позволяет передавать процедуре переменное количество параметров, оно может использоваться только для последнего элемента в списке аргументов.

ParamArray нельзя использовать вместе со словами ByRef, ByVal или Optional, например:

Как видим, мы фактически с помощью ParamArray показываем, что передаем массив, для его обработки мы использовали оператор For …. Each. Тут мы передали при вызове VBA процедуры пять параметров, при этом, первый будет храниться в аргументе a, а остальные в аргументе b, который обрабатывается как массив.

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

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

Что бы все стало понятно, рассмотрим следующий пример:

MySub2 – аналог предыдущей процедуры, но тут происходит вызов MySumm2, в которой происходит передача параметров по значению, о чем говорит ключевое слово ByVal, в итоге, значение переменной MyVar не изменится.

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

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

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

Начало_Процедуры
Любой код
Первая строка дублирующего кода
Любой код
Последняя строка дублирующего кода
Любой код
Первая строка дублирующего кода
Любой код
Последняя строка дублирующего кода
Любой код
Конец_Процедуру

Выносите дублирующие участки кода в отдельные процедуры и указывайте ссылки на них из главной процедуры:

Начало_Главной_Процедуры
Любой код
Ссылка на Процедуру_дублирующегося_кода
Любой код
Ссылка на Процедуру_дублирующегося_кода
Любой код
Конец_Главной_Процедуру
Начало_Процедуры_дублирующегося_кода
Любой код
Конец_Процедуры_дублирующегося_кода

Call Макрос1
«Следующая строка идентична предыдущей
Макрос1

Если материалы сайт Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

What is a Function?

A Function is very similar to a sub. The major difference is that a function can return a value – a sub cannot. There are other differences which we will look at but this is the main one.

You normally create a function when you want to return a value.

 
 
Creating a function is similar to creating a sub

Function PerformCalc()

End Function

 
 
It is optional to add a return type to a function. If you don’t add the type then it is a type by default. This means that VBA will decide the type at runtime.

 
 
The next example shows you how to specify the return type

Function PerformCalc() As Long

End Function

 
 
You can see it is simple how you declare a variable. You can return any type you can declare as a variable including objects and collections.

Ранний выход из VBA процедур «Function» и «Sub»

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

Function VAT_Amount(sVAT_Rate As Single) As Single
VAT_Amount = 0
If sVAT_Rate

Обратите внимание, что перед тем, как завершить выполнение процедуры Function
– VAT_Amount
, в код вставлена встроенная VBA функция MsgBox
, которая показывает пользователю всплывающее окно с предупреждением. Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик»

Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик». Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

Для Excel 2007
. Зайдите в параметры Excel, используя кнопку Office, и в «основных параметрах работы с Excel» установите галочку на пункте «Показывать вкладку Разработчик на ленте».

Для Excel 2010 и 2013
. В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox
, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

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

Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.

Результаты поиска отображаются в поле №3 и представляют из себя таблицу с тремя столбцами: библиотека, класс и член класса.

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

Поле №6 содержит краткую справку о выделенном результате поиска, классе или члене класса.

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен

Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

Для получения полной справки кликните по кнопке с изображением вопросительного знака. В зависимости от версии Excel, Вас перенаправить на файл справки или сайт с документацией от Microsoft.

Изучите самостоятельно возможности функции MsgBox.