В начало

Приложение. Ответы

10-й час. Объект Range

Объект Range (Диапазон), по всей видимости, наиболее часто используемый в процедурах VBA объект Excel. Так же, как и пользователи Excel в основном работают с ячейками и диапазонами, так и у программистов VBA больше всего времени уходит на работу с объектом Range (наряду с созданием интерфейса приложения). В этом часе мы изучим различные методы работы с диапазонами.

В этом часе будут рассмотрены следующие вопросы.

• Роль объекта Range в VBA.
• Использование оператора With.
• Использование оператора For Each.
• Анализ процедур, использующих оператор Range.

Описание объекта Range

Поскольку вы создаете приложение для Excel, то естественно ваше внимание к ячейкам рабочего листа. В VBA ячейки трактуются как объект Range. Среди всех объектов Excel этот объект используется наиболее часто. Поэтому текущий час будет полностью посвящен объекту Range.

В качестве объекта Range могут выступать:

• отдельная ячейка;
• выделенный диапазон ячеек;
• несколько выделенных диапазонов ячеек (т.е. совокупность несмежных диапазонов);
• строка и столбец;
• трехмерный диапазон (т.е. состоящий из диапазонов, расположенных на разных рабочих листах).

Свойства объекта Range

Изучение любого объекта - это прежде всего изучение его свойств и методов. Среди всех свойств объекта Range выделим следующие.

• Address (Адрес) - возвращает текущее положение диапазона.
• Count (Счет) - возвращает количество ячеек в диапазоне.
• Formula (Формула) - возвращает формулу, по которой вычисляется значение, отображаемое в ячейке.
• Offset (Смещение) - возвращает величину смещения одного диапазона относительно другого.
• Resize (Изменение размеров) - позволяет изменять текущее выделение диапазона.
• Value (Значение) - возвращает значения ячеек, составляющих диапазон.

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

Чтобы поэкспериментировать со свойствами объекта Range, создадим процедуру. Сначала закройте все открытые рабочие книги, а затем откройте новую рабочую книгу. Будем считать, что текущий рабочий лист у вас Лист1.

1. Введите число 100 в ячейку В1, число 200 - в ячейку В2 и 300 - в ячейку В3.
2. В ячейку В4 введите формулу =СУММ (В1: В3).
3. Нажмите комбинацию клавиш <Alt+F11>, чтобы открыть редактор Visual Basic, и вставьте модуль в текущую рабочую книгу.
4. Создайте новую процедуру и назовите ее СвойстваДиапазона.
5. Введите код процедуры

ThisWorkbook.Worksheets("Лист1").Range("A1").Activate

ActiveCell.Offset(2,2).Activate

MsgBox "Текущая ячейка - " & ActiveCell.Address

MsgBox "Значение ячейки B4 = " & Range("B4").Value

MsgBox "Формула в ячейке В4: " & Range("B4").Formula

6. Перейдите на Лист1 рабочей книги и выполните процедуру СвойстваДиапазона. Появится первое окно с сообщением, что текущей ячейкой является ячейка С3 (рис. 10.1).
7. Щелкните на кнопке ОК. Следующее окно сообщения покажет значение, отображаемое в ячейке В4.
8. Щелкните на кнопке ОК. Последнее окно покажет формулу, содержащуюся в ячейке В4. (Обратите внимание, что хотя формула в ячейке В4 введена русскими буквами, процедура корректно ее обработала и преобразовала в формулу англоязычной версии Excel. - Прим. ред.)
9. Щелкните на кнопке ОК для закрытия окна сообщения.

Рис. 10.1. В создании текста этого сообщения использовано свойство Address

Рассмотрим эту процедуру подробнее. Ее полный код приведен в листинге 10.1.

Листинг 10.1. Процедура СвойстваДиапазона

1: Sub СвойстваДиапазона ()

2:  ThisWorkbook.Worksheets("Лист1").Range("A1").Activate

3:  ActiveCell.Offset(2,2).Activate

4:  MsgBox "Текущая ячейка" & ActiveCell.Address

5:

6:  MsgBox "Значение ячейки B4 = " & Range("B4").Value

7:  MsgBox "Формула в ячейке В4: " & Range("В4").Formula

8: End Sub

Процедура начинается с активизации ячейки А1 (т.е. установки на ней табличного курсора):

ThisWorkbook.Worksheets("Лист1").Range("A1").Activate

После активизации ячейки A1 свойство Offset перемещает табличный курсор на ячейку С3, процедура показывает адрес новой активной ячейки в окне сообщения:

ActiveCell.Offset(2,2).Activate

MsgBox "Текущая ячейка - " & ActiveCell.Address

Свойство Offset позволяет перемещаться от одного диапазона к другому и имеет следующий синтаксис:

имя__диапазона.Offset (смещ_строк, смещ_столбцов)

Аргументы смещ_строк и смещ_столбцов задают направление перемещения табличного курсора. В нашей процедуре оба аргумента принимают значение 2, что позволяет перейти из ячейки А1 в ячейку С3.

Последние два оператора процедуры с помощью окон сообщения отображают значение и формулу ячейки В4:

MsgBox "Значение ячейки В4 = " & Range("B4").Value

MsgBox "Формула в ячейке В4: " & Range("B4").Formula

Эти операторы показывают различие между свойствами Value и Formula. Свойство Value возвращает то, что отображается в ячейке, а свойство Formula - то, что находится в ячейке.

Методы объекта Range

Объект Range имеет много методов, среди которых следующие.

• Activate (Активизировать) - активизирует (выделяет) диапазон.
• Clear (Очистить) - очищает содержимое диапазона.
• Сору (Копировать) - копирует содержимое диапазона в буфер обмена.
• Cut (Вырезать) - перемещает содержимое диапазона в буфер обмена.
• PasteSpecial (Специальная вставка) - вставляет содержимое буфера обмена в диапазон, используя при этом различные аргументы.
• Select (Выделить) - выделяет диапазон.

Использование оператора With

Теперь, когда вы знакомы с объектами Excel, пришло время изучить несколько конструкций языка VBA, значительно облегчающих работу с объектами. Одна из задач, которую часто придется решать с помощью VBA, - задание различных параметров форматирования объекта. В листинге 10.2 показан пример форматирования диапазона ячеек.

Листинг 10.2. Пример форматирования диапазона

1: Range("A1:A6").NumberFormat = "#,##0.00"

2: Range("A1:A6").Font.Name = "Courier New"

3: Range("A1:A6").Font.FontStyle = "Regular"

4: Range("A1:A6").Font.Size = 11

5: Range("A1:A6").Font.Strikethrough = False

6: Range("A1:A6").Font.Superscript = False

7: Range("A1:A6").Font.Subscript = False

8: Range ("A1:A6").Font.OutlineFont = False

9: Range("A1:A6").Font.Shadow = False

10: Range("A1:A6").Font.Underline = xlUnderlineStyleNone

11: Range("A1:A6").Font.ColorIndex = xlAutomatic

Чтобы ввести код этого листинга, вам надо снова и снова набирать Range("А1:А6"). Конечно, можно использовать переменную-объект для ссылки на объект Range("A1:A6"), но в этом случае вам столько же раз придется вводить имя переменной. Из подобных ситуаций VBA предлагает другой выход - оператор With, который применяется тогда, когда необходимо задать много свойств или выполнить большое количество методов одного объекта. Листинг 10.3 показывает тот же код листинга 10.2, но с использованием оператора With.

Листинг 10.3. Пример использования оператора With

1: With Range("A1:A6")

2:  .NumberFormat = "#,##0.00"

3:  With.Font

4:   .Name = "Courier New"

5:   .FontStyle = "Regular"

6:   .Size = 11

7:   .Strikethrough = False

8:   .SuperScript = False

9:   .SubScript = False

10:   .OutlineFont = False

11:   .Shadow = False

12:   .Underline = xlUnderlineStyleNone

13:   .Font.ColorIndex = xlAutomatic

14:  End With

15: End With

Оператор With имеет следующий синтаксис:

With объект

 [операторы]

End With

Объект - это тот объект, чьи свойства и методы указаны в секции операторы. Каждая строка в этой секции начинается с точки, как показано в листинге 10.3. Операторы With можно вкладывать друг в друга. В нашем листинге оператор With объекта Font вложен в оператор With объекта Range. В листинге 10.4 приведен другой пример использования оператора with.

Листинг 10.4. Оператор With, содержащий оператор и метод

1: Sub WithWorksheet()

2:  With ThisWorkbook

3:   .SaveAs "ПримерWith"

4:   MsgBox "Статус сохранения: " & .Saved

5:  End With

6: End Sub

Оператор For Each

Если оператор With используется для выполнения нескольких операторов для одного объекта, то оператор For Each применяется для выполнения одного и того же набора операторов для нескольких объектов. Этот оператор дает возможность повторить набор операторов для всех элементов коллекции.

Оператор For Each можно применять к массивам.

Оператор For Each имеет следующий синтаксис:

For Each элемент In группа

 [Операторы]

 [Exit For]

 [Операторы]

Next

Отметим, что синтаксис предусматривает выражение Exit For. Так же, как и в других подобных выражениях Exit, для организации досрочного выхода из оператора For Each обычно применяется оператор If.

В листинге 10.5 оператор For Each применяется для изменения значений всех ячеек определенного диапазона.

Листинг 10.5. Пример использования оператора For Each

1: Sub ПримepFor_Each()

2:  Dim x As Range

3:

4:  For Each x In ThisWorkbook.Worksheets("Лист1").Range("A1:A6")

5:   x.Value = x.Value + 10

6:  Next

7: End Sub

Использование объекта Range

В своих лекциях по VBA я постоянно подчеркиваю, что один из эффективных путей изучения программирования - это исследование листингов программ. Оставшуюся часть текущего часа мы посвятим рассмотрению примеров листингов, в которых применяются свойства и методы объекта Range.

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

Рис. 10.2. Рабочий лист, который будет частично переформатирован

Листинг 10.6. Применение объекта Row и оператора цикла

1: Sub Полужирный()

2:  Dim iCounter As Integer

3:

