VBA Excel. Переменная диапазона ячеек (As Range)

Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

Присвоение диапазона ячеек переменной

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

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

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

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

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,

обращение к первой строке присвоенного диапазона размерностью 5х5:

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

Работа с диапазоном в переменной

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

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

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

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 – работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

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

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

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

Пример 3 – копирование и вставка диапазона из переменной

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

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.

Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.

Размер диапазона в переменной

При получении диапазона с помощью метода Application.InputBox и присвоении его переменной диапазона, бывает полезно узнать его размерность. Это можно сделать следующим образом:

Запустите процедуру, выберите на рабочем листе Excel любой диапазон и нажмите кнопку «OK». Информационное сообщение выведет количество строк и столбцов в диапазоне, присвоенном переменной myRange.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

8 комментариев для “VBA Excel. Переменная диапазона ячеек (As Range)”

  1. Добрый день! Подскажите пожалуйста, как можно задать диапазон (например всего одну строку) для поиска Cells.find.

  2. Евгений (автор статьи)

    Привет, Иван!
    Для второй строки: Rows(2).Find(stroka)
    Для диапазона: Range("A2:G10").Find(stroka)
    stroka – это переменная или искомый текст в прямых кавычках.

  3. Добрый день, можете, пожалуйста, подсказать?
    У меня есть книга Excel, мне нужно создать макрос, который мог бы при определённых значениях ячеек делать заливку, т.е. у меня есть таблица, в ней стоят месяца и т.п. и есть значения (Т,А,С): ячейки, где стоят Т, должны быть зелёного цвета; Где А-красного; Где С -жёлтого, и как это можно растянуть на всю книгу

    1. Евгений (автор статьи)

      Добрый день, Антон!
      В этом случае можно обойтись без макроса. Попробуйте использовать условное форматирование. Только правила задавайте не для всей или части строки, а для конкретного столбца, в котором будут находиться ячейки с искомыми значениями.

  4. Здравствуйте. У меня есть таблица, это расположение сидений в кинотеатре. Ну что то наподобие сетки, когда приходишь в кино и покупаешь билет.
    Рядом есть в двух отдельных ячейках мне нужно указать ряд и номер сидения, на котором я хочу сидеть. Я очень хочу понять какой необходим макрос, чтобы я мог набирать в этих ячейках адрес конкретного сидения и это сиденье подсвечивалось значением “Х” на первой таблице

    1. Евгений

      Привет, Дима!
      Если ячейкам с местами присвоить имена, например: ряд1кресло1, ряд1кресло2 и т.д., добавить на лист кнопку с заголовком «Забронировать», тогда для кнопки можно записать следующий код:

      В примере кода: ячейка с номером ряда – «M2», с номером кресла – «N2». При нажатии кнопки, если такое место существует, в него будет записана буква «X».

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег <pre> и по окончании кода </pre>.