Как обратиться к диапазону из VBA
Полагаю не совру когда скажу, что все кто программирует в 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)
- 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
Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления -Параметры папок -вкладка Вид -Скрывать расширения для зарегистрированных типов файлов) указано скрывать расширения - то указывать расширение не обязательно - Workbooks("Книга2"). Но и ошибки не будет, если его указать. Однако, если пункт "Скрывать расширения для зарегистрированных типов файлов" отключен, то указание Workbooks("Книга2") обязательно приведет к ошибке.
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
Однако существует и другой метод:
В чем отличие(я бы даже сказал преимущество) 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] = "Привет!"
Подскажите, пожалуйста, можно ли так же обращаться к динамическим диапазонам, нижняя граница которых записана через ИНДЕКС(ПОИСКПОЗ. У меня всякие ошибки вылезают при попытке обращения к такому диапазону.
Матвей, можно. Но не зная как и что Вы там задаете и какие лезут ошибки помочь нечем :( Попробуйте создать тему на форуме с приложенным файлом и подробным описанием проблемы.