Range и Cells: работа с ячейками

Учимся адресовать ячейки двумя способами — по буквенному адресу и по номерам строки и столбца — и понимаем, чем Value отличается от Text и Formula.

Range — объект Excel, который представляет одну ячейку, прямоугольный блок ячеек, целую строку или столбец. Почти всё, что вы делаете мышью на листе, в VBA делается через Range.

Зачем это нужно

Любой макрос, который хоть что-то полезное делает с таблицей, сводится к трём действиям: найти нужные ячейки, прочитать из них данные и записать результат. Всё остальное — циклы, условия, красивое форматирование — надстройка над этим фундаментом.

Проблема новичка в том, что «найти ячейку» в VBA можно десятком способов, и они не равноценны. Макрорекордер подсовывает один — самый неудобный. В интернете попадается второй. В книге — третий. В итоге в одном макросе живут Range("A1"), Cells(1, 1), ActiveCell и Selection вперемешку, а автор не может объяснить, почему при запуске из другого листа всё разваливается.

В этом уроке мы разложим по полочкам четыре инструмента адресации — Range, Cells, Offset, Resize — и разберём тройку Value / Text / Formula, на которой спотыкается примерно каждый второй начинающий автоматизатор.

Range: адресуем ячейки так же, как в формулах

Range принимает адрес строкой — ровно в том виде, в каком вы пишете его в формуле: "A1", "B2:D10", "C:C" (весь столбец), "3:3" (вся строка).

Sub RangeBasics()
    ' одна ячейка
    Range("A1").Value = "Отчёт за июль"

    ' блок ячеек — одна команда красит сразу три ячейки
    Range("A1:C1").Font.Bold = True
    Range("A1:C1").Interior.Color = RGB(255, 242, 204)

    ' в ячейку можно положить не только значение, но и формулу
    Range("B2").Value = 1500
    Range("C2").Value = 3
    Range("D2").Formula = "=B2*C2"

    MsgBox "В D2 получилось: " & Range("D2").Value
End Sub

Результат: в A1 появится заголовок, шапка станет жирной и жёлтой, а окно сообщения покажет:

В D2 получилось: 4500

Обратите внимание на важную деталь: у Range("A1") не указано, на каком листе эта A1. Excel в таком случае берёт активный лист — тот, что сейчас открыт у пользователя. Пока вы отлаживаете макрос на одном листе, это незаметно. Как только макрос запустят с другого листа, он молча испортит чужие данные. Как этого избежать — тема двух следующих уроков; пока просто держите в голове, что адрес без листа — это «где-то тут».

Cells: адресуем по номерам строки и столбца

Cells(строка, столбец) — то же самое, но координаты задаются числами. Cells(2, 3) — это ячейка C2: вторая строка, третий столбец. Порядок именно такой: сначала строка, потом столбец, как в матрице, а не как в адресе «C2».

Столбец можно указать и буквой — Cells(2, "C"). Это читается заметно приятнее, чем «третий столбец», и я советую пользоваться этим в реальном коде.

Зачем нужен второй способ, если есть привычный Range? Ради циклов. Номер строки — это переменная, и её можно увеличивать:

Sub FillPriceList()
    Dim r As Long          ' номер строки; Long — целое число до 2 млрд

    Cells(1, "A").Value = "Товар"
    Cells(1, "B").Value = "Цена"

    For r = 2 To 11
        Cells(r, "A").Value = "Товар № " & (r - 1)
        Cells(r, "B").Value = (r - 1) * 100
    Next r
End Sub

Десять строк прайса заполняются пятью строчками кода. Через Range то же самое пришлось бы писать конкатенацией: Range("A" & r) — «буква A, приклеенная к номеру строки». Оператор & в VBA склеивает строки (не путайте с +: тот попытается сложить числа и на строке "A" + r выдаст ошибку типов).

Range("A" & r) или Cells(r, "A")?

Последняя строка таблицы — очень полезный приём: Range умеет принимать не только текстовый адрес, но и две ячейки — левый верхний и правый нижний угол. Так строится блок, размеры которого заранее неизвестны:

Sub SelectDataBlock()
    Dim lastRow As Long
    lastRow = 250                       ' в жизни это число вычисляется, см. урок про листы

    ' блок A2:E250 — без единой строковой склейки адреса
    Range(Cells(2, 1), Cells(lastRow, 5)).Interior.Color = RGB(226, 239, 218)
End Sub

Offset: шаг в сторону от известной ячейки

Offset(смещение_строк, смещение_столбцов) возвращает другую ячейку, отсчитанную от текущей. Положительные числа — вниз и вправо, отрицательные — вверх и влево, ноль — остаться на месте.

• Range("B2").Offset(1, 0) → B3 (на строку ниже);
• Range("B2").Offset(0, 1) → C2 (на столбец правее);
• Range("B2").Offset(-1, -1) → A1;
• Range("A1:B2").Offset(2, 0) → A3:B4 — смещается весь блок целиком.

Offset незаменим, когда ячейка найдена поиском и её адрес заранее неизвестен. Классическая задача: найти строку со словом «Итого» и поставить сумму в соседнюю колонку.

Sub PutTotalNextToLabel()
    Dim found As Range

    ' ищем «Итого» в столбце A; xlWhole — совпадение целиком, а не «содержит»
    Set found = Range("A1:A100").Find(What:="Итого", LookAt:=xlWhole)

    If found Is Nothing Then
        MsgBox "Строка «Итого» не найдена — проверьте столбец A"
    Else
        ' соседняя ячейка справа: смещение (0, 1)
        found.Offset(0, 1).Formula = "=SUM(B2:B" & (found.Row - 1) & ")"
        found.Offset(0, 1).Font.Bold = True
    End If
End Sub

Разберём ключевую строку. found.Row — номер строки найденной ячейки; если «Итого» оказалось в A17, то found.Row - 1 = 16, и склейка "=SUM(B2:B" & 16 & ")" даёт текст =SUM(B2:B16). Формула подстраивается под данные сама — именно за это макросы и любят.

Важно: Find ничего не находит — он возвращает Nothing (пустую ссылку), а не пустую строку. Проверка If found Is Nothing Then обязательна, иначе на следующей строке получите ошибку 91 «Object variable not set».

Resize: растянуть диапазон до нужного размера

Resize(строк, столбцов) берёт левый верхний угол диапазона и «растягивает» его до заданного размера. Range("A1").Resize(3, 2) — это A1:B3: три строки, два столбца.

Сила Resize раскрывается в паре с Offset. Типичная задача: есть таблица с шапкой, а обработать нужно только тело — без заголовков.

Sub HighlightBodyOnly()
    Dim tbl As Range, body As Range

    ' CurrentRegion — весь связный блок вокруг A1 (аналог Ctrl+*)
    Set tbl = Range("A1").CurrentRegion

    ' спускаемся на строку вниз и укорачиваем блок на эту же строку
    Set body = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count)

    body.Interior.Color = RGB(242, 242, 242)
    MsgBox "Таблица: " & tbl.Address & vbCrLf & "Только данные: " & body.Address
End Sub

Результат для таблицы A1:D20:

Таблица: $A$1:$D$20
Только данные: $A$2:$D$20

Обратите внимание: Offset(1, 0) сдвинул блок вниз, и его нижняя граница уехала на 21-ю строку. Resize вернул высоту к 19 строкам, отрезав лишнее. Это стандартная пара, и её стоит запомнить как единый приём: Offset двигает, Resize подгоняет размер.

Value, Text и Formula — три разных ответа на вопрос «что в ячейке?»

Представьте ячейку C2. В ней записана формула =A2*B2, результат вычисления — 1234, а формат ячейки — денежный, поэтому на экране вы видите 1 234,00 ₽. Что вернёт VBA, если спросить «а что там»? Зависит от того, о чём именно вы спросите.

Sub ReadCellThreeWays()
    Range("A2").Value = 2
    Range("B2").Value = 617
    Range("C2").Formula = "=A2*B2"
    Range("C2").NumberFormat = "#,##0.00 ₽"

    ' Debug.Print печатает в окно Immediate (открыть: Ctrl+G в редакторе VBA)
    Debug.Print "Value:   "; Range("C2").Value
    Debug.Print "Text:    "; Range("C2").Text
    Debug.Print "Formula: "; Range("C2").Formula
End Sub

Результат в окне Immediate:

Value:    1234
Text:     1 234,00 ₽
Formula: =A2*B2

Три главных правила, которые сэкономят вам часы отладки:

• Считаете — берите .Value. Он даёт число, с которым можно складывать и сравнивать.
• .Text — только для показа. Он возвращает строку, поэтому Range("C2").Text * 2 в лучшем случае сработает случайно, а в худшем упадёт. Хуже того: если столбец узкий и Excel рисует ####, то .Text честно вернёт вам ####. Записать в .Text ничего нельзя — свойство только для чтения.
• Формулу пишите в .Formula английскими именами функций — SUM, IF, VLOOKUP — независимо от языка вашего Excel. Есть парное свойство .FormulaLocal, которое работает с локализованными именами (СУММ, ЕСЛИ), но код с ним перестаёт работать на английской версии Excel у коллеги. Используйте .Formula.

Маленькая тонкость про .Value: если написать Range("D2").Value = "=B2*C2", Excel всё равно распознает строку, начинающуюся со знака «равно», как формулу. То есть .Value и .Formula при записи формулы ведут себя одинаково. А вот при чтении — по-разному: .Value вернёт результат, .Formula — текст формулы. Писать .Formula при записи формул стоит просто ради читаемости: сразу видно намерение.

Как это работает

Excel устроен как матрёшка объектов: Application (сам Excel) → Workbook (книга-файл) → Worksheet (лист) → Range (ячейки). Эта матрёшка называется объектной моделью, и VBA просто дёргает её за ниточки.

Ключевая мысль, которая всё расставляет по местам: Range — это не сами данные, а пульт управления ячейкой. Строка Set r = Range("A1") не копирует содержимое A1 в переменную. Она запоминает, «куда смотреть». Если после этого значение A1 поменяется, r.Value вернёт уже новое значение — переменная указывает на живую ячейку.

Из этого следует важное практическое следствие. Каждое обращение к .Value — это отдельный запрос к Excel через довольно медленный слой COM (механизм, через который приложения Windows общаются друг с другом). Одно обращение занимает доли миллисекунды, и на десяти ячейках вы этого не заметите. Но в цикле на 100 000 ячеек «доли миллисекунды» превращаются в десятки секунд. Именно поэтому опытные разработчики выгружают диапазон в массив одной операцией — но это тема отдельного разговора, и к ней мы вернёмся, когда будем говорить о скорости.

И ещё: Cells(2, 3) — это тоже Range, просто из одной ячейки. Никакого отдельного «объекта Cells» не существует. Поэтому у результата Cells(...) доступны ровно те же .Value, .Offset, .Font, что и у Range(...).

Частые ошибки

• Перепутан порядок аргументов в Cells. Cells(2, 3) — это C2, а не B3. Сначала строка, потом столбец. Ошибка коварна тем, что код не падает: он просто пишет данные не туда. Лечится привычкой указывать столбец буквой: Cells(2, "C") перепутать невозможно.
• Склейка адреса через + вместо &. Range("A" + r) при числовом r даёт «Type mismatch». Для склейки строк в VBA есть только &.
• Чтение .Text вместо .Value для расчётов. Получаете строку с пробелами-разделителями и символом валюты, потом удивляетесь, почему сумма считается неправильно или выскакивает ошибка преобразования.
• Забыли Set при присвоении объекта. r = Range("A1") без Set положит в переменную значение ячейки, а не ссылку на неё. Правило простое: объект — только через Set.
• Не проверен результат Find. Если искомое не найдено, вернётся Nothing, и любое обращение к .Offset уронит макрос с ошибкой 91.
• Адрес без листа. Range("A1") всегда означает «A1 активного листа». В разовом макросе прокатит, в рабочем инструменте — источник трудноуловимых багов.

Итоги

• Range("A1"), Range("B2:D10") — адрес строкой, как в формуле. Удобно для фиксированных адресов.
• Cells(строка, столбец) — адрес числами. Незаменим в циклах; Cells(r, "B") читается лучше, чем Cells(r, 2).
• Range(Cells(2, 1), Cells(lastRow, 5)) — прямоугольник с вычисляемыми границами.
• Offset(вниз, вправо) — шаг от известной ячейки; Resize(строк, столбцов) — растянуть блок. Вместе они вырезают тело таблицы без шапки.
• .Value — считать и сравнивать. .Text — только показать (строка, может вернуть ####, только для чтения). .Formula — прочитать или записать формулу английскими именами функций.
• Range — это ссылка на живую ячейку, а не копия данных. Каждое обращение к .Value стоит времени.