Автоматизация Excel-приложений

Кафедра информатики и ТСО

Автоматизация Excel-приложений

С помощью Visual Basic for Applications

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

«Программное обеспечение ЭВМ»

для студентов 3 курса дневного отделения

физико-математического факультета

Курск

2003

Печатается по решению

Редакционно-издательского

Совета КГУ

Автоматизация Excel-приложений с помощью Visual Basic for Applications: Учебно-методическое пособие по курсу «Программное обеспечение ЭВМ» для студентов 3 курса дневного отделения физико-математического факультета / Сост. В.Б. Тарасюк. – Курск: Изд-во Курск.гос. ун-та, 2003. – 44 с.

Учебно-методическое пособие предназначено для студентов, обучающихся по специальности 03100.00 – информатика.

В пособии рассматриваются основные принципы автоматизации приложений с помощью Visual Basic for Applications, приводятся основные сведения о языке VBA, содержатся варианты индивидуальных заданий, выполняемых в рамках лабораторного практикума по курсу “Программное обеспечение ЭВМ” при изучении раздела “Табличные процессоры”; даются методические рекомендации по выполнению индивидуальных заданий. В приложении представлены примеры типовых программных решений.

     Рекомендуется всем желающим при самостоятельном изучении Visual Basic for Applications применительно к автоматизации Еxcel-приложений.

© Тарасюк В.Б., 2003 г.

© Курский государственный педаг университет, 2003 г.

Содержание

Введение........................................................................................................................... 4

I. Постановка задачи........................................................................................................ 6

II. Этапы разработки приложения................................................................................... 6

III. Особенности интерфейса VBA.................................................................................. 7

IV. Некоторые сведения о языке Visual Basic for Applications..................................... 11

4.1. Основные понятия................................................................................................ 11

4.2. Типы данных......................................................................................................... 13

4.3. Правила присвоения имен в VB.......................................................................... 14

4.4. Проверка синтаксических ошибок...................................................................... 14

V. Пример создания приложения.................................................................................. 15

VI. Тематика индивидуальных заданий......................................................................... 24

VII. Библиографический список................................................................................... 29

Приложение 1................................................................................................................. 30

Приложение 2................................................................................................................. 37

Введение

Табличный процессор Excel обладает богатыми возможностями по построению приложений, ориентированных на решение самых разнообразных задач. Использование встроенных функций позволяет составлять весьма сложные расчетные таблицы, применяемые в различных сферах производственной, учебной и научной деятельности. Применение мастера построения диаграмм помогает визуализировать информацию, облегчая тем самым анализ и интерпретацию результатов. Инструменты обработки данных, такие как фильтрация, сортировка, подведение промежуточных итогов и т.п., незаменимы при работе с большими информационными массивами. Расширенные возможности Excel, благодаря таким инструментам, как сценарии, поиск решения, пакет анализа и т.п., предусматривают возможность решения задач углубленного анализа и прогнозирования. Однако самым мощным компонентом, расширяющим возможности Excel, представляется язык программирования Visual Basic for Applications (VBA).

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

Как и любая среда программирования, VBA обладает не только определенными достоинствами, но и недостатками. К числу достоинств относится возможность широкого использования таких объектов, как готовые рабочие листы, рабочие книги и т.п., а также высокая степень интеграции VBA с другими приложениями Office, полная прозрачность проекта, гибкость и адаптивность к изменяющимся задачам, возможность создания интерфейса для конечного (непрограммирующего) пользователя. Отмеченные достоинства характеризуют Excel как незаменимый инструмент для создания макетов пилотных проектов АРМ или высокоэкономичных многофункциональных АРМ для малых предприятий. Ограничением возможностей Excel-приложений является жесткая привязка к пакету Office, определяющая их медлительность и относительно высокую степень уязвимости для несанкционированного доступа.

Использование VBA как учебного инструмента для создания учебных АРМ имеет свои дополнительные плюсы. Во-первых, прозрачность и наглядность Excel-проекта упрощает задачу его конструирования; во-вторых, обеспечивается возможность блочно-модульного принципа его построения, в третьих, глубже изучается сам пакет, техника объектно-ориентированного программирования. В четвертых, экономичность и технологичность приложений, использующих VBA, позволяет учащемуся полностью сосредоточиться на принципиальных вопросах создания АРМ.

Язык VBA имеет множество диалектов, созданных в разное время для конкретных Office-приложений. Предлагаемое пособие использует в качестве базисной версию VB for Excel’97–2000.

Данное учебное пособие рассматривает вопросы создания АРМ на базе Excel-приложений с использованием VBA. Задания выполняются в рамках лабораторного практикума по курсу «Программное обеспечение ЭВМ» и относится к разделу «Прикладное программное обеспечение». На выполнение и защиту задания отводится 12 часов.

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

