Vba excel cells свойства и методы

Обновлено: 03.07.2024

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

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

Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

Пример 2

В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

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

Рассматриваемые темы включают свойство смещения, чтение значений между ячейками, чтение значений в массивы и форматирование ячеек.

Краткое руководство по диапазонам и клеткам

Введение

Это третья статья, посвященная трем основным элементам VBA. Этими тремя элементами являются Workbooks, Worksheets и Ranges/Cells. Cells, безусловно, самая важная часть Excel. Почти все, что вы делаете в Excel, начинается и заканчивается ячейками.

Вы делаете три основных вещи с помощью ячеек:

Читаете из ячейки.
Пишите в ячейку.
Изменяете формат ячейки.

В Excel есть несколько методов для доступа к ячейкам, таких как Range, Cells и Offset. Можно запутаться, так как эти функции делают похожие операции.

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

Важное замечание

Я недавно обновил эту статью, сейчас использую Value2.

Вам может быть интересно, в чем разница между Value, Value2 и значением по умолчанию:

Использование Value может усечь число, если ячейка отформатирована, как валюта. Если вы не используете какое-либо свойство, по умолчанию используется Value.

Лучше использовать Value2, поскольку он всегда будет возвращать фактическое значение ячейки.

Свойство Range

Рабочий лист имеет свойство Range, которое можно использовать для доступа к ячейкам в VBA. Свойство Range принимает тот же аргумент, что и большинство функций Excel Worksheet, например: «А1», «А3: С6» и т.д.

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

Как видно из кода, Range является членом Worksheets, которая, в свою очередь, является членом Workbook. Иерархия такая же, как и в Excel, поэтому должно быть легко понять. Чтобы сделать что-то с Range, вы должны сначала указать рабочую книгу и рабочий лист, которому она принадлежит.

В оставшейся части этой статьи я буду использовать кодовое имя для ссылки на лист.

Следующий код показывает приведенный выше пример с использованием кодового имени рабочего листа, т.е. Лист1 вместо ThisWorkbook.Worksheets («Лист1»).

Вы также можете писать в несколько ячеек, используя свойство Range

Свойство Cells рабочего листа

У Объекта листа есть другое свойство, называемое Cells, которое очень похоже на Range . Есть два отличия:

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

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

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

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

Использование свойства Cells полезно, если вы обращаетесь к ячейке по номеру, который может отличаться. Проще объяснить это на примере.

В следующем коде мы просим пользователя указать номер столбца. Использование Cells дает нам возможность использовать переменное число для столбца.

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

Чтобы использовать Range здесь, потребуется преобразовать эти значения в ссылку на буквенно-цифровую ячейку, например, «С1». Использование свойства Cells позволяет нам предоставить строку и номер столбца для доступа к ячейке.

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

Использование Cells и Range вместе

Как вы уже видели, вы можете получить доступ только к одной ячейке, используя свойство Cells. Если вы хотите вернуть диапазон ячеек, вы можете использовать Cells с Range следующим образом:

Как видите, вы предоставляете начальную и конечную ячейку диапазона. Иногда бывает сложно увидеть, с каким диапазоном вы имеете дело, когда значением являются все числа. Range имеет свойство Address, которое отображает буквенно-цифровую ячейку для любого диапазона. Это может пригодиться, когда вы впервые отлаживаете или пишете код.

В следующем примере мы распечатываем адрес используемых нами диапазонов.

В примере я использовал Debug.Print для печати в Immediate Window. Для просмотра этого окна выберите «View» -> «в Immediate Window» (Ctrl + G).

Свойство Offset диапазона

У диапазона есть свойство, которое называется Offset. Термин «Offset» относится к отсчету от исходной позиции. Он часто используется в определенных областях программирования. С помощью свойства «Offset» вы можете получить диапазон ячеек того же размера и на определенном расстоянии от текущего диапазона. Это полезно, потому что иногда вы можете выбрать диапазон на основе определенного условия. Например, на скриншоте ниже есть столбец для каждого дня недели. Учитывая номер дня (т.е. понедельник = 1, вторник = 2 и т.д.). Нам нужно записать значение в правильный столбец.

Сначала мы попытаемся сделать это без использования Offset.

Как видно из примера, нам нужно добавить строку для каждого возможного варианта. Это не идеальная ситуация. Использование свойства Offset обеспечивает более чистое решение.

Как видите, это решение намного лучше. Если количество дней увеличилось, нам больше не нужно добавлять код. Чтобы Offset был полезен, должна быть какая-то связь между позициями ячеек. Если столбцы Day в приведенном выше примере были случайными, мы не могли бы использовать Offset. Мы должны были бы использовать первое решение.

Следует иметь в виду, что Offset сохраняет размер диапазона. Итак .Range («A1:A3»).Offset (1,1) возвращает диапазон B2:B4. Ниже приведены еще несколько примеров использования Offset.

Использование диапазона CurrentRegion

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

Строка или столбец пустых ячеек означает конец CurrentRegion.

Вы можете вручную проверить CurrentRegion в Excel, выбрав диапазон и нажав Ctrl + Shift + *.

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

Range («B3»). CurrentRegion вернет диапазон B3:D14

Range («D14»). CurrentRegion вернет диапазон B3:D14

Range («C8:C9»). CurrentRegion вернет диапазон B3:D14 и так далее

Как пользоваться

Мы получаем CurrentRegion следующим образом

Только чтение строк данных

Прочитать диапазон из второй строки, т.е. пропустить строку заголовка.

Удалить заголовок

Использование Rows и Columns в качестве Ranges

Использование Range вместо Worksheet

Код ниже выделит второй столбец диапазона полужирным. Поскольку диапазон имеет только две строки, весь столбец считается B1:B2

Чтение значений из одной ячейки в другую

В следующем примере показано, как это сделать:

Как видно из этого примера, невозможно читать из нескольких ячеек. Если вы хотите сделать это, вы можете использовать функцию копирования Range с параметром Destination.

Функция Copy копирует все, включая формат ячеек. Это тот же результат, что и ручное копирование и вставка выделения. Подробнее об этом вы можете узнать в разделе «Копирование и вставка ячеек»

Использование метода Range.Resize

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

Использование функции Resize позволяет изменить размер диапазона до заданного количества строк и столбцов.

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

Другими словами, мы используем количество строк и столбцов исходного диапазона в качестве параметров для изменения размера:

Мы можем сделать изменение размера в одну строку, если нужно:

Чтение Value в переменные

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

Для чтения текста в переменную вы используете переменную типа String.

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

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

Как копировать и вставлять ячейки

Если вы хотите скопировать и вставить диапазон ячеек, вам не нужно выбирать их. Это распространенная ошибка, допущенная новыми пользователями VBA.

Вы можете просто скопировать ряд ячеек, как здесь:

В следующей таблице приведен полный список всех типов вставок.

Виды вставок

xlPasteAll

xlPasteAllExceptBorders

xlPasteAllMergingConditionalFormats

xlPasteAllUsingSourceTheme

xlPasteColumnWidths

xlPasteComments

xlPasteFormats

xlPasteFormulas

xlPasteFormulasAndNumberFormats

xlPasteValidation

xlPasteValues

xlPasteValuesAndNumberFormats

Чтение диапазона ячеек в массив

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

Значение диапазона в этом примере считается вариантом массива. Это означает, что вы можете легко читать из диапазона ячеек в массив. Вы также можете писать из массива в диапазон ячеек. Если вы не знакомы с массивами, вы можете проверить их в этой статье.

В следующем коде показан пример использования массива с диапазоном.

Имейте в виду, что массив, созданный для чтения, является двумерным массивом. Это связано с тем, что электронная таблица хранит значения в двух измерениях, то есть в строках и столбцах.

Пройти через все клетки в диапазоне

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

Вы можете сделать это, используя цикл For Each, показанный в следующем коде.

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

Стандартный цикл более гибок в отношении используемого вами порядка, но он медленнее, чем цикл For Each.

Форматирование ячеек

Иногда вам нужно будет отформатировать ячейки в электронной таблице. Это на самом деле очень просто. В следующем примере показаны различные форматы, которые можно добавить в любой диапазон ячеек.

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

Первый способ object.Range(Cell1)

Второй способ object.Range(Cell1 [,Cell2])

object - ссылка на объект, например, на рабочий лист или на интервал ячеек. Ссылка необязательна. По умолчанию используется активный лист;
Cell1, Cell2 - аргументы для задания интервала ячеек. Cell1 - указание обязательно при обоих способах записи свойства Range .

Первый способ

Аргумент Cell1 задает интервал ячеек произвольного размера.

Могут использоваться имена, определенные в таблице, или координаты ячеек, столбцов, строк или интервалов.
Координаты задаются в стиле A1.
Координаты и имена заключаются в кавычки.
При задании интервалов координаты левого верхнего угла и правого нижнего угла интервала разделяются двоеточием.
Для задания несмежных интервалов используется запятая.
Для задания пересечения интервалов используется пробел.

Второй способ

Аргументы задают координаты интервала:

Cell1 - единственная ячейка (строка или столбец), задающая левый верхний угол интервала;
Cell2 - единственная ячейка (строка или столбец), задающая правый нижний угол интервала. Необязательный аргумент.

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

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

Например, если выделен интервал C1:D5, то запись Selection.Range("B2") возвратит ячейку D2.

Свойство Cells

Свойство Cells возвращает единственную ячейку рабочего листа , которая находится на пересечении строки и столбца, задаваемых целыми числами.

Синтаксис object. Cells (RowIndex,ColumnIndex)

object - ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
RowIndex - индекс строки;
ColumnIndex - индекс столбца.

В свойстве Cells индекс строки является первым аргументом, а индекс столбца - вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка.
Понятие "индекс" ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число.

Свойство Offset

Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range("A5").Offset(-2,1) возвращает ячейку B3.

object - ссылка на объект Range . Ссылка обязательна и определяет объект, относительно которого задается смещение;
RowOffset - смещение строки искомой ячейки относительно исходной ячейки;
ColumnOffset - смещение столбца искомой ячейки относительно исходной ячейки.

Необязательные аргументы RowOffset и ColumnOffset - числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю.

Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7.

Метод Union и свойство Areas

Метод Union используется для объединения двух и более объектов Range , заданных ссылками на непересекающиеся интервалы, в один объект Range .

Синтаксис Object. Union (arg1,arg2. )

object - всегда объект Application . Ссылка необязательна;
arg1,arg2 - интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов.

Например, оператор Union (Range("A1:C5"),Range("B10:D12")).Select выделяет несмежные интервалы A1:C5 и B10:D12.

Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range .

object - ссылка на объект Range , состоящий из нескольких интервалов;
index - номер интервала в объекте. Аргумент необязателен.

Свойства Column и Row (R/O Integer)

Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств

object - обязательная ссылка на объект Range .

Например, запись Range("C5").Column возвращает число 3, а запись Range("C5").Row возвращает число 5.

Свойства Columns и Rows

Свойство Columns (не путайте со свойством Column !) возвращает объект Range , представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено.

object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
index - индекс колонки в объекте.

Например, запись Columns(1) возвращает колонку A активного рабочего листа , а запись Range("C1:D5").Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5.

Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range .
Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns("B") указывают на одну и ту же колонку B.

Свойство Rows (не путайте со свойством Row !) возвращает объект Range , представляющий строку или коллекцию строк в объекте, к которому это свойство было применено.

object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
index - индекс строки в объекте.

Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range .

Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек.

Свойство CurrentRegion

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

В процедуре сравниваются значения первой ячейки первой строки и первой ячейки каждой следующей строки заполненного данными интервала, включающего первую ячейку. Если значения совпадают, то очередная строка удаляется.

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

Рис. 8.9. Пример работы со свойством CurrentRegion

Range("A1").ColumnWidth=15 устанавливает ширину колонки A в 15 символов

Range("A1").Width возвращает значение 93.75, если ширина колонки 15 символов, шрифт Times New Roman, размер шрифта 12 пунктов (72 пункта равны 1 дюйму или приблизительно 2,54 см).

Debug.Print Range("A1:C3").ColumnWidth распечатает значение 8.43, а оператор Debug.Print Range("A1:C3").Width распечатает значение 144, если для колонок установлена стандартная ширина, шрифт Arial Cyr и размер шрифта 10

ActiveCell.RowHeight = 14 устанавливает высоту строки, в которой находится активная ячейка , в 14 пунктов

Значение True разбивает текст ячейки на несколько строк, если ширина столбца недостаточна для размещения текста целиком

Полагаю не совру когда скажу, что все кто программирует в VBA очень часто в своих кодах общаются к ячейкам листов. Ведь это чуть ли не основное предназначение VBA в Excel. В принципе ничего сложного в этом нет. Например, чтобы записать в ячейку A1 слово Привет необходимо выполнить код:

Тоже самое можно сделать сразу для нескольких ячеек:

Если необходимо обратиться к именованному диапазону:

Диапазон1 - это имя диапазона/ячейки, к которому надо обратиться в коде. Указывается в кавычках, как и адреса ячеек.
Но в VBA есть и альтернативный метод записи значений в ячейке - через объект Cells:

Cells(1, 1).Value = "Привет"

Синтаксис объекта Range:
Range(Cell1, Cell2)

Cell1 - первая ячейка диапазона. Может быть ссылкой на ячейку или диапазон ячеек, текстовым представлением адреса или имени диапазона/ячейки. Допускается указание несвязанных диапазонов(A1,B10), пересечений(A1 B10).
Cell2 - последняя ячейка диапазона. Необязательна к указанию. Допускается указание ссылки на ячейку, столбец или строку.

Синтаксис объекта Cells:
Cells(Rowindex, Columnindex)

Исходя из этого несложно предположить, что к диапазону можно обратиться, используя Cells и Range:

'выделяем диапазон "A1:B10" на активном листе Range(Cells(1,1), Cells(10,2)).Select

и для чего? Ведь можно гораздо короче:

Иногда обращение посредством Cells куда удобнее. Например для цикла по столбцам(да еще и с шагом 3) совершенно неудобно было бы использовать буквенное обозначение столбцов.
Объект Cells так же можно использовать для указания ячеек внутри непосредственно указанного диапазона. Например, Вам необходимо выделить ячейку в 3 строке и 2 столбце диапазона "D5:F56" . Можно пройтись по листу и посмотреть, отсчитать нужное количество строк и столбцов и понять, что это будет "E7" . А можно сделать проще:

Согласитесь, это гораздо удобнее, чем отсчитывать каждый раз. Особенно, если придется оперировать смещением не на 2-3 ячейки, а на 20 и более. Конечно, можно было бы применить Offset. Но данное свойство именно смещает диапазон на указанное количество строк и столбцов и придется уменьшать на 1 смещение каждого параметра для получения нужной ячейки. Да и смещает на указанное количество строк и столбцов весь диапазон, а не одну ячейку. Это, конечно, тоже не проблема - можно вдобавок к этому использовать метод Resize - но запись получится несколько длиннее и менее наглядной:

Range("D5:F56").Offset(2, 1).Resize(1, 1).Select

И неплохо бы теперь понять, как значение диапазона присвоить переменной. Для начала переменная должна быть объявлена с типом Range. А т.к. Range относится к глобальному типу Object, то присвоение значения такой переменной должно быть обязательно с применением оператора Set:

Dim rR as Range Set rR = Range("D5")

если оператор Set не применять, то в лучшем случае получите ошибку, а в худшем(он возможен, если переменной rR не назначать тип) переменной будет назначено значение Null или значение ячейки по умолчанию. Почему это хуже? Потому что в таком случае код продолжит выполняться, но логика кода будет неверной, т.к. эта самая переменная будет содержать значение неверного типа и применение её в коде в дальнейшем все равно приведет к ошибке. Только ошибку эту отловить будет уже сложнее.
Использовать же такую переменную в дальнейшем можно так же, как и прямое обращение к диапазону:

Вроде бы на этом можно было завершить, но. Это как раз только начало. То, что я написал выше знает практически каждый, кто пишет в VBA. Основной же целью этой статьи было пояснить некоторые нюансы обращения к диапазонам. Итак, поехали.

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

'так выглядит запись слова Test в ячейку А1 Range("A1").Select Selection.Value = "Test"

Но как правило выделение - действие лишнее. Можно записать значение и без него:

'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Теперь чуть подробнее разберем, как обратиться к диапазону не выделяя его и при этом сделать все правильно. Диапазон и ячейка - это объекты листа. У каждого объекта есть родитель - грубо говоря это другой объект, который является управляющим для дочернего объекта. Для ячейки родительский объект - Лист, для Листа - Книга, для Книги - Приложение Excel. Если смотреть на иерархию зависимости объектов, то от старшего к младшему получится так:
Applicaton => Workbooks => Sheets => Range
По умолчанию для всех диапазонов и ячеек родительским объектом является текущий(активный) лист. Т.е. если для диапазона(ячейки) не указать явно лист, к которому он относится, в качестве родительского листа для него будет использован текущий - ActiveSheet:

'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Т.е. если в данный момент активен Лист1 - то слово Test будет записано в ячейку А1 Лист1. Если активен Лист3 - в А1 Лист3. Иначе говоря такая запись равносильна записи:

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

'активируем Лист2 Worksheets("Лист2").Select 'записываем слово Test в ячейку A1 Range("A1").Value = "Test"

Чтобы не активируя другой лист записать в него данные, необходимо явно указать принадлежность объекта Range именно этому листу:

'запишем слово Test в ячейку A1 на Лист2 независимо от того, какой лист активен Worksheets("Лист2").Range("A1").Value = "Test"

Таким же образом происходит считывание данных с ячеек - если не указывать лист, данные ячеек которого необходимо считать - считаны будут данные с ячейки активного листа. Чтобы считать данные с Лист2 независимо от того, какой лист активен применяется такой код:

'считываем значение ячейки A1 с Лист2 независимо от того, какой лист активен MsgBox Worksheets("Лист2").Range("A1").Value

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

'запишем слово Test в ячейку A1 на Лист2 книги Книга2.xlsx независимо от того, какая книга и какой лист активен Workbooks("Книга2.xlsx").Worksheets("Лист2").Range("A1").Value = "Test" 'считываем значение ячейки A1 с Лист2 книги Книга3.xlsx независимо от того, какой лист активен MsgBox Workbooks("Книга3.xlsx").Worksheets("Лист2").Range("A1").Value

Очень часто ошибки обращения к ячейкам листов и книг делают начинающие, особенно в циклах по листам. Вот пример неправильного цикла:

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

MsgBox будет выдавать правильные значения, но сами имена листов будут записываться не на каждый лист, а последовательно в ячейку активного листа. Поэтому на активном листе в ячейке А1 будет имя последнего листа.
А вот так выглядит правильный цикл:
Вариант 1 - активация листа (медленный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Activate 'активируем каждый лист Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

Вариант 2 - без активации листа (быстрый и более правильный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox wsSh.Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

Важно: если код записан в модуле листа(правая кнопка мыши на листе-Исходный текст) и для объекта Range или Cells родитель явно не указан(т.е. нет имени листа и книги) - тогда в качестве родителя будет использован именно тот лист, в котором записан код, независимо от того какой лист активный. Иными словами - если в модуле листа записать обращение вроде Range("A1").Value = "привет" , то слово привет всегда будет записывать в ячейку A1 именно того листа, в котором записан сам код. Это следует учитывать, когда располагаете свои коды внутри модулей листов.

В конструкциях типа Range(Cells(,),Cells(,)) Range является контейнером, в котором указываются ссылки на объекты, из которых и будет создана ссылка на непосредственно конечный объект.
Предположим, что активен "Лист1" , а код запущен с листа "Итог" .
Если запись будет вида

Sheets("Итог").Range(Cells(1, 1), Cells(10, 1))

это вызовет ошибку "Run-time error '1004': Application-defined or object-defined error". А ошибка появляется потому, что контейнер и объекты внутри него не могут располагаться на разных листах, равно как и:

Sheets("Итог").Range(Cells(1, 1), Sheets("Итог").Cells(10, 1)) 'запись ниже так же неверна Range(Cells(1, 1), Sheets("Итог").Cells(10, 1))

т.к. ссылки на объекты внутри контейнера относятся к разным листам. Cells(1, 1) - к активному листу, а Sheets( "Итог" ).Cells(10, 1) - к листу Итог.
А вот такие записи будут правильными:

Sheets("Итог").Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1)) Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1))

Вторая запись не содержит ссылки на родителя для Range, но ошибки это в большинстве случаев не вызовет - т.к. если для контейнера ссылка не указана, а для двух объектов внутри контейнера родитель один - он будет применен и для самого контейнера. Однако лучше делать как в первой строке - т.е. с обязательным указанием родителя для контейнера и для его составляющих. Т.к. при определенных обстоятельствах(например, если в момент обращения к диапазону активной является книга, открытая в режиме защищенного просмотра) обращение к Range без родителя может вызывать ошибку выполнения.
Если запись будет вида Range( "A1" , "A10" ), то указывать ссылку на родителя внутри Range не обязательно - достаточно будет указать эту ссылку перед самим Range - Sheets( "Итог" ).Range( "A1" , "A10" ), т.к. текстовое представление адреса внутри Range не является объектом(у которого может быть какой-то родительский объект), что обязывает создать ссылку именно на родителя контейнера.

Разберем пример, приближенный к жизненной ситуации. Необходимо на лист Итог занести формулу вычитания, начиная с ячейки А2 и до последней заполненной. На момент записи активен Лист1. Очень часто начинающие записывают так:

Sheets("Итог").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

Запись смешанная - и текстовое представление адреса ячейки( "A2:A" ) и ссылка на объект Cells. В данном случае явную ошибку код не вызовет, но и работать будет не всегда так, как хотелось бы. А это самое плохое, что может случиться при разработке.
Sheets("Итог").Range("A2:A" - создается ссылка на столбец " A " листа Итог. Но далее идет вычисление последней строки первого столбца. И вот как раз это вычисление происходит на основе объекта Cells, который не содержит в себе ссылки на родительский объект. А значит он будет вычислять последнюю строку исключительно для текущего листа(если код записан в стандартном модуле, а не модуле листа) - т.е. для Лист1. Правильно было бы записать так:

Sheets("Итог").Range("A2:A" & Sheets("Итог").Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

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

lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Rows.Count, 1).End(xlUp).Row

с виду все нормально, но есть нюанс. Rows.Count по умолчанию будет относится к активной книге, если записано в стандартном модуле. Приведенный выше код должен работать с книгой формата 97-2003 и вычислить последнюю заполненную ячейку на листе1. В книгах формата Excel 97-2003(.xls) всего 65536 строк. Если в момент выполнения приведенной строки активна книга формата 2007 и выше(форматы .xlsx, .xlsm, .xlsb и пр) - то Rows.Count вернет 1048576, т.к. именно такое количество строк в листах книг версий Excel, начиная с 2007. И т.к. в книге, в которой мы пытаемся вычислить последнюю строку всего 65536 строк - получим ошибку 1004, т.к. не может быть номера строки 1048576 на листе с количеством строк 65536. Поэтому имеет смысл указывать явно откуда считывать Rows.Count:

lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Workbooks("Книга3.xls").Sheets("Лист1").Rows.Count, 1).End(xlUp).Row

или применить конструкцию With

With Workbooks("Книга3.xls").Sheets("Лист1") lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With

Также не мешало бы упомянуть возможность выделения несмежного диапазона(часто его называют "рваным"). Это диапазон, который обычно привыкли выделять на листе при помощи зажатой клавиши Ctrl. Что это дает? Это дает возможность выделить одновременно ячейки A1 и B10 и записать значения только в них. Для этого есть несколько способов. Самый очевидный и описанный в справке - метод Union:

Однако существует и другой метод:

В чем отличие(я бы даже сказал преимущество) Union: можно применять в цикле по условию. Например, выделить в диапазоне A1:F50 только те ячейки, значение которых больше 10 и меньше 20:

Sub SelOne() Dim rCell As Range, rSel As Range For Each rCell In Range("A1:F50") If rCell.Value > 10 And rCell.Value < 20 Then If rSel Is Nothing Then Set rSel = rCell Else Set rSel = Union(rSel, rCell) End If End If Next rCell If Not rSel Is Nothing Then rSel.Select End Sub

Конечно, можно и просто в Range через запятую передать все эти ячейки, сформировав предварительно строку. Но в случае со строкой действует ограничение: длина строки не должна превышать 255 символов.

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

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Доброго всем дня,коллеги! Подскажите чайнику простейшее.. Есть макрос,который вставляет в активную ячейку текущее время+текст. Необходимо,что бы текст возможно было вставить только в определенном диапазоне (н-р пару столбцов), т.к. при вставке "не туда" данные,записанные ранее удаляются,и откатиться назад уже нельзя. Буду благодарен за помощь! Девочек с наступающим.

