Работа с ячейками - объект Range

15.1. Как обратиться к ячейке

15-01-Excel Обращение к ячейкам.xlsm - пример к п. 15.1.

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

Выше мы уже обращались к ячейкам в некоторых примерах. Здесь мы кратко обобщим и поясним основные способы обращения к ячейкам.

Можно адресовать ячейку или диапазон ячеек, указав их адреса в стиле A1. Здесь и далее мы используем метод Select объекта Range, который выделяет ячейки (листинг 15.1.)

ActiveSheet.Range("A2").Select

Для обращения к диапазону ячеек нужно знать верхнюю левую и нижнюю правую границы диапазона. Например, для обращения к диапазону высотой в одну строку от A2 до E2 или к диапазону A2:E4 - понадобится такой код (листинг 15.2.)

ActiveSheet.Range("A2:E2").Select
ActiveSheet.Range("A2:E4").Select

Можно воспользоваться конструкцией с использованием объекта Cells, который позволяет обращаться к отдельной ячейке по ее индексу в формате R1C1. Чтобы обратиться к ячейке A5 таким способом, нужно заметить, что она расположена в пятой строке и первом столбце (листинг 15.3.):

ActiveSheet.Cells(5,1).Select

Можно объединить использование Range и Cells, указав координаты ячеек при адресации диапазона с помощью Cells (листинг 15.4.).

ActiveSheet.Range(Cells(5, 4), _
        Cells(7, 5)).Select

Нам уже встречалось использование Cells для доступа к группам ячеек в цикле - в качестве индексов ячеек можно использовать переменные (листинг 15.5.)

For i = 1 To 3
        For j = 1 To 3
            ActiveSheet.Cells(i, j).Select
            Application.Wait (Now + _
            TimeValue("0:00:01"))
            p = p + 1
            Selection = p
        Next j
    Next i
ActiveSheet.Range("A1:E5").Clear

Здесь мы циклически выделяем ячейки диапазона A1:C3, делая задержку на 1 секунду после каждого выделения и выводя количество прошедших с начала работы программы секунд. Здесь мы воспользовались для выделения ячейки уже знакомым вам методом Select, а для ввода данных в выделенную ячейку применили объект Selection, который в данном случае ссылается на выделенную ячейку. В конце мы очистили диапазон A1:E5 от введенных данных.

Объект Selection - это еще один способ работы с ячейками, однако он используется сравнительно редко, так как к ячейкам удобнее обращаться по их именам.

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

Dim obj_MyCells As Range
Set obj_MyCells = ActiveSheet.Cells(5, 5)
obj_MyCells.Select

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

В листинге 15.7 мы сначала выделяем столбец A, потом столбец B, используя коллекцию Columns (столбцы), 3-ю строку, используя коллекцию Rows (строки) а далее - лист целиком.

ActiveSheet.Range("A:A").Select
    ActiveSheet.Columns("B:B").Select
    ActiveSheet.Range("3:3").Select
    ActiveSheet.Rows("4:4").Select
    ActiveSheet.Cells.Select

Еще один способ обращения к ячейкам - применение именованных диапазонов (коллекция Names ) мы рассмотрим ниже. А теперь поговорим о методах и свойствах объекта Range.

15.2. Методы Range

15.2.1. Activate - активация ячейки

15-02-Range Activate.xlsm - пример к п. 15.2.1.

Позволяет выбрать ячейку в выделенном диапазоне. Даже когда выделен диапазон ячеек, активной является лишь одна из них. Чтобы изменить эту активную ячейку, и применяется данный метод. Если использовать вместо метода Activate метод Select, то ячейка будет выделена, а остальное выделение - снято. В то же время, если попытаться активировать ячейку, расположенную вне выделенного диапазона, выделение снимется, и активированная ячейка окажется выделенной.

Например, в листинге 15.8. мы сначала выделили диапазон ячеек, а потом, не снимая выделения, сделали одну из ячеек диапазона активной.

Range("A1:E5").Select
    Range("C2").Activate

15.2.2. AddComment - добавляем комментарии к ячейкам

Позволяет добавлять комментарии к ячейкам. Если вы формируете какой-нибудь Excel-документ программно, вы можете добавить в некоторые ячейки комментарии для пояснения данных, которые в них хранятся. В листинге 15.9. мы добавляем комментарий к ячейке C3.

Range("C3").AddComment ("Проверка комментария")

В правом верхнем углу ячейки появится красный треугольник, а наведя мышь на ячейку, можно увидеть текст комментария (рис. 15.1.).

15.2.3. AutoFit - автонастройка ширины столбцов и высоты строк

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

Метод можно применять как к диапазону, так и к отдельным строкам или столбцам.

Например, код в листинге 15.10. позволяет автоматически подобрать ширину столбцов A, B, C, D, E, руководствуясь данными, расположенными в первой строке этих столбцов. Если в других строках столбцов будут более длинные значения - они не будут приняты во внимание.

ActiveSheet.Range("A1:E1").Columns.AutoFit

Мы не случайно обращаемся здесь к свойству Columns объекта Range - иначе метод AutoFit не работает. Если же в подобном вызове не задавать конкретной строки, а выполнить эту команду так (листинг 15.11.), то ширина столбцов A - E будет подстроена таким образом, чтобы наилучшим образом вместить самое длинное из значений, хранящихся в ячейках, принадлежащих столбцам.

ActiveSheet.Range("A:E").Columns.AutoFit

15.2.4. Clear, ClearComments, ClearContents, ClearFormats - очистка и удаление

Метод Clear позволяет очистить диапазон - он удаляет данные и форматирование из ячеек. Например, в листинге 15.12. мы очищаем от форматирования сначала диапазон A1:E5, а потом - весь лист.

ActiveSheet.Range("A1:E5").Clear
Activesheet.Cells.Select
Selection.Clear

Другие методы, название которых начинается с Clear, позволяют очищать ячейки от соответствующих им объектов.

ClearContents очищает содержимое ячеек, не затрагивая форматирование. Если вы выделите ячейки и нажмете клавишу Del на клавиатуре - вы добъетесь того же эффекта.

ClearFormats очищает лишь форматирование ячеек, не затрагивая содержимого.

15.2.5. Copy, Cut, PasteSpecial - буфер обмена

Выше мы уже рассматривали команды для работы с буфером обмена в MS Excel. Метод Copy копирует содержимое диапазона в буфер обмена, Cut - вырезает, PasteSpecial осуществляет специальную вставку.

Как ни странно, объект Range не поддерживает метод Paste, осуществляющий обычную вставку, однако, этот метод поддерживает объект Worksheet.

15.2.6. Delete - удалить диапазон

Удаляет выделенный диапазон - остальные ячейки сдвигаются, занимая его место.

15.2.7. Merge, UnMerge - объединение ячеек

15-03-Range Merge.xlsm - пример к п. 15.2.7.

Merge позволяет создать одну объединенную ячейку из заданного диапазона.

UnMerge разбивает объединенную ячейку на обычные ячейки.

Объединенные ячейки удобно использовать для хранения в них названий таблиц.

В листинге 15.13. мы программно формируем таблицу шириной в 10 ячеек. Заполняем ее данными, автоматически подстраиваем ширину столбцов под введенные значения. После этого вводим в левую верхнюю ячейку строки, которая расположена над таблицей, название таблицы, и объединяем все ячейки до конца таблицы, расположенные левее строки с названием. В итоге название будет отображено в одной большой строчке, занимающей всю верхнюю часть таблицы (рис. 15.2.).

'Заполняем область C3:L2
    'случайными целыми числами
    For i = 1 To 10
        For j = 1 To 10
            ActiveSheet.Cells(i + 2, j + 2) = _
            Int(Rnd * 100)
        Next j
    Next i
    'Выравниваем размер столбцов
    ActiveSheet.Range("C:L").Columns.AutoFit
    'Записываем название таблицы
    'в ячейку верхней строчки
    Range("C2") = "Название таблицы"
    'Объединяем ячейки над таблицей
    Range("C2:L2").Merge

15.2.8. Select - выделение ячейки

15-04-Range Select.xlsm — пример к п. 7.7.2.8.

Выделяет ячейки или ячейку. Выделив ячейку, к ней можно обращаться, используя объект Selection. Так же этот объект можно использовать для работы с ячейками, предварительно выделенными пользователями.

Например, в листинге 15.14. мы находим сумму чисел, которые хранятся в ячейках диапазона, выделенного пользователем перед запуском макроса.

Dim obj_Range As Range
    Dim num_Sum
    'Обращаемся к каждой ячейке
    'в выделенной области
    For Each obj_Range In Selection.Cells
        num_Sum = num_Sum + Val(obj_Range)
    Next
    MsgBox ("Сумма выделенных ячеек: "  _
    num_Sum)

15.3. Свойства Range

15.3.1. Address - адрес ячейки в формате A1

