Файл: Лабораторная работа 3 Работа с диапазонами ячеек (объекты Range, Calls).pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 03.12.2023
Просмотров: 128
Скачиваний: 4
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
к которому принадлежит метод.
Одной из причин того, что объектно-ориентрованное программирование стало популярной техникой проектирования, является то, что оно позволяет разработчика программного обеспечения создавать более эффективные программы с совместно используемым кодом. Вместо сохранения отдельного копии кода для каждого метода каждого объекта. VBA-объекты одного и того же типа совместно используют код своих методов.
Несмотря на то, что объекты одного и того же типа совместно используют код для своих методов, метод считается частью объекта; когда вызывается определенный метод для конкретного объекта, метод воздействует не только на объект, посредством которого обращается к этому методу.
После того как объект создан (если это невстроенный объект), нет необходимости беспокоиться о том, как работают его методы и как объект сохраняет и манипулирует данными. Все что нужно знать – как указать объект и как указать метод, который необходимо использовать (или определенное свойство, которое нужно выбирать и изменить). Встроенный код для объекта
VBA выполняет остальную работу автоматически.
Операторы программ VBA, использующие объекты, обычно выполняют одно или несколько из следующих действий:
• определяют текущее состояние и статус объекта путем выборки значения, сохраняемого в определенном свойстве;
• изменяют состояние или статус объекта установкой значения, сохраненного в определенном состоянии;
• используют один из методов объекта, обеспечивая выполнение объектом одной из его встроенных задач.
Например, можно определить имя активного в данный момент рабочего листа, обращаясь к свойству Name рабочего листа книги.
В операторах VBA используется следящий общий синтаксис для определения свойства или метода объекта:
<Имя обьекта>.<Свойство или метод>
В таблице 1 приведены несколько наиболее важных объектов (с точки зрения программиста VBA) в Excel.
Таблица 1
Объект
Описание
Application
Само приложение Excel
Window
Любое окно в Excel; окна отображения рабочих листов, диаграмм и т.д.
WorkBook
Открытая рабочая книга
WorkSheet
Рабочий лист в книге
Range
Диапазон ячеек (одна или более) или именованный диапазон в рабочем листе
Font
Объект Шрифт содержит атрибуты шрифта и стиля текста, отображаемого в рабочем листе, диапазоне ячеек.
Chart
Диаграмма в рабочей книге
9
Одной из причин того, что объектно-ориентрованное программирование стало популярной техникой проектирования, является то, что оно позволяет разработчика программного обеспечения создавать более эффективные программы с совместно используемым кодом. Вместо сохранения отдельного копии кода для каждого метода каждого объекта. VBA-объекты одного и того же типа совместно используют код своих методов.
Несмотря на то, что объекты одного и того же типа совместно используют код для своих методов, метод считается частью объекта; когда вызывается определенный метод для конкретного объекта, метод воздействует не только на объект, посредством которого обращается к этому методу.
После того как объект создан (если это невстроенный объект), нет необходимости беспокоиться о том, как работают его методы и как объект сохраняет и манипулирует данными. Все что нужно знать – как указать объект и как указать метод, который необходимо использовать (или определенное свойство, которое нужно выбирать и изменить). Встроенный код для объекта
VBA выполняет остальную работу автоматически.
Операторы программ VBA, использующие объекты, обычно выполняют одно или несколько из следующих действий:
• определяют текущее состояние и статус объекта путем выборки значения, сохраняемого в определенном свойстве;
• изменяют состояние или статус объекта установкой значения, сохраненного в определенном состоянии;
• используют один из методов объекта, обеспечивая выполнение объектом одной из его встроенных задач.
Например, можно определить имя активного в данный момент рабочего листа, обращаясь к свойству Name рабочего листа книги.
В операторах VBA используется следящий общий синтаксис для определения свойства или метода объекта:
<Имя обьекта>.<Свойство или метод>
В таблице 1 приведены несколько наиболее важных объектов (с точки зрения программиста VBA) в Excel.
Таблица 1
Объект
Описание
Application
Само приложение Excel
Window
Любое окно в Excel; окна отображения рабочих листов, диаграмм и т.д.
WorkBook
Открытая рабочая книга
WorkSheet
Рабочий лист в книге
Range
Диапазон ячеек (одна или более) или именованный диапазон в рабочем листе
Font
Объект Шрифт содержит атрибуты шрифта и стиля текста, отображаемого в рабочем листе, диапазоне ячеек.
Chart
Диаграмма в рабочей книге
9
2.
Использование свойств объекта
Свойства объектов можно использовать только двумя способами:
получать значения свойства или устанавливать его. Следует отметить, что не все свойства объекта изменяемы. Свойства объектов, которые нельзя изменять, называют свойствами, доступными только для чтения (read-only); свойства, которые можно устанавливать, называют свойствами, доступные на
чтения/запись (read-write).
Можно использовать свойство объекта непосредственно в каком-либо выражении или в качестве аргумента функции или процедуры.
Например,
MsgBox Thisworkbook.FullName
В этом примере,
Thisworkbook
– объект рабочей книги, в которой написана данная строка команд VBA;
FullName
– свойство, объекта рабочей книги, которое хранит в себе полный путь и имя файла рабочей книги.
Чтобы задать свойство объекта, надо присвоить свойству новое значение, используя следующий синтаксис:
ИмяОбъекта.Свойство=Выражение
Пример изменения свойства объекта:
ActiveSheet.Name
= "Первый квартал"
В данном примере изменяется имя свойству Name активного листа.
В таблице 2 перечислены некоторые из наиболее употребительных или полезных свойств объектов. В таблице представлено имя свойств, тип и значение, а также объекты, которые имеют это свойство.
Таблица 2
Объект
Тип/Что означает
Где найти
ActiveCell Object: активная ячейка рабочего листа
Application, Window
ActiveSheet Object: активный лист
Application, Window,
Workbook
Address
Возвращает координаты ячейки указанного объекта
Range
Cells
Диапазон объекта Range
Application, Range,
Worksheet
Formula
Формула для ячейки рабочего листа Range
Name
Имя объекта
Application, Workbook и др.
Path
Драйвер и каталог, в котором сохранен объект
Application, Workbook
Saved
Сохранялась ли рабочая книга
Workbook
Visible
Отображается или нет объект на экране
Application, Range,
Worksheet и др.
Value
Значение ячейки
Range, Cells
Задание 8
Создать новую рабочую книгу с именем «Мои первые макросы».
Написать макрос, который выдаст на экран сообщение-предупреждение (см.
10
рисунок 4) о том, что лист будет переименован и переименует активный лист на «Текущий лист». Результат выполнения макроса приведен на рисунке 5.
Рисунок 4 – Сообщение перед переименованием
Рисунок 5 – Результат выполнения макроса
Задание 9
Создайте макрос переименования активной рабочей книги.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
3.
Использование методов объекта
Методы объекта используются в VBA так, как использовались бы любые встроенные процедуры VBA.
Обращение к методу объекта имеет следующий синтаксис:
ИмяОбъекта.Метод
Для методов объектов, имеющих обязательные и необязательные аргументы, используется такой синтаксис:
ИмяОбъекта.Метод Аргумент1, Аргумент2, …
Во второй строке синтаксиса
Аргумент1, Аргумент2
и так далее представляют аргументы в списке аргументов метода. Необходимо перечислять аргументы метода в определенном порядке, отделяя каждый аргумент списке запятой и включая отмечающие запятые для пропущенных необязательных аргументов. Метод может иметь один или несколько аргументов или не иметь совсем. Аргументы метода могут быть обязательными или необязательными.
Пример вызова метода
ActiveWorkbook.SaveAs "D:\VBA2000\NEWFILE.xls", xlNormal или
ActiveWorkbook.SaveAs "D:\VBA2000\NEWFILE.xls",,”123”
Еще один способ вызова метода заключается в использовании именованных аргументов. При вызове метода указывается названия аргументов,
11
Рисунок 4 – Сообщение перед переименованием
Рисунок 5 – Результат выполнения макроса
Задание 9
Создайте макрос переименования активной рабочей книги.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
3.
Использование методов объекта
Методы объекта используются в VBA так, как использовались бы любые встроенные процедуры VBA.
Обращение к методу объекта имеет следующий синтаксис:
ИмяОбъекта.Метод
Для методов объектов, имеющих обязательные и необязательные аргументы, используется такой синтаксис:
ИмяОбъекта.Метод Аргумент1, Аргумент2, …
Во второй строке синтаксиса
Аргумент1, Аргумент2
и так далее представляют аргументы в списке аргументов метода. Необходимо перечислять аргументы метода в определенном порядке, отделяя каждый аргумент списке запятой и включая отмечающие запятые для пропущенных необязательных аргументов. Метод может иметь один или несколько аргументов или не иметь совсем. Аргументы метода могут быть обязательными или необязательными.
Пример вызова метода
ActiveWorkbook.SaveAs "D:\VBA2000\NEWFILE.xls", xlNormal или
ActiveWorkbook.SaveAs "D:\VBA2000\NEWFILE.xls",,”123”
Еще один способ вызова метода заключается в использовании именованных аргументов. При вызове метода указывается названия аргументов,
11
за которыми следует оператор := и сами передаваемые значения.
ИмяОбъекта.Метод Аргумент1:=Значение1, Аргумент2:=Значение2
Вызов метода с использованием именованных аргументов имеет 2 преимущества. Прежде всего, это наглядность кодов программы. Второе преимущество – в простоте. В этом случае, указывать необходимо только те аргументы, значение которых определяются вызывающей программой.
InstBook.Activate
ActiveWorkbook.SaveAs _
Filename:="D:\VBA2000\NEWFILE. xls”, _
FileFormat:=xlNormal
Многие объекты имеют методы которые возвращают значения так же, как это делает функция. Чтобы использовать значение, возвращаемое методом, необходимо поместить список аргументов метода в круглые скобки и включить вызов метода в другое выражение. Можно также игнорировать результат, возвращаемый методом. Чтобы игнорировать результат метода (если он имеет результат), вызовите метод без круглых скобок вокруг списка аргументов, как если бы метод не возвращал результата.
MsgBox myRange.Address
MsgBox myRange.Address(,,xlR1C1)
Метод
Address
(
который принадлежит объекту
Range
) возвращает адрес диапазона ячеек в рабочем листе как строку.
Хотя в первом примере метод
Address вызывается без каких-либо аргументов, он, на самом деле, имеет несколько необязательных аргументов.
Эти необязательные аргументы определяют стиль адреса ячеек листа, а так же являются ли координаты ячеек абсолютными или относительными. Во втором примере показан вызов метода
Address с его третьим необязательным аргументом (который определяет стиль возвращаемых координат).
В таблице 3 приведены некоторые из наиболее употребительных или полезных методов объектов Excel.
Таблица 3
Метод
Назначение
Имеется в объектах
Activate
Активирует объект
Window,
WorkBook,
WorkSheet, Range и др.
Calculate
Выполняет вычисления в открытой рабочей книге, рабочем листе или диапазоне
Application,
WorkSheet,
Range
Clear
Удаляет данные, сохраненные в указанном объекте
Range
Close
Закрывает указанный объект
Window,
WorkBook,
WorkBooks
Save
Сохраняет файл рабочей книги
Application, WorkBook
SaveAs
Сохраняет указанный объект в другом файле
WorkBook, WorkSheet
Select
Выбирает указанный объект
Range,
Sheets,
12
ИмяОбъекта.Метод Аргумент1:=Значение1, Аргумент2:=Значение2
Вызов метода с использованием именованных аргументов имеет 2 преимущества. Прежде всего, это наглядность кодов программы. Второе преимущество – в простоте. В этом случае, указывать необходимо только те аргументы, значение которых определяются вызывающей программой.
InstBook.Activate
ActiveWorkbook.SaveAs _
Filename:="D:\VBA2000\NEWFILE. xls”, _
FileFormat:=xlNormal
Многие объекты имеют методы которые возвращают значения так же, как это делает функция. Чтобы использовать значение, возвращаемое методом, необходимо поместить список аргументов метода в круглые скобки и включить вызов метода в другое выражение. Можно также игнорировать результат, возвращаемый методом. Чтобы игнорировать результат метода (если он имеет результат), вызовите метод без круглых скобок вокруг списка аргументов, как если бы метод не возвращал результата.
MsgBox myRange.Address
MsgBox myRange.Address(,,xlR1C1)
Метод
Address
(
который принадлежит объекту
Range
) возвращает адрес диапазона ячеек в рабочем листе как строку.
Хотя в первом примере метод
Address вызывается без каких-либо аргументов, он, на самом деле, имеет несколько необязательных аргументов.
Эти необязательные аргументы определяют стиль адреса ячеек листа, а так же являются ли координаты ячеек абсолютными или относительными. Во втором примере показан вызов метода
Address с его третьим необязательным аргументом (который определяет стиль возвращаемых координат).
В таблице 3 приведены некоторые из наиболее употребительных или полезных методов объектов Excel.
Таблица 3
Метод
Назначение
Имеется в объектах
Activate
Активирует объект
Window,
WorkBook,
WorkSheet, Range и др.
Calculate
Выполняет вычисления в открытой рабочей книге, рабочем листе или диапазоне
Application,
WorkSheet,
Range
Clear
Удаляет данные, сохраненные в указанном объекте
Range
Close
Закрывает указанный объект
Window,
WorkBook,
WorkBooks
Save
Сохраняет файл рабочей книги
Application, WorkBook
SaveAs
Сохраняет указанный объект в другом файле
WorkBook, WorkSheet
Select
Выбирает указанный объект
Range,
Sheets,
12
Метод
Назначение
Имеется в объектах
WorkSheets
Задание 10
Создайте новую книгу, введите некоторые данные на лист. Напишите макрос, который сначала сохраняет активную книгу в папку
“\VBA_Excel\
Рабочая\Методы.xls
”
, а затем очищает данные с листа. После чего выдает предупреждение, что книга будет закрыта и после щелчка по кнопке Ок закрывает активную книгу и само приложение Excel.
!
Обратите внимание, что если вызывать метод закрытия книги перед вызовом метода
закрытия приложения, то приложение не закрывается по причине отсутствия макроса
в момент закрытия приложения.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
4.
Работа с коллекциями объектов и контейнерами объектов
Коллекция объектов – это группа связанных объектов, таких как все рабочие листы в рабочей книге. Объект в коллекции называется элементом этой коллекции.
Сама коллекция тоже является объектом. Коллекции имеют собственные свойства и методы. Каждая коллекция имеет свойство Count, которое возвращает число элементов в коллекции.
Application.ActiveWorkbook.Worksheets.Count
В этом выражении
Worksheets
– коллекция всех рабочих листов в рабочей книге,
ActiveWorkbook
– свойство объекта
Application
, возвращающее активную рабочую книгу.
Объект Application включает в себя коллекцию (семейство) объектов
Книга (WorkBook). Семейство книг называется WorkBooks. Обратиться к конкретной к книге из семейства можно указав название книги или ее номер в коллекции.
Application.Workbooks("
Книга1.xlsx").Activate
Application.Workbooks(1).Activate
Application.Workbooks.Item(1).Activate
Application.Workbooks.Item(“
Книга1.xlsx”).Activate
Все четыре примера имеют одинаковый результат. В первом случае, мы напрямую обратились к книге из семейства по имени, во втором по номеру, а в третьем используя свойство
Item
– вложенная коллекция элементов (в данном случае книг).
В таблицах 4 и 5 приведены часто используемые свойства и методы коллекций
Workbooks и
Worksheets
Таблица 4
Свойство
Назначение
Имеется в объектах
Count
Возвращает количество объектов в коллекции
Workbooks, Worksheets
Item вложенная коллекция элементов
(
объектов)
Workbooks, Worksheets
Visible
Устанавливает видимость или Worksheets
13
скрытость листа
Таблица 5
Метод
Назначение
Имеется в объектах
Add
Добавляет в коллекцию объект
Workbooks, Worksheets
Open
Открывает заданный по имени и пути объект
Workbooks
Close
Закрывает все книги семейства
Workbooks
Copy
Копирует заданный лист
Worksheets
Delete
Удаляет заданный лист
Worksheets
Move
Перемещает заданный лист
Worksheets
Select
Выбирает заданный лист
Worksheets
Задание 11
Выясните каков результат выполнения следующих макросов:
Sub Test1()
Application.Workbooks.Add ("
Книга")
End Sub
Sub Test2()
Application.Workbooks.Open ("c:\1\My.xls")
End Sub
Sub Test3()
Application.Workbooks.Item(1).Close
End Sub
Sub Test4()
Application.Workbooks.Close
End Sub
Sub Test5()
MsgBox Application.Workbooks.Item(1).Name
MsgBox Application.Workbooks.Item("Test5.xls").FullName
End Sub
5.
Задание для самостоятельного выполнения
Написать следующие макросы:
1)
Создать новую книгу, запрашивает имя этой книги и дает такое же имя первому листу этой книги.
!
Используйте диалоговое окно InputBox() для диалога с пользователем и запроса имени
книги.
2)
Создать книгу под именем Пример.xlsx. В этой книге создать таблицу с 2 столбцами – Должность и Оклад, заполните в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в книгу, содержащую
14
Таблица 5
Метод
Назначение
Имеется в объектах
Add
Добавляет в коллекцию объект
Workbooks, Worksheets
Open
Открывает заданный по имени и пути объект
Workbooks
Close
Закрывает все книги семейства
Workbooks
Copy
Копирует заданный лист
Worksheets
Delete
Удаляет заданный лист
Worksheets
Move
Перемещает заданный лист
Worksheets
Select
Выбирает заданный лист
Worksheets
Задание 11
Выясните каков результат выполнения следующих макросов:
Sub Test1()
Application.Workbooks.Add ("
Книга")
End Sub
Sub Test2()
Application.Workbooks.Open ("c:\1\My.xls")
End Sub
Sub Test3()
Application.Workbooks.Item(1).Close
End Sub
Sub Test4()
Application.Workbooks.Close
End Sub
Sub Test5()
MsgBox Application.Workbooks.Item(1).Name
MsgBox Application.Workbooks.Item("Test5.xls").FullName
End Sub
5.
Задание для самостоятельного выполнения
Написать следующие макросы:
1)
Создать новую книгу, запрашивает имя этой книги и дает такое же имя первому листу этой книги.
!
Используйте диалоговое окно InputBox() для диалога с пользователем и запроса имени
книги.
2)
Создать книгу под именем Пример.xlsx. В этой книге создать таблицу с 2 столбцами – Должность и Оклад, заполните в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в книгу, содержащую
14
этот макрос.
3)
Создать книгу под именем Проба.xlsx. В этой книге создать таблицу с 2 столбцами – Должность и Оклад, заполните в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в
новую книгу.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
6.
Обработчики событий
Событие – это действие, распознаваемое объектом, для которого можно запрограммировать отклик.
Например, в качестве события можно использовать открытие или закрытие документа, щелчок мыши, нажатие клавиши.
Набор действий или повторяющихся явлений, которые можно сопоставить с кодом VBA, называются событиями, а специальный тип процедуры, которая выполняется при возникновении события, называется
обработчиком события.
Обработчики событий дают возможность привязать свой код к действиям пользователя, например к открытию или закрытию книги, активации листа, сохранению документа… Обработчики событий создаются в модулях листов или книги (в зависимости от того, с каким объектом будет связано это событие.
Чтобы создать процедуру обработки события, необходимо открыть редактор
Visual Basic
, выберите например Лист1 и из двух выпадающих списков сверху выберите объект и событие (см. рисунок 6). Редактор автоматически создаст процедуру обработки события. Разработчику остается написать в ней свой код
(
см. рисунок 7).
Рисунок 6 – Выбор объекта и события
15
3)
Создать книгу под именем Проба.xlsx. В этой книге создать таблицу с 2 столбцами – Должность и Оклад, заполните в этой таблице 3 строки. Написать макрос, который открывает эту книгу и копирует лист с этой таблицей в
новую книгу.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
6.
Обработчики событий
Событие – это действие, распознаваемое объектом, для которого можно запрограммировать отклик.
Например, в качестве события можно использовать открытие или закрытие документа, щелчок мыши, нажатие клавиши.
Набор действий или повторяющихся явлений, которые можно сопоставить с кодом VBA, называются событиями, а специальный тип процедуры, которая выполняется при возникновении события, называется
обработчиком события.
Обработчики событий дают возможность привязать свой код к действиям пользователя, например к открытию или закрытию книги, активации листа, сохранению документа… Обработчики событий создаются в модулях листов или книги (в зависимости от того, с каким объектом будет связано это событие.
Чтобы создать процедуру обработки события, необходимо открыть редактор
Visual Basic
, выберите например Лист1 и из двух выпадающих списков сверху выберите объект и событие (см. рисунок 6). Редактор автоматически создаст процедуру обработки события. Разработчику остается написать в ней свой код
(
см. рисунок 7).
Рисунок 6 – Выбор объекта и события
15
Рисунок 7 – Процедуры обработки событий
Многие события имеют параметры. Это выглядит так:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range,
Cancel As Boolean)
End Sub
Здесь
Target
– диапазон ячеек, подвергшийся процедуре правого клика,
Cancel
– параметр, позволяющий отменить событие, если установить его в
True
Таблица 6
События объекта Workbook
Событие
Описание
BeforeClose
При закрытии рабочей книги
BeforePrint
Перед печатью рабочей книги
BeforeSave
Перед сохранением рабочей книги
Deactivate
Когда рабочая книга теряет фокус
NewSheet
При добавлении нового листа
Open
При открытии рабочей книги
SheetActivate
При активации любого рабочего листа
SheetDeactivate
Когда рабочий лист теряет фокус
Таблица 7
События объекта WorkSheet
Событие
Описание
Activate
Когда активируется рабочий лист
Deactivate
Когда рабочий лист теряет фокус
BeforeDblClick
Перед двойным щелчком
BeforeRightClick
Перед щелчком правой клавиши мыши
Change
Когда меняется значение ячеек листа
16
Задание 12
Определите в каждом из приведённых ниже обработчиков событий объект воздействия и событие.
Private Sub Workbook_Open()
Workbooks.Open "
Макросы.xlsx"
End Sub
Private Sub Worksheet_Activate()
MsgBox "
Привет!"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sheets(5).Visible = True
End Sub
Задание 13
Назовите в каждом из задании события и объекты этих событий:
1)
Создать обработчик, в котором при изменении данных в ячейки первого листа этот лист перемещается в конец.
2)
Создать обработчик, в котором при активизации листа, лист следующий за ним скрывается, а при уходе с этого листа снова открывается.
3)
Создать обработчик, в котором при изменении данных в ячейке эта ячейка окрашивается цветом. После выхода из нее возвращается в прежнее состояние.
4)
Создать обработчик, в котором при открытии книги первый и второй лист скрываются, а при активизации Листа3 они снова отображаются.
Пригласите преподавателя и продемонстрируйте выполнение заданий.
Задание 14
Создайте макросы для пунктов 2-4 предыдущего задания.
7.
Вопросы для самоконтроля
(1)
Перечислите основные объекты VBA MS Excel.
(2)
Перечислите основные коллекции объектов VBA MS Excel.
(3)
В чем разница объекта и коллекции объектов VBA MS Excel?
(4)
Назовите пункты, в которых указаны идентификаторы объектов,
коллекций:
a. ActiveSheet
b. Worksheets
c. ActiveWorkbook
d.
ThisWorkbook
e. Workbooks
f.
Sheets
(5)
В каких случаях используют обращение через идентификаторы
коллекций?
17