Добрый день всем! В очередной раз за помощью! Простой макрос по кнопке вставляет дату + N дней. Но часто полученная дата попадает на выходной. Что нужно дописать,или изменить, что бы полученная дата "проскакивала" вперед на ближайший рабочий день?

Sub Через_10д()
If Not Intersect(ActiveCell, Range("J14:M350")) Is Nothing Then ActiveCell = Date + 10
End Sub

1. Что бы записать "привет" в ячейку А1 не надо объявлять переменную как диапазон и присваивать ей значение из ячейки D5. Самое начало статьи сразу с ошибки начинаете.
2. Диапазон - это диапазон, я не ячейка с номером типа Cell(х,y). Если уж начали писать про диапазоны - пишите про диапазоны! У меня задача - обратиться к именованному диапазону и вытащить оттуда через ВБА значения в массив для дальнейшей обработки. А вот теперь посмотрите что из написанного может помочь это сделать. Ничего.
Спасибо, бесполезная статья с намеком на гениальность и уводящая постоянно в сторону от озвученной темы

Артем,
1. Спасибо. Вообще в самом начале статьи написан другой код:

Range("A1").Value = "Привет"
Тоже самое можно сделать сразу для нескольких ячеек:
Range("A1:C10").Value = "Привет"

Просто не так давно сменил скрипт подсветки синтаксиса кода и он почему-то отобразил дважды один и тот же код из этой статьи, вместо других. В общем поправил.
2. Прежде чем критиковать, изучите хотя бы мат.часть. Ячейка - это тот же диапазон технически. И обращение Range("A1") равносильно обращению Cells(1,1). И свойства и методы у них идентичны. Только Range может содержать более одной ячейки, Cells нет.
Цель данной статьи - научить правильно обращаться к диапазонам и понимать отличие обращения к диапазону в активном листе или в других листах и книгах. И научить обращаться как через Range, так и через Cells. И никаких намеков на гениальность, просто описание технической части и её нюансов. Впрочем, об этом я тоже упоминаю:

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

И в сторону статья уводит лишь от ВАШЕЙ проблемы, но не от темы. Ваша проблема - это работа не столько с диапазонами, сколько с массивами. Различайте понятия - вся статья про обращение к диапазонам, но описать в одной статье ответы на вопросы по диапазонам каждого проходящего нереально. Ведь само обращение к диапазону может использоваться в любой задаче: отобрать уникальные, загнать в массив, покрасить, подсчитать ячейки с тем-то и тем-то и т.д. и т.п. И все это будет подходить под определение диапазона - ведь мы к нему обращаемся :) Поэтому вменяемые люди пишут комментарии со своим вопросом, а не жалуются, что именно их вопрос здесь не озвучен.
А тем не менее Ваша задача решается элементарно и без всякого выпендрежа вроде "статья бесполезна". Надо было чуть пошерстить хотя бы этот же сайт - я приводил и такие примеры. Хотя бы в этой статье: Как ускорить и оптимизировать код VBA . Там есть советы по оптимизации для чуть более опытных, в том числе использование массивов, вместо прямого обращения к ячейкам.
И решается Ваша задача одной строкой кода:

Dim arr 'объявляем переменную, в которую помещать массив arr = Range("A1:F10").Value 'дальше делаем с массивом все, что надо

Есть еще один забавный способ адресоваться в VBA к ячейке.
[a2] = "Привет!" ' Занесет в ячейку А2 текущей книги текущего листа "Привет!"
Работает при способе адресации А1. При адресации RC будет работать такой способ:
[r1c2] = "Привет!"

Подскажите, пожалуйста, можно ли так же обращаться к динамическим диапазонам, нижняя граница которых записана через ИНДЕКС(ПОИСКПОЗ. У меня всякие ошибки вылезают при попытке обращения к такому диапазону.

Матвей, можно. Но не зная как и что Вы там задаете и какие лезут ошибки помочь нечем :( Попробуйте создать тему на форуме с приложенным файлом и подробным описанием проблемы.

Читайте также: