Использование VBA в MS EXCEL

понятия
2 Редактор VBA
3 Создание функций пользователя
4 Автоматизация расчетов на рабочем

листе
5 Работа с формами в среде VBA

приложений) — немного упрощенная реализация языка программирования Visual Basic, встроенная

в линейку продуктов Microsoft Office, а также во многие другие программные пакеты, такие как AutoCAD, CorelDRAW, WordPerfect и др.
VBA покрывает и расширяет функциональность ранее использовавшихся специализированных макро-языков, позволяя создавать макросы
VBA является интерпретируемым языком. VBA, будучи языком, построенным на COM, позволяет использовать все доступные в операционной системе COM объекты и компоненты ActiveX. По сути, возможно создание приложения на основе Microsoft Word VBA, использующего только средства Corel Draw.
Преимущество программирования на VBA в Office состоит в том, что практически любую операцию, которую можно выполнить с помощью мыши, клавиатуры или диалогового окна, можно также проделать с помощью VBA. Более того, если операцию удалось осуществить с использованием VBA однажды, ее можно легко повторить сотню раз. По сути, автоматизация повторяющихся задач — это одно из наиболее распространенных применений VBA в Office.

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

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

Начиная с 1993 года, в состав Excel входит VBA.

Объектная модель
Разработчики организуют объекты программирования в виде иерархии, и такая иерархия называется объектной моделью приложения. В Word, например, есть объект верхнего уровня, приложения Application, который содержит объект Document. Объект Document содержит объекты Paragraph и т. д. Объект Worksheet в Excel, Document в Word, Presentation в PowerPoint. В объектных моделях приблизительно отражено то, что вы видите в пользовательском интерфейсе. Они являются концептуальной картой приложения и его возможностей.
Определение объекта называется классом. С технической точки зрения класс это описание или шаблон, используемый для формирования или создания экземпляра объекта.

его методы. Изменение свойства приводит к модификации определенной характеристики внешнего

вида или поведения объекта. Вызов одного из методов объекта приводит к выполнению какого-либо действия.
Понятие объекта в VBA отвечает общепринятому понятию объекта. То есть, объект - это объединение данных с кодом, который предназначен для их обработки, в единое целое. Во время работы из VBA в Microsoft Excel можно создавать и использовать уже знакомые нам объекты приложений - формы, командные кнопки, флажки, переключатели и др. Все объекты одного типа принадлежат к определенному классу.
Объекты характеризуются свойствами. Свойства представляют собой атрибуты объектов, которые определяют их характеристики, внешний вид. К каждому объекту можно применить определенные методы. Метод - это действия, которые могут выполняться по отношению к объектам определенного класса. В VBA также не изменяется смысл понятия "событие". Событие - это действие, которое выполнено по отношению к определенному объекту, для которого можно запрограммировать процедуру обработки.

VBA нужно ознакомиться с объектами приложения Excel. Объектная модель Microsoft

Excel представляет собой иерархию объектов, которые подчинены объекту Application, который соответствует приложению Excel. Объект Application - это главный корневой объект в иерархии объектов, он имеет большое количество свойств и методов. Объекту Application подчинены другие объекты. Все визуальные объекты, такие как рабочая книга - Workbook, рабочий лист - Worksheet, диапазон ячеек - Range, диаграмма - Chart, являются объектами VBA.
Несколько объектов, которые обычно являются объектами одного класса, часто группируются, образовывая коллекцию объектов. Коллекция объектов является объектом, в котором содержится несколько других объектов. Например, коллекция Workbooks содержит все открытые рабочие книги - объекты Workbook, коллекция Worksheets включает все листы определенной рабочей книги - объекты Worksheet. Каждый элемент коллекции нумеруется, к нему можно обратиться по номеру или имени. Например, Worksheets(1) - это первый лист активной рабочей книги, Worksheets("Лист3") - лист активной рабочей книги с именем Лист3.

имя, после точки ввести имя метода или свойства. Например, в

строке кода
Application.Quit
к объекту Application применяются метод Quit - завершается работа программы Microsoft Excel.
Для обращения к объектам с целью изменения их свойств или применения к ним методов, нужно определить имена объектов в определенной иерархии, отделяя имена, названия свойств и методов точками. Рассмотрим как пример такую строку кода :
Application.Workbooks("Книга2").Worksheets("Лист1").Name = "Отчет"
В строке кода осуществляется обращение к приложению Microsoft Excel, к рабочей книге с именем "Книга2", а также к рабочему листу с именем "Лист1", свойство Name которого изменяется - присваивается новое имя "Отчет" листа рабочей книги.
В VBA свойства и методы объектов могут возвращать другие объекты. В этом случае, для доступа к свойству объекта следует отметить имя свойства.

