Команда select в excel
Полагаю не совру когда скажу, что все кто программирует в 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(Range("A1"), Range("B10")).Value = "Привет"
Однако существует и другой метод:
В чем отличие(я бы даже сказал преимущество) Union: можно применять в цикле по условию. Например, выделить в диапазоне A1:F50 только те ячейки, значение которых больше 10 и меньше 20:
Конечно, можно и просто в 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] = "Привет!"
Подскажите, пожалуйста, можно ли так же обращаться к динамическим диапазонам, нижняя граница которых записана через ИНДЕКС(ПОИСКПОЗ. У меня всякие ошибки вылезают при попытке обращения к такому диапазону.
Матвей, можно. Но не зная как и что Вы там задаете и какие лезут ошибки помочь нечем :( Попробуйте создать тему на форуме с приложенным файлом и подробным описанием проблемы.
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
var1
Необязательный элемент. Псевдоним, который может использоваться для ссылки на результаты выражения столбца.
fieldName1
Обязательный. Имя поля, возвращаемого в результате запроса.
Комментарии
Для определения результатов, возвращаемых из запроса, можно использовать Select предложение. Это позволяет либо определить члены нового анонимного типа, созданного запросом, либо целевые элементы именованного типа, возвращаемые запросом. Select Предложение не является обязательным для запроса. Если предложение не Select указано, запрос возвратит тип, основанный на всех членах переменных диапазона, определенных для текущей области. Дополнительные сведения см. в статье Анонимные типы. Когда запрос создает именованный тип, он вернет результат типа IEnumerable , где T — это созданный тип.
Select Предложение может ссылаться на любые переменные в текущей области. Сюда входят переменные диапазона, From определенные в предложении (или From предложениях). Он также включает все новые переменные, созданные с помощью псевдонима Aggregate предложениями, Let Group By , или Group Join или переменными из предыдущего Select предложения в выражении запроса. Select Предложение также может включать статические значения. Например, в следующем примере кода показано выражение запроса, в котором Select предложение определяет результат запроса как новый анонимный тип с четырьмя элементами: ProductName , Price , Discount и DiscountedPrice . ProductName Значения элементов и Price берутся из переменной диапазона продукта, определенной в From предложении. DiscountedPrice Значение элемента вычисляется в Let предложении. Discount Член является статическим значением.
Select Предложение вводит новый набор переменных диапазона для последующих предложений запроса, а предыдущие переменные диапазона больше не находятся в области. Последнее Select предложение в выражении запроса определяет возвращаемое значение запроса. Например, следующий запрос возвращает название компании и идентификатор заказа для каждого заказа клиента, для которого сумма превышает 500. Первое Select предложение определяет переменные диапазона для Where предложения и второе Select предложение. Второе Select предложение определяет значения, возвращаемые запросом в виде нового анонимного типа.
Select Если предложение определяет один возвращаемый элемент, выражение запроса возвращает коллекцию типа этого отдельного элемента. Select Если предложение определяет несколько возвращаемых элементов, выражение запроса возвращает коллекцию нового анонимного типа на основе выбранных элементов. Например, следующие два запроса возвращают коллекции из двух различных типов на основе Select предложения. Первый запрос возвращает коллекцию названий компаний в виде строк. Второй запрос возвращает коллекцию Customer объектов, заполненных названиями и адресами компании.
Пример
Следующее выражение запроса использует From предложение для объявления переменной cust диапазона для customers коллекции. Select Предложение выбирает имя клиента и значение идентификатора и заполняет CompanyName столбцы и CustomerID новой переменной диапазона. For Each Инструкция циклически перебирает каждый возвращаемый объект и отображает CompanyName столбцы и CustomerID для каждой записи.
В Microsoft Excel обычно выбирается ячейка или ячейки, а затем выполняется действие, например форматирование ячеек или ввод значений. В Visual Basic обычно не требуется выбирать ячейки перед их изменением.
Например, чтобы ввести формулу в ячейку D6 с помощью Visual Basic, не нужно выбирать диапазон D6. Просто возвратите объект Range для этой ячейки, а затем присвойте свойству Formula нужную формулу, как показано в следующем примере.
Дополнительные сведения и примеры использования других методов для управления ячейками без их выбора см. в статье Ссылки на ячейки и диапазоны.
Использование метода Select и свойства Selection
Метод Select активирует листы и объекты на листах; свойство Selection возвращает объект, представляющий текущее выделение на активном листе в активной книге. Перед использованием свойства Selection необходимо активировать книгу, активировать или выбрать лист, а затем выбрать диапазон (или другой объект) с помощью метода Select.
Средство записи макросов часто создает макрос, использующий метод Select и свойство Selection. Следующая процедура Sub была создана с помощью средства записи макросов и показывает, как Select и Selection работают вместе.
В следующем примере выполняется та же задача без активации или выбора листа или ячеек.
Выбор ячеек на активном листе
Если для выбора ячеек используется метод Select, имейте в виду, что Select работает только на активном листе. Если выполнить процедуру Sub из модуля, метод Select завершится ошибкой, если процедура не активирует лист перед использованием метода Select для диапазона ячеек. Например, следующая процедура копирует строку из Листа1 в Лист2 в активной книге.
Активация ячейки в выделенном фрагменте
Используйте метод Activate для активации ячейки в выделенном фрагменте. Активной может быть только одна ячейка, даже если выделен диапазон ячеек. Следующая процедура выделяет диапазон и активирует ячейку в диапазоне, не изменяя выделенный фрагмент.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
In Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them.
For example, to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6. Just return the Range object for that cell, and then set the Formula property to the formula you want, as shown in the following example.
For more information and examples of using other methods to control cells without selecting them, see How to: Reference Cells and Ranges.
Using the Select Method and the Selection Property
The Select method activates sheets and objects on sheets; the Selection property returns an object that represents the current selection on the active sheet in the active workbook. Before you can use the Selection property successfully, you must activate a workbook, activate or select a sheet, and then select a range (or other object) using the Select method.
The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it shows how Select and Selection work together.
The following example performs the same task without activating or selecting the worksheet or cells.
Selecting Cells on the Active Worksheet
If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, the following procedure copies a row from Sheet1 to Sheet2 in the active workbook.
Activating a Cell Within a Selection
Use the Activate method to activate a cell within a selection. There can be only one active cell, even when a range of cells is selected. The following procedure selects a range and then activates a cell within the range without changing the selection.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера - Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно - быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет - пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.
Для начала рассмотрим два кода, выполняющие одни те же действия - запись в ячейку А3 листа Лист2 слова "Привет". При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово "Привет" и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:
Sub Макрос1() Sheets("Лист2").Select 'выделяем Лист2 Range("A3").Select 'выделяем ячейку А3 ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 Sheets("Лист1").Select 'возвращаемся на Лист1 End Sub
Нигде не говорится, что в большинстве случаев все эти Select и Activate в кодах не нужны. Однако вышеприведенный код можно значительно улучшить, если убрать все ненужные Select и Activate:
Sub Макрос1() Sheets("Лист2").Range("A3").FormulaR1C1 = "Привет" End Sub
Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 - VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку - так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение - VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово "Привет" рекордер предложит нам такой код:
Sub Макрос1() Range("A3").Select 'выделяем ячейку А3 ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 End Sub
однако выделять ячейку( Range("A3").Select ) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке - ActiveCell . .FormulaR1C1 = "Привет" означает запись значения "Привет" в эту ячейку.
Пусть не смущает FormulaR1C1 - VBA всегда так указывает запись и значения и формулы. Т.к. перед словом "Привет" нет знака равно - то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто "сократить":
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 End Sub
Теперь у нас код получился короче и понятнее. Однако остался один Select: Range("A4").Select . Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" End Sub
Теперь несложно догадаться, что с листами все в точности так же. Sheets("Лист2").Select - Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel - Application, потом книга - Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах - ячейки и диапазоны - Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range("A3").FormulaR1C1 = "Привет" , то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA
Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку "Subscript out of range":
буквально это означает, что указанный индекс вне досягаемости. А появляется эта ошибка потому, что нельзя выделить ячейку НЕактивного листа или лист НЕактивной книги. Легко эту ошибку получить например в таком коде:
Sub Макрос2() Windows("Книга3").Activate 'здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 'а на данный момент активной является Книга3 Windows("Книга2").Sheets("Лист3").Select End Sub
Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу "Книга3", а потом пытаемся активировать лист НЕактивной на этот момент книги "Книга2". А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:
Sub Макрос2() Sheets("Лист3").Select 'здесь появится ошибка, т.к. пытаемся выделить ячейку на листе "Лист1" 'а на данный момент активным является Лист3 Sheets("Лист1").Range("C7").Select End Sub
Так же такая ошибка может появиться и по той простой причине, что указанного листа или книги нет(листа с указанным именем нет в данной книге или книга, к которой обращаемся просто закрыта).
Еще небольшой пример оптимизации:
Sub Макрос2() Windows("Книга3").Activate Sheets("Лист3").Select Range("C7").Select ActiveCell.FormulaR1C1 = "Привет" Range("C7").Select Selection.Font.Bold = True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub
Этот код записывает в ячейку С7 Лист3 книги "Книга3" слово "Привет", потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:
далее делаем для ячейки жирный шрифт:
With Workbooks("Книга3").Sheets("Лист3").Range("C7").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With
Тут есть нюанс. Windows необходимо всегда заменять на Workbooks - в кодах я сделал именно так. Если этого не сделать, то получите ошибку 438 - объект не поддерживает данное свойство или метод(object dos't support this property or metod), т.к. коллекция Windows не содержит определения для Sheets.
Вместо Workbooks("Книга3.xlsx") можно использовать обращение к активной книге или книге, в которой расположен код. Обращение к Лист3 активной книги, когда активен Лист2 или другой:
Но бывают случаи, когда необходимо производить действия исключительно в той книге, в которой сам код. И не зависеть при этом от того, какая книга активна в данный момент и как она называется. Ведь в процессе книга может быть переименована. За это отвечает ThisWorkbook:
ActiveWorkbook - действия с активной на момент выполнения кода книгой
ThisWorkbook - действия с книгой, в которой записан код
Однако так же бывают случаи, когда необходимо обращаться к новой книге или листу, которые были созданы в процессе работы кода. В таком случае необходимо использовать переменные:
Sub NewBook() 'объявляем переменную для дальнейшего обращения Dim wbNewBook As Workbook 'создаем книгу Set wbNewBook = Workbooks.Add 'теперь можно обращаться к wbNewBook как к любой другой книге 'но уже не указывая её имя wbNewBook.Sheets(1).Range("A1").Value = "Привет" 'Sheets(1) - обращение к листу по его порядковому номеру '(отсчет с начинается с 1 слева) End Sub Sub NewSheet() 'объявляем переменную для дальнейшего обращения Dim wsNewSheet As Worksheet 'добавляем новый лист в активную книгу Set wsNewSheet = ActiveWorkbook.Sheets.Add 'теперь можно обращаться к wsNewSheet как к любому другому листу 'но уже не указывая его имя или индекс wsNewSheet.Range("A1").Value = "Привет" End Sub
Не везде Activate лишний
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):
Sub Freeze_Panes() ThisWorkbook.Activate Sheets(2).Activate Range("B2").Select ActiveWindow.FreezePanes = True End Sub
В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.
Читайте также: