МИНИСТЕРСТВО
ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ
Учреждение
образования
«Брестский государственный университет
имени А. С. Пушкина»
Математический
факультет
Кафедра математического моделирования
Курсовая
работа
Применение Excel для анализа информации
из базы данных
Волк
Татьяна Владимировна,
студентка 3 курса специальности «Экономическая
кибернетика»
Будько
Дмитрий Александрович – ассистент
кафедры математического моделирования
Брест
2011
содержание
ВВЕДЕНИЕ 3
1 ПРЕИМУЩЕСТВА
И ВОЗМОЖНОСТИ ПРИМЕНЕНИЯ VBA 4
1.1 Что
такое язык VBA 4
1.2 Создание
приложений VBA в Excel 4
2 ИЕРАРХИЯ
СТАНДАРТНЫХ ОБЪЕКТОВ В EXCEL 6
2.1 Объект Application.
Свойства и методы объекта Application 6
2.2 Коллекция Workbooks
и объект Workbook 10
2.3 Коллекция Sheets
и объект Worksheet, их свойства и методы 11
2.4 Программная
работа с ячейками и диапазонами ячеек
средствами VBA 14
3 ПРИМЕРЫ
ВОЗМОЖНОСТЕЙ ЯЗЫКА VBA 19
3.1 Задание
к курсовой работе 19
3.2 Описание
алгоритма 19
3.3 Листинг
программы 22
ЗАКЛЮЧЕНИЕ 24
СПИСОК
ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 25
ВВЕДЕНИЕ
Программирование в Office —
это прежде всего уменьшение количества
повторяющихся действий и ручной работы,
которая для этого требуется. В общем,
любое действие, которое вам приходится
повторять больше нескольких раз — это
возможный кандидат на автоматизацию.
Например, занесение сотен контактов в
Outlook, или замена ресурса в десятках проектов
Project, или анализ информации из базы данных
за разные периоды в таблице Excel — те ситуации,
когда знание объектных моделей приложений
Office спасет вас от часов и дней скучного
труда.
Так зачем усложнять себе
жизнь?
Тема моей курсовой работы актуальна
в наше время, т.к. VBA имеет огромную область
применения. Язык Vba очень прост, потому
что изначально был ориентирован на пользователей.
Применение встроенного языка программирования
Visual Basic for Applications (VBA) существенно расширяет
возможности электронных таблиц MS Excel
при обработке и анализе корпоративных
данных.
Целью моей работы является
изучение языка программирования Visual
Basic for Applications (VBA), применение этого языка
в одном из приложений Office и создание приложений
VBA в Excel. Задачи курсовой работы:
- научиться
использовать интегрированную среду разработки
— редактор VBA для просмотра, редактирования
и ввода программного кода;
- применять
в своих рабочих книгах процедуры и формы
VBA;
- использовать
объектную модель Excel, свойства, методы
и события объектов (рабочих книг, листов,
диапазонов ячеек и т.д.).
1 ПРЕИМУЩЕСТВА
И ВОЗМОЖНОСТИ ПРИМЕНЕНИЯ VBA
1.1 Что такое
язык VBA
VBA (Visual Basic for Applications) — это диалект
языка Visual Basic, расширяющий возможности
Visual Basic и предназначенный для работы с
приложениями Microsoft Office и другими приложениями
от Microsoft и третьих фирм.
Главная причина проста —
язык VBA встроен в приложения Office (и не
только), и код на языке VBA можно хранить
внутри документов приложений Office — документах
Word, книгах Excel, презентациях PowerPoint и т.п.
Конечно же, этот код можно запускать оттуда
на выполнение, поскольку среда выполнения
кода VBA (на программистском сленге — хост)
встроена внутрь этих приложений.
VBA изначально был ориентирован
на пользователей, а не на
профессиональных программистов
(хотя профессионалы пользуются
им очень активно), поэтому создавать
программы на нем можно очень
быстро и легко. Кроме того,
в Office встроены мощные средства, облегчающие
работу пользователя: подсказки по объектам
и по синтаксису, макрорекордер и т.п.
При создании приложений на
VBA вам, скорее всего, не придется заботиться
о установке и настройке специальной
среды программирования и наличии
нужных библиотек на компьютере пользователя —
MS Office есть практически на любом компьютере.
Несмотря на то, что часто
приложения VBA выполняются медленнее,
чем бы вам хотелось, они нересурсоемки
и очень хорошо работают, например, на
сервере терминалов. Но, как правило, для
программ на VBA особых требований по производительности
и нет: для написания игр, драйверов, серверных
продуктов они не используется. Чаще всего
проблемы с производительностью VBA-приложений —
это не проблемы VBA, а проблемы баз данных,
к которым они обращаются. Если проблемы
действительно в VBA (обычно тогда, когда
вам требуется сложная математика), то
всегда есть возможность написать важный
код на C++ и обращаться к нему как к обычной
библиотеке DLL или встраиваемому приложению
(Add-In) для Word, Excel, Access и т.п.
Программы на VBA по умолчанию
не компилируются и поэтому вносить
в них исправления очень удобно.
Не нужно разыскивать исходные коды
и перекомпилировать программы.
1.2 Создание
приложений VBA в Excel
Excel — это наиболее часто используемое
с точки зрения программирования приложение
Office. В подавляющем большинстве случаев
сотрудников предприятий интересует,
как автоматизировать выполнение операций
именно в Excel. Чаще всего на предприятиях
встречаются следующие ситуации:
- необходимо автоматизировать загрузку
данных в таблицу Excel из базы данных, а
затем в автоматическом режиме произвести
обработку этой таблицы (расчеты, моделирование
и т.п.), и представить эту информацию в
стандартном виде. На практике, конечно,
намного правильнее постараться перенести
выполнение расчетов на сервер баз
данных, но обычно у пользователей для
этого нет ни необходимых знаний, ни прав
для работы с сервером баз данных. Поэтому
Excel в таких ситуациях остается незаменимым
средством;
- приложение, работающее с базой данных,
уже умеет генерировать отчеты в формате
файлов Excel. Но со временем потребности
в отчетах изменяются, появляется необходимость
в новых отчетах или в изменении старых.
Чаще всего в этом случае пользователи
самостоятельно создают новые отчеты,
используя данные из старых. Повторяющейся
работы очень много, поэтому автоматизация
таких операций бывает просто необходимой;
- очень часто пользователи, не имея возможности
обратиться к профессиональным программистам,
самостоятельно реализуют нужные им приложения
в таблицах Excel;
- формат файлов Excel удобен не только для
вывода информации из базы данных, но и
для загрузки введенной вручную информацию
в базу данных. Часто на предприятиях информация
из филиалов, подразделений, сотрудников
и т.п. собирается в формате Excel. В результате
со временем возникает вопрос — как автоматизировать
процесс загрузки информации из Excel в базу
данных.
Приемы, необходимые для
решения подобных задач, я решила рассмотреть
в своей курсовой работе, так как это может
пригодиться в будущем.
С программной точки зрения
Excel, в отличие от Word, чаще всего используется
не как средство для вывода и редактирования
данных, а для выполнения различных расчетов
и отображения их в специальных форматах
(график, сводная таблица и т.п.).
Иерархия стандартных
объектов в Excel немного больше, чем в Word,
т.к. появляется новый элемент — лист, поэтому
главная его иерархия выглядит следующим
образом: Application — Workbook (книга) — Worksheet (лист) —
Range (диапазон).
В Excel предусмотрена очень богатая
библиотека встроенных функций (статистических,
финансовых, математических и т.п.), которые
вполне можно использовать в приложениях.
Навигация по книгам и листам Excel производиться
просто, поскольку у каждой ячейки есть
свой адрес (и даже два адреса — в формате
A1 и в формате R1C1). Кроме того, в Excel есть
возможность присваивать имена диапазонам
ячеек, что также очень удобно.
2
ИЕРАРХИЯ СТАНДАРТНЫХ ОБЪЕКТОВ В EXCEL
2.1 Объект Application.
Свойства и методы объекта Application
Объект Application в Microsoft Excel представляет
все приложение Excel и находится на самом
верхнем уровне объектной модели Excel. Если
вам потребуется вызвать Excel из другого
приложения, вам потребуется создать объект
Excel.Application (не забудьте при этом при помощи
меню Tools->References добавить ссылку на библиотеку
Microsoft Excel 11.0 Object Library).
Если вы работаете из уже запущенного
Excel, создавать объект Application вам не потребуется.
Он будет доступен всегда. Если вы обращаетесь
к какому-либо свойству без указания вышестоящего
объекта, то редактор Visual Basic в Excel будет
считать, что вы обращаетесь к свойству
объекта Application.
Вообще объекты Application
в большинстве приложений Office очень похожи,
и к ним применяются те же соображения.
Точно так же многие разработчики считают,
что удобнее и надежнее работать со скрытым
окном Excel, точно так же открывать новый
экземпляр Excel удобнее, чем разыскивать
экземпляр, уже открытый пользователем.
Для того чтобы в окне редактора кода для
форм появился объект Application, точно так
же необходимо в разделе Declarations кода формы
объявить объект Application с ключевым словом
WithEvents.
В Excel предусмотрено огромное
количество свойств объекта Application, подробно
рассмотреть которые не позволяют рамки
данной работы. Ниже представлен обзор
основных из них.
Свойства объекта Application:
- свойства Active возвращают
соответственно активную ячейку (ту, на
которую указывает курсор ввода данных),
активную диаграмму, активный лист, активную
книгу, активное окно. Все эти свойства
доступны только для чтения. Собственно
говоря, использовать их для создания
объектов совсем не обязательно — объекты
ActiveCell, ActiveSheet и т.п. создаются автоматически
во время работы приложения и доступны
всегда. Немного отличается свойство ActivePrinter —
оно позволяет не только вернуть, но и
установить активный принтер.
- AutoRecover — возвращает
одноименный объект, который позволяет
определить параметры автосохранения
Excel. Например, чтобы открытые документы
Excel автосохранялись каждые 5 минут, можно
использовать код. Время указывается в
минутах, можно использовать значения
в интервале от 1 до 120.
- свойство Calculation позволяет определить или
узнать режим пересчета рабочей книги
(по умолчанию — автоматически, можно также
использовать ручной пересчет или полуавтоматический,
когда автоматически пересчитывается
все, кроме таблиц). Есть смысл отключать
автоматический пересчет тогда, когда
пересчет значений после каждого изменения
ячейки занимает много времени и мешает
вводу данных
- свойство CalculationState
позволяет проверять, занимается ли Excel
пересчетом данных или пересчет уже завершен.
- Cells — одно из самых
важных свойств объекта Application. Оно возвращает
объект Range, представляющий все ячейки
в активном листе активной книги.
- Очень похоже действуют свойства Columns
и Rows.
- Еще раз отметим, что свойства Cells, Columns
и Rows возвращают вовсе не наборы объектов
Cell, Column и Row, как считают многие пользователи,
а наборы объектов Range. На использовании
объекта Range построена в Excel почти вся работа
с ячейками и их значениями.
- Cursor — это свойство
позволяет поменять внешний вид указателя
мыши в Excel
- DataEntryMode — очень интересное
свойство, которое может уберечь пользователя
от множества ошибок. Оно позволяет перейти
в режим ввода данных — data entry mode, когда
пользователю разрешается только вводить
данные в разблокированные ячейки выбранного
диапазона. Всего в вашем распоряжении
три варианта: xlOn — включить этот режим,
xlStrict — включить плюс сделать так, чтобы
пользователь не мог из него выйти при
помощи клавиши <Escape>, xlOff — режим отключить.
- DisplayAlerts — позволяет
отключить показ различных предупреждений,
которые пользователю обычно в ходе работы
приложения показывать не надо (например,
подавить предупреждение при закрытии
ненужного файла, в котором не были сохранены
изменения).
- FileSearch — это свойство
позволяет провести поиск по указанному
вами каталогу и вывести результаты.
- Interactive — это свойство
позволяет полностью заблокировать ввод
в приложение Excel со стороны пользователя
(как клавиатуру, так и мышь).
- свойство LibraryPath возвращает
путь к каталогу, где лежат файлы надстроек
Excel — XLA. По умолчанию — \Office11\Library.
- свойство MoveAfterReturn
позволяет включить/отключить переход
на следующую ячейку после завершения
ввода данных и нажатия на <Enter> (по умолчанию —
включено), а свойство MoveAfterReturnDirection
позволяет определить направление перехода.
В некоторых ситуациях это может сильно
упростить ввод данных пользователем.
- Range — очень важное
свойство. Возвращает объект Range, который
представляет собой диапазон ячеек и используется
в Excel практически для любых операций с
ячейками.
- ReferenceStyle — возможность
переключить режим отображения ячеек
между A1 (буквы — столбцы, цифры — строки)
и R1C1 (когда и строки, и столбцы указываются
цифрами).
- Selection — как несложно
догадаться, это свойство возвращает то,
что в настоящий момент выбрал пользователь.
Если он выбрал обычные ячейки в таблице,
то вернется объект Range. Если же пользователь
выбрал что-то на диаграмме, то может вернуться
объект осей, легенды и т.п. — в зависимости
от того, что было выбрано.
- Sheets — возвращает
коллекцию Sheets — набор листов книги (если
вызывается для приложения, то для активной
книги) и набор диаграмм, которые находятся
на отдельных листах. Если используется
свойство Worksheets, то вернется та же коллекция
Sheets, но уже состоящая только из объектов
Worksheet — обычных листов (без диаграмм).
- ThisCell и ThisWorkbook —
очень удобные свойства, которые позволяют
обращаться к текущей ячейке и текущей
книге, не обременяя себя созданием объектных
переменных. Эти объекты создавать не
нужно — они и так изначально существуют
в работающем Excel.
- Свойства Windows, Workbooks
и Sheets возвращают,
соответственно, все открытые окна, книги
и листы Excel.
- Свойство WorkSheetFunction
позволяет использовать в программе на
VBA функции Excel напрямую, без необходимости
прописывать их в какую-либо ячейку.
Самые важные методы объекта
Excel.Application перечислены ниже:
- ActivateMicrosoftApp() — специальный
метод, который предназначен для запуска
и активизации (или просто активизации,
если приложение уже запущено) приложений
Office (Word, Access, PowerPoint).
- AddCustomList() — возможность
создать новый пользовательский список.
В качестве параметра принимает либо объект
Range (элементами списка станут те значения,
которые есть в диапазоне), либо просто
стандартный объект Array.
Удалить можно при помощи метода DeleteCustomList().
- методы, которые начинаются на Calculate…,
позволяют пересчитать значения в ячейках.
Просто Calculate() — обычный
пересчет значений (чаще всего нужен, если
автопересчет отключен), CalculateFull() —
пересчитать значения во всех открытых
книга, CalculateFullRebuild() —
еще и произвести перестроение формул
(аналогично занесению всех формул заново).
Остановить пересчет можно при помощи
метода CheckAbort().
- ConvertFormula() — возможность
преобразовать формулу двумя способами:
либо перевести адресацию ячеек в другой
режим (например, вместо A1 в R1C1), либо поменять
абсолютные ссылки на относительные и
наоборот. В качестве параметра принимает
строковую переменную с текстом формулы
(должна начинаться с символа =) и флаги
конвертации.
- DoubleClick() — эквивалентно
двойному щелчку мышью поактивной ячейки,
то есть переход в режим ввода данных в
эту ячейку.
- Evaluate() — очень полезный
и часто используемый метод. Позволяет
по имени найти объект книги Excel и преобразовать
его в объект или значения для дальнейшего
использования. В качестве имен этот метод
принимает:
- имена ячеек в стиле A1 (возвращается объект
Cell)
- имена диапазонов (возвращается объект
Range)
- имена, определенные в макросе (чаще всего —
названия переменных)
- ссылки на внешние книги (например, Evaluate("[Book1.xls]Sheet1!A5"))
- GetSaveAsFilename() — такой
же по функциональности способ, который
работает с окном Save As.
- GoTo() — важный и часто
используемый метод. Принимает в качестве
параметра объект Range, строку со ссылкой
на ячейку (в формате R1C1) или имя процедуры
VBA и выделяет и активизирует диапазон/ячейку
или запускает на выполнение (если передается
имя процедуры). В отличие от метода Select(),
этот метод еще и автоматически активизирует
лист, на котором расположен диапазон
или ячейка.
- Help() — этот метод
позволяет открыть и показать информацию
из файла справки (в том числе пользовательского).
В качестве параметра принимает имя файла
справки и метку темы в нем.
- метод Intersect() возвращает
диапазон, который представляет область
пересечения двух других (или более) диапазонов.
Если передаваемые в качестве параметров
диапазоны не пересекаются, возвращается
Nothing.
- OnKey() — этот метод
позволяет "посадить" процедуру VBA
на определенную клавиатурную комбинацию.
Например, чтобы назначить процедуру Msg()
из модуля Лист1 клавиатурной комбинации
<Alt>+<M>, можно воспользоваться командой:
Application.OnKey
"%{m}", "Лист1.Msg"
- метод Repeat() позволяет
просто повторить действие, которое было
выполнено последним пользователем (не
программным способом). Отменить действие
пользователя можно при помощи метода Undo().
- метод Run() позволяет
выполнить: процедуру или функцию VBA, макрос
Excel или процедуру или функцию в модуле
XLL (и передать ей до 30 параметров).
- Union() — это важный
метод позволяет объединить два или более
диапазонов.
- Wait() — это метод позволяет
приостановить работу Excel на указанное
вами время, сняв нагрузку с процессора.
Используется для демонстраций — чтобы
пользователь успел увидеть, что происходит,
для ожидания завершения выполнения какой-либо
внешней операции и т.п. При этом ввод пользователя
блокируется, а указатель мыши приобретает
вид песочных часов.
2.2 Коллекция Workbooks
и объект Workbook
Следующий по иерархии после
Application объект в объектной модели Excel —
это объект Workbook, который представляет
книгу Excel. Он нужен для получения ссылки
на нужную нам книгу в наборе открытых
книг Excel, а также для настройки общих свойств
и выполнения общих действий со всеми
листами книги. Получить этот объект можно
очень просто:
- первый способ — воспользоваться коллекцией
Workbooks, которая доступна через свойство
Workbooks объекта Application. Впрочем, применять
это свойство совершенно не обязательно —
коллекция Workbooks в Excel и так постоянно доступна.
- второй способ — использовать свойство
Application.ActiveWorkbook. При помощи этого свойства
мы обращаемся к активной в настоящей
момент книге.
- третий способ — использовать свойство
Application.ThisWorkbook. При этом мы обращаемся
к той книге, которой принадлежит данный
программный модуль.
На практике чаще всего
нам нужно либо создать в Excel
новую книгу, либо открыть существующую
книгу. Для этой цели используются методы Add() и Open() соответственно.
Помимо стандартных метода в коллекции
Workbooks предусмотрено также три специальных:
- OpenDatabase() — открыть
базу данных, выполнить к ней запрос;
- OpenText() — почти то
же самое, но в качестве источника здесь
выступает текстовый файл.
- OpenXML() — в качестве
источника данных будет выступать файл
в формате XML.
Теперь о самых
важных свойствах объекта Workbook —
самой рабочей книги:
- Name, CodeName, FullName —
разные имена этой книги. Самое простое
имя — Name, это имя совпадает с именем файла
книги. FullName — это имя файла книги вместе
с полным путем к нему в операционной системе.
CodeName — как эта книга будет называться
в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства
книги в окне Properties, кодовое имя книги будет
представлено в строке (Name). Все три свойства доступны только
для чтения, менять их можно другими способами
(например, сохраняя файл под другим именем
или прямо в окне Properties).
- Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties
и CustomDocumentProperties, Windows, WebOptions
возвращают одноименные коллекции соответствующих
объектов
- ConflictResolution — как будут
разрешаться конфликты изменения данных,
если книга открыта несколькими пользователями
сразу (shared workbook). Есть возможность сделать
так, чтобы локальный пользователь автоматически
выигрывал, автоматически проигрывал
или возникало диалоговое окно с возможностью
разобраться в конфликте вручную. Существует
большое количество свойств, которые позволяют
настроить параметры совместной работы
с книгой, но по причине того, что такая
работа не рекомендуется (данные для совместного
доступа необходимо переносить в базу
данных), рассматриваться они здесь не
будут, за исключением: