Работа с ячейками - объект 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.1. Комментарий в ячейке MS Excel

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. Название таблицы в объединенной ячейке

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)