Главная » 2013 » Март » 17 » Применение VBA и макросов в Microsoft Excel
19:46
Применение VBA и макросов в Microsoft Excel
В этой книге рассматривается автоматизация выполнения всевозможных задач с помощью Excel VBA - от создания простого отчета до разработки полноценного приложения Excel "с нуля". Авторы книги полагаются на достаточно высокий уровень подготовки читателя, однако допускают, что материал каждой главы не знаком ему в полном объеме. Особое внимание при изложении материала уделяется таким высокоэффективным средствам Excel, как диаграмма, расширенный фильтр и сводная таблица. Прежде чем продемонстрировать решение той или иной задачи с помощью VBA, авторы кратко останавливаются на ее выполнении с помощью пользовательского интерфейса Excel. Прочитав книгу, читатель получит знания, необходимые для автоматизации выполнения повседневных задач и создания собственных решений в Excel с помощью VBA. Книга предназначена для опытных пользователей Excel.
Название: Применение VBA и макросов в Microsoft Excel Автор: Билл Джелен, Трейси Сирстад Издательство: Вильямс Год: 2006 Страниц: 621 Формат: PDF Размер: 6,19 МБ ISBN: 5-8459-0882-5 Качество: Отличное Серия или Выпуск: Бизнес-решения
Содержание:
Об авторах Посвящения Благодарности Введение VBA - работа на результат Как организована эта книга Часть I, “Первые шаги” Часть II, “Автоматизация Excel” Часть III, “Удивительные возможности Visual Basic for Applications” Для кого предназначена эта книга История развития электронных таблиц и макросов Будущее Excel и VBA Соглашения, принятые в этой книге Рассматриваемые версии Excel Программный код Следующий шаг Ждем ваших отзывов! Часть I. Первые шаги Глава 1. Excel и VBA - гремучая смесь Excel всемогущий Камни преткновения Средство записи макросов не работает! Visual Basic - это не BASIC Хорошие новости Отличные новости Панель инструментов “Visual Basic” Безопасность макросов Уровень безопасности “Очень высокая” Уровень безопасности “Высокая” Уровень безопасности “Средняя” Уровень безопасности “Низкая” Запись, хранение и выполнение макросов Диалоговое окно “Запись макроса” Выполнение макроса Создание кнопки выполнения макроса Назначение макроса элементу управления формы Редактор Visual Basic Параметры редактора Visual Basic Диспетчер проектов Окно свойств Изучение кода макроса Непредвиденные результаты Возможное решение: использование относительных ссылок Отчаяние Следующий шаг Глава 2. Знакомство с Visual Basic for Applications Загадочный код Учимся понимать “речь” VBA Справочная система VBA Спасительная клавиша <F1> Просмотр разделов справочной системы Изучение кода записанного макроса Необязательные параметры Предопределенные константы Возврат объектов свойством Использование отладчика кода Пошаговое выполнение кода Точки прерывания Перемещение по коду Выполнение фрагмента кода Вычисление значения переменной или выражения Установка точки прерывания с помощью окна Watches Отслеживание состояния объекта с помощью окна Watches Диспетчер объектов 5 советов по исправлению и оптимизации автоматически сгенерированного кода Совет 1: ничего не выделяйте Совет 2: перемещайтесь на последнюю строку данных с конца рабочего листа Совет 3: используйте переменные Совет 4: используйте одно выражение для копирования и вставки данных Совет 5: используйте конструкцию With… End With Исправление и оптимизация автоматически сгенерированного кода Следующий шаг Глава 3. Работа с диапазоном ячеек Объект Range Обращение к диапазону ячеек с помощью указания адреса его верхнего левого и нижнего правого угла Сокращенная форма обращения к диапазону ячеек Именованные диапазоны ячеек Обращение к диапазону ячеек, расположенному на другом рабочем листе Обращение к диапазону ячеек с помощью указания его относительного адреса Обращение к диапазону ячеек с помощью свойства Cells Использование свойства Cells в качестве параметра свойства Range Обращение к диапазону ячеек с помощью свойства Offset Изменение размера диапазона ячеек с помощью свойства Resize Обращение к диапазону ячеек с помощью свойств Columns и Rows Объединение диапазонов ячеек с помощью метода Union Создание нового диапазона ячеек из пересекающихся диапазонов с помощью метода Intersect Проверка пустых ячеек с помощью функции IsEmpty Обращение к диапазону ячеек с помощью свойства CurrentRegion Обращение к диапазону несмежных ячеек с помощью коллекции Areas Следующий шаг Глава 4. Функции, определенные пользователем Создание функций, определенных пользователем Наиболее распространенные задачи программирования в Excel Вывод имени файла текущей рабочей книги в ячейке Вывод полного имени файла текущей рабочей книги в ячейке Как проверить, открыта ли рабочая книга Проверка существования рабочего листа в открытой книге Подсчет количества файлов рабочих книг в папке Получение имени пользователя, зарегистрировавшегося в системе Получение даты и времени последнего сохранения рабочей книги Получение постоянного значения даты и времени Проверка адреса электронной почты Суммирование значений ячеек на основе цвета заливки Получение имени и номера цвета заливки ячейки Получение номера цвета текста в ячейке Подсчет количества уникальных значений Удаление повторяющихся значений из диапазона ячеек Поиск первой непустой ячейки в диапазоне Замена нескольких символов в строке Извлечение чисел из смешанного текста Преобразование номера недели в дату Разбор строки с символами-разделителями Сортировка и конкатенация значений ячеек из заданного диапазона Сортировка числовых и строковых значений Поиск строки в диапазоне ячеек Запись содержимого ячейки в обратном порядке Поиск наибольших значений в диапазоне ячеек Получение адреса гиперссылки Получение адреса столбца ячейки Генерация постоянных случайных чисел Использование структуры Select… Case Следующий шаг Глава 5. Циклы и управление выполнением кода Цикл For… Next Использование переменных в выражении For Изменение шага в цикле For… Next Досрочное завершение выполнения цикла Вложение циклов Циклы Do… Loop Использование операторов While и Until Цикл While… Wend Цикл For Each… Next Объектные переменные Управление выполнением кода: использование конструкций If… Then… Else и Select Case Знакомство с конструкцией If… Then… Else Условие Конструкция If… Then… End If Конструкция If… Then… Else… End If Конструкция If… ElseIf… End If Конструкция Select Case… End Select Использование сложных выражений Case Вложение выражений If Следующий шаг Глава 6. Стиль записи ссылок R1C1 Сравнение стилей записи ссылок A1 и R1C1 R1C1 - дела давно минувших дней? R1C1 - сильные стороны Использование стиля ссылок R1C1 в Excel Чудесный мир формул Excel Как “размножаются” формулы Разоблачение Ссылки в стиле R1C1 Относительные ссылки в стиле R1C1 Абсолютные ссылки в стиле R1C1 Смешанные ссылки в стиле R1C1 Обращение к строке или столбцу с помощью ссылок в стиле R1C1 Замена нескольких A1-формул одной R1C1-формулой Тренируем память Использование ссылок в стиле R1C1 при условном форматировании ячеек Задание условного форматирования с помощью пользовательского интерфейса Задание условного форматирования с помощью VBA Использование ссылок в стиле R1C1 при создании формулы массива Следующий шаг Глава 7. Имена Глобальные и локальные имена Создание имен Удаление имен Типы имен Имена формул Имена строк Имена чисел Имена массивов Зарезервированные имена Скрытие имен Проверка существования имени Следующий шаг Глава 8. События Использование событий Параметры событий Запрет обработки событий События рабочей книги Событие Workbook_Activate () Событие Workbook_Deactivate () Событие Workbook_Open () Событие Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) Событие Workbook_BeforePrint (Cancel As Boolean) Событие Workbook_BeforeClose (Cancel As Boolean) Событие Workbook_NewSheet (ByVal Sh As Object) Событие Workbook_WindowResize (ByVal Wn As Window) Событие Workbook_WindowActivate (ByVal Wn As Window) Событие Workbook_WindowDeactivate (ByVal Wn As Window) Событие Workbook_AddinInstall () Событие Workbook_AddinUninstall () Событие Workbook_SheetActivate (ByVal Sh As Object) Событие Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие Workbook_SheetBeforeRightClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие Workbook_SheetCalculate (ByVal Sh As Object) Событие Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range) Событие Workbook_SheetDeactivate (ByVal Sh As Object) Событие Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink) Событие Workbook_SheetSelectionChange (ByVal Sh As Object, ByVal Target As Range) События рабочего листа Событие Worksheet_Activate () Событие Worksheet_Deactivate () Событие Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Событие Worksheet_BeforeRightClick (ByVal Target As Range, Cancel As Boolean) Событие Worksheet_Calculate () Событие Worksheet_Change (ByVal Target As Range) Событие Worksheet_SelectionChange (ByVal Target As Range) Событие Worksheet_FollowHyperlink (ByVal Target As Hyperlink) События листа диаграммы Встроенные диаграммы Событие Chart_Activate () Событие Chart_BeforeDoubleClick (ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Событие Chart_BeforeRightClick (Cancel As Boolean) Событие Chart_Calculate () Событие Chart_Deactivate () Событие Chart_DragOver () Событие Chart_DragPlot () Событие Chart_MouseDown (ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_MouseMove (ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_MouseUp (ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_Resize () Событие Chart_Select (ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) Событие Chart_SeriesChange (ByVal SeriesIndex As Long, ByVal PointIndex As Long) События приложения Событие AppEvent_NewWorkbook (ByVal Wb As Workbook) Событие AppEvent_SheetActivate (ByVal Sh As Object) Событие AppEvent_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие AppEvent_SheetBeforeRightClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие AppEvent_SheetCalculate (ByVal Sh As Object) Событие AppEvent_SheetChange (ByVal Sh As Object, ByVal Target As Range) Событие AppEvent_SheetDeactivate (ByVal Sh As Object) Событие AppEvent_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink) Событие AppEvent_SheetSelectionChange (ByVal Sh As Object, ByVal Target As Range) Событие AppEvent_WindowActivate (ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WindowDeactivate (ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WindowResize (ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WorkbookActivate (ByVal Wb As Workbook) Событие AppEvent_WorkbookAddinInstall (ByVal Wb As Workbook) Событие AppEvent_WorkbookAddinUninstall (ByVal Wb As Workbook) Событие AppEvent_WorkbookBeforeClose (ByVal Wb As Workbook, Cancel As Boolean) Событие AppEvent_WorkbookBeforePrint (ByVal Wb As Workbook, Cancel As Boolean) Событие AppEvent_WorkbookBeforeSave (ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Событие AppEvent_WorkbookDeactivate (ByVal Wb As Workbook) Событие AppEvent_WorkbookNewSheet (ByVal Wb As Workbook, By Val Sh As Object) Событие AppEvent_WorkbookOpen (ByVal Wb As Workbook) Следующий шаг Глава 9. Введение в пользовательские формы Способы взаимодействия с пользователем Окно ввода Окно сообщения Создание пользовательской формы Вызов и скрытие пользовательской формы Программирование пользовательской формы Основные элементы управления формы Использование списков и комбинированных списков Использование переключателей Использование изображений Использование счетчиков Использование вкладок для объединения форм Проверка ввода обязательных данных Закрытие формы Следующий шаг Часть II. Автоматизация Excel Глава 10. Диаграммы Встроенные диаграммы и диаграммы, расположенные на отдельном листе Встроенные диаграммы и контейнер ChartObject Диаграммы, расположенные на отдельном листе Создание диаграмм с помощью VBA Изменение размещения диаграммы Стандартный тип диаграмм Использование объектных переменных для упрощения кода “Анатомия” диаграммы Область диаграммы (ChartArea) Область построения диаграммы (PlotArea) Ряды данных (Series) Оси диаграммы (Axis) Линии сетки (HasMajorGridlines и HasMinorGridlines) Подписи данных (DataLabels и DataLabel) Название диаграммы, легенда и таблица данных (ChartTitle, HasLegend и HasDataTable) Линии тренда и полосы погрешности (Trendlines и ErrorBar) Типы диаграмм Параметры трехмерных и круговых диаграмм Параметры трехмерных диаграмм Параметры круговых диаграмм Интерактивные диаграммы События диаграмм Экспорт диаграммы в файл изображения Удивительные возможности точечных диаграмм Создание нестандартных диаграмм Круговая пузырьковая диаграмма Диаграмма с точками данных в виде спидометров Диаграмма кривой предложения Иерархическая кольцевая диаграмма Следующий шаг Глава 11. Анализ данных с помощью расширенного фильтра Преимущества VBA перед пользовательским интерфейсом Excel Использование расширенного фильтра для отбора уникальных значений из заданного диапазона Отбор уникальных значений из заданного столбца с помощью пользовательского интерфейса Отбор уникальных значений из заданного столбца с помощью VBA Отбор уникальных значений из комбинации нескольких столбцов с помощью VBA Использование расширенного фильтра с указанием условия отбора данных Объединение нескольких условий с помощью логической операции “ИЛИ” Объединение нескольких условий с помощью логической операции “И” Дополнительные аспекты объединения условий с помощью логической операции “ИЛИ” Задание условия отбора с помощью формулы Отбор пустого множества записей Фильтрация диапазона исходных данных “на месте” Отбор пустого множества записей Отображение записей, скрытых в результате фильтрации “на месте” Отбор только уникальных записей при фильтрации “на месте” Использование расширенного фильтра для копирования всех записей, удовлетворяющих заданному условию Копирование всех столбцов исходного диапазона данных Копирование и переупорядочивание подмножества столбцов исходного диапазона данных Автофильтр Следующий шаг Глава 12. Сводные таблицы Сводные таблицы в различных версиях Excel Создание сводных таблиц с помощью пользовательского интерфейса Excel Создание сводных таблиц с помощью VBA Подсчет суммы чисел вместо количества значений Перемещение или изменение части сводной таблицы Определение размера сводной таблицы Создание отчета о структуре спроса на товары Заполнение значениями пустых ячеек в области данных Изменение порядка сортировки списка заказчиков Изменение порядка следования столбцов сводной таблицы вручную Изменение формата отображения числовых значений Запрет автоматического добавления промежуточных итогов Запрет подсчета общей суммы по столбцам Создание отчета о структуре спроса на товары: завершающая стадия Создание новой рабочей книги Копирование содержимого сводной таблицы Улучшение внешнего вида отчета Стилевое форматирование отчета Добавление промежуточных итогов Результирующий код Создание отчета о прибыльности товаров Определение вычисляемых полей области данных “Подводные камни” вычисляемых элементов Суммирование значений полей области данных сводной таблицы путем группирования Группирование дат по неделям Определение сроков выполнения заказов Дополнительные возможности сводных таблиц Отображение лучшей десятки заказчиков Использование сводной таблицы для фильтрации исходных данных Использование полей области страницы сводной таблицы Фильтрация элементов полей сводной таблицы вручную Сумма, среднее, количество, минимум, максимум и др. Дополнительные вычисления в полях области данных сводной таблицы Доля от общей суммы Приведенное отличие от значения предыдущего элемента поля Приведенное отличие от значения заданного элемента поля Нарастающий итог Следующий шаг Глава 13. Excel всемогущий Расширение возможностей Excel с помощью VBA Условное форматирование с более чем тремя условиями Расширенный фильтр с более чем двумя условиями Файловые операции Поиск файлов Удаление рабочей книги после определенной даты Создание команды меню “Закрыть и удалить” Импорт CSV-файлов Считывание текстового файла в память и его последующий анализ Объединение и разделение рабочих книг Сохранение листов рабочей книги в виде отдельных рабочих книг Объединение нескольких рабочих книг в одну Фильтрация данных с последующим копированием полученного результата в отдельные рабочие листы Экспорт данных в Word Работа с примечаниями Вывод примечаний Изменение размера области примечания Изменение размера области примечания с помощью центрирования Размещение диаграммы в примечании Замечательные возможности Excel VBA Выделение ячейки с помощью условного форматирования Выделение ячейки без применения условного форматирования Транспонирование данных Выделение и отмена выделения несмежных ячеек VBA для профессионалов Установка параметров страницы Вычисление времени выполнения кода макроса Запрет/разрешение выполнения операций вырезания, копирования и вставки Определение порядка сортировки Создание индикатора хода процесса Создание защищенного поля для ввода пароля Изменение регистра текста Обработка события удаления строки или столбца Поиск заданного текста с помощью свойства SpecialCells Условное удаление строк Сокрытие строки формул На закуску Извлечение информации о курсах акций из Internet Вставка программного кода во вновь созданную рабочую книгу Следующий шаг Глава 14. Взаимодействие с Internet Извлечение данных из Internet Создание Web-запроса с помощью пользовательского интерфейса Excel Обновление существующего Web-запроса с помощью VBA Создание Web-запроса с помощью VBA Извлечение данных из Internet в режиме реального времени Анализ данных, извлеченных из Internet Условия выполнения метода OnTime Определение временного окна для выполнения макроса Отмена назначенного задания Отмена всех назначенных заданий Выполнение макроса по прошествии заданного периода времени Периодическое выполнение макроса через определенные промежутки времени Размещение данных на Web-странице Создание Web-страниц с помощью VBA Применение Excel в качестве системы управления содержимым Загрузка Web-страницы на FTP-сервер Следующий шаг Глава 15. Поддержка XML в профессиональном выпуске Excel 2003 Введение в XML Правила XML Универсальный формат файлов XML набирает обороты Схемы и сопоставления XML Сохранение и считывание содержимого рабочей книги Excel в формате XML Следующий шаг Глава 16. Автоматизация Word Раннее связывание Ошибка компиляции: отсутствие библиотеки Позднее связывание Работа с объектами Ключевое слово New Функция CreateObject Функция GetObject Объекты Word Объект Document Объект Selection Объект Range Закладки Следующий шаг Часть III. Удивительные возможности Visual Basic for Applications Глава 17. Массивы Объявление массива Многомерные массивы Заполнение массива Манипулирование элементами массива Еще одно преимущество массивов Динамические массивы Передача массива в качестве параметра Следующий шаг Глава 18. Работа с текстовыми файлами Импорт данных из текстового файла Импорт текстовых файлов, содержащих менее 65 536 записей Импорт текстовых файлов, содержащих более 65 536 записей Экспорт данных в текстовый файл Следующий шаг Глава 19. Использование Microsoft Access ADO и DAO Объекты ADO Добавление записи в таблицу Access Извлечение записей из таблицы Access Обновление записей таблицы Access Удаление записей таблицы Access Создание итоговых запросов Несколько полезных макросов Проверка существования таблицы в базе данных Access Проверка существования поля в таблице базы данных Access Добавление таблицы в базу данных Access Добавление поля в таблицу базы данных Access Следующий шаг Глава 20. Создание пользовательских объектов, типов и коллекций Создание модуля класса Обработка событий уровня приложения и встроенной диаграммы События уровня приложения События встроенной диаграммы Создание пользовательского объекта Применение пользовательского объекта на практике Использование выражений Property Let и Property Get Коллекции Создание коллекции в стандартном модуле Создание коллекции в модуле класса Создание пользовательских типов Следующий шаг Глава 21. Пользовательские формы - профессиональный подход Панель инструментов UserForm Создание коллекций элементов управления формы Дополнительные элементы управления формы Переключатели Набор вкладок Поле ввода адреса диапазона ячеек Немодальные формы Гиперссылки в формах Добавление элементов управления на форму во время выполнения программного кода Изменение размеров формы во время выполнения программного кода Добавление элемента управления на форму во время выполнения программного кода Определение размера и положения элемента управления на форме во время выполнения программного кода Ограничения, связанные с добавлением элементов управления на форму во время выполнения программного кода Типы элементов управления Добавление изображения на форму во время выполнения программного кода Результирующий код Использование полосы прокрутки для выбора значений Добавление подсказки к элементу управления Использование сочетаний клавиш Подсказка элемента управления Порядок переноса фокуса Изменение цвета фона активного элемента управления Использование эффекта прозрачности формы Следующий шаг Глава 22. Интерфейс прикладного программирования (API) Windows Знакомство с Windows API Объявления Windows API Использование объявлений Windows API Примеры полезных объявлений Windows API Определение имени компьютера Проверка возможности доступа к файлу Определение разрешения экрана Блокирование кнопки закрытия окна приложения Блокирование кнопки закрытия окна формы Часы Создание гиперссылок Воспроизведение звуковых файлов Создание диалогового окна выбора файла Дополнительные источники объявлений Windows API Следующий шаг Глава 23. Обработка ошибок Отладка кода с помощью редактора VBA Отладка кода пользовательской формы Обработка ошибок с помощью выражения On Error GoTo Использование нескольких обработчиков ошибок Универсальные обработчики ошибок Игнорирование ошибок Игнорирование сообщений Excel Извлечение пользы из ошибок Общение с заказчиками “Отложенные” ошибки Ошибка времени выполнения 9: “Subscript out of range” Ошибка времени выполнения 1004: “Method 'Range' of object '_Global' failed” Несовершенство защиты проекта VBA Защита проекта VBA в различных версиях Excel Совместимость различных версий Excel Следующий шаг Глава 24. Создание пользовательских меню и панелей инструментов Создание пользовательского меню Создание и удаление пользовательского меню Добавление команд меню Группирование команд меню Создание подменю Создание пользовательской панели инструментов Создание и удаление пользовательской панели инструментов Добавление кнопок на панель инструментов Выбор значка кнопки панели инструментов Добавление раскрывающегося списка на панель инструментов Сохранение и восстановление координат панели инструментов Другие способы запуска макросов Запуск макроса с помощью сочетания клавиш Запуск макроса с помощью кнопки Запуск макроса с помощью элемента управления ActiveX Следующий шаг Глава 25. Надстройки Стандартные надстройки Excel Преобразование рабочей книги Excel в надстройку Преобразование рабочей книги Excel в надстройку с помощью диалогового окна “Сохранение документа” (Save As) Преобразование рабочей книги Excel в надстройку с помощью редактора VBA Использование надстроек Безопасность стандартных надстроек Excel Выгрузка надстроек Удаление надстроек Альтернативное решение: использование скрытой рабочей книги Следующий шаг Глава 26. Практикум: создание приложения Excel ''с нуля'' О Тушаре Мехта Постановка задачи Решение Реализация решения с помощью Excel и VBA Этап 1а: нисходящее программирование Этап 1б: создание ключевых компонентов Этап 2а: нисходящее программирование Этап 2б: создание ключевых компонентов Этап 3а: нисходящее программирование Этап 3б: создание ключевых компонентов Резюме Предметный указатель
