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

ПРАКТИЧЕСКАЯ РАБОТА

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

наязыке Visual Basic for Application

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 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А[2]

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

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

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 текст «информатика»