Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как обратиться к диапазону из VBA

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

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

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

Но в VBA есть и альтернативный метод записи значений в ячейке - через объект Cells:

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

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

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

  • Rowindex - номер строки
  • Columnindex - номер столбца

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления -Параметры папок -вкладка Вид -Скрывать расширения для зарегистрированных типов файлов) указано скрывать расширения - то указывать расширение не обязательно - Workbooks("Книга2"). Но и ошибки не будет, если его указать. Однако, если пункт "Скрывать расширения для зарегистрированных типов файлов" отключен, то указание Workbooks("Книга2") обязательно приведет к ошибке.


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

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

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

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

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

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

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

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

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

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

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

с виду все нормально, но есть нюанс. 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:

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


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

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

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

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

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

 

Также см.:
Как определить последнюю ячейку на листе через VBA?
Как определить первую заполненную ячейку на листе?

 


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

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 16 коммент.
  1. Евгений:

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

  2. Евгений:

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

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

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти