Excel vba активировать лист

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»:

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

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» слово «Привет», потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:

Активация листа

Активация нужного рабочего листа
Добрый день! Господа! Нужна ваша помощь, вот код: Dim exсеl_арр Аs Оbjесt Dim excel_sheet As.

активация листа по значению ComboBox
Здравствуйте, столкнулся с проблемой как перейти на лист=ComboBox.value? Workbooks.Open.

Сохранение листа книги в файле — проблема с защитой листа и привязкой макросов
С толкнулся с такой проблемой при сохранении листа в файле вот код который сохраняет лист в.

Запуск макроса (написанного для актив. нужного листа) с др. листа (сложно)
Подскажите пожалуйста, есть большие макросы которые работают на активном листе, существует какой-то.

Нижеприведённый код работать не способен.
И вообще он не нужен — у Вас и так этот лист активен. Кнопка ведь на нём!

А, понял что хотели — уберите слово Set!

Добавлено через 2 минуты
Лучше проще так:

Подправь так, если нужен только лист

Нижеприведённый код работать не способен.
И вообще он не нужен — у Вас и так этот лист активен. Кнопка ведь на нём!

А, понял что хотели — уберите слово Set!

Добавлено через 2 минуты
Лучше проще так:

Это Вы не мне говорите, а rean

Добавлено через 39 секунд
Я уже понял

Это Вы не мне говорите, а rean

Добавлено через 39 секунд
Я уже понял

Да, обсолютно верно. Это погрешности скорости инета.

Добавлено через 14 минут

Нижеприведённый код работать не способен.
И вообще он не нужен — у Вас и так этот лист активен. Кнопка ведь на нём!

А, понял что хотели — уберите слово Set!

Добавлено через 2 минуты
Лучше проще так:

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

В ячейке B2 второго листа вывести значение ячейки A1 первого листа
Необходимо чтобы, например, в ячейке B2 второго листа автомотически вводилось значение ячейки A1.

Макрос на создание листа и перенос данных с предыдущего листа
Помогите пожалуйста, мне нужен макрос на создание листа и перенос данных с предыдущего листа. Есть.

В столбец 3-го листа вставить формулу суммы столбцов 1-го и 2-го листа
Найдите, пожалуйста, ошибку. Нужно сложить столбец А первого листа и столбец А второго листа.

Заполнение 2 листа данными из 3-го листа при событии на 1-м листе
Уважаемые форумчане, добрый вечер! Бьюсь челом и молю о помощи с решением задачи! Задача очень.

excel-vba Избегайте использования SELECT или ACTIVATE

пример

Очень редко вы когда-либо захотите использовать Select или Activate в своем коде, но некоторые методы Excel требуют, чтобы рабочий лист или рабочая книга были активированы до того, как они будут работать должным образом.

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

Помните, однако, макрорекордер создает строку кода для КАЖДОГО из ваших (пользовательских) действий. Это включает в себя щелчок на вкладке рабочего листа, чтобы выбрать Sheet2 ( Sheets(«Sheet2»).Select ), щелкнув по ячейке D3 перед вводом значения ( Range(«D3»).Select ) и с помощью клавиши Enter (которая эффективно « выбрав «ячейку ниже текущей выбранной ячейки: Range(«D4»).Select ).

Существует несколько проблем с использованием. .Select здесь:

• Рабочий лист не всегда указывается. Это происходит, если вы не меняете рабочие листы во время записи и означает, что код даст разные результаты для разных активных рабочих листов.
• .Select() работает медленно. Даже если для параметра Application.ScreenUpdating установлено значение False , это необработанная операция, которая должна быть обработана.
• .Select() неуправляем. Если Application.ScreenUpdating остается равным True , Excel будет фактически выбирать ячейки, рабочий лист, форму . независимо от того, с чем вы работаете. Это стрессово для глаз и действительно неприятно смотреть.
• .Select() вызовет прослушиватели. Это уже немного продвинуто, но если не работать, будут запускаться такие функции, как Worksheet_SelectionChange() .

Когда вы кодируете в VBA, все действия «набрав» (т. Select Команды Select ) больше не нужны. Ваш код может быть сведен к одному оператору, чтобы поместить значение в ячейку:

(Пример BETTER выше показывает использование промежуточных переменных для разделения разных частей ссылки на ячейку. Пример GOOD всегда будет работать очень хорошо, но может быть очень громоздким в гораздо более длинных модулях кода и более сложным для отладки, если одна из ссылок неверна. )

** ПРИМЕЧАНИЕ. Макросъемщик делает много предположений о типе данных, которые вы вводите, в этом случае вводите строковое значение в качестве формулы для создания значения. Ваш код не должен делать этого и может просто назначить числовое значение непосредственно ячейке, как показано выше.

** Примечание 2: рекомендуемая практика , чтобы установить локальную переменную рабочую книгу ThisWorkbook вместо ActiveWorkbook (если явно не нужно). Причина заключается в том, что ваш макрос обычно должен / использовать ресурсы в любой книге, из которой возникает код VBA, и НЕ будет выглядеть за пределами этой книги — опять же, если вы явно не назовете свой код работать с другой книгой. Когда вы открываете несколько книг в Excel, ActiveWorkbook — это та, которая может отличаться от рабочей книги, просматриваемой в редакторе VBA . Итак, вы думаете, что работаете в одной книге, когда вы действительно ссылаетесь на другую. ThisWorkbook относится к книге, содержащей исполняемый код.

Макрос на VBA Excel – Формируем документы по шаблону

Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

• ФИО – fio;
• № — number;
• Должность – dolgn;
• Адрес проживания – addres;
• Тел. № сотрудника – phone;
• Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

Для удобства можете на листе с данными создать кнопку и задать ей событие выполнить наш только что созданный макрос, и после чего простым нажатием выполнять этот макрос. Вот и все! Удачи!

Excel vba активировать лист

1270 просмотра

3 ответа

11 Репутация автора

Я хочу выбирать и модифицировать разные листы программно каждый раз при сохранении книги. В конце, однако, я хочу установить фокус на конкретном листе, чтобы книга сохранялась с этим конкретным листом в фокусе. Что я заметил, так это то, что всякий раз, когда код исполняется, он активирует рабочие листы, модифицирует их, но в конце возвращается к рабочему листу, который я выбрал перед запуском кода. Вот мой код:

Приведенный выше код выполняется в пустой локальной рабочей книге с 2 пустыми рабочими листами Sheet1 и Sheet2 . Всякий раз, когда я сохраняю рабочую книгу с Sheet2 выбранным, я вижу, что она действительно активирована, потому что журнал консоли печатает Sheet1 , однако в рабочей Sheet2 книге выбранный рабочий лист остается. Я использую SAP BusinessObjects Analysis, но, как отмечалось выше, рабочая книга является локальным макросом. включенная рабочая книга, которая не сохраняется на платформе SAP NetWeaver.

Могу ли я постоянно установить фокус на другой лист, чтобы он был виден в книге?

О нет. У меня есть надоедливая проблема непоследовательного поведения с различными кнопками сохранения еще раз, и это еще предстоит решить! Я только что понял, что если я сохраню с помощью кнопки сохранения рабочей книги, лист будет постоянно изменен, однако при сохранении с помощью редактора кода это не так. Предыдущая проблема, с которой я столкнулся, была в книгах, сохраненных в SAP NetWeaver, где код VBA выполняется не с помощью кнопки сохранения рабочей книги, а с помощью кнопки сохранения редактора кода. Я предполагаю, что мне придется зарегистрировать Oss с SAP для этого несоответствия.

Ответы (3)

0 плюса

35058 Репутация автора

То, что вы говорите, возможно, только если кто-то написал:

На рабочих листах (1).

В противном случае код, который вы используете:

должен активировать первый, Sheet и он не должен быть изменен позже.

Автор: Vityata Размещён: 21.12.2017 12:35

0 плюса

14432 Репутация автора

Я понятия не имею, где Sheet2 по сравнению с Sheet1 .

Код (выше) выполняется в пустой локальной рабочей тетради с 2 пустыми листами Sheet1 и Sheet2. Всякий раз, когда я сохраняю книгу с выбранным Sheet2, я вижу, что она действительно активирована, потому что журнал консоли печатает Sheet1, однако в рабочей книге выбранный лист остается Sheet2

Ваш код ничего не делает с именем листа Sheet2 . Он смотрит только на первый лист в порядке табуляции — лист можно назвать как угодно.
Он активирует первый лист, а затем помещает имя первого листа в ближайшее окно.

Этот код выберет лист с именем вкладки Sheet2 , затем поместит имя активного листа ( Sheet2 ) в ячейку A1 листа с именем вкладки Sheet1 .
Наконец, он выбирает лист с кодовым именем Sheet3 (кодовое имя — это имя, которое не указано в скобках Project Explorer ).