VBA Excel. Пользовательская функция (синтаксис, компоненты). VBA обратный цикл For Loop с инструкцией STEP. Оператор прерывания цикла «Exit For»

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

Список этих функций можно посмотреть в редакторе VBA:

Откройте рабочую книгу Excel и запустите редактор VBA (нажмите для этого Alt+F11 ), и затем нажмите F2 .
В выпадающем списке в верхней левой части экрана выберите библиотеку VBA .
Появится список встроенных классов и функций VBA. Кликните мышью по имени функции, чтобы внизу окна отобразилось её краткое описание. Нажатие F1 откроет страницу онлайн-справки по этой функции.

Кроме того, полный список встроенных функций VBA с примерами можно найти на сайте Visual Basic Developer Centre .

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

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

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

Аргументы

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

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

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

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

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

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

Sub AddToCells(Optional i As Integer = 0)

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

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

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

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

ByVal – передача аргумента по значению. Это значит, что процедуре передаётся только значение (то есть, копия аргумента), и, следовательно, любые изменения, сделанные с аргументом внутри процедуры, будут потеряны при выходе из неё.
ByRef – передача аргумента по ссылке. То есть процедуре передаётся фактический адрес размещения аргумента в памяти. Любые изменения, сделанные с аргументом внутри процедуры, будут сохранены при выходе из процедуры.

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

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

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

VBA процедура «Function»

Редактор VBA распознаёт процедуру Function

Function ... End Function

Как упоминалось ранее, процедура Function в VBA (в отличие от Sub ), возвращает значение. Для возвращаемых значений действуют следующие правила:

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

Это отлично проиллюстрировано в следующем примере.

Пример VBA процедуры «Function»: Выполняем математическую операцию с 3 числами

Ниже приведён пример кода VBA процедуры Function , которая получает три аргумента типа Double (числа с плавающей точкой двойной точности). В результате процедура возвращает ещё одно число типа Double , равное сумме первых двух аргументов минус третий аргумент:

Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function

Эта очень простая VBA процедура Function иллюстрирует, как данные передаются процедуре через аргументы. Можно увидеть, что тип данных, возвращаемых процедурой, определён как Double (об этом говорят слова As Double после списка аргументов). Также данный пример показывает, как результат процедуры Function сохраняется в переменной с именем, совпадающим с именем процедуры.

Вызов VBA процедуры «Function»

Если рассмотренная выше простая процедура Function вставлена в модуль в редакторе Visual Basic, то она может быть вызвана из других процедур VBA или использована на рабочем листе в книге Excel.

Вызов VBA процедуры «Function» из другой процедуры

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

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Вызов VBA процедуры «Function» из рабочего листа

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

SumMinus(10, 5, 2)

VBA процедура «Sub»

Редактор VBA понимает, что перед ним процедура Sub , когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:

Sub ... End Sub

VBA процедура «Sub»: Пример 1. Выравнивание по центру и изменение размера шрифта в выделенном диапазоне ячеек

Рассмотрим пример простой VBA процедуры Sub , задача которой – изменить форматирование выделенного диапазона ячеек. В ячейках устанавливается выравнивание по центру (и по вертикали, и по горизонтали) и размер шрифта изменяется на заданный пользователем:

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Данная процедура Sub выполняет действия, но не возвращает результат.

В этом примере также использован необязательный (Optional) аргумент iFontSize . Если аргумент iFontSize не передан процедуре Sub , то его значение по умолчанию принимается равным 10. Однако же, если аргумент iFontSize передается процедуре Sub , то в выделенном диапазоне ячеек будет установлен размер шрифта, заданный пользователем.

VBA процедура «Sub»: Пример 2. Выравнивание по центру и применение полужирного начертания к шрифту в выделенном диапазоне ячеек

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

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Вызов процедуры «Sub» в Excel VBA

Вызов VBA процедуры «Sub» из другой процедуры

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

Sub main() Call Format_Centered_And_Sized(20) End Sub

Если процедура Format_Centered_And_Sized имеет более одного аргумента, то они должны быть разделены запятыми. Вот так:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Вызов VBA процедуры «Sub» из рабочего листа

Процедура Sub не может быть введена непосредственно в ячейку листа Excel, как это может быть сделано с процедурой Function , потому что процедура Sub не возвращает значение. Однако, процедуры Sub , не имеющие аргументов и объявленные как Public (как будет показано далее), будут доступны для пользователей рабочего листа. Таким образом, если рассмотренные выше простые процедуры Sub вставлены в модуль в редакторе Visual Basic, то процедура Format_Centered_And_Bold будет доступна для использования на рабочем листе книги Excel, а процедура Format_Centered_And_Sized – не будет доступна, так как она имеет аргументы.

Вот простой способ запустить (или выполнить) процедуру Sub , доступную из рабочего листа:

Нажмите Alt+F8 (нажмите клавишу Alt и, удерживая её нажатой, нажмите клавишу F8 ).
В появившемся списке макросов выберите тот, который хотите запустить.
Нажмите Выполнить (Run)

