2 семестр / Программирование на VBA

Министерство образования Республики Беларусь Учреждение образования

«МОГИЛЕВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПРОДОВОЛЬСТВИЯ»

Кафедра «Информатика и вычислительная техника»

Программирование на VBA

Учебно-методическое пособие

Могилев 2008

УДК 681.3

Рассмотрено и рекомендовано к изданию на заседании кафедры

«Информатика и вычислительная техника» Протокол № 9 от “ 14 ” апреля 2008 г.

Составитель

В.Л. Титов

Рецензенты:

кандидат технических наук, доцент, заведующий кафедрой «Автоматизированные системы управления» Белорусско-Российского университета

С.К. Крутолевич

кандидат физико-математических наук, доцент кафедры «Математического анализа, информатики и вычислительной техники» Могилевского государственного университета им. А.А. Кулешова С.Н. Батан

кандидат физико-математических наук, доцент, заведующий кафедрой «Информатика и вычислительная техника»

Могилевского государственного университета продовольствия Г.Н. Воробьев

Учебно-методическое пособие состоит из 8 частей и содержит 8 лабораторных работ по изучению Visual Basic for Applications (VBA), являющимся базовым языком в приложениях

Microsoft Office (Word, Excel, Access, PowerPoint, FrontPage, Visio и др.). Материала учебно-

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

Данное учебно-методическое пособие предназначено для аспирантов и студентов всех специальностей, изучающих дисциплины «Информатика», «Информатика и компьютерная графика», «Основы информационных технологий», «Сетевые технологии и базы данных».

© УО «Могилевский государственный университет продовольствия», 2008

2

Введение

Без преувеличения можно сказать, Microsoft Office любых версий является самым полезным и самым используемым продуктом Microsoft. И современный руководитель, и менеджер, и преподаватель, и студент, и школьник – все, кто имеет в своем распоряжении компьютер, в той или иной степени используют эту систему.

Одним из наиболее важных и полезных аспектов подготовки Microsoft Office к выполнению определенных задач является автоматизация процессов взаимодействия пользователя с приложениями Microsoft Office. Эти приложения не являются законченными продуктами, настроенными на выполнение всех возможных задач, а представляют собой системы, которые нуждаются в определенной настройке, что обеспечивается разнообразными средствами, как интерактивными, так и программными. Все приложения Microsoft Office

поддерживают язык программирования Visual Basic for Applications (VBA). VBA

позволяет работать с Microsoft Office, как с некоторым конструктором: в распоряжении разработчика VBA-приложения не только большое количество объектов и коллекций, но и возможности настроек, позволяющие до такой степени программно настроить любое приложение, что пользователь такого приложения может и не понять, с каким приложением происходит «общение».

Важнейшим достоинством VBA является возможность объединять любые приложения Microsoft Office для решения практически любых задач по обработке информации. В этом смысле Microsoft Office можно считать системой программирования, подобной C++, Delphi и т.д., но с более мощными и разнообразными функциями, поскольку здесь имеется неизмеримо большее

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

Привлекательная особенность VBA в том, что он очень удобен для первого знакомства с программированием в среде Windows. Этому способствует широкое распространение приложений Microsoft Office, бесконечное разнообразие возможных практических задач, интуитивно понятная интегрированная среда редактора Visual Basic, возможность обучения программированию посредством анализа кода, записанного при помощи макрорекордера, наличие огромного количества объектов, которыми можно управлять из VB-кода. Более глубокие знания VBA-программирования позволят решать практически любые задачи: от

автоматизации создания простых документов до обработки баз данных с использованием как настольных, так и сетевых СУБД.

В данном пособии предлагается набор заданий, объединенных в лабораторные работы по изучаемым вопросам. Каждая лабораторная работа предполагает выполнение заданий по прописанным действиям, осмысление проделанного и выполнение упражнений на закрепление полученных навыков.

На базе предложенного материала можно организовать вариативность в преподавании VBA в зависимости от подготовки студентов. Лабораторные работы охватывают основы языка Visual Basic, а также операции по созданию макросов, процедур и функций, приложений обработки электронных таблиц с

3

использованием диалоговых окон. Предполагается освоить язык программирования Visual Basic.

Рекомендуется начать изучение VBA с лекционного курса по данной теме. Он создаст теоретическую основу для практической деятельности.

По структуре каждая работа имеет следующие составные части:

üцель занятия;

üнеобходимые материалы к заданиям;

üсправочный материал;

üкомментированные практические задания;

üупражнения для самостоятельной работы;

üконтрольные вопросы.

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

Пособие может быть использовано аспирантами и студентами любых специальностей, изучающими дисциплины «Информатика», «Информатика и компьютерная графика», «Основы информационных технологий», «Сетевые технологии и базы данных».

4

Часть 1. МАКРОСЫ И ЯЗЫК ПРОГРАММИРОВАНИЯ VBA. СРЕДА РЕДАКТОРА VISUAL BASIC

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

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

Макросы, кроме удобства, имеют и другие преимущества. Поскольку компьютеры больше приспособлены для выполнения повторяющихся задач, чем люди, запись макрорекордером неоднократно выполняемых команд повышает точность и скорость работы. Другим преимуществом использования макросов является то, что при их выполнении обычно нет необходимости в присутствии человека-оператора.

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

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

