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

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

Информатика. Лекция 9

2. Использование Visual Basic for Application в Microsoft Excel 2016

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

3.

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

4.

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

5.

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

6.

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

7.

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

8.

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

9.

10.

Для активизации редактора Visual Basic можно:
воспользоваться инструментом Visual Basic на вкладке Разработчик
нажать клавиши 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, в дереве
содержатся элементы, которые
используются для создания и
редактирования
модулей
для
листов рабочей книги, для книги в
целом,
для
каждой
формы
пользователя, которая создана в
проекте, для макросов.

11.

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

12.

13.

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

14.

15.

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

16.

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

17.

Рассмотрим простой пример создания функции пользователя, в которой аргументом функции
является адрес ячейки. Например, нужно разработать функцию, с помощью которой можно
ускорить расчет скидок, который вычитаются из суммы оплаты. Допустим, алгоритм вычисления
суммы следующий:
если сумма меньше или равна 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):

18.

Function СКИДКА_СЛОЖНАЯ(x)
const1 = 100
const2 = 150
If x <= 1000 Then
N=0
Else
If x <= 2000 Then
N = (x - 1000) * 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

19.

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

20.

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

21.

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

22.

Function Discont2(Покупатель, ВсеПокупатели,
СуммыРеализации)
N = ВсеПокупатели.Rows.Count
s=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

23.

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

24.

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

25.

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

26.

4 Автоматизация расчетов на рабочем листе
Язык Visual Basic for Application дает возможность для автоматизации расчетов на листах рабочих книг
использовать элементы управления - командные кнопки, переключатели, флажки и др. Для создания этих
объектов используется инструмент Вставить - Элементы управления на ленте Разработчик.

27.

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

28.

Двойной щелчок левой кнопкой мыши на объекте дает возможность создать процедуру для обработки события
по умолчанию для объектов данного класса. Например, после двойного щелчка на командной кнопке можно
начать создание процедуры, которая будет обрабатывать событие 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

29.

Во время создания программ часто используется свойство объектов Application, Worksheet, Range, Selection
Cells(i, j), которое возвращает объект - определенную ячейку листа рабочей книги. В круглых скобках
определяется адрес ячейки, причем i - номер строки, j - номер столбца. Объект Cells(i, j) характеризуется
свойствами:
Value - содержание ячейки;
NumberFormat - числовой формат;
Formula - содержание ячейки, формула в обычном виде;
Font - шрифт символов;
FormulaR1C1 - формула в формате R1C1.
Можно привести примеры изменения свойств объекта Cells(i, j) :
Cells(5, 6).NumberFormat = "0.00%"
В ячейку А1 записывается число 23.
В ячейку С20 записывается текст «Всего:».
Для ячейки В4 определяется числовой формат с двумя
знаками после десятичной точки.
Для ячейки F5 определяется процентный формат.
Cells(3, 1).Formula = "=A1 + A2"
Cells(3, 1).Font.Bold = True
В ячейку А3 записывается формула =A1 + A2.
Для ячейки А3 определяется полужирный шрифт.
Cells(3, 1).Font.Size = 16
Для ячейки А3 определяется размер шрифта 16 пунктов.
Cells(3, 1).Font.Color = QBColor(9)
Для ячейки А3 определяется синий цвет символов.
Cells(1, 1).Value = 23
Cells(20, 3).Formula = «Всего:»
Cells(4, 2).NumberFormat = "0.00"

30.

Ввод формулы в формате FormulaR1C1 дает возможность определить как абсолютные, так и
относительные относительно текущей ячейки адреса ячеек в формуле. При использовании абсолютных
адресов ячеек можно определять номера строк (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 и тому подобное.

31.

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

32.

В окне кода ввести содержание процедуры:
Данные в строке кода
Sub Расчет_сумм_Щелчок ()
n = Selection.Rows.Count
m = Selection.Columns.Count
Комментарий
Заглавие процедуры, которая будет выполняться после нажатия на кнопке "Расчет сумм".
Переменной n присваивается значение количества строк в выделенном диапазоне ячеек.
Переменной m присваивается значение количества столбцов в выделенном диапазоне ячеек.
' Итоги по строкам
For i = 1 To n
s=0
For j = 1 To m
s = s + Selection.Cells(i, j).Value
Next j
Selection.Cells(i, m + 1).Value = s
Комментарий.
Для i, которая изменяется от 1 к n, производятся действия (для всех ячеек строки i ).
Переменной s присваивается значение 0.
Для j, которая изменяется от 1 к m, производятся действия (для всех ячеек столбца j).
В переменной s накапливается сумма значений ячеек определенной строки.
Конец цикла вычисления итогового значения по строке.
В ячейке с адресом - определенная строка и столбец m + 1 (ячейка влево от последней ячейки выделенного
диапазона) выводится значение суммы, что вычислено.
Next i
' Итоги по столбцам
For j = 1 To m + 1
Конец цикла вычисления итоговых значений по всем строкам.
Комментарий.
Для j, который изменяется от 1 к m+1, производятся действия (для всех ячеек столбца j). В цикле
обрабатываются значения для m+1 столбца потому, что суммы следует рассчитать и для ячеек, которые
являются итогами по строкам.
s=0
For i = 1 To n
s = s + Selection.Cells(i, j).Value
Next i
Selection.Cells(n + 1, j).Value = s
Переменной s присваивается значение 0.
Для i, который изменяется от 1 к n, производятся действия (для всех ячеек строки i ).
В переменной s накапливается сумма значений ячеек определенного столбца.
Конец цикла вычисления итогового значения по столбцу.
В ячейке с адресом - строка n + 1 и столбец j (ячейка ниже последней ячейки выделенного диапазона)
выводится значение вычисленной суммы.
Next j
End Sub
Конец цикла вычисления итоговых значений по всем столбцам.
Конец процедуры.

33.

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

34.

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

35.

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

36.

С кнопкой "Расчёт" следует связать следующую процедуру:
Данные в строке кода
Private Sub cmdR_Click()
k=0
For i = 4 To 60
If Cells(i, 3).Value <> 0 Then
k=k+1
Cells(i, 4).FormulaR1C1 = "=RC[-1]- RC[-2]"
Cells(i, 5).FormulaR1C1 = "=(RC[-2]- RC[-3])/RC[-3]*100"
Cells(i, 5).NumberFormat = "0.00"
End If
Next i
Cells(k + 4, 1).Formula = “Всего:”
Комментарий
Заглавие процедуры, которая будет выполняться после нажатия на кнопке "Расчёт".
Переменной k, которая используется для подсчета заполненных строк таблицы, присваивается значение 0.
Для i от 4 до 60 производятся действия в цикле. 4 это номер первой заполненной строки таблицы, 60 номер максимальной
возможной заполненной строки.
Если для строки и фактическое значение реализации не равняется 0, то ..
Переменная k увеличивается на 1.
В ячейку Cells(i, 4) - D4, D5.. для выведения суммы отклонения фактической реализации от прогнозного значения
записывается формула =Cі - Bi (=C4 - B4, =C5 - B5..). Операнды формулы определяются так: ячейка той же строки, что и
определенная (D4, D5..), и столбца с номером - 1 - столбик слева минус ячейка той же строки и столбца с номером - 2 - на 2
столбика слева.
В ячейку Cells(i, 5) для выведения проценту отклонения фактической реализации от прогнозного значения записывается
формула записывается формула =(Cі - Bі)/Bі*100 (=(C4 - B4)/B4*100..).
Для ячейки, в которой выводится процент отклонения, определяется числовой формат с двумя знаками после десятичной
точки.
Конец оператора If.
Конец цикла для расчета отклонений и определения количества заполненных строк таблицы.
В ячейке с адресом строка - k + 4, столбик 1 выводится текст «Всего:".
Cells(k + 4, 3).FormulaR1C1 = "=Sum(R4C3: R[-1]C3)"
В ячейку для расчета итогового значения по прогнозным суммам реализации вводится формула =СУММ(B4: B(k+4-1)).
Адреса ячеек диапазона : R4C2 - $B$4, R[- 1]C2 - ячейка предыдущей строки относительно ячейки (k + 4, 2) и второго
столбика.
В ячейку для расчета итогового значения по сумах фактической реализации вводится формула =СУММ(С4:С(k+4-1)).
Cells(k + 4, 4).FormulaR1C1 = "=Sum(R4C4: R[-1]C4)"
В ячейку для расчета итогового значения по сумме отклонений вводится формула ==СУММ(D4: D(k+4-1)).
Cells(k + 4, 5).FormulaR1C1 = "=(RC[-2]- RC[-3])/RC[-3]*100"
В ячейку для расчета итогового значения по проценту отклонения вводится формула =(C(k+4) - B(k+4))/B(k+4)*100.
Cells(k + 4, 5).NumberFormat = "0.00"
Для ячейки, в которой выводится итог по проценту отклонения, определяется числовой формат с двумя знаками после
десятичной точки.
Переменной n присваивается значение k + 4 в символьном формате.
Cells(k + 4, 2).FormulaR1C1 = "=Sum(R4C2: R[-1]C2)"
n = Trim(k + 4)
For i = 4 To k + 3
Cells(i, 6).FormulaR1C1 = "=RC[-3]/R" & n & "C3"
Cells(i, 6).NumberFormat = "0.00%"
Для и от 4 к k+3 (для всех информационных строк таблицы) производятся действия в цикле.
В ячейку очередной строки помещается формула =Ci/C(k+4).
Для ячеек, в которых выводится удельный вес реализации в общем объеме реализации, устанавливается процентный
формат.

37.

После создания процедуры можно воспользоваться кнопкой "Расчет". Таблица - пример после
применения командной кнопки будет выглядеть следующим образом:

38.

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

39.

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

40.

Для реализации поставленной задачи необходимо в окне редактора VBA создать новую форму с помощью команды меню
Insert - UserForm. В результате выполнения команды к дереву основных объектов проекта будет добавлена ветка Forms, к
которой принадлежит объект UserForm1 - окно новой формы пользователя. Когда окно формы активно, с помощью
панели элементов можно создавать объекты в форме и определять их свойства с использованием окна свойств Properties.
Для разработки формы, с помощью которой можно определить размер скидки и осуществить расчет показателей
документа "Расходная накладная", нужно создать объекты формы и определить их свойства по описанию:
Объект
Свойство
Наименование
Значение
Форма
Name
Caption
UserForm1
BackColor
Font
Командная кнопка Name
Caption
CommandButton
frmCalc
Расчёт показателей документа
Белый
Times New Roman, кириллица, обычный, 11 пунктов
cmdCalc
Расчёт
Командная кнопка Name
Caption
CommandButton
cmdEsc
Отмена
Группа
FraChoice
Изберите вид скидки для данного покупателя
белый
opt1
скидка не предоставляется
белый
opt2
скидка 1%
белый
opt3
скидка 2%
Frame
Переключатель
OptionButton
Переключатель
OptionButton
Переключатель
OptionButton
Name
Caption
BackColor
Name
Caption
BackColor
Name
Caption
BackColor
Name
Caption

41.

42.

43.

После создания формы можно начинать разработку процедур, которые обрабатывают события – щелчёк
левой кнопкой мыши на командных кнопках. Для этого нужно выполнить двойной щелчёк левой кнопкой
мыши по соответствующей кнопке. Чтобы создать процедуру, которая будет выполнена, если пользователь
нажмет на кнопке "Отмена", нужно дважды щелкнуть по этой кнопке. В итоге откроется окно кода с
заглавием и завершающей строкой процедуры :
Private Sub cmdEsc_Click()
End Sub
Внутри процедуры с помощью операторов языка VBA следует описать действия, которые должны
выполняться, если состоится событие Click с данным объектом cmdEsc. В данном случае следует завершить
работу с помощью оператора End. Процедура будет выглядеть так:
Private Sub cmdEsc_Click()
End
End Sub

44.

Аналогичным способом следует приступить к созданию процедуры, которая обрабатывает событие Click для командной кнопки "Расчёт".
Процедуру нужно создать по описанию:
Строка кода
Private Sub cmdCalc_Click()
If opt1.Value = True Then
Range("B6").Value = 0
Else
If opt2.Value = True Then
Range("B6").Value = 1
Else
Range("B6").Value = 2
End If
End If
k=0
For i = 8 To 50
If Cells(i, 4).Value <> 0 Then
Cells(i, 5).FormulaR1C1 = "=RC[- 1]*(100 - R6C2)/100"
Cells(i, 5).NumberFormat = "0.00"
Cells(i, 6).FormulaR1C1 = "=RC[- 3]*RC[- 2]"
Cells(i, 7).FormulaR1C1 = "=RC[- 4]*RC[- 2]"
k=k+1
End If
Next i
If k > 0 Then
Cells(k + 8, 1).FormulaR1C1 = «Всего:"
Cells(k + 8, 1).Font.Bold = True
Комментарий
Заглавие процедуры, которая выполняется каждый раз после щелчка левой кнопкой мыши на командной кнопке в форме cmdCalc (Расчет).
Если выбран переключатель opt1 (скидка не предоставляется), то
в ячейку B6 записывается число 0
иначе
если выбран переключатель opt2 (скидка 1%), то
в ячейку B6 записывается число 1
иначе (выбран третий переключатель)
в ячейку B6 записывается число 2.
Конец внутреннего оператора If.
Конец внешнего оператора If.
Переменной k присваивается значение 0. Переменная k будет использоваться в качестве счетчик информационных строк документа.
Для и, который изменяется от 8 (первая информационная строка документа) до 50 (максимально возможное количество строк) производятся действия.
Если содержимое ячейки Cells(i, 4) - ячейки і-й строки и 4-й столбца не равняется 0 (в документе есть очередная информационная строка), то
В ячейку Cells(i, 5) - ячейку і-й строки и 5-й столбца помещается формула расчета цены со скидкой =RC[- 1]*(100 - R6C2)/100.
Формулу можно прочитать так: =ячейка той же строки, что и ячейка с формулой, и столбца с сдвигом на - 1 (столбца, которая расположена слева) * на (100 - ячейка R6C2 при
абсолютной адресации это ячейка 6-й строки и 2-го столбца B6)/100.
Значение ячейки Cells(i, 5) превращается в числовой формат с двумя знаками после десятичной точки.
В ячейку Cells(i, 6) - ячейку і-го строки и 5-й столбца помещается формула расчета суммы без НДС и без скидки =RC[- 3]*RC[- 2].
Формулу можно прочитать так: =ячейка той же строки и столбца с сдвигом на - 3 (количество) * на ячейку той же строки и столбца с сдвигом на - 2 (цена без НДС).
В ячейку Cells(i, 7) - ячейку і-го строки и 7-й столбца помещается формула расчета суммы без НДС и со скидкой =RC[- 4]*RC[- 2].
Формулу можно прочитать так: =ячейка той же строки и столбца с сдвигом на - 4 (количество) * на ячейку той же строки и столбца с сдвигом на - 2 (цена без НДС со скидкой).
Значение k - количество информационных строк в документе увеличивается на 1.
Конец оператора If.
Переменная цикла увеличивается на 1.
Если k > 0, то производятся следующие действия. В ином случае, когда в документе нет информационных строк, процедура завершает свою работу.
В ячейку с адресом (строка k + 8, столбик 1) вводится новое содержание «Всего:".
Шрифт ячейки (k + 8, 1) превращается в полужирный.
Cells(k + 8, 6).FormulaR1C1 = "=Sum(R8C6: R[- 1]C)"
В ячейку с адресом (строка k + 8, столбец 6) вводится формула расчета итога по сумме без НДС - сумма ячеек блока R8C6 (строка 8, столбец 6 - первая информационная ячейка
данного показателя) : ячейка R[- 1]C, которая расположена на строку выше от ячейки (k + 8, 6) и в том же столбце .
Cells(k + 8, 7).FormulaR1C1 = "=Sum(R8C7: R[- 1]C)"
В ячейку (k + 8, 7) вводится формула для расчета суммы без НДС со скидкой.
Cells(k + 9, 5).FormulaR1C1 = "Общая сумма скидки :"
В ячейку (k + 9, 5) вводится текст "Общая сумма скидки :".
Cells(k + 9, 5).Font.Bold = True
Шрифт ячейки (k + 9, 5) превращается в полужирный.
Cells(k + 9, 7).FormulaR1C1 = "=R[- 1]C[- 1]- R[- 1]C"
В ячейку с адресом (строка k + 9, столбец 7) вводится формула расчета общей суммы скидки =R[- 1]C[- 1]- R[- 1]C.
Cells(k + 10, 5).FormulaR1C1 = «НДС:"
В ячейку (k + 10, 5) вводится текст «НДС:".
Cells(k + 10, 5).Font.Bold = True
Шрифт ячейки (k + 10, 5) превращается в полужирный.
Cells(k + 10, 7).FormulaR1C1 = "=R[- 2]C*0.18"
В ячейку с адресом (строка k + 10, столбик 7) вводится формула расчета суммы НДС =R[- 2]C*0.18.
Cells(k + 11, 5).FormulaR1C1 = "Всего с НДС :"
В ячейку (k + 11, 5) вводится текст "Всего с НДС :".
Cells(k + 11, 5).Font.Bold = True
Шрифт ячейки (k + 11, 5) превращается в полужирный.
Cells(k + 11, 7).FormulaR1C1 = "=R[- 3]C+R[- 1]C"
В ячейку (k + 11, 7) вводится формула расчета суммы реализации из НДС =R[- 3]C+R[- 1]C.
Range(Cells(k + 8, 6), Cells(k + 11, 7)).Font.Bold = True Шрифт диапазона ячеек с итоговыми значениями превращается в полужирный.
Range(Cells(k + 8, 6), Cells(k + 11, 7)).Font.Italic = True Шрифт диапазона ячеек с итоговыми значениями превращается в курсив.
End If
Конец If.
End
Завершение выполнения программы.

45.

По завершению создания формы и процедур, которые с ней связаны, нужно к проекту добавить новый
модуль командой меню Insert - Module, в окне модуля создать процедуру - макрос, который будет
активизировать форму frmCalc. Это можно осуществить с помощью метода Show, который будет
применяться по отношению к объекту frmCalc. Макрос будет выглядеть так:
Sub VN()
frmCalc.Show
End Sub
Для активизации формы нужно выполнить макрос VN() с помощью инструмента Макросы на ленте
Разработчик.
В окне инструмента Макросы можно настроить Параметры макроса, привязав его вызов, например, к
комбинации клавиш.

46.

47.

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