Кафедра физики, математики и информатики ПСПбГПМУ им. И.П. Павлова

Практическая работа

практикум по программированию

на языке Visual Basic for Application

Санкт–Петербург

2014

Методические указания

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

Термином «макрос» обычно называют файл, хранящий последовательность действий, заданных пользователем системы. Каждый макрос должен иметь собственное имя. Макрос – это именованная последовательность заданных пользователем команд и действий, хранящаяся в форме программы на языке VBA (Visual Basic for Application). Язык программирования (Visual Basic) поддерживает идеологию объектно–ориентированного программирования, является языком визуального проектирования приложений и языком управления событиями. Событие – распознаваемое объектом действие, для которого можно запрограммировать отклик.¹ Файл с макросами помогает автоматизировать типовые технологические этапы при работе с системой. Если макрос создан, то после его запуска хранящаяся в нем последовательность действий (команд) будет автоматически исполнена.

Макрос представляет собой программу и может быть создан автоматически в специальном режиме работы программной среды (в том числе и Excel) или как результат программирования (VBA), отредактирован, могут быть устранены ошибки и добавлены новые возможности. Средство VBA в MS Excel является универсальным инструментом для быстрого и точного решения любых пользовательских задач в MS Excel. Готовый файл с макросами нужно сохранить. Для этого необходимо войти по вкладке «Файл», команда «Сохранить как…», в выпадающем списке выбрать «Книга Excel с поддержкой макросов» (рисунок 1).

Рисунок 1. Сохранение файла с макросом

При первоначальном запуске системы вкладка «Разработчик», в группе «Код» которой расположены «Visual Basic», «Макросы», «Безопасность макросов», «Запись макроса» и «Относительные ссылки» может отсутствовать, поэтому необходимо изменить основные параметры работы с Excel. Для этого выберем вкладку «Файл», команду «Параметры» / «Настройка ленты» и поставить галочку для вкладки «Разработчик» (рисунок 2):

Рисунок 2. Добавление вкладки «Разработчик» на ленту

В начале работы диалоговое окно Макрос, вызываемое пунктом Макросы, может показать пустой список. Вкладка «Разработчик» / «Код» «Безопасность макросов» открывает дополнительное окно, позволяющее задавать уровень безопасности при его использовании (рисунок 3).

Рисунок 3. Параметры макросов

Рисунок 3. Параметры макросов

При работе с Excel, как, и с другими программами пакета Microsoft Office, для создания макроса легче всего использовать автоматический режим, вызываемый по вкладке «Разработчик», группа «Код» команда «Запись макроса». В этом случае применяется стандартное средство записи – MacroRecorder, посредством которого можно записывать последовательность действий пользователя, которые автоматически преобразуются в программный код на языке VBA.

При выборе команды «Запись макроса» открывается диалоговое окно, позволяющее задать имя макроса и, комбинацию клавиш, с помощью которой он может быть вызван, выбираем пункт Начать запись, по умолчанию система предлагает стандартное имя Макрос#, напишите собственное имя макроса, отличное от стандартного. С этого момента все Ваши действия записываются в файл макроса. Остановить запись макроса можно кнопкой «Остановить запись» дополнительно открывшейся панели инструментов или по вкладке «Разработчик», группа «Код», команда «Остановить запись». Записанный макрос может быть сохранен в текущей рабочей книге, в личной книге макросов. В последнем случае он может быть доступен в любой открытой книге.

Удалить макрос, созданный в текущей рабочей книге, можно кнопкой «Удалить» диалогового окна по вкладке «Разработчик», группа «Код», команда «Макросы».

После записи макроса его можно редактировать, для этого необходимо выделить макрос и в диалоговом окне выбрать кнопку «Изменить», откроется окно редактора Visual Basic for Application.

Рисунок 4. Редактирование макроса

Редактор Visual Basic вызывается по вкладке «Разработчик», группа «Код», команда «Visual Basic».

Рисунок 5. Вызов редактора VBA

Для быстрого запуска макроса можно создать кнопку и присвоить ей имя макроса. Для этого выбираем вкладку «Разработчик», группу «Элементы управления» и кнопку «Вставить», появляется выпадающее меню «Элементы управления формы». Выбрав элемент управления «Кнопка» размещаем его на экране (рисунок 6). Появляется окно «Назначить макрос объекту» (рисунок 7), в котором вводим название макроса или выбираем из списка уже имеющийся. Осталось только переименовать вновь созданную кнопку (выделить старое название «Кнопка1» и написать новое).

Рисунок 6. Добавление элементов управления

Рисунок 7. Назначение макроса объекту

Стиль ссылок в формулах в среде VBA

В среде VBA существует два типа записи ссылок на ячейки в Excel – A1 и R1C1. По умолчанию, при программировании формул, используется стиль A1, для которого адрес каждой ячейки представляет собой строку символов, содержащую имя столбца и номер строки. Использование этого стиля позволяет организовать относительную и абсолютную адресацию (вводится символ $) к ячейкам таблицы. Тем не менее, иногда при записи макросов Excel использует тип ссылки R1C1. В обозначении этого типа присутствуют первые буквы английских слов Row (строка) и Column (колонка). Обратите внимание на то, что, в отличие от типа A1, при использовании типа ссылок R1C1 сначала записывается строка, а потом столбец. При использовании абсолютной адресации после символов R и C указывается собственно номер строки и столбца. При использовании относительной адресации в стиле R1C1, после обозначения строки или колонки в квадратных скобках указывается смещение по отношению к текущей ячейке.

• RC - относительная ссылка на текущую ячейку

• R3C2 - то же самое, что $B$6 (абсолютная ссылка)

• RC5 - ссылка на ячейку из пятого столбца в текущей строке

• RC[-1] - ссылка на ячейку из предыдущего столбца в текущей строке

• RC[2] - ссылка на ячейку, отстоящую на два столбца правее в той же строке

• R[2]C[-3] - ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки

• R5C[-2] - ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки

Для включения стиля R1C1 необходимо перейти по вкладке «Файл» команда «Параметры» / «Формулы» и поставить галочку в разделе «Работа с формулами» стиль ссылок R1C1.

Основные понятия языка VBА²

Объекты – рабочая книга, рабочий лист и его составляющие

Примеры объектов:

Sheet, Worksheets – лист Excel

Cell – ячейка

Range – диапазон ячеек

Application – приложение

UserForm – пользовательская форма

Доступ к объекту возможен через его методы и свойства.

Примеры:

Cells(1, 1) – ячейка А1

Cells(1, 2) - ячейка В1

Range(Cells(1, 1) , Cells(5, 3)) – диапазон А1:С5

Методы

Над объектом можно совершать различные действия, которые называются методами.

Например, ячейку можно очистить (Clear), приложение закрыть (Quit), пользовательскую форму показать (Show) или скрыть (Hide).

Selection – выбирает объект или группу объектов. Activate – активизирует выбранный объект.

Название метода отделяется от названия объекта точкой: объект.метод.

Примеры использования методов:

Range(“B2:E2”).Select – выбрать диапазон ячеек B2:E2;

Range(“C1:C5”).Clear – очистить диапазон ячеек C1:C5;

Worksheets(“Лист1”).Activate – активизирует Лист1;

UserForm2.Hide – скрыть форму № 2;

UserForm5.Show – показать форму № 5;

Application.Quit – выйти из приложения.

Свойства

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

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

Примеры:

Range(“D1”).Value = 2005 – поместить в ячейку D1 значение 2005

Range(“A11”).Value = “информатика” – поместить в ячейку A11 текст «информатика»

Методы и свойства некоторых объектов VBA

Sheets(“Исследования”).Select – выбрать лист Исследования

Sheets(“Исследования”).Range(“B4:B10”).Select – выделить диапазон B4:B10 на листе Исследования

События

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

Элементы языка VBA

Объекты

Константы – числовые (целое, вещественное число) и символьные (заключаются в “”)

Переменные – данные, которые меняются в ходе выполнения программы, задается имя и тип. В имени нельзя использовать “.”, “ “, “!”, “%”, “&”, “$”, “#”, “@”. Длина имени не должна превышать 255 символов.

Основные типы переменных, их размеры и диапазоны принимаемых значений приведены в таблице 1.

Таблица 1. Типы переменных

Тип данных	Размер (байт )	Диапазон значений
Byte (байт)	1	от 0 до 255
Boolean (логический)	2	True или False
Integer (целое число)	2	от –32768 до 32767
Long (длинное целое)	4	от –2147483648 до 2147483647
Single (число с плавающей запятой обычной точности)	4	для отрицательных значений от -3,4E^38 до -1,4Е^-45 для положительных значений от 1,4^-45до 3,4E^38
Double (число с плавающей запятой двойной точности)	8	для отрицательных значений от -1,79E^308 до -4,94Е^-342 для положительных значений от 4,94^-324до 1,79E^308
Currency (денежный)	8	числа с четырьмя десятичными знаками от –922 337 203 685 477.5808 до 922 337 203 685 477,5807
Date (дата и время)	8	от 1 января 100 до 31 декабря 9999 г.
String (строковый переменной длины)	10+длина строки	от 0 до 2^31
String (строковый постоянной длины)	задается при выполнении оператора Dim	от 1 до 2^16
Variant	для чисел – 16	значения соответствуют типу данных Boolean, Byte, Integer, Long, Currency, Single, Double или Date, определяемому автоматически
	для строк – 22+длина строки	от 0 до 2^31
Object	4	используется при объявлении объектов, аналогичен Variant

В таблице 2 приведен пример некоторых функций.

Объект Application позволяет вызвать функции при помощи конструкций вида:

Application.Функция (действия над объектами)

Примеры:

Application.Sum(Sheets(“Исследования”).Range(“B2:B15”)) – суммируются значения из ячеек диапазона B2:B15, расположенного на листе «Исследования».

Range("F2:F5") = Rnd(5) – в диапазон от F2 до F5 записывается случайное число от 0 до 5.

Sheets(“Числа”).Cells(5,1) = Int (Rnd() * 100) – 50.

Таблица 2. Функции

Функция	Выражение
Математические функции
Abc(x)	Модуль (абсолютная величина числа)
Cos(x)	Косинус
Exp(x)	Экспонента, т.е. результата возведения основания натурального логарифма в указанную степень
Log(x)	Натуральный логарифм
Sin(x)	Синус
Sqr(x)	Квадратный корень
Rnd() Rnd(x)	генерирует случайное число от 0 до 1 генерирует случайное число от 0 до х
Int()	округляет до целого числа
Функции времени и даты
Date	Текущая дата
Now	Текущая дата и время
Day	День месяца
DateDiff	Разность двух дат
WeekDayName	Название дня недели

Структура редактора VBA

Редактор активизируется вкладкой «Разработчик»/»Код»/»Visual Basic» (рисунок 8):

Меню

Панель инструментов

Возврат в рабочую книгу нажатием кнопки MS Excel

Окно проекта

Окно свойств

Окно редактирования кода

Окно редактора состоит из следующих компонентов:

Рисунок 8. Структура редактора Visual Basic

Процедуры и модули для кода VBA

Программа – последовательность операторов, включая оператор комментариев (для этого вводится (‘) или слово Rem вместо апострофа, комментируется текст до конца строки).

Модуль – совокупность объявлений (описательная часть) и процедур, хранящихся как единое целое

Процедура – совокупность кода VBA, рассматриваемая как единое целое, имеет свое уникальное имя. Выполнение процедуры является реакцией на какое-либо событие, т.е. процедура обрабатывает событие, ограничивается ключевыми словами Sub ….. End Sub.

Описание данных начинается с оператора Dim. Тип переменных лучше указывать, так как от типа данных зависит время выполнения процедур и ресурсы памяти.

Пример:

Dim A As Integer, B As Byte, C As String

Для объявления констант и их значений используется оператор Const.

Пример:

Const Рост_девочки As Byte = 11

Управляющие структуры VBA

Управляющие структуры определяют последовательность выполнения программы.

 Условные операторы

1) If условие Then выражение – для одной строки

If условие Then выражение1, выражение2 End If

– в случае истинности условия выполняется последовательность операторов

If условие Then выражение1, … Else выражение2, … End If

– в зависимости от условия выполняются разные блоки операторов

If условие Then выражение1, … ElseIf условие Then выражение2, … Else выражение3, … End If – определяет вложенность конструкций

2) Select Case проверяемое выражение

Case список выражений1

блок операторов1

Case список выражений2

блок операторов2

Case список выражений3

блок операторов3

….

Case Else {не обязательный блок}

End Select

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

 Операторы цикла

 фиксированное число повторений

1)  For переменная = M1 To M2 [Step M3]

операторы

Next

Если шаг не указан, то он полагается равным 1.

 переменное число повторений

2)  Do While условие

операторы

Loop

Цикл с предусловием.  Все операторы выполняются до тех пор, пока условие будет истинным.

3)  Do

…

Loop Until <условие>

Цикл с постусловием. Проверяется условие перед началом цикла и цикл выполняется до тех пор, пока оно False. Как только условие цикла станет равно True, выполнение цикла прекратится.

Макрос3()

Range("D2").Select

ActiveCell.FormulaR1C1 = "=(RC[-1]*10000)/RC[-2]^2"

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

Range("D2:D5").Select

Range("D6").Select

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

Range("E2").Select

ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]<18,""low"",IF(AND(RC[-1]>18,RC[-1]<24),""middle"",""heigh""))"

Selection.AutoFill Destination:=Range("E2:E5"), Type:=xlFillDefault

Range("E2:E5").Select

End Sub

Пример 1. Рассмотрим таблицу, показанную на рисунке 9. В ней необходи­мо рассчитать индекс массы тела (ИМТ) каждого пациента, рассчитать среднее значения индекса по всем пациентам и выставить оценку индекса. Записывался макрос с именем Макрос3.

Рисунок 9. Таблица с расчетом ИМТ

Рассмотрим листинг макроса.

Первый исполняемый оператор программы Range(“D2”).Select создан системой в виде выражения, которое содержит в терминологии VBA свойс­тво Range в сочетании с методом Select. Обратите внимание на то, что свойство имеет записанный в круглых скобках аргумент в виде строки символов и отделяется от метода точкой. В нашем примере аргумент свойс­тва представляет собой ссылку на ячейку в стиле A1, с которой началось программирование макроса.

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

Отдельно остановимся на заливке ячеек, диапазона ячеек.

Цвет фона ячейки в Microsoft Office Excel определяется свойством ColorIndex объекта Interior этой ячейки.

Так, заливка ячейки осуществляется командой:

Cells(4,5).interior.colorindex = 5, где 5 – синий цвет или

Заливка некоторого диапазона –

Range(“A1:D1”).interior.colorindex = 3, где 3 – красный цвет

Установка цвета текста:

Cells(4,5).Font.Color = vbBlue

Range(“A1:D1”). Font.Color =vbRed

Числовой индекс цветов и константы RGB представлены в таблице 3.

Таблица 3. Числовой индекс цвета и константы RGB

код	цвет		константы RGB	цвет
1	черный		vbRed	красный
2	белый		vbBlack	черный
3	красный		vbGreen	зеленый
4	зеленый		vbYellow	желтый
6	желтый		vbBlue	синий
7	малиновый		vbMagenta	фиолетовый
33–42	пастельные тона		vbCyan	бирюзовый
9–14, 18, 21, 25, 49, 51-56	темные тона

Если мало стандартных цветов (56 цветов) то используют RGB палитру, например

Cells(3,4).Interior.Color = RGB(160,255,30)