Visual Basic for Application, просмотра и редактирования макросов используется редактор

VBA. Для использования редактора VBA и других инструментов разработки используется лента команд Разработчик. Для вывода ленты Разработчик нужно:
1. Вызвать команду меню Файл - Параметры - Настройка ленты.
2. В окне диалога Параметры Excel (см. рис. 1) :
- в списке Выбрать команды выбрать часто используемые команды
- в списке Настройка ленты выбрать Основные вкладки
- включить флажок Разработчик.
В итоге в окне MS Excel появится вкладка Разработчик.

вкладке Разработчик
- нажать клавиши Alt + F11.
Интерфейс редактора Visual Basic for

Application включает основные компоненты (см. рис. 2) :
- окно проекта Project - VBA Project;
- окно редактирования кода;
- окно свойств Properties;

- окно просмотра объектов Object Browser;
- окно редактирования форм UserForm.

Окно проекта Project - VBA Project в редакторе VBA можно вывести командой меню View - Project Explorer или нажатием на кнопке Project Explorer на панели инструментов Standard. В окне выводится дерево объектов приложения Microsoft Excel. Для каждой рабочей книги, которая открыта в среде Excel, в дереве содержатся элементы, которые используются для создания и редактирования модулей для листов рабочей книги, для книги в целом, для каждой формы пользователя, которая создана в проекте, для макросов.

дереве проекта или командой меню View - Code (открывается окно

кода для объекта, который выбран в дереве). Окно редактирования кода используется для создания подпрограмм приложения - процедур и функций, редактирования макросов.
В окне свойств перечислены установки свойств объектов - выбранной формы, элементов управления, листов рабочей книги, рабочей книги в целом. Это окно можно применять для просмотра и изменения свойств объектов. Для выведения окна свойств следует выполнить команду меню View - Properties Window или воспользоваться инструментом Properties Window на панели инструментов Standard.
Окно просмотра объектов Object Browser отображается в редакторе VBA после выполнения команды меню View - Object Browser или нажатия на инструменте Object Browser на панели инструментов Standard. В этом окне содержится список всех объектов, которые существуют в системе и которые можно использовать при создании проекта. Можно выбрать любой объект из списка Classes и в списке Members будет отображаться перечень свойств и методов для данного объекта. Значение свойства или метода можно просмотреть в строке состояния окна Object Browser.

Форма к проекту добавляется выполнением команды Insert - UserForm. В

итоге к проекту добавляется пустая форма с панелью элементов (рис. 4). Используя панель элементов, можно поместить в форму необходимые элементы управления - командные кнопки, этикетки, текстовые поля, флажки и тому подобное, с помощью окна свойств определить свойства формы и каждого элемента управления. Панель элементов становится активной после активизации формы. Если панель элементов закрыта, то вывести ее в окне редактора VBA можно с помощью команды меню View - Toolbox.

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

функций Microsoft Excel. Например, можно самостоятельно разработать функцию для расчета суммы реализации товара клиенту с учетом количества проданных товаров, скидки постоянным клиентам и других факторов. Обратиться к такой функции можно будет с помощью мастера функций Microsoft Excel, после определения всех необходимых аргументов, с помощью функции, созданной средствами VBA, можно будет получить нужный результат.
Для создания новой функции нужно:
- активизировать окно редактора VBA;
- добавить к проекту новый модуль командой меню Insert - Module (обратите внимание на то, что к дереву элементов проекта будет добавлена новая ветка Modules, к которой добавляются модули проекта, - в нашем случае Module1);
- в окне кода модуля следует ввести новую функцию.
Функции в VBA создаются, как и в среде Visual Basic. Создание функции может начинаться с определения статуса функции, дальше должно следовать зарезервированное слово Function, после которого определяется имя функции и в круглых скобках список аргументов функции. После списка аргументов может быть определен тип значения, которое возвращает функция. Параметрами функции являются данные, которые будут определяться как аргументы функции. Это могут быть адреса ячеек, числа, блоки ячеек. Синтаксис функций следующий:
[Public | Private ] Function <имя функции> [(список аргументов)] [As тип]
[операторы][имя функции = выражение]
End Function

