Андрей Гарнаев Лада Рудикова Санкт-Петербург «БХВ-Петербург» 2011 УДК 681.3.06 ББК 32.973.26-018.2 Г20 Гарнаев, А. Ю. Г20 Microsoft Office Excel 2010: разработка приложений / А. Ю. Гарнаев, Л. В. Рудикова. — СПб.: БХВ-Петербург, 2011. — 528 с.: ил. + (CD-ROM) — (Профессиональное программирование) ISBN 978-5-9775-0042-5 Продемонстрированы широкие возможности Microsoft Office Excel 2010 по созданию приложе- ний средствами VBA, работе с макросами, технологии ООП, конструированию пользовательского интерфейса и форм. Рассмотрены вопросы автоматизации операций с рабочим листом и диаграмма- ми, в том числе при обработке и анализе данных и принятии решений. Изложены методы интеграции офисных приложений, работы с Интернетом и базами данных, применения XML. Книга содержит более 300 примеров тщательно разработанных приложений: от создания пользовательских функций до построения информационных систем по сбору и обработке данных, программный код которых может быть непосредственно использован читателем при разработке собственных проектов. Прилагаемый компакт-диск содержит файлы рассмотренных в книге примеров. Для программистов, преподавателей и студентов УДК 681.3.06 ББК 32.973.26-018.2 Группа подготовки издания: Главный редактор Екатерина Кондукова Зам. главного редактора Евгений Рыбаков Зав. редакцией Григорий Добин Редактор Анна Кузьмина Компьютерная верстка Натальи Караваевой Корректор Виктория Пиотровская Оформление обложки Елены Беляевой Зав. производством Николай Тверских Лицензия ИД № 02429 от 24.07.00. Подписано в печать 27.06.11. Формат 70 1001/16. Печать офсетная. Усл. печ. л. 42,57. Тираж 1500 экз. Заказ № "БХВ-Петербург", 190005, Санкт-Петербург, Измайловский пр., 29. Санитарно-эпидемиологическое заключение на продукцию № 77.99.60.953.Д.005770.05.09 от 26.05.2009 г. выдано Федеральной службой по надзору в сфере защиты прав потребителей и благополучия человека. Отпечатано с готовых диапозитивов в ГУП "Типография "Наука" 199034, Санкт-Петербург, 9 линия, 12. ISBN 978-5-9775-0042-5 © Гарнаев А. Ю., Рудикова Л. В., 2011 © Оформление, издательство "БХВ-Петербург", 2011 Оглавление Введение .................................................................................................................................. 1 О чем эта книга? ...................................................................................................................... 1 Для кого предназначена эта книга? ....................................................................................... 2 Типографские соглашения ..................................................................................................... 2 От издательства ....................................................................................................................... 3 Благодарности .......................................................................................................................... 3 Глава 1. Быстрое начало — первые программы на VBA ............................................. 5 Что такое VBA? ....................................................................................................................... 5 Объекты и не только ........................................................................................................... 6 Создание функции пользователя в VBA ............................................................................... 7 Где пишется код функции пользователя? ......................................................................... 8 Структура кода функции пользователя ........................................................................... 10 Ваша первая функция пользователя ................................................................................ 10 Вычисление стоимости партии продаваемых книг при помощи пользовательской функции .............................................................................................................................. 12 Использование ссылок на диапазон в качестве параметров пользовательских функций .............................................................................................................................. 13 Об элементах автоматизации Microsoft Office Excel ......................................................... 14 Зачем нужны макросы? ..................................................................................................... 14 Запись макроса и размещение его на панели быстрого доступа .................................. 16 Структура кода процедуры ............................................................................................... 21 Процедура обработки события ......................................................................................... 22 Автоматизация работы рабочего листа при помощи элементов управления .............. 22 Использование элемента управления Кнопка на рабочем листе .................................. 23 Построение шаблона таблицы .......................................................................................... 26 Управление диаграммой ................................................................................................... 29 Наши итоги ............................................................................................................................ 30 Глава 2. Как организуются программы на языке VBA............................................... 31 Язык Visual Basic for Applications: как он устроен? ........................................................... 31 Быстрый взгляд на процедуры и функции ...................................................................... 31 Переменные, константы и типы данных ......................................................................... 34 Ссылки на объекты ............................................................................................................ 38 Область действия переменных и процедур ..................................................................... 38 IV Оглавление Что нужно знать о массивах? ............................................................................................... 41 Как используются массивы? ............................................................................................. 41 Поэлементная инициализация массива ........................................................................... 43 Инициализация массива при помощи функции Array()................................................. 44 Массив и диапазон............................................................................................................. 44 Использование динамических массивов ......................................................................... 45 Как проверить, содержит ли переменная типа Variant массив значений? ................... 45 Повторная инициализация массива и высвобождение памяти, выделенной под массив ................................................................................................................................. 46 Структурированные типы данных: что это такое? ................................................................. 47 Строки ................................................................................................................................. 47 Перечисляемый тип ........................................................................................................... 47 Тип данных, определенный пользователем .................................................................... 48 Дополнительные элементы языка VBA: как они помогают при написании программ? .................................................................................................... 50 Комментарии ...................................................................................................................... 50 Перенос строки кода ......................................................................................................... 50 Расположение нескольких операторов в одной строке ................................................. 51 Операции VBA ................................................................................................................... 51 Математические операции ............................................................................................ 51 Операции отношения..................................................................................................... 52 Логические операции .................................................................................................... 52 Директива Option Compare ........................................................................................... 52 Приоритеты операций ................................................................................................... 53 Встроенные функции VBA ............................................................................................... 54 Встроенные диалоговые окна ........................................................................................... 54 Окно ввода ...................................................................................................................... 54 Как обработать нажатие кнопки Cancel? .................................................................... 56 Окно сообщения ............................................................................................................. 56 Определение нажатой кнопки в окне ввода ................................................................ 59 Управляющие конструкции: формируем логику программы ........................................... 59 Оператор присваивания .................................................................................................... 60 Ветвления ........................................................................................................................... 61 Циклы ................................................................................................................................. 64 Выход из циклов и процедур ............................................................................................ 65 Примеры использования операторов цикла .................................................................... 66 Оператор For...Next ........................................................................................................ 66 Оператор For Each ......................................................................................................... 67 Оператор While ............................................................................................................... 68 Оператор Do ................................................................................................................... 69 Альтернативный выход из цикла ................................................................................. 70 Создание бесконечного цикла оператором Do ........................................................... 70 Оператор безусловного перехода GoTo .......................................................................... 70 Процедуры: знакомимся с деталями.................................................................................... 71 Создание пользовательских функций .............................................................................. 72 Список параметров процедуры ........................................................................................ 74 Оглавление V Организация программы на языке VBA .......................................................................... 75 Вызов процедуры и передача значений параметров ...................................................... 76 Процедура с необязательными параметрами .................................................................. 76 Специфицирование значений по умолчанию необязательным параметром ............... 77 Использование неопределенного количества параметров ............................................ 78 Использование массива в качестве параметра процедуры ............................................ 78 Передача параметров по ссылке и значению .................................................................. 79 Рекурсивные процедуры ................................................................................................... 80 Фракталы ............................................................................................................................ 81 Создаем классы, объекты и семейства ................................................................................ 83 Объявление класса............................................................................................................. 83 Создание экземпляра класса ............................................................................................. 84 Инициализация значений полей ................................................................................... 85 Ключевое слово Me ....................................................................................................... 85 Ключевое слово Nothing и удаление объекта из памяти ............................................ 86 Методы ............................................................................................................................... 86 Свойства как средство ограничения доступа к полям класса ....................................... 87 Свойства "только для чтения" и "только для записи" .................................................... 89 События .............................................................................................................................. 90 Объект Collection ............................................................................................................... 92 Наши итоги ............................................................................................................................ 92 Глава 3. Обрабатываем данные при помощи формул и функций рабочего листа ................................................................................. 93 Немного об адресации ячейки.............................................................................................. 93 Методы объекта Range ...................................................................................................... 95 Активизация и выбор диапазона .................................................................................. 96 Автоматический подбор размеров диапазона так, чтобы в нем помещались введенные данные .......................................................................................................... 96 Заполнение диапазона по одному значению ............................................................... 96 Обрамление диапазона границей ................................................................................. 97 Очистка ячейки .............................................................................................................. 97 Копирование, вырезание и удаление данных из диапазона ...................................... 97 Специальная вставка ..................................................................................................... 98 Вставка диапазона с транспонированием.................................................................... 99 Снятие выделения после специальной вставки .......................................................... 99 Добавление ячейки, строки или столбца ..................................................................... 99 Что дает автозаполнение? ..................................................................................................... 99 Заполнение диапазона прогрессией ............................................................................... 100 Автозаполнение ячеек диапазона элементами последовательности .......................... 101 Табуляция функции ......................................................................................................... 103 Используем автозамену ...................................................................................................... 105 Ищем значения .................................................................................................................... 106 Поиск значения в диапазоне ........................................................................................... 106 Повторный поиск и поиск всех значений ..................................................................... 107 Замена значений .............................................................................................................. 108 VI Оглавление Как отобразить примечания? ............................................................................................. 108 Проверяем данные ............................................................................................................... 110 Что нужно знать о форматах данных? ............................................................................... 110 Форматирование числа на VBA ..................................................................................... 110 Пользовательский формат .............................................................................................. 112 Форматирование чисел ................................................................................................ 117 Форматирование процентов ....................................................................................... 118 Денежный формат ........................................................................................................ 118 Форматирование даты и времени ............................................................................... 118 Условное форматирование ............................................................................................. 119 Форматирование рабочих листов ................................................................................... 120 Автоматическое переоформление таблицы при изменении в ней значений ......... 120 Управление стилем границы диапазона и объекта Border ...................................... 121 Функции RGB() и QBСolor() ....................................................................................... 122 Объект Characters (как форматировать часть содержимого ячейки) ..................... 123 Объект Font (задание шрифта) ................................................................................... 124 Объект Interior (заливка диапазона) .......................................................................... 125 Отмена заливки диапазона .......................................................................................... 126 Установка числового формата .................................................................................... 126 Задание угла, под которым выводится текст в диапазоне ....................................... 126 Работаем с формулами ........................................................................................................ 127 Ссылки на ячейки в формулах ....................................................................................... 127 Ссылка на другие листы рабочей книги или на другие рабочие книги .................. 128 Задание групп строк и столбцов ................................................................................. 129 Связь объекта Range и свойства Cells объекта Worksheet ....................................... 129 Свойства объекта Range .............................................................................................. 129 Ввод или считывание значения из диапазона ........................................................... 130 Ввод в диапазон массива значений ............................................................................ 130 Поиск по шаблону подобных значений в диапазоне ............................................... 131 Ввод или считывание формулы в ячейку в формате A1 .......................................... 131 Ввод или считывание формулы в ячейку в формате R1C1 ..................................... 132 Ввод или считывание формулы локальной версии в ячейку в формате A1 .......... 132 Ввод или считывание формулы локальной версии в ячейку в формате R1C1 ...... 132 Ввод формулы массива в диапазон ............................................................................ 132 Ввод формулы массива локальной версии в диапазон ............................................ 132 Ввод формулы массива в диапазон с относительными ссылками на ячейки ........ 132 Как узнать, спрятана ли формула на защищенном листе? ...................................... 133 Как узнать, имеется ли в ячейке формула? ............................................................... 133 Определение адреса ячейки ........................................................................................ 133 Может ли ячейка быть отредактирована на рабочем листе? ................................... 134 Определения числа областей, из которых состоит данный диапазон .................... 134 Операторы ........................................................................................................................ 135 Операции с текстом и датами ......................................................................................... 135 Операции сравнения и адресные операции................................................................... 136 Автоматическое вычисление .......................................................................................... 137 Используем функции .......................................................................................................... 137 Логические функции ....................................................................................................... 138 Оглавление VII Встроенные функции VBA ............................................................................................. 139 Ошибки в формулах и отслеживание зависимостей .................................................... 139 Примеры использования различных функций в Microsoft Office Excel ........................ 141 Подготовка различных ведомостей ............................................................................... 142 Ведомость о продаже квартир .................................................................................... 142 Ведомость, связанная с переоценкой основных средств производства ................. 143 Отчетная ведомость по работе сети компьютерных клубов ................................... 144 Ведомость по расчету заработной платы .................................................................. 145 Использование встроенных функций для решения различных задач ........................ 149 Принадлежность точек плоскости ............................................................................. 149 Пример решения системы линейных уравнений ...................................................... 151 Пример создания итоговой конструкции по заданному образцу ............................ 152 Пример разделения информации, находящейся в одной ячейке ............................ 153 Пример создания ведомости для учета проката фильмов ....................................... 154 Использование функций в программах на языке VBA ................................................ 155 Получение случайного числа из целочисленного интервала .................................. 155 Вывод строки посимвольно в окно Immediate .......................................................... 156 Строка, состоящая из указанного числа пробелов ................................................... 156 Определение числа секунд, прошедших с полуночи ............................................... 156 Наши итоги .......................................................................................................................... 157 Глава 4. Как создаются пользовательские формы ..................................................... 159 Используем элементы управления на рабочем листе ...................................................... 159 О панели инструментов Элементы управления ............................................................ 160 Как расположить элемент управления на рабочем листе и написать код? ................ 161 Ваш первый проект с элементом управления ............................................................... 163 Общие свойства элементов управления .................................................................... 165 Общие методы элементов управления ...................................................................... 166 Общие события элементов управления ..................................................................... 167 Кнопка (CommandButton) ................................................................................................ 168 Кнопочное меню .......................................................................................................... 168 Навигация по книге при помощи гиперссылок ........................................................ 169 Кнопочный сценарий ................................................................................................... 170 Кнопочный сценарий для ввода формул с кнопками, украшенными рисунками, и пользовательским указателем мыши .............................. 171 Интерактивная кнопка и определение среднего объема продаж ............................ 174 Обмен значений между двумя выбранными ячейками ............................................ 175 Переключатель (OptionButton) ....................................................................................... 175 Переключатели и объемы продаж.............................................................................. 175 Флажок (CheckBox) и Выключатель (ToggleButton) .................................................. 176 Флажок и управление отображением элементов диаграммы .................................. 177 Выключатель и отображение примечаний ................................................................ 178 Полоса прокрутки (ScrollBar) и Счетчик (SpinButton) ............................................... 179 Ввод значений в ячейку и управление цветом .......................................................... 180 Ввод в ячейку с помощью полосы прокрутки и счетчика нецелочисленных значений ........................................................................................................................ 181 VIII Оглавление Список (ListBox) .............................................................................................................. 183 Сценарии со списком .................................................................................................. 184 Защита ячеек рабочего листа ...................................................................................... 185 Управление печатью элементов управления ............................................................ 186 Создаем пользовательские формы с помощью VBA ....................................................... 187 Добавление формы в проект ........................................................................................... 187 Семейство форм ............................................................................................................... 187 Свойства формы .............................................................................................................. 188 Методы формы ................................................................................................................. 190 События формы ............................................................................................................... 190 Отображение и скрытие формы ..................................................................................... 191 Первый проект с формой ................................................................................................ 191 Как запустить проект на исполнение? ....................................................................... 193 Ключевое слово Me ..................................................................................................... 193 Форма с обновляемым фоновым рисунком .............................................................. 193 Удаление рисунка ........................................................................................................ 195 Форма с мозаичным фоном и установкой свойств на этапе инициализации ........ 195 Закрытие формы при нажатии клавиши <Esc> ........................................................ 196 Подтверждение закрытия окна ................................................................................... 197 Задание местоположения формы ............................................................................... 197 Модальная форма......................................................................................................... 198 Использование нескольких форм ............................................................................... 198 "Пасхальное яйцо" ....................................................................................................... 199 Элементы управления ..................................................................................................... 200 Размещение элемента управления на форме ............................................................. 201 Label (Надпись) ................................................................................................................ 201 TextBox (Поле) .................................................................................................................. 202 Сложение двух чисел................................................................................................... 202 Кнопка с "горячей" клавишей ..................................................................................... 203 Клавиши <Enter> и <Esc> ........................................................................................... 204 Суммирование с блокировкой результата для пользователя .................................. 204 Как сделать, чтобы при нажатии кнопки она не получала фокус? ......................... 204 Перемещение фокуса между полями при нажатии клавиши <Enter> .................... 205 Всплывающая подсказка ............................................................................................. 205 Поле ввода пароля ....................................................................................................... 206 Многострочное поле ввода ......................................................................................... 206 Обмен значениями между формами .......................................................................... 207 Таймер как пример класса, генерирующего события .................................................. 208 CheckBox (Флажок) и ToggleButton (Выключатель) ..................................................... 208 Управление видимостью элементов управления ...................................................... 208 Управление доступностью для пользователя элементов управления .................... 209 Frame (Рамка)................................................................................................................... 209 OptionButton (Переключатель) ....................................................................................... 209 Переключатель и выбор результирующей операции ............................................... 209 ScrollBar (Полоса прокрутки) и SpinButton (Счетчик) ................................................. 211 Синхронизированная работа поля ввода и счетчика ................................................ 211 Оглавление IX ListBox (Список) ............................................................................................................... 211 Поэлементное заполнение списка .............................................................................. 212 Заполнение списка из массива и выбор операции .................................................... 212 Заполнение списка из диапазона ................................................................................ 213 Выбор нескольких элементов из списка.................................................................... 214 Согласованная работа двух списков .......................................................................... 215 Многостолбцовый список ........................................................................................... 216 Заполнение многостолбцового списка из диапазона и нахождение среднего значения выбранных чисел ......................................................................... 217 Скрытие данных в многостолбцовом списке ............................................................ 218 Вывод в многостолбцовом списке выбранного значения при помощи свойств Text и Value ............................................................................... 219 Буксировка элементов из одного списка в другой ................................................... 219 ComboBox (Поле со списком) ......................................................................................... 220 Поле со списком, ввод данных в алфавитном порядке и объект Collection .......... 221 Добавление и удаление данных в поле со списком .................................................. 221 Image (Рисунок) ............................................................................................................... 222 Окно О программе ....................................................................................................... 223 Просмотр слайдов ........................................................................................................ 223 Модифицированный мастер диаграмм ...................................................................... 224 Элемент управления RefEdit ........................................................................................... 226 Определение статистических параметров диапазона .............................................. 226 Решение системы линейных уравнений .................................................................... 227 MultiPage (Набор страниц) и TabStrip (Набор вкладок) .............................................. 228 Статистика и набор страниц ....................................................................................... 229 Последовательность перехода элементов управления................................................. 230 Отображение встроенных диалоговых окон ................................................................. 230 Открытие документа и метод GetOpenFilename ....................................................... 232 Простейший браузер для графических файлов......................................................... 233 Сохранение документа и метод GetSaveAsFilename ................................................ 234 Дополнительные элементы управления ........................................................................ 234 Добавление дополнительного элемента управления ............................................... 235 Удаление дополнительного элемента управления ................................................... 235 Разрабатываем пользовательские приложения ................................................................ 235 Заполнение табличного списка данных......................................................................... 235 Сервисные возможности для рабочей книги ................................................................ 238 Разработка модели склада .............................................................................................. 239 Наши итоги .......................................................................................................................... 242 Глава 5. Настройка ленты — это так просто! ............................................................. 243 Как настроить панель быстрого доступа? ......................................................................... 243 Записываем макрос и назначаем его кнопке .................................................................... 246 Назначаем кнопкам процедуры VBA ................................................................................ 250 Очень быстро настраиваем ленту ...................................................................................... 252 Настраиваем ленту с использованием формата Microsoft Office Open XML ................ 253 Формат Microsoft Office Open XML в рабочих книгах Microsoft Office Excel 2010 ............................................................... 253 X Оглавление Настройка ленты прямым редактированием XML-файлов рабочей книги Excel ........................................................................................................ 255 Настройка ленты с использованием XML и VBA ........................................................ 258 Пример создания динамического меню ленты ............................................................. 260 Дополнительные замечания по настройке ленты ......................................................... 263 Создаем панели инструментов из ранних версий MS Excel ........................................... 265 Конструируем контекстное меню ...................................................................................... 266 Наши итоги .......................................................................................................................... 267 Глава 6. Строим диаграммы ........................................................................................... 269 Что нужно знать о диаграммах?......................................................................................... 269 Создаем шаблон отчета с диаграммой .............................................................................. 272 Что представляют собой семейства ChartObjects, Charts и объекты ChartObject, Chart? ........................................................................................... 274 Добавление нового элемента в семейства ChartObjects и Charts ............................... 275 Свойства объекта Chart ................................................................................................... 276 Методы объекта Chart ..................................................................................................... 278 События объекта Chart ................................................................................................... 279 Строим диаграмму с помощью VBA ................................................................................. 280 Изменяем диапазон, по которому строится диаграмма ................................................... 285 Изменяем тип диаграммы ................................................................................................... 286 Автоматически перестраиваем диаграмму при изменении диапазона данных ............ 288 Последовательно отображаем ряды данных на диаграмме ............................................ 289 Создаем проект с линией тренда ....................................................................................... 290 Строим поверхности и управляем ориентацией............................................................... 293 Устанавливаем защиту на вложенную в рабочий лист диаграмму ................................ 296 Защита диаграммы, расположенной на отдельном листе............................................ 297 Немного о событиях и диаграммах ................................................................................... 298 Привязка события к вложенным в рабочий лист диаграммам .................................... 300 Изменение типа диаграммы при помощи контекстного меню ................................... 301 Наши итоги .......................................................................................................................... 302 Глава 7. Обрабатываем списки в Microsoft Excel ....................................................... 303 Что нужно знать о списке? ................................................................................................. 303 Сортируем данные ............................................................................................................... 304 Используем VBA для сортировки данных ........................................................................ 307 Сортировка данных списка по трем полям ................................................................... 308 Сортировка данных на защищенном листе ................................................................... 310 Сортировка данных в выделенном диапазоне .............................................................. 310 Сортировка всех столбцов списка ................................................................................. 311 Фильтруем данные .............................................................................................................. 312 Как найти данные с использованием автофильтра? .................................................... 312 Как программировать автофильтрацию? ...................................................................... 314 Пример приложения, фильтрующего данные ............................................................... 316 Как использовать расширенный фильтр? ..................................................................... 317 Немного о методе AdvancedFilter .................................................................................. 321 Наши итоги .......................................................................................................................... 323