15-05-Range Address.xlsm - пример к п. 15.3.1.

Возвращает строку, представляющую собой адрес ячейки в формате A1. Адрес выводится в абсолютном виде - снабжается знаками $.

Листинг 15.15 позволяет, задав адрес ячейки в виде R1C1, вывести ее адрес в формате A1.

Dim num_Row
    Dim num_Col
    Dim MyRange As Range
    num_Row = Val(InputBox("Введите строку"))
    num_Col = Val(InputBox("Введите столбец"))
    Set MyRange = _
        ActiveSheet.Cells(num_Row, num_Col)
    MsgBox (MyRange.Address + _
    " - имя ячейки "  _
    " с индексами "  num_Row  " и "  num_Col)

15.3.2. Areas - работа с несмежными выделенными областями

15-06-Range Areas.xlsm - пример к п. 15.3.2.

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

'Для хранения исходной
    'выделенной области
    Dim obj_Area As Range
    'Для хранения ссылки на
    'отдельные диапазоны
    Dim obj_Range As Range
    'Для исходного листа
    Dim obj_Sheet As Worksheet
    'Для каждого из новых листов
    Dim obj_OldSheet As Worksheet
    'Присвоим ссылку на выделенную область
    Set obj_Area = Selection
    'Ссылка на активный лист
    Set obj_OldSheet = ActiveSheet
    'Для каждой несмежной области в
    'выделении
    For Each obj_Range In obj_Area.Areas
        'Копируем эту область
        obj_OldSheet.Activate
        obj_Range.Select
        Selection.Copy
        'Создаем новый лист
        'и вставляем в него
        Set obj_Sheet = Worksheets.Add
        obj_Sheet.Select
        obj_Sheet.Paste
    Next

15.3.3. Borders - управление границами ячеек

15-07-Range Borders.xlsm - пример к п. 15.3.3.

Позволяет управлять границами ячеек. Границы ячеек обычно используются для оформления таблиц. Как правило, работа ведется с неким выделенным диапазоном ячеек, для которого настраивают внешние границы, внутренние границы, типы и цвета линий. Работа с границами ячеек ведется посредством свойств объектов Border. Собственно говоря, самое важное свойство коллекции Borders - это Item, дающее доступ к отдельным объектам Border - то есть к границам. Остальные действия с границами проводятся с помощью свойств объектов Border.

Item - свойство, которое возвращает отдельную границу по индексу или имени границы. Принято использовать следующие имена границ:

Когда выбрана граница, с которой вы хотите работать, можно использовать свойства объекта Border, в частности, следующие:

Color - позволяет задавать цвет границы. Для задания цвета можно использовать функцию RGB, которая по переданным ей значениям цветовых компонентов в формате RGB возвращает нужный цвет. Например, такой вызов этой функции возвратит красный цвет: RGB(255,0,0). Также здесь можно использовать цветовые константы: vbBlack, vbRed и т.д.

LineStyle - позволяет задавать тип линии. Здесь применимо несколько констант. В частности, следующие:

Weight - задает толщину линии при помощи указания одной из констант:

Давайте рассмотрим пример (листинг 15.17.). Выведем набор значений в таблицу, отформатируем ее таким образом, чтобы внешние границы состояли из сплошных черных линий средней толщины, внутренние - из точечных тонких красных линий (рис. 15.3.)

Dim obj_Range As Range
    'Добавляем в книгу новый лист
    'он автоматически становится активным
    Worksheets.Add
    ActiveSheet.Name = "Новая таблица"
    'заполняем небольшую таблицу данными
    For i = 1 To 5
        For j = 1 To 5
            ActiveSheet.Cells(i + 1, j + 1) = _
            Int(Rnd * 100)
        Next j
    Next i
    'Свойство CurrentRegion возвращает
    'заполненную данными область вокруг
    'ячейки, для которой вызывается
    Set obj_Range = ActiveSheet.Cells(i, j).CurrentRegion
    'Настраиваем свойства каждой из границ
    With obj_Range.Borders(xlEdgeLeft)
        .LineStyle = xlDContinuous
        .Color = vbBlack
        .Weight = xlMedium
    End With
    With obj_Range.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlMedium
    End With
    With obj_Range.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlMedium
    End With
    With obj_Range.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlMedium
    End With
    With obj_Range.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .Color = RGB(255, 0, 0)
        .Weight = xlThin
    End With
    With obj_Range.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .Color = RGB(255, 0, 0)
        .Weight = xlThin
    End With