и выбирает соответствующую последовательность действий на основе этих условий. Что касается повторяющихся действий в самом макросе, записанные рекордером макросы имеют значительные ограничения. Если вам необходимо, чтобы записанный макрос повторял какое-либо действие несколько раз, вы должны вручную повторять это действие нужное количество раз, когда записываете макрос. Такой макрос всегда повторяет это действие одинаковое количество раз, всякий раз, когда вы его запускаете, до тех пор, пока вы не отредактируете или не перезапишете его.

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

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

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

5

Лабораторная работа № 1. Запись новых макросов в Excel. Выполнение

макросов

Цель занятия: Уметь задавать стартовые условия для макроса, запускать макроредактор и присваивать имя макросу, выполнять действия, которые необходимо записать для использования позже, останавливать макроредактор, запускать на выполнения макросы.

Материалы к занятию: MS Excel 2003.

Задание 1. Создайте макрос в Excel, который форматирует текст в текущей ячейке шрифтом Arial, полужирным, 12 размером.

1. Задайте стартовые условия.

Для этого:

ü Запустите Excel 2003 (Пуск/Все программы ►/Microsoft Office ►/Microsoft Office Excel 2003), если он еще не запущен;

üоткройте какую-либо рабочую книгу;

üвыберите какой-либо рабочий лист;

üвыделите любую ячейку в рабочем листе.

2. Выберите место и имя хранения макроса. Для этого:

üвыберите в меню Сервис/Макрос/Начать запись… (Tools/Macro/Record New Macro…);

üв раскрывшимся диалоговом окне Запись макроса (Record Macro) (рис.1), в текстовом окне Имя макроса (Macro Name) введите FormatArialBold12 в

качестве имени макроса;

Рис. 1

üоставьте без изменений текст, который Excel вставила в поле Описание

(Description), но добавьте следующее: Форматирует текст диапазона: Arial, Bold, 12; этот дополнительный комментарий поможет вам (и другим) определить назначение данного макроса;

üв списке Сохранить в (Store macro in) для выбора места, в котором будет сохранен записанный макрос, выберите Личная книга макросов;

üесли вы уверены в том, что будете часто использовать макрос, который

6

собираетесь записывать, можете назначить для его запуска горячую клавишу; если – да, введите горячую клавишу в текстовое окно Сочетание клавиш (Shortcut Key) окна Запись макроса;

ü щелкните на кнопке ОК для начала записи макроса; как только вы щелкните на кнопке ОК в диалоговом окне Запись макроса, Excel запустит

макрорекордер, отобразит панель Остановить запись (Stop Recorder) и начнет запись ваших действий. Макрорекордер сохранит каждое ваше действие в новом макросе.

Замечание 1. Доступными вариантами при сохранении макросов являются

Личная книга макросов (Personal Macro Workbook), Новая книга (New Workbook) и

Эта книга (This Workbook). Когда вы выбираете в качестве места для хранения макроса Личная книга макросов, Excel сохраняет макрос в файле специальной книги с именем Personal.xls в папке, в которую установлена Excel. Excel автоматически открывает эту книгу каждый раз в начале работы. Поскольку вам всегда доступны макросы из всех открытых книг, макрос, сохраненный в книге Personal.xls, также будет доступен вам всегда. Если книга Personal.xls не существует, Excel создаст ее. Выбор Эта книга приведет к тому, что Excel сохранит новый макрос в текущей активной рабочей книге. Выбор Новая книга приведет к созданию в Excel новой рабочей книги, в которой будет сохранен этот макрос, – рабочая книга, которая была активной при запуске вами макрорекордера, остается активной рабочей книгой; любые действия, которые вы записываете, выполняются в этой книге, а не в новой рабочей книге, созданной для сохранения макроса.

Замечание 2. Не всегда при запуске макрорекордера вы можете увидеть на экране панель Остановить запись. Поскольку это – обычная панель, ее можно отображать или не отображать. Этим, как и другими панелями, управляет команда Панели инструментов (Toolbars) меню Вид (View). В любом случае (при наличии на экране панели Остановить запись или ее отсутствии) вы можете остановить макрорекордер, выбрав Сервис/Макрос/Остановить запись.

3. Запишите действия и остановите макрорекордер. Для этого:

üвыберите команду Формат/Ячейки… (Format/Cells…) для отображения диалогового окна Формат ячеек (Format Cells);

üщелкните на ярлычке Шрифт (Font) для отображения опций шрифта

(рис. 2);

üвыберите Arial в списке Шрифт (Font); выполните этот шаг, даже если шрифт Arial уже выбран;

üвыберите Полужирный (Bold) в списке Начертание (Font Style);

üщелкните на кнопке ОК, чтобы закрыть диалоговое окно Формат ячеек

иизменить выделенную ячейку в рабочем листе;

üщелкните на кнопке Остановить запись (Stop Macro) на панели Остановить запись (Stop Recorder) или выберите команду

