Excel vba выбрать активный лист

Обновлено: 07.07.2024

Все начинающие изучать 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 и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.

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

Как указать ADO, какой лист в Excel-файле активный?
Из Excel-файла можно взять данные, не открывая Excel-файл, с помощью ADO. Можно ли с помощью ADO.

Как скопировать активный лист с одной книги в определёную другую под заданым именем?
ПОМОГИТЕ ПОЖАЛУСТА: скопировать активный лист с одной книги в определёную другую под заданым.

Активный лист и форма
При нажатии на кнопку открывается форма с комбобоксом.. Выбираю нужный пункт, появляется активный.

Запуск макроса из макроса ИЛИ повторение одного и того же кода
Excel Есть код в 10 строк (назовем его "блок"), который повторяется 5 раз в модуле (макросе).

Что-то пошло не так Выполняется первая часть, где переносятся данные, после активации код не работает (то есть не удаляет ячейки)

На листе 1 столбец цифр и каждой из них соответствует картинка. Нужно чтобы на лист 2 вывелись не нулевые значения вместе с картинкой. Вот мой код (или его подобие):
Sub Perenos()

For i = 1 To 200 Step 1

If (Cells(i, "A") >= 1) Then
Cells(i, "A").Copy Destination:=Sheets("Лист2").Cells(i, "A")
Cells(i, "B").Copy Destination:=Sheets("Лист2").Cells(i, "B")
End If
Next i

Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.

Откройте редактор VBA и обратите внимание на вашу книгу в проводнике, где уникальные имена листов указаны без скобок, а в скобках — имена листов, отображаемые на ярлычках в открытой книге Excel. Уникальные имена листов отсортированы по алфавиту и их расположение по порядку не будет соответствовать их индексам (номерам), если листы перемещались по отношению друг к другу. Индексы листов смотрите по порядку расположения ярлычков в открытой книге. Переместили листы — изменились их индексы.

Обращение к рабочему листу в коде VBA Excel:

УникИмяЛиста — уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
N — индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
Имя листа — имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.

Количество листов в рабочей книге Excel определяется так:

Переименование листов

С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:

expression.Name

где expression — переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.

Допустим, у нас есть лист с уникальным именем (Name) — Лист1, индексом — 1 и именем Name — МойЛист, которое необходимо заменить на имя — Реестр.

Скрытие и отображение листов

Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:

expression.Visible

где expression — переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:

False — лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
xlVeryHidden — лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
True — лист становится видимым.

Аналоги присваиваемых значений:

False = xlHidden = xlSheetHidden = 1
xlVeryHidden = xlSheetVeryHidden = 2
True = xlSheetVisible = -1 (константа xlVisible вызывает ошибку)

Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.

5 комментариев для “VBA Excel. Рабочий лист (обращение, переименование, скрытие)”

Отличная статья, спасибо!

Здравствуйте!
Хотел поделиться моментом, заслуживающим внимания, на мой взгляд:
обратите внимание, что обращаться к листу можно и через переменную
Worksheets(i).Activate
но допустим, что мы пишем
i = 2 ,
а у нас в книге присутствуют несколько листов, в т.ч. лист с именем 2
Каким же образом будет происходить обращение?
Опытным путём я установил, что это зависит от типа переменной
т.е. i будет использоваться в качестве имени листа, только если оно предварительно обозначено, как текстовая строка
Dim i as String
в других же случаях будет происходить переключение на второй лист (т.е. переменная считается индексом) если переменная никак не обозначена (при отключенном Option Explicit) или обозначена:
Dim i или Dim i as Variant
Dim i as Byte
Думаю, и с другими числовыми форматами будет вести себя также.

Добрый день!
Нигде не могу найти информацию о максимально возможном количестве листов в Excel
Спасибо за внимание

= Мир MS Excel/Активная страница - Мир MS Excel

Войти через uID

Спасибо за ответ. Просто ыместо Active надо было написать Activate. Сам задал сам ответил.))))) Fireman13

Спасибо за ответ. Просто ыместо Active надо было написать Activate. Сам задал сам ответил.))))) Автор - Fireman13
Дата добавления - 24.04.2013 в 10:13

To select a cell or a range of cells, use the Select method. To make a single cell the active cell, use the Activate method.

Fireman13, проблемы не вижу, как говорится : Всё правильно сделал
Fireman13, проблемы не вижу, как говорится : Всё правильно сделалМатраскин

Вы не правильно поняли у меня с 1 листа обращаеться ко 2 у выделяет там ячейки. Автор - Fireman13
Дата добавления - 24.04.2013 в 10:50

, то лист 2 на момент начала выполнения макроса НЕ ЯВЛЯЕТСЯ АКТИВНЫМ

В таком случае его необходимо сначала активировать/выбрать, что Вы правильно и сделали:

, то лист 2 на момент начала выполнения макроса НЕ ЯВЛЯЕТСЯ АКТИВНЫМ

В таком случае его необходимо сначала активировать/выбрать, что Вы правильно и сделали:

, то лист 2 на момент начала выполнения макроса НЕ ЯВЛЯЕТСЯ АКТИВНЫМ

В таком случае его необходимо сначала активировать/выбрать, что Вы правильно и сделали:

И где же она, великая сермяжная правда?

Из общего модуля работает и так
[vba]

[/vba]
А если вы пытаетесь запустить этот-же макрос из модуля листа, тогда нужно обращаться так
[vba]

И где же она, великая сермяжная правда?

Из общего модуля работает и так
[vba]

[/vba]
А если вы пытаетесь запустить этот-же макрос из модуля листа, тогда нужно обращаться так
[vba]

Быть или не быть, вот в чем загвоздка!

И где же она, великая сермяжная правда?

Из общего модуля работает и так
[vba]

[/vba]
А если вы пытаетесь запустить этот-же макрос из модуля листа, тогда нужно обращаться так
[vba]

Зачем вообще все эти селекты/активейты?
Может быть лучше сразу делать как нужно, а не как пишет рекодер?

Зачем вообще все эти селекты/активейты?
Может быть лучше сразу делать как нужно, а не как пишет рекодер? Hugo

Excel дает возможность управлять своими объектами (листами, ячейками, графиками) с помощью языка VBA. Чтобы создавать первые программы в Excel нужно иметь представление об объектной модели приложения.

Объектная модель Excel

Перед созданием своей первой программы в Excel необходимо разобраться в объектную модели приложения. Имея четкое понимания об объектной модели Excel вы сможете управлять практически всеми объектами, создавать/изменять листы, графики, отдельные файлы и многое другое. Возможно на первый взгляд она покажется довольно сложной, но не пугайтесь на конкретных примерах у вас сложится целостная картина.

Структура объектов

Проще всего рассматривать объектную модель как некое дерево или иерархическую структуру, так как каждый объект имеет свое ответвление. Кусочек этой структуры вы можете увидеть на рисунке далее.

Самый главный объект, вершина нашей структуры, это собственно само приложение Excel - объект Application. Какие объекты входят в Excel? правильно книги (файлы Excel), поэтому в объекте Application содержится коллекция из книг - объект Workbooks. Из чего состоят книги - из листов, диаграмм, соответственно внутри объекта Workbooks мы видим объект Worksheets и Charts. Идем дальше, лист в свою очередь состоит из строк, столбцов, ячеек это объекты Rows, Columns, Range.

Это, конечно, как вы понимаете только часть объектной модели Excel, мы перечислили только одни их самых основных объектов. Полное дерево объектов исчисляется сотнями объектов. Возможно она сейчас кажется сложной, не переживайте со временем вы начнете быстро в ней ориентироваться. Главное сейчас - это понять, что есть некие объекты, которые могут состоять из других объектов.

Объекты и коллекции объектов

Многие объекты, которые мы перечислили выше являются коллекциями. Коллекция - это группа однотипных объектов (или классов). Например Workbook s - это коллекция всех открытых в данный момент книг (объектов Workbook). А коллекция Worksheet s состоит из листов книги (объектов Worksheet), и так далее. Как правило все коллекции заканчиваются буквой S.