использовать функцию при работе с другими рабочими книгами при условии,

что книга, в проекте которой создана функция, открыта. Необязательный параметр Private ограничивает применение функции лишь процедурами одного и того же модуля.
Обязательным параметром является имя функции, после имени определяется список аргументов. В списке аргументов параметры функции отделяются запятыми, могут отмечаться лишь именами, а можно определять имена аргументов и их типы (например, X As Integer).
Необязательный параметр [As тип] определяет тип значения, которое возвращает функция (Integer, String, Byte или др.). Если данный параметр пропущен, то функция возвращает значение типа Variant. Обязательно в теле функции присвоить имени функции значение, которое она должна возвращать.
При разработке функции можно применять операторы присваивания, условный оператор If, оператор цикла For . . . Next, другие операторы языка Visual Basic После создания функции можно активизировать рабочую книгу Microsoft Excel и с помощью мастера функций поместить в любую ячейку формулу с применением новой функции. Созданная таким способом функция будет отнесена к категории функций Определенные пользователем.

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

можно ускорить расчет скидок, который вычитаются из суммы оплаты. Допустим, алгоритм вычисления суммы следующий:
если сумма меньше или равна 1000 руб. , то скидка не применяется;
если сумма меньше или равна 2000 руб., то от суммы, которая превышает 1000 руб., вычитается 10%; (max 100 руб.)
если сумма меньше или равна 3000 руб., то от суммы, которая превышает 2000 руб., вычитается 15%, плюс вычет от суммы 2000 руб.; (max 100 руб. + 150 руб.)
если сумма больше 3000 руб., то от суммы, которая превышает 3000 руб., вычитается 20% плюс прежние вычеты. (max 100 руб. + 150 руб. + напр. для 4000 руб. max 200 руб.)
Для создания функции нужно загрузить табличный процессор Excel, перейти к редактору VBA (команда меню Сервис - Макрос - Редактор Visual Basic), выполнить команду меню Insert - Module для добавления к проекту нового модуля, в окне кода модуля ввести код функции СКИДКА_СЛОЖНАЯ (имя функции СКИДКА_СЛОЖНАЯ, так как просто СКИДКА уже есть в Excel):

= 0
Else
If x

* 0.1
Else
If x <= 3000 Then
N = (x - 2000) * 0.15 + const1
Else
N = (x - 3000) * 0.2 + const2 + const1
End If
End If
End If
СКИДКА_СЛОЖНАЯ = N
End Function

текущей рабочей книги и с помощью мастера функции создать формулу.

Функцию можно выбрать, раскрыв категорию Определенные пользователем. Таблица с применением функции СКИДКА_СЛОЖНАЯ может выглядеть так:

также диапазон ячеек.
Если аргументом функции является диапазон ячеек, то

VBA работает с ним как с массивом, индексация элементов которого начинается с 1. После имени массива - аргумента функции - в круглых скобках определяются номер строки и через запятую номер столбца. Чтобы узнать сколько строк и столбцов содержится в диапазоне можно использовать свойства Rows и Columns, которые отвечают коллекции, которая состоит из всех строк или столбцов диапазона. Поскольку каждая коллекция характеризуется свойством Count - количество элементов в коллекции, то определить общее количество строк, колонок в диапазоне, который является аргументом функции, не сложно.
Например, если аргументом функции является диапазон с именем Block, то можно поместить в переменные x и y значения количества строк и столбцов в блоке ячеек:
x = Block.Rows.Count
y = Block.Columns.Count
Чтобы обратиться к ячейке, которая является верхним левым углом блока, нужно выделить имя диапазона и индексы элементов массива Block(1, 1), ячейка, которая является правым нижним углом блока, отвечает элементу массива Block(x, y).

основе данных о реализации товаров в текущем периоде может быстро

определить размер скидки, которая предоставляется конкретному покупателю. Тем покупателям, которые в течение определенного периода приобрели товаров больше или ровно на 10000 руб., предоставляется скидка в размере 3%. Если покупатель приобрел товаров больше или ровно на 5 000 руб., он может получить скидку в 1,5%. Если покупатель приобрел товаров меньше чем на 5 000 руб., однако осуществил больше трех операций по закупки товара, он может рассчитывать на скидку в 1%. Другим покупателям скидка не предоставляется. Для автоматизации определения размера скидки данному покупателю можно создать следующую функцию:

0
k = 0
For i = 1 To N

If ВсеПокупатели(i, 1) = Покупатель Then
k = k + 1
s = s + СуммыРеализации(i, 1).Value
End If
Next i
If s >= 10000 Then
Discont2 = 3
Else
If s >= 5000 Then
Discont2 = 2
Else
If k > 3 Then
Discont2 = 1
Else
Discont2 = 0
End If
End If
End If
End Function

ячейки, в котором содержится название покупателя, для которого определяется размер

скидки;
ВсеПокупатели - блок ячеек электронной таблицы, в котором расположены наименования покупателей, блок ячеек включает ячейки одного столбца и нескольких строк;
СуммыРеализации - блок ячеек электронной таблицы, в котором расположенные суммы реализации товаров по каждой операции, блок ячеек включает ячейки одного столбца и нескольких строк.
Поскольку блоки ячеек, которые являются аргументами функции, содержат по одной ячейке в столбцах и несколько ячеек в строках, нужно определить лишь количество ячеек в строке хотя бы одного из блоков (блоки должны быть пропорционального размера). С этой целью используется переменная N, которой присваивается значение ВсеПокупатели.Rows.Count - общее количество строк в массиве ячеек с именем ВсеПокупатели.
Переменным s - итоговая сумма реализации и k - количество операций присваивается значение 0.
Дальше выполняется обработка значений всех ячеек массивов. Индексация элементов массивов осуществляется от 1 к N. Внутри цикла происходит проверка: если название очередного элемента массива ВсеПокупатели совпадает со значением аргумента функции Покупатель, то на 1 увеличивается общее количество операций и накапливается значение в переменной s - итоговая сумма реализации. При расчете итога осуществляется обращение к очередному элементу массива СуммыРеализации, а именно к значению свойства Value элементов - содержанию ячеек электронной таблицы.
По завершению работы цикла в зависимости от значений переменных s и k присваивается новое значение имени функции Discont2, это значение функция и будет возвращать.

мастеру функций, выбрать функцию Discont2 из категории Определенные пользователем, определить,

например так, аргументы функции :

создана сводная таблица, в которой содержатся данные об общей сумме

реализации и количестве операций по каждому клиенту. Для того, чтобы не определять размеры скидок вручную в ячейках C13, C14, C15, C16 созданы формулы с применением функции Discont2. Обратите внимание на то, что после создания формулы с функцией в ячейке C13 перед автозаполнением ее нужно отредактировать - изменить адреса ячеек в аргументах - диапазонах на абсолютные для того, чтобы во время автозаполнения они не индексировались.

дает возможность для автоматизации расчетов на листах рабочих книг использовать

элементы управления - командные кнопки, переключатели, флажки и др. Для создания этих объектов используется инструмент Вставить - Элементы управления на ленте Разработчик.

классов. При работе с элементами управления используется режим конструктора для

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

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

Например, после двойного щелчка на командной кнопке можно начать создание процедуры, которая будет обрабатывать событие Click - щелчок левой кнопкой мыши на командной кнопке. С помощью поля со списком Procedure можно выбрать другое событие. Процедура обработки события будет создаваться в окне редактора VBA и будет связана с тем листом рабочей книги, на котором расположен элемент управления.
При создании процедур можно изменять, использовать разные свойства разных объектов. Причем следует помнить, что в VBA свойства объектов могут возвращать другие объекты. Например, свойство Selection объекта Application возвращает выделенный диапазон ячеек.
Можно работать с объектом Range, которому отвечает конкретная ячейка или диапазон ячеек. Объект Range характеризуется свойством Value. Свойство Value - это содержимое ячейки. Например, для записи в ячейку D3 числа 4 можно подать команду:
Range("D3").Value = 4
С помощью следующей команды во все ячейки диапазона D3 : E5 записывается значение 0:
Range("D3: E5").Value = 0
К объекту Range можно применить метод Select, который приводит к выделению ячеек определенного диапазона. Для выделения ячеек диапазона А2:А5 можно выполнить команду:
Range("a3: a5").Select

Range, Selection Cells(i, j), которое возвращает объект - определенную ячейку

листа рабочей книги. В круглых скобках определяется адрес ячейки, причем i - номер строки, j - номер столбца. Объект Cells(i, j) характеризуется свойствами:
Value - содержание ячейки;
NumberFormat - числовой формат;
Formula - содержание ячейки, формула в обычном виде;
Font - шрифт символов;
FormulaR1C1 - формула в формате R1C1.