Чтобы выполнять процедуру Sub быстро и легко, можно назначить для неё комбинацию клавиш. Для этого:

Нажмите Alt+F8 .
В появившемся списке макросов выберите тот, которому хотите назначить сочетание клавиш.
Нажмите Параметры (Options) и в появившемся диалоговом окне введите сочетание клавиш.
Нажмите ОК и закройте диалоговое окно Макрос (Macro).

Внимание: Назначая сочетание клавиш для макроса, убедитесь, что оно не используется, как стандартное в Excel (например, Ctrl+C ). Если выбрать уже существующее сочетание клавиш, то оно будет переназначено макросу, и в результате пользователь может запустить выполнение макроса случайно.

Область действия процедуры VBA

В части 2 данного самоучителя обсуждалась тема области действия переменных и констант и роль ключевых слов Public и Private . Эти ключевые слова так же можно использовать применительно к VBA процедурам:

Помните о том, что если перед объявлением VBA процедуры Function или Sub ключевое слово не вставлено, то по умолчанию для процедуры устанавливается свойство Public (то есть она будет доступна везде в данном проекте VBA). В этом состоит отличие от объявления переменных, которые по умолчанию бывают Private .

Ранний выход из 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 <= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

Обратите внимание, что перед тем, как завершить выполнение процедуры Function – VAT_Amount , в код вставлена встроенная VBA функция MsgBox , которая показывает пользователю всплывающее окно с предупреждением.

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

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

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

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

После этого на экране появится новое окно редактора VBA. Рассмотрим ниже некоторые его элементы, которые могут понадобиться в самом начале.

№2 на изображении . Кнопки запуска, остановки и прерывания выполнения кода. Во время запуска начинает выполняться код той процедуры, на которой размещен курсор. Данные кнопки дублируются вкладкой «Run» основного меню редактора.

№3 на изображении . Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении . Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении . Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.

Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Добавьте в модуль следующий код:

Sub Моя_процедура() MsgBox "Привет пользователь!" End Sub

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

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

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

В этом примере аргументы не используются, поэтому указываются пустые скобки.

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

Последняя строка содержит оператор End с последующим ключевым словом Sub и сообщает о завершении процедуры.

Учимся пользоваться 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.

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

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

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

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

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

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

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

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

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

Подробнее о том, что можно сделать в VBA

Выше, в разделе Зачем мучиться с VBA?, я уже упоминал о том, как с помощью VBA можно усовершенствовать имеющиеся приложения. Пришло время обсудить это подробнее, чтобы вам стало ясно, наконец, что же все-таки можно получить от VBA.

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

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

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

(На всякий случай замечу, что пользовательский интерфейс означает то, как программа выглядит на экране, как в ней работают мышь, клавиатура и другие всевозможные средства, предоставленные программой вам, пользователю, для взаимодействия с программным обеспечением.) Во многих приложениях VBA позволяет изменять элементы пользовательского интерфейса так, как это удобно. С помощью всего нескольких строчек программного кода вы можете добавлять, удалять или изменять взаимное расположение кнопок на панелях инструментов, создавать новые панели инструментов, переопределять раскладку клавиатуры или модифицировать структуру меню. На рис. 1.1 показано окно Microsoft Word с любезно измененным VBA пользовательским интерфейсом.

Вам потребуется VBA, если вы захотите работать с документами Word на чистом экране, совершенно свободном ото всех панелей инструментов

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

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

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

До сих пор я говорил об изменении пользовательского интерфейса VBA приложения, но не о создании пользовательского интерфейса самой VBA программы. Хотя многие VBA-программы и представляют собой автоматы, выполняющие работу безо всякого вмешательства с вашей стороны, неизбежно наступит момент, когда вам понадобится создавать свои диалоговые и другие окна (для них в VBA используется обобщающий термин формы).

Из книги Давайте создадим компилятор! автора Креншоу Джек

Из книги Разгони свой сайт автора Мациевский Николай

Рассматриваем подробнее Рис. 3.1. Диаграмма работы pre-check и post-checkКогда к браузеру поступает запрос на открытие ресурса, который находится в кэше, и при этом кэш содержит расширения Cache-Control (отправленные с сервера как часть заголовка HTTP-ответа), тогда IE использует эти

Из книги Win2K FAQ (v. 6.0) автора Шашков Алексей

(1.13) Можно ли из W2kPro сделать W2kServer, и зачем это надо? Можно. Хотя и нельзя:-) Для этого служит программка под названием NTSwitch. Всё что она делает, это сохраняет копию вашего реестра, редактирует его, и восстанавливает его обратно. Благодаря тому, что различные версии

Из книги AutoCAD 2009 для студента. Самоучитель автора Соколова Татьяна Юрьевна

(5.4) В 1С Бухгалтерия 6 вместо русских букв показываются закорючки, что можно с этим сделать? Это происходит потому, что Microsoft заменил название шрифта MS SANS SERIF на MICROSOFT SANS SERIF, в результате чего 1С Бухгалтерия не может его найти. Для решения проблемы достаточно поменять

