Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
Объект WORKBOOK и семейство WORKBOOKS В иерархии Excel объект Workbook идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга) или XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Свойства объекта WORKBOOK и семейства WORKBOOKS Свойство Описание 95 ActiveSheet Возвращает активный лист книги. В следующем примере устанавливается имя активного рабочего листа: ActiveSheet.Name = "Отчет" Sheets Возвращает семейство всех листов книги. Worksheets Возвращает семейство всех рабочих листов книги. Name Возвращает или устанавливает имя книги. Path Возвращает полное имя папки, в которой находится книга. FullName Возвращает полное имя книги, включая путь. Например, Имя = ActiveWorkbook. FullNaine. Saved Логическое свойство, которое устанавливает, не производились ли изменения в книге со времени ее последнего сохранения. WriteReserved Логическое свойство, которое устанавливает, закрыта ли книга для записи. Методы объекта WORKBOOK и семейства WORKBOOKS Метод Описание Activate Активизирует рабочую книгу так, что ее первый рабочий лист становится активным. Например, Workbooks("Отдел кадров").Activate Add Создает новый объект в семействе Workbooks. Синтаксис: Add (Template) где Template - необязательный. Задает шаблон, на основе которого создается новая рабочая книга. Допустимые значения: - xlWBATChart; - xlWBATExcel4IntlMacroSheet; - xlWBATExcel4MacroSheet; - xlWBATWorksheet. Если аргумент Template опущен, то создается новая рабочая книга с количеством листов, заданных свойством SheetsInNewWorkbook. Protect Защищает рабочую книгу от внесения в нее изменений. Синтаксис: - Protect (Password, Structure, Windows) - Password - необязательный. Строка, используемая в качестве пароля для защиты книги. Если параметр опущен, то книга защищена без пароля; - Structure - необязательный. Логический параметр, который устанавливает, защищена ли структура книги, т. 96 е. взаимное расположение листов; - Windows - необязательный. Логический параметр, который устанавливает, защищено ли окно книги. В следующем примере устанавливается защита для активной рабочей книги: ActiveWorkbook.Protect Password:= "ВинниПух" Unprotect Снятие защиты с рабочей книги. Синтаксис: Unprotect(Password) где Password - необязательный. Строка, используемая в качестве пароля для защиты листа. В следующем примере снимается защита с активной книги: ActiveWorkbook.Unprotect Password:= "ВинниПух" Close Закрытие книги. Open Открытие существующей книги. Синтаксис: Open(FileName, Readonly, Password, Converter, AddToMRU) - FileName - обязательный. Имя открываемого файла; - Readonly - необязательный. Логический параметр, задающий открытие файла в режиме, доступном только для чтения; - Password - необязательный. Строка с паролем для защищенной книги; - Notify - необязательный. Логический параметр, задающий, надо ли извещать пользователя о том, что файл доступен в режиме только для чтения; - AddToMRU - необязательный. Логический параметр, задающий, надо ли добавить открываемый файл в список недавно использованных файлов. Пример:Workbooks.Open "000 Рога и Копыта" Save Сохраняет книгу. Пример: ActiveWorkbook.Save. SaveAs Сохраняет книгу в другой файл. Синтаксис: SaveAs (Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecornmended, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout) - D Filename - строка, указывающая имя файла, в который будет сохранена рабочая книга; - FileFormat - необязательный. Задает формат файла; 97 - Password - необязательный. Строка с паролем для защищенной книги; - WriteResPassword - необязательный. Строка с паролем для сохранения защищенной книги; - ReadOnlyRecommended - необязательный. Логический параметр, указывающий, надо ли отображать сообщение, что файл доступен только для чтения; - ConflictResolution - необязательный. Задает режим разрешения конфликтов при многопользовательском доступе к файлу; - TextCodePage и TextVisualLayout - необязательные. Используются только в локальных версиях для задания кодовой страницы. Пример: ActiveWorkBook.SaveAs Filename:="Новая жизнь" SaveAsCopy Сохранить рабочую книгу в другой файл, оставляя рабочую книгу в памяти с прежним именем. Синтаксис: SaveAs(Filename) где Filename - строка, указывающая имя файла, в который будет сохранена рабочая книга. В следующем примере активная рабочая книга сохраняется в файл с именем "ЗапаснаяВерсия": ActiveWorkbook.SaveCopyAs "С:\ЗапаснаяВерсия". Объект Worksheet и семейство Worksheets В иерархии Excel объект worksheet идет сразу после объекта workbook и представляет рабочий лист. Приведем несколько наиболее часто используемых свойств и методов объекта Worksheet. Свойства объекта WORKSHEET и семейства WORKSHEETS Свойства Описание Name Возвращает имя рабочего листа: Worcsheets(1).Name=”Итоги” Visible True (False) – рабочий лист видим (невидим) на экране. Range Возвращает ссылку на указанный диапазон ячеек. Например: ActiveSheet.Range("B1") UsedRange Возвращает диапазон ячеек рабочего листа. ActiveCell Возвращает активную ячейку рабочего листа. Методы объекта WORKSHEET и семействаWORKSHEETS Методы Описание 98 Activate Активизирует рабочий лист: Worksheet(2).Activate Add Создает новый рабочий лист. Параметры: Before – лист, перед которым будет размещен новый лист; After – лист после которого будет помещен новый лист; Count – число добавляемых листов; Type – тип добавляемого листа. Например, ActiveWorkbook.Worksheets.Add Delete Удаляет рабочий лист: Worksheets(1).Delete Evaluate Преобразует текстовую строку в объект Excel или значение. Используется, например, для ввода ссылок на ячейки: MyCell = InputBox("Введите имя ячейки") Evaluate(myCell).Value = "Новое значение" Copy Копирование активного рабочего листа в другое место рабочей книги: Worksheets(―Лист2‖). _ Copy After:= Worksheets(―Лист3‖) Move Перемещение активного рабочего листа в другое место рабочей книги: Worksheets(―Лист2‖). _ Move After:= Worksheets(―Лист3‖) Задание 1 Написать макрос, который открывает книгу ―Сотрудники‖, создает новую книгу ―Копия‖ и копирует из книги ―Сотрудники‖ лист ―Штат‖ после первого листа книги ―Копия‖. Предполагается, что книга ―Сотрудники‖ находится в той же папке, что и файл с книгой, содержащей макрос. 99 Задание 2 Переставить листы в обратном порядке. Обработчики событий Событие — это действие, распознаваемое объектом, для которого можно запрограммировать отклик. Например, в качестве события можно использовать открытие или закрытие документа, щелчок мыши, нажатие клавиши. Набор действий или повторяющихся явлений, которые можно сопоставить с кодом VBA, называется событиями, а специальный тип процедуры, которая выполняется при возникновении события, называется обработчиком событий. Обработать можно события следующих обьектов Excel: Application WorkBook WorkSheet Chart Обработчики событий дают возможность привязать свой код к действиям пользователя, например к открытию или закрытию книги, активации таблицы, сохранению документа ... Обработчики событий создаются с модулях лисов или книги (в зависимости от того, с каким объектом будет связано это событие. Чтобы создать процедуру обработки события, откройте редактор Visual Basic (Alt + F11), выберите например Лист1 и из двух раскрывающихся списков сверху выберите объект и событие. Редактор автоматически создаст процедуру для обработки события. Вам остается только написать в ней свой код (см. рисунок). Однако в некоторых ситуациях события для объектов не появляются в окне редактора кода (например, это справедливо для очень важного объекта Application). В этом случае необходимо явно объявить этот объект с событиями — при помощи ключевого слова WithEvents, например так: Public WithEvents App As Word.Application Делается это в области объявлений модуля (Declarations). После этого в редакторе кода Visual Basic появляется новый объект App со всеми необходимыми событиями. 100 Многие события имеют параметры. Это выглядит вот так: Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean) End Sub Здесь Target - диапазон ячеек, подвергшийся процедуре правого клика, Cancel - параметр, позволяющий отменить событие, если установить его в True. События объекта Application Событие Описание NewWorkbook При создании новой рабочей книги WorkbookActivate При активизации рабочей книги WorkbookBeforeClose Перед закрытием рабочей книги WorkbookBeforeSave Перед сохранением рабочей книги WorkbookDeactivate Когда активная книга теряет фокус WorkbookNewSheet При добавлении нового листа в рабочую книгу WorkbookOpen При открытии рабочей книги События объекта Workbook Событие Описание BeforeClose При закрытии рабочей книги BeforeSave Перед сохранением рабочей книги Deactivate Когда рабочая книга теряет фокус NewSheet При добавлении нового листа Open При открытии рабочей книги SheetActivate При активизации любого рабочего листа SheetDeactivate Когда рабочий лист теряет фокус 101 События объекта Worksheet Событие Описание BeforeClose При закрытии рабочей книги BeforePrint Перед печатью рабочей книги BeforeSave Перед сохранением рабочей книги Deactivate Когда рабочая книга теряет фокус NewSheet При добавлении нового листа Open При открытии рабочей книги SheetActivate При активизации любого рабочего листа Sheet Deactivate Когда рабочий лист теряет фокус Задание 3 При открытии книги открывать файл Макросы.xls. Задание 4 При активации ―Лист4‖ и ―Лист5‖ сделать видимым ―Лист5‖. При активации других листов ―Лист5‖ становится невидимым. Для этой задачи надо выбрать событие активации листа но для книги, а не для Листа 4 и Листа 5, чтобы можно было анализировать состояние одних листов и управлять свойствами других листов. Задание 5 При активации ―Листа 3‖ запрашивать пароль. Если пароль правильный, строки отображаются и с листа снимается защита. Если пароль не совпадает со значением в ячейке AH1 этого листа, оставить скрытыми все строки и защищенным лист. 102 Проверить корректность работы программы и исправить ошибки. После работы с листом надо привести Лист 3 в первоначальное состояние, т.е. скрыть строки и защитить лист. Для этого надо использовать событие деактивации листа. Задание 6 Если на листе поместить курсор в ячейку H6 Лист очищается. Задания для самостоятельной работы Вариант № 1 1. Создать книгу под именем Данные.xls. В этой книге создать таблицу с 2 столбцами – ФИО, Должность и 4 строками. Написать макрос, который открывает эту книгу и после последней строки дописывает Вашу фамилию. 2. Создать обработчик, в котором при активизации листа, лист следующий за ним скрывается, а при уходе с этого листа, снова открывается. Вариант № 2 1. Написать макрос, который создает новую книгу. Запрашивает имя этой книги и дает такое же имя первому листу этой книги. 2. Создать обработчик в котором при открытии книги все листы сортируются в обратном порядке. 103 Вариант № 3 1. Написать макрос, который создает новую книгу под именем Итоги.xls и изменяте названия ее листов на 1, 2 и 3. 2. Создать обработчик, в котором при изменении данный в ячейке F3 этот лист перемещается в конец. Вариант № 4 1. Создать книгу под именем Пример.xls. В этой книге создать таблицу с 2 столбцами – Должность и Оклад , заполнить в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в книгу, содержащую данный макрос. 2. Создать обработчик, в котором при изменении данных в последней заполненной ячейке столбца B эта ячейка окрашивается черным цветом. После выхода из этой ячейки возвращается в прежнее состояние. Вариант № 5 1. Написать макрос, который создает новую книгу. Запрашивает имя этой книги , а затем добавляет в конец этой книги еще 3 листа. 2. Создать обработчик, в котором при перемещении на лист 4 создается новый лист и в ячейке A1 нового листа появляется слово «Привет». Вариант № 6 1. Написать макрос, который создает новую книгу под именем Листы.xls и удаляет 1 и 3 листы этой книги и добавляет в начало новый лист. 2. Создать обработчик, в котором при создании нового листа этот новый лист становится первым. Вариант № 7 1. Создать книгу под именем Проба.xls. В этой книге создать таблицу с 2 столбцами – Должность и Оклад , заполнить в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в новую книгу. 2. Создать обработчик, в котором при создании нового листа этот лист получает в названии индекс этого листа. Вариант № 8 1. Создать книгу под именем Данные.xls. В этой книге создать таблицу с 2 столбцами – ФИО, Должность и 4 строками. Написать макрос, который открывает эту книгу и после последней строки дописывает Вашу фамилию. 2. Создать обработчик, в котором при открытии книги первый и второй лист скрываются, а при активизации Листа 3 они снова отображаются. 104 Вариант № 9 1. Написать макрос, который создает новую книгу. Запрашивает имя этой книги и дает такое же имя первому листу этой книги. 2. Создать обработчик, в котором на Листе 3при изменении данных ячейке A1 весь лист становится черным, при уходе с Листа 3 все возвращается в первоначальное состояние. Вариант № 10 1. Написать макрос, который создает новую книгу под именем Итоги.xls и изменяет названия ее листов на 1, 2 и 3. 2. Создать обработчик, для Листа 2в котором при перемещении в любую ячейку столбца D этот столбец скрывается. При уходе с листа столбец открывается Вариант № 11 1. Создать книгу под именем Пример.xls. В этой книге создать таблицу с 2 столбцами – Должность и Оклад , заполнить в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в книгу, содержащую данный макрос. 2. Создать обработчик, в котором при активации листа ширина столбцов с A до H становится равным 1 символ. При уходе с листа ширина этих столбцов становится равной 8 символов. Вариант № 12 1. Написать макрос, который создает новую книгу. Запрашивает имя этой книги, а затем добавляет в конец этой книги еще 3 листа. 2. Создать обработчик, в котором при изменении данных в 10 ряду. Шрифт во всем ряду становится белого цвета. При ухода курсора из этого ряда шрифт становится черным. 105 Лабораторная работа № 11 Пользовательские формы Практически во всех приложениях Office используются пользовательские диалоговые окна. Диалоговые окна в VBA называются формами (объект UserForms). Каждому объекту UserForm присущи определенные свойства, методы и события, которые он наследует от класса объектов UserForms. Диалоговые окна (формы) и элементы управления составляют основу современного визуального интерфейса. Все элементы управления и технология работы с ними в основном стандартизованы и похожи для разных платформ и программных сред. Эти объекты помещены в специальную библиотеку MSForms. Выделим основные моменты, которые следует иметь в виду при создании визуального интерфейса. Все загруженные диалоговые окна представляют коллекцию UserForms со стандартными методами и свойствами. Элемент коллекции – объект класса UserForm – задает отдельное окно. Для каждого типа элементов управления в библиотеке msforms имеется класс объектов, имя которого совпадает с именем элемента управления (его типа). Например, есть классы SpinButton и TextBox. Диалоговые окна создаются, как правило, не программно, а визуально. Вначале создается само окно, а затем оно наполняется элементами управления при помощи соответствующей панели элементов. Этот этап называется этапом проектирования, и его следует отличать от этапа выполнения, когда приложение выполняется и конечный пользователь взаимодействует с приложением, в частности через диалоговые окна и их элементы управления. Как только создается диалоговое окно и помещается в него тот или иной элемент управления, в этот же самый момент автоматически в программе появляется объект соответствующего класса, с которым можно работать, вызывая его методы и изменяя его свойства. На этапе проектирования, используя окно свойств, можно задать большинство свойств как самого диалогового окна, так и всех элементов управления, помещенных в него, кроме этого, программно необходимо прописать все обработчики событий. Последний момент – отладка. Для ведения отладки нужно предварительно откомпилировать приложение и затем перейти в режим выполнения приложения. Для того чтобы в разрабатываемое приложение можно было добавить форму, необходимо выполнить следующие действия: запустить редактор VBA; выделить правой кнопкой мыши объект Project, выполнить команду Insert + UserForm, после чего появляются новая форма и панель элементов Toolbox. |