Структура программного кода VBA

Программа VBA представляет собой совокупность процедур и функций, размещённых, в зависимости от особенностей решаемой задачи, в одном или нескольких модулях (это специальные разделы Excel). Добавление модуля осуществляется по команде Insert→Module (в этом случае программный код составляется пользователем) или при создании макроса (когда программа создается автоматически). Созданному модулю присваивается стандартное имя Module1, Module2 и т.д.

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

Для удаления модуля следует: выделить имя модуля →- выбрать команду меню File → Remove Module→Щелкнуть в окне диалога на кнопке «Нет».

Макросы

Макрос - это последовательность команд и действий пользователя, записанная и хранимая внутри документа и исполняемая Excel так же, как пользователь производил эти действия. Последовательность действий пользователем выполняется один раз при записи макроса, затем автоматически производится Excel при каждом запуске макроса. Большинство команд, которые можно выполнить с использованием меню, клавиатуры или мыши, можно записать в макрос и выполнить их при необходимости. С помощью макроса можно строить числовые ряды, копировать таблицы, выполнять переход на нужные листы, сохранять информацию, работать в режиме ввода и т.д. Макрос записывается в виде процедуры с заданным именем в модуль. Имя макроса может состоять из букв, цифр и символа подчеркивания (_).

Для записи макроса необходимо: выполнить команду Сервис → Макрос→Начать запись; в окне диалога «Запись макроса» задать имя макроса и сочетание клавиш для быстрого выполнения макроса; затем выполнить нужные действия; после чего закончить запись макроса по команде меню Сервис → Макрос → Остановить запись. При записи макроса используются команды меню, кнопки панели инструментов, ярлычки листов, клавиатура.

Рассмотрим создание макроса на конкретном примере. Необходимо создать макрос с именем «Ввод», выполняющий переход на Лист1 в открытой рабочей книге, ввод в диапазон ячеек А1:С1 заголовков столбцов, в ячейки А2 и В2 исходных данных, в ячейку С2 - результат перемножения значений ячеек А2 и В2 и возврат на Лист2.

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

1. Создаем Книгу1.xls.

2. Выбираем команду меню Сервис →Макрос→Начать запись. Откроется диалоговое окно «Запись макроса».

3. Вводим с клавиатуры имя макроса «Ввод» и задаем сочетание клавиш нажатием на букву «в». В дальнейшем этот макрос будет выполняться при нажатии комбинации клавиш «Ctrl+в».

4. Щелкаем на кнопке «ОК». Если была активна панель инструментов «Visual Basic», на ней появится кнопка «Остановить запись», говорящая о том, что можно выполнять действия для макроса.

5. Переходим на Лист1, для этого щелкаем на ярлычке Лист1.

6. Выделяем ячейку A1 и вводим слово «Цена».

7. Выделяем ячейку B1 и вводим слово «Количество».

8. Выделяем ячейку С1 и вводим слово «Стоимость».

9. Выделяем ячейку A2 и вводим любое число.

10. Выделяем ячейку В2 и вводим любое число.

11. Выделяем ячейку С2 и вводим формулу =А2*В2. Щелкаем на кнопке «Остановить запись» или выбираем команду Сервис → Макрос→Остановить запись для завершения записи макроса. В результате наших действий формируется процедура. Чтобы ее просмотреть требуется выполнить команду меню Сервис→ Макрос → Макросы, выбрать в окне диалога макрос «Ввод» и нажать кнопку Изменить. После этого мы попадаем в режим редактора Visual Basic, где в специальном окне отображается программный код макроса (рис.8).

Рис.8. Вид окна программного кода с макросом

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

Рассмотрим подробно команды макроса.

Sheets("Лист1").Select 'переход на Лист1. Данная команда состоит из указания на объект Sheets("Лист1") и метода Select для рабочего листа.

Range("A1").Select ‘ выделение ячейки A1.

ActiveCell.FormulaR1C1 = "Цена" ‘ввод текста в выделенную ячейку. Команда включает ссылку на активную ячейку, произведенную с помощью свойства ActiveCell, и свойство FormulaR1C1, которому присваивается значение Цена. Подробнее про адресацию ячеек см. в подразделе «Адресация ячеек в Excel».

Range("В1").Select ‘ выделение ячейки В1.

ActiveCell.FormulaR1C1 = "Количество" ‘ ввод текста в активную ячейку. Аналогично поступаем с ячейками С1, А2, В2. В ячейку С2 введем формулу:

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

Sheets("Лист2").Select ‘ переход на Лист2.

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

· Использовать комбинацию клавиши Ctrl и выбранной буквы, обращая внимание на регистр русских и латинских букв.

· Выполнить команду меню Сервис → Макрос→Макросы. В окне диалога «Макрос» указать в списке имя нужного макроса и щелкнуть на кнопке Выполнить.

· В окне редактора Visual Basic выбрать в списке процедур имя макроса и выполнить команду меню Run → Sub/UserForm,

· воспользоваться кнопкой панели инструментов или нажать клавишу F5.

Формы

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

 

Язык программирования VBA

Язык программирования VBA имеет свои правила написания программного кода. Он использует свой алфавит, включающий буквы латинского алфавита и кириллицу; цифры от 0 до 9;символ подчеркивания.

Из этих символов состоят имена процедур, переменных, меток переходов, константы и команды.

В состав алфавита также входят пробелы для отделения слов друг от друга и знаки операций (+ - * / ^ = > < () [ ] { } ‘ & @), в том числе составные (>=, <=, <>(знак «не равно»).

Программный код VBA представляет собой последовательность команд, записанных по правилам языка Basic. Имена переменных, констант, процедур обозначают посредством идентификаторов, учитывая при этом, что имя должно быть содержательным, оно должно начинаться с буквы, не должно содержать точки, пробела, разделительных символов, зарезервированных слов. Зарезервированные слова (As, Public, Then, All и др) выводятся синим цветом. Примеры правильных имен: strMyName, strФамилия. Примеры неправильных имен: 2Week, Second.Week.

Каждая команда записывается в отдельной строке по определенному правилу.

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

Операторы VBA имеют следующие форматы:

Объект.Свойство = Значение

Объект.Метод [Параметр1] [,Параметр2]…]

где:

Объект - имя объекта, для которого устанавливается свойство или метод. Обычно цепочка объектов начинается с объекта Application, который можно опускать. Например, ссылка Application.ActiveCell или просто ActiveCell - это обращение к активной ячейке. При работе с одной рабочей книгой объект WorkBook также можно опустить.

Свойство - характеристика объекта, подлежащая изменению.

Значение - новое состояние объекта.

Метод - команда или процедура.

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

Знак апостроф (‘) в начале строки означает, что команда или сообщение в строке используется как поясняющий текст и игнорируется VBA. Такие строки в процедуре отмечены зеленым цветом.

Если в командной строке нарушен синтаксис или сама команда неверна, она выделяется красным цветом.

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

Примеры:

Sheets(“Лист1”).Select - оператор выделения рабочего листа, где Sheets(“Лист1”) - объект, Select - метод..

Sheets(“Лист1”).Range(“A1”).Value = Sheets(“Выпуск продукции”).Range(“D5”) - оператор, присваивающий ячейке Лист1!А1 значение из ячейки Выпуск продукции!D5, Value - свойство.

Типы данных

В VBA все данные подразделяются на числа, текст, даты, логические и другие типы. Для числовых данных обычно применяют тип Integer (целочисленные значения в диапазоне от -32768 до +32767), Currency для хранения чисел с 4 знаками после десятичной точки и Single - для вещественных чисел одинарной точности. Для текстовых данных используется строковый тип String (содержит любые символы, строчные данные заключаются в кавычки).

Объявление переменных

Переменные - это имена, предназначенные для хранения и передачи данных. В различные моменты времени переменные могут иметь различные значения. Как правило, переменная имеет первоначальное значение, которое меняется в процессе работы подпрограммы. Имя переменной связывает ее с областью памяти, в которой хранится значение. Перед использованием переменной в программе ее желательно объявить с помощью оператора Dim. При объявлении переменных указывается ее имя и тип, который определяет способ представления переменной. Например, текстовая переменная имеет тип String, переменная целого числа – Integer, дата - Date.

В небольших приложениях переменную можно использовать без объявления, присваивая ей какое-либо значение, но явное объявление предпочтительней.

Синтаксис:

| Dim ИмяПеременной1 [As Тип], ИмяПеременной2 [As Тип],

где:

Dim - ключевое слово, которое сообщает программе имя переменной и резервирует область памяти для хранения ее значения.

ИмяПеременной - идентификатор, определяющий допустимое имя.

As - ключевое слово для определения типа.

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

Примеры:

Dim tovar As String - текстовая переменная,

Dim Cost As Integer - переменная типа целое число.

 

Адресация ячеек в Excel

В Excel ячейка, строка, столбец и диапазон ячеек рассматривается как один объект Range.

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

Range - свойство, определяющее диапазон ячеек;

Cells - свойство, позволяющее выбирать ячейки рабочего листа;

ActiveCell - свойство, возвращающее одну активную ячейку;

Selection - метод, возвращающий выделенный объект.

Для доступа к диапазону ячеек чаще всего используются свойства Range и Cells.

Для ссылки на ячейки используется два формата:

Формат A1 (обычный). Ссылка состоит из имени столбца и номера строки. Например, D4. Ссылка может быть относительной(D4) и абсолютной ($D$4). Ссылка может указывать диапазон ячеек. Полный адрес диапазона может включать имя рабочего листа.

Формат R1C1. В данном формате R задает номер строки, C – номер столбца, ссылка является абсолютной. Для указания относительной ссылки задается смещение по отношению к активной ячейке. Смещение указывается в квадратных скобках, знак указывает направление смещения. Например, R[-2]C - ссылка на ячейку, расположенную на 2 строки выше в том же столбце. R[2]C[3] - ссылка на ячейку, расположенную на 2 строки ниже и 3 столбца правее. Ссылка на одиночную ячейку имеет вид:

[Объект.] Range (“адрес ячейки”). Для свойства Range в качестве аргумента используется любая ссылка в формате A1. Например, WorkSheets(“Лист1”).Range(“A7”)=34 или для текущего листа Range(“A7”)=34 - запись в ячейку A7 числа 34.

[Объект.] Cells(<номер строки>, <номер столбца>). Свойство используется для доступа к отдельной ячейке. В качестве аргументов указываются номер строки и столбца. Например, для ввода числа 34 в ячейку Лист1!A7, необходима команда: Sheets(1).Cells(7, 1). Value=34

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

Пример. Нужно поместить число 3500 в ячейку Прейскурант!D3. Для этого напишем процедуру с неявным объявлением переменных: i - для номера строки и j – для номера столбца.

Sub Ввод()

i =3

j =4

Sheets(“Прейскурант”).Cells(i, j). Value=3500

End Sub

Для указания диапазона можно использовать разные способы. Например, для диапазона A1:D7 применимы ссылки: Range(Cells(1,1), Cells(7,4)) или Range(“A1:D7”), для отдельно стоящих ячеек можно задать перечисление: Range(“A1,B2,C3,D4”) или Range(“A2:C4,D4:D8”).