4:  For iCounter = 3 To ThisWorkbook.Worksheets("Лист1"). _

      Range("A1:C25").Rows.Count Step 2

5:   ThisWorkbook.Worksheets("Лист1"). _

      Range("A1:C25").Font.Bold = True

6:  Next

7:

8: End Sub

Основным в этой процедуре является оператор цикла For... Next. Отметим, что переменная-счетчик этого цикла iCounter имеет начальное значение 3, а затем увеличивается с шагом 2, обеспечивая тем самым выделение полужирным начертанием каждой второй строки. На рис. 10.3 показан результат выполнения этой процедуры.

Рис. 10.3. Результат выполнения процедуры

Очевиден недостаток приведенной выше процедуры: здесь заранее указывается размер диапазона. На практике размер диапазона может часто изменяться, например в результате импорта данных из какой-либо базы данных или в результате внесения Новых либо удаления старых данных. Другими словами, в процедуре не должен быть указан конкретный размер диапазона. Код листинга 10.7 выполняет выбор диапазона без указания его размера и местоположения.

Листинг 10.7. Выделение диапазона

1: Sub ВыделениеДиапазона()

2:  ThisWorkbook.Worksheets("Лист1").Range("A1").Activate

3:  ActiveCell.CurrentRegion.Select

4:  MsgBox "Выделен диапазон" & Selection.Address

5: End Sub

Ключевой элемент в этой процедуре - свойство CurrentRegion (Текущая область). Это свойство возвращает диапазон ячеек, содержащий активную ячейку и ограниченный пустыми строками и пустыми столбцами. При выделении диапазона с помощью свойства CurrentRegion нет необходимости заранее знать размер и местоположение выделяемого диапазона.

Пример следующей процедуры показывает выполнение операции "копировать и вставить". Код листинга 10.8 копирует любой заранее выделенный объект в буфер обмена, а затем вставляет его в новое положение на рабочем листе.

Листинг 10.8. Копирование и вставка диапазонов

1: Sub Копировать_Вставить()

2:  Selection.Copy

3:  Range("F3").Select

4:  ActiveSheet.Paste

5:  Application.CutCopyMode = False

6: End Sub

Оператор Selection.Copy помещает выделенный диапазон в буфер обмена. Следующий оператор перемещает табличный курсор в ту ячейку, начиная с которой будет вставлен скопированный диапазон. Вставка диапазона из буфера обмена осуществляется оператором ActiveSheet.Paste. Последний оператор устанавливает значение свойства CutCopyMode (Режим вырезать-вставить) объекта Application как False (Ложь). Если не выполнить этот оператор, то вокруг исходного диапазона (того, что копировался) сохранится пунктирная рамка выделения, а в строке состояния будет по-прежнему отображаться инструкция по копированию диапазона.

Резюме

В этом часе мы исследовали много процедур, где использовались различные свойства и методы объекта Range. Мы рассмотрели также две конструкции VBA, помогающие в работе с объектами. Это оператор With, применяемый для выполнения набора операторов и методов какого-либо объекта, и оператор For Each, позволяющий выполнять одинаковую группу операторов применительно к нескольким объектам.

Вопросы и ответы

Вопрос. Почему Range не является коллекцией?

Ответ. Одним из основных признаков коллекции является возможность добавления новых элементов в коллекцию. Число ячеек (т.е. диапазонов) в рабочем листе предопределено Microsoft и не может быть изменено пользователем. Поэтому Range не является коллекцией.

Вопрос. Чем оператор цикла For отличается от оператора For Each?

Ответ. Давайте сначала посмотрим, что между ними общего. Оба эти оператора выполняют определенную последовательность операторов несколько раз. Но от чего зависит, сколько раз они выполнят эту последовательность операторов? Число выполнений цикла For зависит от заданных начального и конечного значений счетчика цикла. Число выполнений операторов, заданных в операторе For Each, зависит только от числа объектов или количества элементов массива.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Истинно или ложно следующее утверждение: в VBA диапазон всегда состоит из многих ячеек?
2. Какое свойство объекта Range позволяет определить адрес одного диапазона на основе адреса другого?
3. Если необходимо изменить значения всех ячеек диапазона, то какой оператор, требующий минимального кода, следует применить?
4. Какое свойство позволяет выделить диапазон, не определяя заранее его размеры и местоположение, а только зная местоположение хотя бы одной ячейки внутри диапазона?
5. Как определить, какое количество ячеек составляют диапазон?
6. Какой метод применяется для удаления содержимого диапазона?
7. Необходимо установить значения нескольких свойств определенного объекта. Как наиболее эффективно это сделать?

Упражнение

Откройте новую рабочую книгу. Введите следующие значения в первый лист рабочей книги.

Создайте процедуру с именем СнижениеЦен. Процедура должна уменьшить значения всех ячеек из диапазона В2:В6 на 5. Если цена будет меньше или равна нулю, выделите ее и название товара красным полужирным шрифтом. Также в этом случае (цена меньше или равна нулю) процедура должна вывести окно сообщения с соответствующим текстом предупреждения. Выполните процедуру.

1 Если ничего интересного не приходит в голову, ведите просто Товар 1, Товар 2 и т.д. - Прим. ред.