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

15.3.4. Cells, Columns, Rows - ячейки, столбцы, строки

15-08-Range Cells.xlsm - пример к п. 15.3.4.

Свойство Cells позволяет обращаться к отдельным ячейкам в диапазоне. При работе с этим свойством в отдельном диапазоне ячеек нумерация ячеек ведется по собственной системе координат. Иными словами, диапазон выступает как небольшой виртуальный рабочий лист: левая верхняя ячейка диапазона получает индекс (1, 1), ячейка, расположенная во втором столбце и третьей строке диапазона, - индекс (3,2) и т.д.

Свойства Columns и Rows возвращают, соответственно, коллекции, которые содержат столбцы и строки диапазона. Например, так (листинг 15.18.) можно узнать количество строк в диапазоне, ссылка на который хранится в переменной obj_Range.

num_Rows = obj_Range.Rows.Count

Воспользуемся свойством Cell для диапазона размером 6х5 ячеек, чтобы заполнить этот диапазон данными (листинг 15.19.).

Dim obj_Range As Range
    Set obj_Range = ActiveSheet.Range("B2:F7")
    For i = 1 To obj_Range.Rows.Count
        For j = 1 To obj_Range.Columns.Count
            obj_Range.Cells(i, j) = _
            Int(Rnd * 100)
        Next j
    Next i

Как видите, мы присваиваем ссылку на диапазон ячеек B2:F7 переменной obj_Range, после чего в цикле, используя свойство Cells для этой переменной, заполняем выбранный диапазон значениями. Еще раз обращаю ваше внимание на то, что свойство Cells для Range "работает" в пределах диапазона и ячейки в диапазоне имеют собственную нумерацию, отличную от ячеек листа

15.3.5. CurrentRegion - область, заполненная данными

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

15.3.6. Characters, Font - форматирование текста

15-09-Range Font.xlsm - пример к п. 15.3.6.

Свойство Characters позволяет настраивать форматирование отдельных символов текста ячейки, а параметр Font нужен для настройки параметров шрифта по ячейке в целом. Рассмотрим пример. В ячейке, выделенной пользователем, отформатируем текст шрифтом Times New Roman, размером 15, красного цвета. А первый символ отформатируем курсивом (листинг 15.20.).

Dim obj_Range As Range
    Set obj_Range = Selection
    With obj_Range
        .Font.Name = "Times New Roman"
        .Font.Size = 15
        .Font.Color = vbRed
        .Characters(1, 1).Font.Italic = True
   End With

Свойство Name объекта Font позволяет задать имя шрифта, Size - размер, Color - цвет. При работе с Characters мы задаем номер символа, с которого начинается форматирование, а так же - количество символов, начиная с первого. Чтобы отформатировать второй и третий символы, нам понадобилось бы вызвать это свойство так: Characters(2, 2) - два символа, начиная со второго.

15.3.7. Formula, FormulaR1C1 - формулы в ячейках

15-10-Range Formula.xlsm - пример к п. 15.3.7.

Formula позволяет записать в ячейку формулу, а также - узнать, какая формула записана в ячейке. Формулы используют ссылки на ячейки в стиле A1. Например, для записи в ячейку A1 суммы ячеек A2 и A3, нам понадобится такая команда (листинг 15.21.)

Range("A1").Formula = "=$A$2+$A$3"

Свойство FormulaR1C1 записывает в ячейку формулу, используя стиль ссылок R1C1.

Рассмотрим пример. Программно создадим таблицу с числами и рассчитаем после каждого столбца и каждой строки таблицы суммы составляющих их элементов. Для расчетов сумм строк используем свойство Formula, для расчета сумм по столбцам - FormulaR1C1 (листинг 15.22.).

'Для хранения ссылки на ячейку
    'в которую запишем формулу
    Dim obj_Range As Range
    'Для хранения ссылки на первую
    'ячейку диапазона (для формулы в стиле A1)
    Dim obj_Range1 As Range
    'Для хранения ссылки на последнюю
    'ячейку диапазона (для формулы в стиле A1)
    Dim obj_Range2 As Range
    'Для сборки адресов ячеек в
    'стиле R1C1
    Dim str_R1C11 As String
    Dim str_R1C12 As String
    'Заполняем ячейки данными
    For i = 1 To 5
        For j = 1 To 5
            ActiveSheet.Cells(i, j) = _
            Int(Rnd * 100)
        Next j
    Next i
    'Заполняем формулами ячейки, которые
    'будут отображать суммы по строкам
    For i = 1 To 5
        'Ссылка на ячейку с формулой
        Set obj_Range = ActiveSheet.Cells(i, 6)
        'Ссылка на первую ячейку диапазона
        Set obj_Range1 = ActiveSheet.Cells(i, 1)
        'На последнюю ячейку
        Set obj_Range2 = ActiveSheet.Cells(i, 5)
        'Формула передается в ячейку в виде строки
        'формируем строку такого вида:
        '=SUM($A$1:$A$5)
       obj_Range.Formula = _
        "=sum(" + obj_Range1.Address + ":" + _
            obj_Range2.Address + ")"
    Next i
    'Заполняем ячейки с суммами по
    'столбцам
    For i = 1 To 5
        'ссылка на ячейку с формулой
        Set obj_Range = ActiveSheet.Cells(6, i)
        'Собираем ссылку на первую ячейку
        'она будет иметь вид R1C1 для первого
        'столбца, R1C2 для второго и т.д.
        str_R1C11 = "R" + Mid(Str(1), 2, _
            Len(Str(1)) - 1) + _
            "C" + Mid(Str(i), 2, Len(Str(i)) - 1)
        'Собираем ссылку на последнюю ячейку
        'она будет иметь вид R5C1 для первого
        'столбца, R5C2 для второго и т.д.
        str_R1C12 = "R" + Mid(Str(5), 2, _
            Len(Str(5)) - 1) + _
            "C" + Mid(Str(i), 2, Len(Str(i)) - 1)
       'Записываем в ячейку формулу вид
        obj_Range.FormulaR1C1 = _
            "=sum(" + str_R1C11 + ":" + _
            str_R1C12 + ")"
    Next i

15.3.8. Interior - внешний вид ячейки

Позволяет управлять внешним видом ячейки. В частности, настраивать ее цвет.

Например, такой код (листинг 15.23.) позволяет окрасить ячейку A1 в красный цвет

Range("A1").Interior.Color = vbRed

15.3.9. Name - работа с именованными диапазонами

15-11-Range Name.xlsm - пример к п. 15.3.9.

Позволяет узнать или установить имя ячейки или диапазона ячеек. Имена организованы в коллекцию Names. Именами удобно пользоваться для автоматического заполнения каких-либо заранее созданных таблиц.

Рассмотрим пример. Создадим на рабочем листе MS Excel таблицу такого вида (табл. 15.1.)

Дадим ячейкам, в которые должны быть введены имя и возраст, имена - cell_Name и cell_Age.

Для задания имени щелкните правой кнопкой мыши по ячейке и выберите в появившемся меню команду Имя диапазона (рис. 15.4.)

Рис. 15.4. Задаем имя ячейке

Заполните поле Имя, здесь же можно выбрать область видимости имени, ввести примечание и отредактировать, при необходимости, диапазон, для которого назначается имя.

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

Теперь добавим на лист кнопку и присвоим ее обработчику Click такой код (листинг 15.24.).

'Переменная для работы с ячейкой
    Dim obj_Range As Range
    'Переменная для адреса ячейки
    Dim str_Name As String
    'Так как имя ячейки возвращается
    'в виде =лист1$A$1  мы вырезаем из
    'переданного значения все, кроме знака =
    str_Name = Mid( _
         ActiveWorkbook.Names("cell_Name"), _
         2, Len(ActiveWorkbook.Names("cell_Name")) - 1)
    'Вводим в ячейку данные
    Range(str_Name).Value = _
        InputBox("Введите имя")
    str_Name = Mid( _
         ActiveWorkbook.Names("cell_Age"), _
         2, Len(ActiveWorkbook.Names("cell_Age")) - 1)
    Range(str_Name).Value = _
        Val(InputBox("Введите возраст"))

Здесь мы используем коллекцию Names объекта ActiveWorkbook, чтобы обратиться к конкретной ячейке. Здесь мы задавали имена ячеек вручную, но используя свойство Name можно задавать их в автоматическом режиме. Например, для присвоения ячейке A1 имени cell_A, надо выполнить такой код (листинг 15.25.)

Range("A1").Name = "cell_A"

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

15.3.10. Value - содержимое ячейки

15-12-Range Value.xlsm - пример к п. 15.3.10.

Позволяет узнать или установить содержимое ячейки. Того же эффекта можно добиться, если обращаться к ячейке без указания каких-либо свойств.

Рассмотрим пример - здесь мы получаем с помощью свойства Value значение, хранящееся в ячейке, если оно меньше 0 - записываем в ячейку модуль хранящегося в ней числа и меняем цвет ячейки на vbCyan (голубой) (листинг 15.25.)

Dim obj_Cell As Range
For Each obj_Cell In ActiveSheet.Range("A1:E8")
    If obj_Cell.Value < 0 Then
        obj_Cell.Value = Abs(obj_Cell.Value)
        obj_Cell.Interior.Color = vbCyan
    End If
Next

15.4. Выводы

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