Глава 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
Что нужно знать о массивах? 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
Организация программы на языке 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
Как отобразить примечания? 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
Встроенные функции 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
Список (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
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
Настройка ленты прямым редактированием 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
Глава 8. Обрабатываем данные средствами Microsoft Office Excel
325
Подводим промежуточные итоги 325
Простые промежуточные итоги 326
Вложенные промежуточные итоги 328
Метод Subtotal 331
Удаление промежуточных итогов 331
Обобщаем однородные данные с помощью консолидации 332
Консолидация при помощи трехмерных формул на рабочем листе 332
Консолидация при помощи трехмерных формул в коде 333
Консолидация данных по положению и категориям 333
Методы и свойства, используемые при программировании консолидирующей таблицы 336
Пример приложения, консолидирующего данные 337
Структурируем рабочие листы 338
Структура и объект Outline 340
Отображение указанного числа уровней структуры 341
Удаление структуры 341
Отображение значков структуры 341
Автоматическое создание структуры 341
Пример приложения, подводящего промежуточные итоги и управляющего структурой 342
Используем сценарии 343
Расчет внутренней скорости оборота инвестиций 344
Объект Scenario 348
Пример приложения по работе со сценариями 349
Создаем сводные таблицы 350
Пример создания сводной таблицы на рабочем листе Excel 352
Объекты, связанные со сводной таблицей 357
Объект PivotTable 358
Объект PivotCache 359
Объект PivotField 360
Пример построения сводной таблицы средствами VBA 360
Наши итоги 362
Глава 9. Используем поиск решения и подбор параметра
363
Поиск решения: как это работает? 364
Постановка задачи оптимизации в общем случае 364
Надстройка Поиск решения 365
Рекомендации по решению задач оптимизации с помощью надстройки Поиск решения 370
Построение математической модели задачи 370
Подготовка рабочего листа MS Excel для решения задачи оптимизации 371
Решение задачи с помощью надстройки Поиск решения 371
Анализ решения задачи оптимизации 372
Решаем задачу линейного программирования 372
Планирование производства материалов 373
Определение состава удобрений 377
Решаем транспортную задачу 381
Пример решения транспортной задачи 382
Что такое дискретное программирование? 386
Решаем задачу нелинейного программирования 389
Какие функции программируют поиск решения? 393
Приложение "Транспортная задача" 395
Решение оптимизационных задач, зависящих от параметра 397
Работаем со средством Подбор параметра 399
Пример определения затрат на проект 399
Нахождение корней уравнения 401
Подбор параметра и решение уравнения с одним неизвестным с использованием VBA 402
Усовершенствование средства Подбор параметра 404
Наши итоги 406
Глава 10. Интеграция Microsoft Office Excel и XML
407
Что необходимо знать о формате XML? 407
Изучаем синтаксис XML 409
Основные компоненты документа XML 409
Структура документа XML 411
Русификация XML 412
Зачем нужны схемы XML? 412
Пространства имен 413
Схема XML, расположенная в документе 414
Внешняя схема XML 415
Экспортируем и импортируем данные XML в рабочую книгу Excel 416
Как выполнить импорт данных XML в Excel? 416
Импорт данных из XML-файла в случае отсутствия схемы XML 417
Создание карты XML и импорт данных из файла XML 418
Как выполнить экспорт данных из Excel в документ XML? 421
Как выполнить импорт и экспорт с помощью VBA? 422
Наши итоги 423
Глава 11. MS Excel и Интернет - рядом!
425
Что нужно знать об Интернете? 425
Работаем с гиперссылками в Microsoft Office Excel 429
Как добавить гиперссылки на документы MS Office? 429
Как задать гиперссылку формулой рабочего листа? 430
Что такое условная гиперссылка? 431
Объект Hyperlink и семейство Hyperlinks 432
Переход по гиперссылке из списка 434
Работаем с веб-страницами 436
Веб-запрос и получение данных с веб-страницы 436
Создаем скрипты 439
Как создать скрипты для веба на стороне клиента? 440
Как создать скрипты для веба на стороне сервера? 441
Как передать данные от клиента к серверу? 445
Наши итоги 447
Глава 12. Об интеграции приложений
449
Что такое технология ActiveX? 449
Связываем и внедряем объекты 450
Связь данных 450
Внедрение данных из других приложений 453
Немного примеров 454
Управляем объектами с помощью технологии Automation 458
Программные идентификаторы приложений-серверов Automation 459
Функции доступа к объектам Automation 459
Позднее и раннее связывание 460
Организуем совместную работу Microsoft Excel и Microsoft Word 461
Создание нового документа Microsoft Word функцией CreateObject() 461
Открытие документа Microsoft Word функцией GetObject() 462
Отправка отчета из MS Excel в MS Word 462
Используем Access в качестве сервера автоматизации 464
Отправляем сообщения по электронной почте 465
Создаем презентацию в MS PowerPoint 467
Наши итоги 468
ПРИЛОЖЕНИЯ 469
Приложение 1. Краткая справка по Visual Basic for Applications 471
Основные понятия объектной модели 471
Объектная модель Visual Basic для приложений 472
Объектная модель Microsoft Office 2010 473
Объектная модель Microsoft Office Excel 2010 474
Полная и неявная ссылка на объект 478
Объект Application и его некоторые свойства 478
Ссылка на активную рабочую книгу, лист, ячейку, диаграмму и принтер 478
Инсталлированные надстройки 479
Диапазон ячеек 482
Столбцы и строки рабочего листа 483
Установка заголовка окна MS Excel 483
Семейство встроенных диалоговых окон 484
Отображение строки формул, полосы прокрутки и строки состояния 485
Полноэкранное отображение рабочего листа 485
Установка высоты и ширины окна приложения 485
Семейство всех имен активной рабочей книги 485
Ссылка на выбранный объект 486
Методы объекта Application 486
События объекта Application 487
Наши итоги 489
Приложение 2. Интегрированная среда разработки Microsoft Visual Basic 490
Где набирается код VBA? 490
Структура редактора VBA 491
Окно Project - VBAProject 491
Копирование модулей и форм из одного проекта в другой 492
Окно редактирования кода 492
Интеллектуальные возможности редактора кода 493
Окно UserForm (Редактирование форм) 495
Окно Properties (Свойства) 497
Окно Object Browser (Просмотр объектов) 498
Наши итоги 499
Приложение 3. Отладка приложений 500
Ошибки компиляции 500
Ошибки выполнения 501
Логические ошибки 503
Инструкция Option Explicit 503
Пошаговое выполнение программ 504
Точка прерывания 505
Вывод значений свойств и переменных 506
Окно Watches 506
Окно Locals 506
Окно Immediate 507
Программный способ вывода значений в окно Immediate 507
Наши итоги 507
Приложение 4. Описание компакт-диска 508
Рекомендуемая литература 509
Предметный указатель 511
