Работа с книгами и листами

14.10. Свойства коллекции Worksheets

14.10.1. Count, Item, Visible - работа с объектами

Мы уже не раз сталкивались с этими свойствами для других объектов. Count позволяет узнать количество объектов в коллекции, Item - обратиться к объекту по имени или индексу, Visible - отображать и скрывать объект.

14.11. Объект Worksheet

Worksheet - рабочий лист - его методы, свойства и события находятся в непосредственной близости от ячеек, с которыми, в основном, ведется работа при программировании для MS Excel.

14.12. Методы объекта Worksheet

Практически все часто используемые методы Workheet вам уже знакомы - либо по описаниям предыдущих объектов и коллекций Excel, либо - по Microsoft Word.

14.12.1. Activate, Calculate, Copy, Delete, Move, Select, PivotTables, ShowAllData

• Activate - делает лист активным.
• Calculate - пересчитывает лист.
• Copy, Delete, Move - соответственно, копирует, удаляет, перемещает лист в книге.
• Select - выделяет лист.
• PivotTables - возвращает коллекцию сводных таблиц, расположенных на листе. Подробнее о сводных таблицах мы поговорим в п. 7.10., посвященному работе с базами данных. ShowAllData - показывает все содержимое рабочего листа - отменяет скрытие данных при фильтровании и т.д.

14.12.2. Paste, PasteSpecial, о работе с Selection

14-08-Excel Worksheet Paste.xlsm - пример к п. 14.2.2.

Метод Paste вставляет содержимое буфера обмена в указанную область. Для копирования данных в буфер обмена используется метод Copy объекта Range (или объекта Selection ).

PasteSpecial осуществляет специальную вставку. Например, с помощью этого метода можно скопировать содержимое ячеек, содержащих формулы и вставить его в другие ячейки, преобразовав таким образом, чтобы вставлены были лишь вычисленные значения формул.

Рассмотрим пример. Внесем в пять горизонтально расположенных ячеек (от A2 до E2 ) какие-нибудь числа, а в шестой ( F2 ) запишем такую формулу: =сумм(A2:E2). В шестой ячейке будет отображена сумма первых пяти. Теперь вручную создадим такой макрос, назвав его DoubleCopy (листинг 14.11.).

Перед запуском макроса выделите область, заполненную данными.

'Копируем выделенную область
    'в буфер обмена
    Application.Selection.Copy
    'Выделяем ячейку A3
    Range("A3").Select
    'Вставляем содержимое буфера обмена
    ActiveSheet.Paste
    'Теперь выделяем диапазон ячеек,
    'в котором хранятся исходные данные
    Range("A2:F2").Select
    Application.Selection.Copy
    Range("A4").Select
    'Вставляем значения - без формул
    Selection.PasteSpecial Paste:=xlPasteValues

Смысл команд этой программы ясен из комментариев. Обратите внимание на то, что здесь мы работали с объектом Selection. Так же посмотрите на содержимое ячеек в файле-примере. При вставке скопированного методом Paste, вставляется абсолютно все - в том числе и формулы, а при использовании PasteSpecial со свойством Paste, установленным в xlPasteSpecial, вставляются лишь значения, без формул.

14.12.3. Protect и Unprotect - защита и снятие защиты

Метод Protect защищает лист от изменений. Его можно вызывать без параметров, однако, при необходимости вы можете очень тонко настроить защиту листа. Например, задать пароль, разрешить пользователю некоторые операции и т.д.

Метод Unprotect снимает защиту с листа.

Установка защиты, особенно - защиты с паролем - может быть полезной, если вы хотите, чтобы пользователь гарантированно не мог внести изменения в какой-либо из листов вашей книги.

14.13. Свойства объекта Worksheet

14.13.1. Cells - ячейки листа

Это свойство возвращает объект типа Range, который представляет собой все ячейки листа. С помощью Cells можно обращаться к каждой конкретной ячейке по отдельности. Например, чтобы записать число 8 в ячейку, находящуюся на пересечении первой строки и пятого столбца текущего листа, надо выполнить такую команду (листинг 14.12.)

ActiveSheet.Cells(1, 5) = 8

Выше мы пользовались свойством Cell для того, чтобы работать с данными на листе. Помимо записи данных в ячейки, данные можно считывать, ячейки можно форматировать и т.д. Об этом - ниже.

14.13.2. EnableCalculation - управление автоматическим пересчетом ячеек

Позволяет включать и отключать автоматический пересчет ячеек. В проектах большого размера пересчет ячеек может занимать много системных ресурсов.

Это свойство можно установить в False если вы, например, осуществляете вывод большого объема данных на лист и хотите ускорить этот процесс.

14.13.3. Next и Previous - перемещение по листам книги

Эти свойства позволяют получать ссылки на следующий, и, соответственно, предыдущий лист книги.

14.13.4. QueryTables - таблицы запросов

Возвращает коллекцию QueryTables, содержащую объекты QueryTable - таблицы запросов, которые используются для вставки в документ информации из баз данных.

14.13.5. Range - работа с ячейками и диапазонами ячеек

Это свойство возвращает объект Range, соответствующей ячейке или диапазону ячеек, указанных при его вызове. Собственно говоря, Range наряду с Cells - это основные инструменты для работы с ячейками листа. Например, так (листинг 14.13.) можно вставить число 4 в ячейку E1:

ActiveSheet.Range("E1") = 4

А такая конструкция (листинг 14.14.) позволяет прибавить по 1 к значению каждой из ячеек диапазона A1:K100

Dim MyCell As Variant
    For Each MyCell In ActiveSheet.Range("A1:K100")
        MyCell.Value = MyCell.Value + 1
    Next

При использовании конструкции For-Each обход ячеек осуществляется по строкам.

Ниже мы рассмотрим объект Range подробнее.

14.13.6. UsedRange - заполненные ячейки

Это свойство возвращает объект Range, содержащий все заполненные ячейки на листе. Это может быть полезным, например, если вы хотите скопировать эти ячейки на другой лист и в других подобных ситуациях.

14.13.7. Visible - скрытие и отображение листов

Позволяет скрывать и отображать листы. По умолчанию листы отображаются - их свойство Visible установлено в True, установив его в False, вы скрываете лист.

14.14. События объекта Worksheet

14-09-Excel Worksheet Change.xlsm - пример к п. 14.14.

Работа с событиями листов очень похожа на работу с событиями книг. Вы могли заметить, что в VBA-проекте, присоединенном к книге, есть объекты каждого из добавленных в книгу листов. Для создания обработчика события листа достаточно сделать двойной щелчок по его объекту и в появившемся окне редактора выбрать объект (для каждого листа это будет Worksheet ), и, как обычно, выбрать событие. Лист может воспринимать следующие события: Activate, BeforeDoubleClick, BeforeRightClick, Calculate, Change, Deactivate, FollowHyperlink, PivotTableUpdate, SelectionChange. Эти события практически полностью идентичны аналогичным событиям для объекта Workbook. Главное отличие заключается в том, что они имеют смысл для каждого конкретного листа, а не для всех листов, как в случае с книгой.

Используем событие Change для проверки данных, вводимых пользователем в различные ячейки рабочего листа. Например, давайте напишем программу, которая предлагает пользователю создать пароль и тут же сообщает о том, насколько его пароль надежен. В качестве критерия надежности будем использовать длину пароля:

• Пароль высокой надежности: длина не менее 8 символов
• Пароль средней надежности: длина 5-7 символов
• Пароль низкой надежности: до 5 символов.

Когда пользователь ввел пароль в ячейку, в соседней ячейке должен появляться прогноз надежности пароля. Пусть пользователь будет вводить пароль в ячейку A2, в ячейке B2 будет отображаться прогноз.

Для решения этой задачи создадим обработчик события Change для листа, на котором будут расположены вышеуказанные поля. Добавим в обработчик такой код (листинг 14.15.)

'Для хранения пароля
    Dim str_Pass As String
    str_Pass = ActiveSheet.Range("A2")
    If Len(str_Pass) >= 8 Then _
        ActiveSheet.Range("B2") = "Надежный пароль"
    If Len(str_Pass) < 8 And Len(str_Pass) >= 5 Then _
        ActiveSheet.Range("B2") = _
        "Пароль средней надежности"
    If Len(str_Pass) < 5 Then _
        ActiveSheet.Range("B2") = _
        "Ненадежный пароль"

14.15. Выводы

В этой лекции мы познакомились с книгами и листами MS Excel. В следующей лекции мы займемся работой с ячейками листа.