Из книги Добавьте в корзину. Ключевые принципы повышения конверсии веб-сайтов автора Айзенберг Джеффри

Из книги AutoCAD 2009. Учебный курс автора Соколова Татьяна Юрьевна

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

Из книги AutoCAD 2008 для студента: популярный самоучитель автора Соколова Татьяна Юрьевна

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

Из книги Раскрутка: секреты эффективного продвижения сайтов автора Евдокимов Николай Семенович

Из книги Графика DirectX в Delphi автора Краснов Михаил

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

Из книги Цифровой журнал «Компьютерра» № 197 автора Журнал «Компьютерра»

Из книги Программирование для Linux. Профессиональный подход автора Митчелл Марк

Три вывода, которые можно сделать после обсуждения причин различий между людьми по цвету их кожи Дмитрий Шабанов Опубликовано 31 октября 2013 Две общем-топредыдущие колонки были посвящены анализу одного, в, весьма простого признака человека -

Из книги C++ для начинающих автора Липпман Стенли

4.1.4. Подробнее об идентификаторах потоков Иногда в программе возникает необходимость определить, какой поток выполняет ее в данный момент. Функция pthread_self() возвращает идентификатор потока, в котором она вызвана. Для сравнения двух разных идентификаторов предназначена

Из книги Как тестируют в Google автора Уиттакер Джеймс

9.3.1. Подробнее о точном соответствии Самый простой случай возникает тогда, когда типы фактических аргументов совпадают с типами формальных параметров. Например, есть две показанные ниже перегруженные функции max(). Тогда каждый из вызовов max() точно соответствует одному

Из книги автора

Немного подробнее о Buganizer Самый старый баг, зарегистрированный в Google, создан 18 мая 2001 года в 15:33 и существует до сих пор. Он называется «Test Bug», а его описание выглядит как «First Bug!». Смешно, что этот баг до сих пор случайно всплывает, когда разработчики привязывают

Определение VBA, преимущества, возможности применения

VBA (Visual Basic for Applications) - это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.

В принципе, как это не удивительно, при программировании в Office можно вполне обойтись без языка VBA. Подойдет любой COM-совместимый язык, например, обычный Visual Basic, VBScript, JScript, C++, Delphi, Java и т.п. Можно использовать и.NET-совместимые языки программирования - VB.NET, C# и т.п. Все возможности объектных моделей приложений Office вполне можно будет использовать. Например, если сохранить следующий код в файле с расширением *.vbs и запустить его на выполнение, то будет запущен Word, в нем открыт новый документ и впечатан текст:

Set oWord = CreateObject("Word.Application")

oWord.Visible = true

oWord.Documents.Add

oWord.Selection.TypeText ("Привет от VBScript")

Однако VBA обычно - самый удобный язык для работы с приложениями Office.

Главная причина проста - язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office - документах Word, книгах Excel, презентациях PowerPoint и т.п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге - хост) встроена внутрь этих приложений.

В настоящее время VBA встроен:

во все главные приложения MS Office - Word, Excel, Access, PowerPoint, Outlook, FrontPage, InfoPath;
в другие приложения Microsoft, например, Visio и M icrosoft Project;
в более чем 100 приложений третьих фирм, например, CorelDraw и CorelWordPerfect Office 2000, AutoCAD и т.п.

У VBA есть также множество других преимуществ:

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

создавать полноценные приложения на Visual Basic (поскольку эти языки - близкие родственники);
использовать все возможности языка VBScript (это - вообще урезанный VBA). В результате в вашем распоряжении будут универсальные средства для создания скриптов администрирования Windows (об этом - в конце курса), для создания Web-страниц (VBScript в Internet Explorer), для создания Web-приложений ASP, для применения в пакетах DTS и заданиях на MS SQL Server, для создания серверных скриптов Exchange Server и многое-многое другое.

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

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

Несмотря на то, что часто приложения VBA выполняются медленнее, чем бы вам хотелось, они нересурсоемки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований про производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. По моему опыту, чаще всего проблемы с производительностью VBA-приложений - это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т.п.

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

В среде программистов-профессионалов считается, что самый короткий путь "с нуля" и программ типа "Hello, World" до профессиональных программ, которые делаются под заказ - именно через связку Office- VBA (а конечно, не через C++, Java или Delphi).

Функция написанная на VBA - это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:

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

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

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

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

В чем отличие процедуры (Sub) от функции (Function)?

Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.

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

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

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

Function Цифры(Текст As String) As Long Dim i As Long Dim result As String For i = 1 To Len(Текст) If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1) Next Цифры = CLng(result) End Function

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

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

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

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

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

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

Function Цифры(Текст As String) As Long

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

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

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

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

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

Dim i As Long Dim result As String

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

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

For i = 1 To Len(Текст)

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

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

If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)

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

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

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

Цифры = CLng(result)

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

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

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