Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Select и Activate - зачем нужны и нужны ли?

Все начинающие изучать 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":
VBA error 9 - 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

Не везде Activate лишний
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):

Sub Freeze_Panes()
    ThisWorkbook.Activate
    Sheets(2).Activate
    Range("B2").Select
    ActiveWindow.FreezePanes = True
End Sub

В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.

Так же см.:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
Как обратиться к диапазону из VBA
Что такое переменная и как правильно её объявить?


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

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 7 комментариев
  1. Oleg:

    Можно написать ещё проще..
    Range("A1") = "Привет"
    зачем усложнять "FormulaR1C1"?

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

      [A1] = "Привет"

      Однако цель статьи не запутать начинающего всеми нюансами и возможностью опустить указание свойства и даже самого Range. Цель статьи помочь упростить записанный макрорекордером код тем, кто только начинает работать в VBA. И для них может стать проблемой: оставить запись формулы(а вдруг там реально формула?) или ничего не указывать. Если при этом нужна будет именно запись формулы - то придется оставить FormulaR1C1 и Ваш вариант приведет к неверному результату(в большинстве случаев). Поэтому усложняете как раз Вы: начинающим проще будет убрать только то, что можно убрать простым сокращением, не вдаваясь в нюансы, которых он может не знать. Со временем придет понимание большего упрощения кодов.
      Это мое мнение. Однако кому-то Ваш комментарий будет полезен и понятен. Но раз уж Вы написали, что можно упростить до такого: то можно было бы расписать почему это можно и когда это действительно нужно. Иначе те, кто не в теме совершенно не уяснят чем это им поможет :)

  2. Денис:

    Если уж на то пошло, то почему в один блок не впихнуть, разделив отступами и убрать из оформления все что передает "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

          With Workbooks("Книга3.xlsb").Sheets("Лист3").Range("C7")
              .Value = "Привет"
              .Font.Bold = True
              With .Interior
                  .Pattern = xlSolid
                  .PatternColorIndex = xlAutomatic
                  .Color = 65535
              End With
          End With

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

          With Workbooks("Книга3.xlsb").Sheets("Лист3").Range("C7")
              .Value = "Привет"
              .Font.Bold = True
              .Interior.Color = 65535
          End With

          Поэтому как всегда - все зависит от ситуации.

          • Денис:

            Спасибо за пример Дмитрий, впрочем и за ресурс тоже.

            Я и сам новичек, многие вещи очень полезны.

            Кстати, может тоже будет кому полезно. Тоже все зависит от цели, но [A1] = "Привет" - важно понимать что в данном случае работаем с активным листом и в некоторых случаях Evaluate работает заметно дольше. Как циклы For Each лучше не использовать с массивами For Next с классами. Но скорее относится к Вашей статье об оптимизации в общем.

            И еще раз спасибо большое за то что делитесь опытом.

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

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


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

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти