10.6 Объект Range, его свойства и методы
Объект Excel.Range, программная работа с ячейками и диапазонами (наборами) ячеек средствами VBA, создание объекта Excel.Range, свойства, методы и события объекта Excel.Range
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
- если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
- если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
- если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.
В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:
- самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("A1")
А на диапазон ячеек с A1 по D10 — так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("A1:D10")
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRange1 = Worksheets("Лист1").Range("C1")
Set oRange2 = oRange1.Range("B1")
oRange2.Value = 20
пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).
- второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Dim oRange
Set oRange = Range(Cells(1, 1), Cells(5, 3))
- третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
oRange.Value = "Мое значение"
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:
- Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))
А номер строки — так :
sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))
На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().
- AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
- Areas — свойство исключительно важное. Дело в том, что, как уже говорилось, объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или просто возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки "нестандартности" диапазона:
If Selection.Areas.Count > 1 Then
Debug.Print "Диапазон с несмежными областями"
End If
- Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
- Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range(Cells(2, 2), Cells(5, 3))
Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2
Debug.Print oRange2.Address 'Так оно и есть
Точно такие же особенности у свойств Row и Rows, Column и Columns.
- Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом
Dim oRange As Range
Set oRange = Range("A1")
oRange.Value = "Мой текст"
oRange.Characters(1, 1).Font.Color = vbRed
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
Worksheets("Лист1").Activate
ActiveCell.CurrentRegion.Select
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets("Лист1").Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.
- End — еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В какой стороне будет считаться последняя ячейка, можно определить при помощи передаваемого параметра.
- Errors — свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.
- Font — как и в Word, это свойство позволяет получить доступ к объекту Font, при помощи которого можно настроить особенности оформления текста в ячейке (цвет, шрифт, размер букв и т.п.)
- FormatConditions — возможность создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.
- Formula — одно из самых важных свойств объекта Range. Доступно и на чтение, и на запись. Если используется на чтение, то возвращает текст формулы, прописанной в ячейку (а не вычисленное значение), если используется на запись, то позволяет записать формулу в ячейку. Если применить это свойство для диапазона, в который входит несколько ячеек, то формула будет прописана по все ячейки диапазона. Пример применения этого свойства может выглядеть так:
Worksheets("Лист1").Range("A3").Formula = "=$A$1+$A$2"
- Свойство FormulaLocal, как и AddressLocal, позволяет внести поправки на особенности нумерации ячеек в локализованной версии Excel (для русских версий Excel в нем нет необходимости).
- FormulaHidden — возможность спрятать формулы в диапазоне от пользователя. Работает только на защищенных листах.
- HasFormula — проверить диапазон на наличие вычисляемых значений (формул).
- Hidden — спрятать диапазон. Будет работать только в случае, если диапазон включает в себя хотя бы одну строку или столбец целиком, в противном случае вернется ошибка.
- Interior — еще одно свойство, связанное с форматированием. В основном позволяет покрасить ячейки диапазона.
- Item — позволяет получить еще один объект Range, который определяется путем смещения исходного диапазона.
- Locked — это свойство позволяет заблокировать ячейки диапазона при защите листа.
- Name — возможность получить ссылку на специальный объект именованного диапазона Name. На графическом экране с его возможностями можно познакомиться при помощи меню Вставка -> Имя. Он позволяет обращаться к диапазонам и формулам по именам и несколько напоминает по функциональности объект закладки в Word.
- Next — перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если защищен — то следующая незаблокированная ячейка.
- NumberFormat — возможность установить один из предопределенных форматов для чисел. Соответствует возможностям вкладки Число в меню Формат -> Ячейки на графическом экране.
- Offset — это свойство позволяет получить новый объект Range с определенным смещением от исходного. Например, чтобы получить ячейку со смещением на три ячейки вверх от сходной и три ячейки влево, можно использовать код
Worksheets("Лист1").Activate
ActiveCell.Offset(rowOffset:=-3, columnOffset:=-3).Activate
- свойство Orientation позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах. Например, чтобы расположить текст по диагонали, можно использовать код
oRange.Orientation = -45
- PageBreak — это свойство обычно используется для программной вставки разрывов страницы. Его применение может выглядеть так:
Worksheets("Лист1").Rows(50).PageBreak = xlPageBreakManual
- все свойства, которые начинаются на Pivot…, относятся к работе с объектом PivotTable (сводная таблица). Особенности работы с ней будут рассмотрены ниже.
- QueryTable — это очень важное свойство позволяет получить ссылку на объект QueryTable — полученные с внешнего источника данные. Это свойство для объекта Range позволяет получить ссылку на объект QueryTable, которые находится в данном диапазоне. Подробнее про объект QueryTable будет рассказано ниже.
- Range — это свойство, как уже говорилось выше, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.
- Resize — возможность изменить текущий диапазон. Например, увеличение его на один столбец вниз и одну строку вправо может выглядеть так:
oRange.Resize(oRange.Rows.Count + 1, oRange.Columns.Count + 1).Select
- ShrinkToFit — это свойство позволяет автоматически настроить размер текста в диапазоне таким образом, чтобы текст умещался в ширину столбца.
- Style — это свойство позволяет вернуть объект Style, представляющий стиль для указанного диапазона. На графическом экране то, что позволяет сделать объект Style, можно сделать через меню Формат -> Стиль.
- Text — возможность получить значение первой ячейки диапазона в виде значения типа String. Для объекта Range это свойство доступно только для чтения.
- Validation — это свойство позволяет вернуть объект Validation, при помощи которого можно настроить проверку вводимых в диапазон данных.
- Value — наиболее часто используемое свойство объекта Range. Позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона. Точно для той же цели используется свойство Value2, единственное отличие — это свойство не поддерживает типы данных Currency и Date.
- WrapText — возможность включить/отключить перевод текста на следующую строку в ячейках диапазона.
Теперь — о методах объекта Range:
- Activate() — выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку.
- AddComment() — возможность добавить комментарий к ячейке. Ячейка будет помечена красным уголком, а текст комментария будет показан в виде всплывающей подсказки. Этот метод можно вызвать только для диапазона, состоящего из одной ячейки. То же самое на графическом экране можно сделать при помощи меню Вставка -> Примечание.
- AutoFill() — возможность использовать автозаполнение для диапазона (например, если первые две ячейки будут заполнены как 1 и 2, то дальше в автоматическом режиме будет продолжено: 3, 4, 5 и т.п.)
- AutoFit() — автоматически поменять ширину всех столбцов и высоту всех строк в диапазоне, чтобы туда уместился текст ячеек. Можно применять только к тем диапазонам, которые состоят из набора столбцов (полностью) или набора ячеек (также полностью), иначе будет ошибка.
- AutoFormat() — возможности использовать один из стилей автоформатирования (то, что на графическом экране доступно через меню Формат -> Автоформат).
- BorderAround() — возможность поместить диапазон в рамку с выбранными вами параметрами.
- методы Clear… позволяют очистить содержимое диапазона — от значений, форматирования, комментарий и т.п.
- Consolidate() — возможность слить данные нескольких диапазонов (в том числе на разных листах) в один диапазон, используя при этом выбранную вами агрегатную функцию.
- Copy() — возможность скопировать диапазон в другое место. Если место назначения не указано, он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона вырезаются.
- CopyFromRecordset() — очень удобный метод, который позволяет вставить данные из объекта ADO Recordset на лист Excel, начиная с верхнего левого угла указанного диапазона.
- DataSeries() — метод, который может сэкономить множество времени и избежать возни с функциями даты и времени. Этот метод позволяет увеличить вами значения даты в диапазоне на указанный вами временной интервал. Например, если у вас в диапазоне стоит первое января, то при помощи этого метода можно сгенерировать первое число любого другого месяца.
- Delete() — удаляет данные текущего диапазона. В качестве необязательно параметра можно определить, с какой стороны будут сдвигаться ячейки на место удаленных.
- Dirty() — пометить ячейки диапазона как "грязные". Такие ячейки будут пересчитаны при следующем же пересчете. Обычно используется, когда Word сам не может догадаться, что их нужно пересчитать. Пересчитать ячейки диапазона можно и принудительно — при помощи метода Calculate().
- методы Fill… (FillDown(), FillUp(), FillLeft(), FillRight()) позволяют размножить одно и то же значение по ячейкам диапазона в указанном вами направлении.
- метод Find() позволяет произвести поиск по ячейкам диапазона и вернуть новый объект Range, который представляет первую ячейку, в котором было найдено нужное значение. У этого метода есть множество необязательных параметров, которые позволяют определить направление поиска, чувствительность к регистру, искать все значение ячейки или часть и т.п. Методы FindNext() и FindPrevious() позволяют продолжить поиск, начатый методом Find(), в разных направлениях.
- метод GoalSeek() позволяет применить автоподбор значений для функции Excel программным способом. На графическом экране то же самое можно сделать при помощи меню Сервис -> Подбор параметра.
- метод Insert() позволяет вставить ячейки в диапазон, сдвинув остальные (вы можете выбрать — вправо или вниз).
- метод Justify() позволяет равномерно распределить текст по диапазону. Если в данный диапазон он не помещается, он будет распространен на соседние ячейки (с перезаписью их значений).
- метод Merge() позволяет слить все ячейки диапазона в одну. При этом останется только одно значение — верхней левой ячейки. Разбить обратно такую слитую ячейку на несколько обычных можно при помощи метода UnMerge().
- Parse() — позволяет разбить одну ячейку на несколько по указанному вами шаблону (например, чтобы отделить код города от номера телефона).
- PasteSpecial() — операция, дополняющая Copy() и Cut(). Она позволяет вставить то, что лежит в буфере обмена, с указанием специальных параметров вставки (вставлять с добавлением к существующим данным, с умножением, вычитанием, делением и т.п.)
- PrintOut() и PrintPreview() — позволяют вывести диапазон на печать или открыть режим просмотра перед печатью..
- Replace() — метод, дополняющий метод Find(). Позволяет проводить поиск и замену значений в диапазоне.
- Select() — возможность выделить указанный диапазон. Объекта Selection в Excel нет — вместо него есть возможность получить объект Range, представляющий выделенную область.
- Show() — экран будет проскроллирован таким образом, чтобы показать указанный диапазон.
- ShowDependents() — показать стрелки для ячеек, которые зависят от указанного диапазона (только первый уровень зависимости) или эти стрелки убрать. Обратный метод — ShowPrecedents().
- ShowErrors() — показать источник ошибки для указанной ячейки.
- Sort() — возможность произвести сортировку ячеек в диапазоне. Можно использовать большое количество необязательных параметров для настройки сортировки. SortSpecial() — с учетом особенностей азиатских языков.
- Speak() — удивительный метод, который позволяет зачитывать вслух содержимое диапазона (можно определить, в каком направлении и будут ли зачитываться формулы). К сожалению, в локализованной версии Excel не работает.
- SpecialCells() — очень удобный метод, который позволяет вернуть объект Range, включающий в себя все ячейки определенного типа (пустые, с ошибками, с комментариями, последние, с константами, с формулами, с определенным форматированием) и с определенным значением. Например, чтобы вернуть объект Range, состоящий из всех пустых ячеек диапазона, можно использовать код
Set oRange2 = oRange.SpecialCells(xlCellTypeBlanks)
oRange 2. Select 'проверяем, так ли это
- метод SubTotal() позволяет посчитать итоговое значение для диапазона (можно выбрать агрегатную функцию и множество других параметров).
- метод Table() позволяет создать таблицу на основе передаваемого столбца, строки и функции, которую нужно использовать для вычисления ячеек таблицы. Пример из документации по этому методу позволяет автоматически сгенерировать таблицу умножения.
- TextToColumns() — сложный метод, который позволяет разбить столбцы в диапазоне на несколько столбцов в соответствии с определенным алгоритмом. Принимает множество необязательных параметров.