Давайте посмотрим, как обращаться к конкретным элементам коллекции в коде VBA.

Мы можем указать порядковый номер (начиная с единицы) элемента в коллекции. Worksheets(3) - в этом случае мы обращаемся к 3-му листу книги.

Мы также можем указать название листа в кавычках Worksheets("Название листа").

Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").

Чтобы обратиться к объекту Excel (к листу, или ячейке) необходимо указать ссылку на этот объект, перечислив полный путь к нему в соответствии с объектной моделью.

Например, для ячейки A1 полная ссылка будет выглядеть следующим образом:

На самом деле полный путь писать не обязательно. Application - можно практически всегда не указывать. Workbooks("Название книги") - можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:

Но всегда имейте ввиду, что это будет ячейка на активном листе.

Свойства объектов

Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, в котором содержится значение.

Вы можете получать значения из свойств объекта, просто указав путь к нужному свойству. Следующий код выведет значение ячейки A1 на активном листе.

А следующий код присвоит свойству Value новое значение 12345.

Протестируйте эти функции в своем файле.

Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count - вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.

Методы объектов

В отличие от свойств, которые просто сохраняют или передают значение, методы выполняют какие-либо действия с объектом. Например метод Worksheets.Add создает новый пустой лист в книге. Еще один пример это метод Clear, который позволяет очистить содержимое ячеек. Следующий код очистит данные и форматы из диапазона ячеек A1:C10.

Есть аналогичная функция, которая не удаляет при этом форматирование ячеек, а только очищает их от значений

Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в котором необходимо передать текст комментария к ячейке.

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

Приемы и лайфхаки

Автоматическое заполнение кода по начальным символам

Часто, особенно на первых порах, вы не запомните точное название всех объектов, но будете помнить их первые символы. В редакторе кода вы можете использовать отобразить список названий и автоматически записать объект по первым символам. Для этого используйте сочетание клавиш Ctrl+ J и увидите список из предложенных вариантов.

Просмотр свойств и методов у объекта

Когда вы будете писать свои программы, то редактор кода VBE будет подсказывать список свойств и методов у объекта. Чтобы отобразить этот список достаточно ввести объект, например, Worksheets и поставить точку, отобразится список свойств и методов. Выберите нужный с помощью мышки или же используйте стрелки. Вы также можете начать писать название свойства и редактор кода подберет подходящее. Когда выбор сделан - нажимайте клавишу Tab.

Явно указывайте тип переменных

Предыдущий лайфхак не будет работать для созданных локальных переменных до тех пор, пока вы явно не укажите их тип. Например, если вы захотели создать переменную MySheet в которой хотите хранить текущий лист. То список свойств и методов у такой переменной вы не увидите. Но стоит добавить строчку кода и явно указать тип переменной:

И подсказки снова заработают.

Быстрый просмотр справки

Все свойства и методы знать невозможно, так как их тысячи. Первое время вы будете активно пользоваться справкой. Советую использовать официальную справку на сайте Microsoft.

Чтобы быстро посмотреть информацию по интересующему вас объекту, свойству или методу просто поставьте курсор на интересующий элемент кода и нажмите клавишу F1. В окне браузера откроется официальная справка по этому элементу.

Поиск по объектной модели

Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения - просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.

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

Открываем объектную модель клавишей F2.
Так как мы хотим защитить лист, то логично предположить, что это метод в объекте Worksheet. Введем его в строку поиска и нажмем бинокль.
В результатах поиска выбираем наш объект Worksheet и просматриваем его элементы.
Находим метод Protect (Защита).
Мы также можем просмотреть описание всех аргументов этого метода
Конечно если что-то не понятно, то жмем клавишу F1 и открываем справку по этому методу с подробным описанием каждого аргумента.

На этом данную статью завершим, а в следующих мы более подробно поговорим про самый распространенный объект Excel - объект Range.

Читайте также: