Visual Basic for Applications (vba). Лабораторные работы. Часть 1 Введение

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

Запись макрорекордером неоднократно выполняемых команд – макросов - повышает точность и скорость работы. Макрорекордер (или просто «рекордер») записывает все действия пользователя, включая ошибки и неправильные запуски. Когда программа воспроизводит макрос, она выполняет каждую записанную рекордером команду точно в такой последовательности, в которой их выполнял пользователь во время записи. Записанный макрос сохраняется как процедура VBA.

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

Тема № 1: «Запись новых макросов в Excel. Выполнение макросов»

1. Запись макроса с помощью макрорекордера.

2. Сохранение записанного макроса.

3. Редактор Visual Basic.

4. Окна редактора VB.

5. Панель инструментов редактора VB.

6. Способы записи ссылок на ячейки.

7. Пример 1.

8. Задание №1. Варианты задания.

Вместо повторения последовательности команд каждый раз, когда необходимо выполнить какую-либо задачу, можно создать макрос (macro), который будет автоматически выполнять эту последовательность. Макросы позволяют вводить одиночную команду, выполняющую ту же задачу, для реализации которой было бы необходимо вводить несколько команд вручную.

1. Для создания макроса легче всего использовать автоматический режим его создания с помощью макрорекордера, вызываемый из главного меню командами Сервис (или вкладка Вид)->Макрос. Если выбрать пункт Начать запись…, то откроется диалоговое окно, позволяющее задать имя макроса и, при желании, комбинацию клавиш, с помощью которой он также может быть вызван в обход пункта меню Макросы. Далее необходимо выполнить все действия, которые вы хотите, чтобы были выполнены с помощью макроса. Запишите действия и остановите макрорекордер.

Остановить запись макроса можно кнопкой Остановить запись дополнительно открывшейся панели инструментов или через аналогичный пункт главного меню Сервис (или вкладка Вид)->Макрос.

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

Макросы сохраняются в специальной части файла данных, называемой Modules (модули). Модуль VBA содержит исходный код (source code) макроса – текстовое представление инструкций. Каждый файл рабочей книги Excel может не содержать модулей или содержать один или несколько модулей. Модули, сохраняемые в одной рабочей книге Excel, имеют общее название Project (проект).

Когда Excel создает модуль, в котором сохраняется записанный макрос, модулю присваивается имя ModuleN, где N – это количество модулей, созданных для определенной рабочей книги во время текущего сеанса работы.

3. Для просмотра модулей, сохраненных в определенной рабочей книге (и исходного кода макроса, который они содержат), необходимо использовать компонент Visual Basic Editor (Редактор Visual Basic или Редактор VB). Этот компонент предоставляет инструментальные средства, которые используются для создания новых модулей, просмотра содержимого существующих модулей, создания и редактирования исходного кода макроса, создания пользовательских диалоговых окон и выполнения других задач, относящихся к написанию и обслуживанию программ на VBA. Редактор VB содержит одни и те же возможности в Excel, Word и Access.

Для перехода в редактор нужно выполнить команду Сервис ->Макрос ->Редактор Visul Basic (или вкладка Разработчик ->Visul Basic), либо нажать клавиши <Alt>+<F11>. Если вкладка Разработчик недоступна, щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel. В категории Личная настройка в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Разработчик" на ленте и нажмите кнопку ОК.

4. В окне редактора Visual Basic можно работать одновременно с работой в приложении, откуда этот редактор был вызван. Общий вид интегрированной среды разработки приложений IDE приведен на рисунке.

Среда состоит из нескольких компонентов

Всего в редакторе Visual Basic 9 окон. Найти нужное окно, можно выбрав его в меню View.

1.Project Explorer (Окно проекта) — окно проводника проекта. Содержит дерево-диаграмму открытых в данный момент файлов (рабочих книг) и объектов, содержащихся в этих файлах. По умолчанию оно открыто. Project Explorer можно использовать для перехода к различным модулям и другим объектам в проекте VB при помощи трех кнопок на панели инструментов этого окна: View Code (Программа), View Object (Объект) и Toggle Folders (Папки).

2. Properties Window (Окно свойств) — окно со свойствами объекта текущего выбора. Через него можно просмотреть свойства элемента управления или компонента проекта и их изменить.

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

3.UserForm — окно формы. Появляется тогда, когда вы редактируете пользовательскую форму из окна дизайнера формы.

4. Toolbox — панель инструментов управления. Из него можно добавить элементы управления в форму или в документ. Появляется вместе с окном дизайнера форм.

5. Code Window — окно программного кода, в котором вы можете просматривать, редактировать или создавать исходный код VBA. При открытии программного модуля окно открывается автоматически.

ВрежимеFull Module View весь исходный код макроса в модуле отображается сразу в прокручивающемся текстовом окне, а макрос отделяется от другого макроса серой линией. Редактор VB позволяет также просматривать содержимое модуля в режиме Procedure View (представление процедуры). При этом видимым является исходный код только одного макроса.

Чтобы выбрать режим просмотра, щелкайте кнопки в нижнем левом углу Code Window.

6.Watch — окно контролируемых выражений. Используется во время отладки для отслеживания значений выбранных переменных программы и выражений.

7. Locals — окно локальных переменных. Нужно для отслеживания во время отладки значений переменных текущей процедуры.

8.Object Browser — обозреватель объектов. Отображает

все имеющиеся модули и объекты в списке Classes (Классы). Для этого выберите проект в поле Project/Library, введите имя макроса в поле Search Text (Образец поиска). Если Object Browser обнаружил макросы, они отображаются в списке Search Results (Результаты поиска). В противном случае, в этом списке отображается сообщение No items found (Элементы не найдены).

9.Immediate — окно, дающее возможность при отладке выполнить отдельные строки программного кода и немедленно получить результат.

5. Всем основным командам меню соответствуют кнопки на панели инструментов.

• Загрузить проект (File-Import File)

• Сохранить проект (File-Save)

• Вырезать выделенный фрагмент и перенести его в буфер обмена (Edit-Cut)

• Копировать выделенный фрагмент в буфер обмена (Edit-Copy)

• Вставить фрагмент из буфера обмена (Edit-Paste)

• Найти фрагмент в процедуре, модуле, проекте (Edit-Find)

• Откат на одну операцию назад (Edit-Undo)

• Переход к последующей операции (Edit-Redo)

• Запустить программу-проект (Run-Run Macro)

• Приостановить выполнение программы-проекта (Run-Break)

• Прекратить выполнение программы-проекта (Run-Reset)

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

При использовании абсолютной адресации в стиле R1C1 после символов R и C указывается номер строки и столбца. Так, например, ячейка $B$3 имеет адрес R3C2.

При использовании относительной адресации в стиле R1C1 после обозначения строки или колонки в квадратных скобках указывается смещение по отношению к текущей ячейке. Так, например, если данные находятся в ячейке B3, а ссылка на нее программируется в ячейке А5, то в формуле она запишется как R[-2]C[1]. Эта запись может интерпретироваться как обращение к ячейке, находящейся на две строки выше и одну колонку правее текущей. Соответственно запись R[2]C[-1] означает обращение к ячейке на две строки ниже и одну колонку левее (по отношению к активной ячейке A5 такая ячейка не существует).

Пример 1.

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

Текст макроса с именем Расчет_заработной_платы имеет вид:

Sub Расчет_заработной_платы()

Range("C2").Select

ActiveCell.FormulaR1C1 = "=RC[-1]*R7C3"

Range("D2").Select

ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"

Range("C2:D2").Select

Selection.AutoFill Destination:=Range("C2:D5"), Type:=xlFillDefault

Range("C6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Range("D6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

End Sub

Оператор Sub представляет собой заголовок процедуры. Имя процедуры совпадает с именем макроса Sub Расчет_заработной_платы().

Оператор Range("C2").Select создан системой в виде выражения, которое содержит в терминологии VBA свойство Range в сочетании с методом Select. В нашем примере аргумент свойства представляет собой ссылку на ячейку в стиле A1, с которой началось программирование макроса. Фактически эта строка программы представляет собой набор действий по активизации (выделению) ячейки C2 рабочего листа Excel.

Оператор ActiveCell.FormulaR1C1="=RC[-1]*R7C3" заносит в активную ячейку формулу для вычисления величины подоходного налога. В формуле используется стиль ссылок R1C1, причем ее первый операнд задан в относительной адресации, а второй в абсолютной.

Операторы Range("D2").Select и ActiveCell.FormulaR1C1="=RC[-2]-RC[-1]" программы задают другую активную ячейку и заносят в нее формулу для вычисления суммы к выдаче.

Оператор Range("C2:D2").Select выделяет диапазон ячеек листа Excel, после чего выделенные ячейки копируются во все содержащие фамилии сотрудников строки таблицы Selection.AutoFill Destination:=Range("C2:D5"), Type:=xlFillDefault

Для расчета суммы уплачиваемых налогов делается активной предназначенная для этого ячейка рабочего листа Range("C6").Select и в нее заносится формула, содержащая функцию суммирования данных выделенных ячеек. ActiveCell.FormulaR1C1="=SUM(R[-4]C:R[-1]C)". Система использовала относительную адресацию в формате R1C1.

Аналогичная операция проводится и с ячейкой, предназначенной для хранения общей суммы к выдаче: Range("D6").Select и ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Оператор End Sub заканчивает действие процедуры (макроса).