I. Постановка задачи

Цель работы – разработать компонент прикладного программного обеспечения для автоматизированного рабочего места в виде Excel-VBA-приложения.

Критерии, предъявляемые к разрабатываемому приложению:

· функциональность – соответствие приложения реальным технологическим задачам, для решения которых оно предназначено;

· открытость – наличие возможности расширения функций и интеграции с другими компонентами АРМ;

· технологичность – простота в эксплуатации, высокая устойчивость к ошибочным действиям конечного пользователя;

· эргономичность – предполагает наличие простого традиционного интерфейса пользователя с хорошо читаемыми надписями и спокойным цветовым, наличие возможности управления с клавиатуры.

Требования к содержанию отчета.

В процессе выполнения задания готовятся следующие сопроводительные материалы, включаемые в отчет:

§ уточненный перечень задач, решаемых приложением;

§ блок-схема информационной модели;

§ состав оперативных исходных данных;

§ состав условно-постоянных исходных данных;

§ таблица соответствия переменных и констант приложения;

§ инструкция пользователя;

§ предложения по совершенствованию приложения.

Защита проекта.

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

Основные понятия

Объект представляет собой элемент приложения, например, «лист», «ячейка», «диаграмма», «форма» или «отчет». В программе на языке Visual Basic необходимо идентифицировать объект, прежде чем применять к нему методы или изменять значения его свойств.

Метод представляет собой действие, выполняемое над объектом. Например, Add – метод объекта ComboBox, поскольку он добавляет новую запись в поле со списком.

В следующей процедуре для внесения нового элемента в объект ComboBox (поле со списком) используется метод Add.

End Sub

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

Чтобы задать значение свойства, надо указать объект, через точку указать имя свойства, затем знак равенства (=) и новое значение свойства. Например, в следующей процедуре изменяется заголовок формы Visual Basic посредством задания свойства Caption.

Sub ChangeName(newTitle)

       myForm.Caption = newTitle

End Sub

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

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

· инструкции описания, именующие переменные, константы или процедуры, а задающие типы данных;

· инструкции присвоения, присваивающие значение или выражение переменной или константе;

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

Обычно инструкция располагается на одной строке, но ее можно продолжить и на следующую строку с помощью символа продолжения строки – "_".

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

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

Типы данных

Допускаемые VBA типы данных приведены в таблице 1.

Таблица 1

Типы данных VBA

Массивы любых типов данных требуют 20 байт памяти плюс 4 байта на каждую размерность массива плюс число байт, требуемых для хранения данных. Объем памяти, требуемый для сохранения данных, рассчитывается как произведение числа элементов на размер элемента. Например, данные в одномерном массиве, который содержит четыре элемента типа Integer, требующих по 2 байта на элемент, занимают 8 байт. Вместе с 20 байтами на массив и 4 байтами на размерность общий требуемый объем составляет 32 байта.

Объектом (при компиляции) следует указать ссылку на объект в переменной, описанной с конкретным именем класса.

V. Пример создания приложения

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

Разрабатываемое приложение предназначено для автоматизации рабочего места оператора-кассира по продаже билетов на автобус междугородного сообщения. Приложение должно хранить информацию о маршрутах, времени отправления и времени в пути, тарифах, льготах и типе автобуса, а также наличии свободных мест; формировать билет по выбранному маршруту, с расчетом его цены, с учетом имеющейся льготы; вести оперативный учет свободных мест; обеспечивать простую перенастройку реквизитов предприятия-оператора.

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

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

2) хранение информации о реквизитах предприятия-оператора;

3) хранение бланка билета;

4) автоматизированное формирование билета в соответствии с выбранным маршрутом, имеющейся льготой, временем отправления с расчетом цены и только при наличии свободных мест;

5) распечатывание билета или пустого бланка билета;

6) автоматическое обновление оставшихся свободных мест в процессе работы приложения;

7) автоматизированная реинициализация системы в начале рабочего дня.

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

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

Рис.3. Блок-схема информационной модели приложения

После уточнения информационной модели создаются макеты таблиц для хранения данных и форма билета. Таблица, предназначенная для хранения данных о маршрутах, помещается на рабочем листе «Расписание». Макет этой таблицы приведен на рис. 4.

Рис.4. Фрагмент таблицы «Расписание»

Таблица для хранения реквизитов предприятия размещается на рабочем листе «Реквизиты» и выглядит, как показано на рис. 5.

Рис.5. Фрагмент рабочего листа «Реквизиты»

Таблица для хранения сведений о льготах и их размерах размещена на рабочем листе «Льготы» и показана на рис.6.

Рис.6. Фрагмент рабочего листа «Льготы»

Примерная форма заполненного бланка билета размещена на рабочем листе “Билет” и показана на Рис.7.

Москва