Можно привести примеры изменения свойств объекта Cells(i, j) :

так и относительные относительно текущей ячейки адреса ячеек в формуле.

При использовании абсолютных адресов ячеек можно определять номера строк (R - row) и номера столбцов (C - column). Так, например, в строке кода
Cells(5, 1).FormulaR1C1 = "=R1C1+R2C1"
в ячейку А5 вводится формула =$A$1+$A$2.
Можно также формировать формулу с адресами ячеек, которые задаются относительно текущей, активной ячейки. Относительные значения определяют сдвиг на определенное количество строк, столбиков, значение сдвига задается всегда в квадратных скобках. Так, в строке кода
Cells(3, 2).FormulaR1C1 = "=R[- 2]C+R[- 1]C"
в ячейку В3 вводится формула =В1+В2. Адрес ячейки В1 определяется так: ячейка, которая расположена на 2 строки выше текущей ячейки В3 и в том же столбце.
Использование элементов управления на рабочих листах можно рассмотреть на примере кнопки "Расчет сумм", с помощью которой можно рассчитать суммы по строкам и столбецм для всех ячеек выделенного диапазона. То есть если выделить, например, блок ячеек B2:C3, то в ячейку D2 будет записана сумма значений, которая находится в ячейках B2:C2, в ячейку D3 - сумму значений диапазона B3:C3, в ячейку В4 - сумму значений диапазона B2 :В3 и тому подобное.

рабочей книги командную кнопку с помощью инструмента Вставить - Элементы

управления - Кнопка на ленте Разработчик;
в окне Назначить макрос объекту определить имя кнопки и события, с которым будет связан макрос (процедура обработки события) Расчет_сумм_Щелчок; нажмите Создать;
в окне VBA введите текст макроса (процедуры), согласно предлагаемой таблице;
на листе выделите и переименуйте кнопку в Расчёт сумм.

кнопка "Расчёт сумм", выйти из режима конструктора, выделить любой диапазон

ячеек и нажать на кнопке "Расчёт сумм" для проверки правильности выполнения расчетов.
В примере был выделен диапазон C4:D5 и нажатак нопка Расчёт сумм. Результат на рисунке.

по таблице, в которую введены данные для анализа реализации товаров.

Таблица имеет следующую структуру:

процента отклонения от плана реализации по каждой товарной группе. Независимо

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

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

для расчета показателей по документу "Расходная накладная". Допустим, что на

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

с помощью которого на любом листе книги можно быстро приступить

к созданию предметной части расходной накладной.
Менеджерами предприятия могут предоставляться скидки покупателям - 1% или 2% для постоянных покупателей. В зависимости от предоставленной скидки рассчитывается цена каждого товара со скидкой, суммы реализации товаров, итоговые значения по документу.
Для начала расчета пользователь может нажать на соответствующем инструменте на панели инструментов после чего появится форма:

новую форму с помощью команды меню Insert - UserForm. В

результате выполнения команды к дереву основных объектов проекта будет добавлена ветка Forms, к которой принадлежит объект UserForm1 - окно новой формы пользователя. Когда окно формы активно, с помощью панели элементов можно создавать объекты в форме и определять их свойства с использованием окна свойств Properties. Для разработки формы, с помощью которой можно определить размер скидки и осуществить расчет показателей документа "Расходная накладная", нужно создать объекты формы и определить их свойства по описанию:

– щелчёк левой кнопкой мыши на командных кнопках. Для этого

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

Private Sub cmdEsc_Click()

End Sub

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

Private Sub cmdEsc_Click()
End
End Sub

Click для командной кнопки "Расчёт". Процедуру нужно создать по описанию:

нужно к проекту добавить новый модуль командой меню Insert -

Module, в окне модуля создать процедуру - макрос, который будет активизировать форму frmCalc. Это можно осуществить с помощью метода Show, который будет применяться по отношению к объекту frmCalc. Макрос будет выглядеть так:

Sub VN()
frmCalc.Show
End Sub

Для активизации формы нужно выполнить макрос VN() с помощью инструмента Макросы на ленте Разработчик.
В окне инструмента Макросы можно настроить Параметры макроса, привязав его вызов, например, к комбинации клавиш.

выполнена соответствующая процедура для расчета итогов по информационным строкам и

документу в целом. После выполнения процедуры, которая связана с командной кнопкой «Расчёт», таблица будет выглядеть следующим образом (выбрана скидка 3 %):