Сервис/Макрос/Остановить запись (Tools/Macro/Stop Recording).

7

Рис. 2

Замечание 3. По умолчанию панель Остановить запись в Excel содержит две командные кнопки. Левая кнопка – это кнопка Остановить запись (Stop); щелкните на этой кнопке для остановки макрорекордера. Правая кнопка – это кнопка Относительная ссылка (Relative Reference). По умолчанию Excel записывает абсолютные ссылки на ячейки в ваши макросы. Кнопка Относительная ссылка является кнопкой-переключателем (toggle). Когда запись с относительными ссылками отключена, кнопка Относительная ссылка выглядит плоской; при помещении курсора мыши на кнопку вид кнопки изменяется и она выглядит отжатой. Когда запись с относительной ссылкой включена, кнопка

Относительная ссылка на панели Остановить запись нажата (находится в

«утопленном» положении). Щелкая на кнопке Относительная ссылка, можно

включать и выключать запись с относительными ссылками во время записи по вашему желанию.

Задание 2. Выполните макрос FormatArialBoIdl2.

Для этого:

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

üвыберите команду Сервис/Макрос/Макросы… для отображения диалогового окна Макрос;

üвыберите макрос PERSONAL.XLS!FormatAriaIBoIdl2 в списке Имя макроса и щелкните на кнопке Выполнить для запуска макроса FormatArialBoldl2. Текст в любой ячейке, которая была выделена до запуска вами этого макроса, будет теперь иметь формат полужирного шрифта Arial 12-го размера.

Упражнение

a)Создайте макрос в Excel, который вычисляет сумму ячеек A1, A2 и выводит результат в ячейку A3 шрифтом Times New Roman, курсивом, 12 размером.

b)Создайте макрос в Excel, который выводит на желтом фоне синими буквами в текущую ячейку имя, а справа от нее фамилию шрифтом Arial, полужирным, 12 размером.

c)Создайте макрос в Excel, который выводит красными буквами в ячейку A3 слово “Частное:”, справа от нее выводит частное ячеек A1 и A2 шрифтом

8

Times New Roman, курсивом, 12 размера.

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

При записи макроса в Excel вы можете определять только рабочую книгу, в которой Excel сохраняет записанный макрос, – текущую рабочую книгу, новую рабочую книгу или рабочую книгу Personal.xls. Excel выбирает модуль, в котором сохраняется записанный макрос, и при необходимости создает этот модуль. Когда Excel создает модуль, в котором сохраняется записанный макрос, модулю присваивается имя ModuleN, где N – это количество модулей, созданных для определенной рабочей книги во время текущего сеанса работы. Например, в первый раз, когда вы сохраняете записанный макрос в Personal.xls (личной книге макросов), Excel создает модуль с именем Module1. Если вы продолжаете записывать макросы в том же сеансе работы и сохранять их в Personal.xls, Excel продолжает сохранять записанные макросы в том же модуле Module1 до тех пор, пока вы не выберете другую рабочую книгу. Если позже в том же сеансе работы вы опять захотите сохранить записанные макросы в Personal.xls, Excel добавляет другой модуль с именем Module2 в эту книгу.

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

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

Задание 3. Запустите Редактор VB. Для этого:

üвыберите Сервис/Макрос/Редактор Visual Basic (Tools/Macro/Visual Basic Editor) или нажмите сочетание клавиш Alt+F11;

üвыберите в меню View/Code или нажмите клавишу F7.

Excel запустит Редактор VB (рис. 3).

9

Project
Explorer
Object		List	Procedure
Code
Window

Properties

Window

Procedure View

Full Module View

Рис. 3

Окна Редактора VB

В окне Редактора VB имеются три дочерних окна, каждое из которых отображает важную информацию о VBA-проекте. Project (Проект) – это группа модулей и других объектов, сохраняемых в определенной рабочей книге или шаблоне рабочей книги. Каждое из окон Редактора VB отображается по умолчанию в прикрепленных (docked) положениях (рис. 3).

Если необходимо, вы можете переместить любое из дочерних окон Редактора VB в любое место на экране, перетаскивая строку заголовка (title bar) этого окна таким же образом, каким бы вы перемещали любое окно на рабочем столе Windows. Перетаскивание одного из дочерних окон из его прикрепленного положения приводит к тому, что оно становится плавающим окном. Плавающие (floating) окна всегда остаются видимыми поверх других окон. Вы можете также изменять размер любого из дочерних окон Редактора VB, расширяя или уменьшая рамку окна для увеличения или уменьшения его размера, что подобно изменению размера любого окна на рабочем столе Windows.

Project Explorer (Окно проекта) содержит дерево-диаграмму открытых в данный момент файлов (рабочих книг) и объектов, содержащихся в этих файлах (объекты host-приложения, модули, ссылки, формы и так далее). Project Explorer

можно использовать для перехода к различным модулям и другим объектам в проекте VB при помощи кнопок (панели инструментов этого окна) View Code

(Программа), View Object (Объект) и Toggle Folders (Папки).

Properties Window (Окно свойств) содержит все свойства объекта текущего выбора. Вкладка Alphabetic (по алфавиту) этого окна предоставляет список

10