Все начинающие изучать 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 |
однако выделять ячейку(
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто "сократить":
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 End Sub |
Теперь у нас код получился короче и понятнее. Однако остался один Select:
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" End Sub |
Теперь несложно догадаться, что с листами все в точности так же.
Маленький нюанс: если сокращаем обращение к объектам, то 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" слово "Привет", потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:
Workbooks("Книга3").Sheets("Лист3").Range("C7").FormulaR1C1 = "Привет" |
далее делаем для ячейки жирный шрифт:
Workbooks("Книга3").Sheets("Лист3").Range("C7").Font.Bold = True |
и цвет заливки:
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.
Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления -Параметры папок -вкладка Вид -Скрывать расширения для зарегистрированных типов файлов) указано скрывать расширения - то указывать расширение не обязательно - Workbooks("Книга2"). Но и ошибки не будет, если его указать. Однако, если пункт "Скрывать расширения для зарегистрированных типов файлов" отключен, то указание Workbooks("Книга2") обязательно приведет к ошибке.
Вместо Workbooks("Книга3.xlsx") можно использовать обращение к активной книге или книге, в которой расположен код. Обращение к Лист3 активной книги, когда активен Лист2 или другой:
ActiveWorkbook.Sheets("Лист3").Range("A1").Value = "Привет" |
Но бывают случаи, когда необходимо производить действия исключительно в той книге, в которой сам код. И не зависеть при этом от того, какая книга активна в данный момент и как она называется. Ведь в процессе книга может быть переименована. За это отвечает ThisWorkbook:
ThisWorkbook.Sheets("Лист3").Range("A1").Value = "Привет" |
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 |
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):
Sub Freeze_Panes() ThisWorkbook.Activate Sheets(2).Activate Range("B2").Select ActiveWindow.FreezePanes = True End Sub |
В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.
Так же см.:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
Как обратиться к диапазону из VBA
Что такое переменная и как правильно её объявить?
Можно написать ещё проще..
Range("A1") = "Привет"
зачем усложнять "FormulaR1C1"?
Олег, спасибо за комментарий. Но на мой взгляд, если подходить с этой стороны к вопросу, то можно и еще проще:
[A1] = "Привет"
Однако цель статьи не запутать начинающего всеми нюансами и возможностью опустить указание свойства и даже самого Range. Цель статьи помочь упростить записанный макрорекордером код тем, кто только начинает работать в VBA. И для них может стать проблемой: оставить запись формулы(а вдруг там реально формула?) или ничего не указывать. Если при этом нужна будет именно запись формулы - то придется оставить FormulaR1C1 и Ваш вариант приведет к неверному результату(в большинстве случаев). Поэтому усложняете как раз Вы: начинающим проще будет убрать только то, что можно убрать простым сокращением, не вдаваясь в нюансы, которых он может не знать. Со временем придет понимание большего упрощения кодов.
Это мое мнение. Однако кому-то Ваш комментарий будет полезен и понятен. Но раз уж Вы написали, что можно упростить до такого: то можно было бы расписать почему это можно и когда это действительно нужно. Иначе те, кто не в теме совершенно не уяснят чем это им поможет :)
Если уж на то пошло, то почему в один блок не впихнуть, разделив отступами и убрать из оформления все что передает "0" или "false"?:
With Workbooks("Книга3.xlsb").Sheets("Лист3").Range("C7")
.FormulaR1C1 = "Привет"
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 65535
End With
Денис, приведен код, записанный макрорекордером. И потом показано как упростить именно его, убрав Select и Activate. Это цель статьи. Другие аспекты не затрагиваются, чтобы не запутывать начинающих. А Ваше замечание лишь усложнит начинающим понимание того, как это правильно сделать на приведенном куске кода, т.к. нет связи именно с записанным кодом.
Поверьте, я упростить могу и Ваш код. Но повторюсь: цель статьи раскрыть другие вещи.
Я не с целью съязвить или еще какой... Может кому понятно и будет.
А по куску кода. Упростите, пожалуйста. Интересно глянуть.
Ну первое, это применить With
но т.к. параметры Pattern и PatternColorIndex никакой роли здесь опять же не играют(мы все равно назначаем заливку с жесткой привязкой к цветовому индексу), то их можно тоже убрать:
Поэтому как всегда - все зависит от ситуации.
Спасибо за пример Дмитрий, впрочем и за ресурс тоже.
Я и сам новичек, многие вещи очень полезны.
Кстати, может тоже будет кому полезно. Тоже все зависит от цели, но [A1] = "Привет" - важно понимать что в данном случае работаем с активным листом и в некоторых случаях Evaluate работает заметно дольше. Как циклы For Each лучше не использовать с массивами For Next с классами. Но скорее относится к Вашей статье об оптимизации в общем.
И еще раз спасибо большое за то что делитесь опытом.