Выбрать лист vba: Метод Worksheet.Activate (Excel) | Microsoft Learn

Как избежать выбора метода в VBA и почему подробное руководство

У первого телевизора, который я помню, у нас в детстве НЕ было пульта дистанционного управления. Как неудобно, правда?

Каждый раз, когда кто-то в семье хотел сменить канал или громкость, нам приходилось вставать с дивана и подходить к телевизору, чтобы нажать кнопку. Никто не хотел сидеть ближе всех к телевизору, если только ему не нужны были физические упражнения…

Представьте, сколько раз вы нажимали кнопки на пульте телевизора прошлой ночью. Наверное, много! Особенно, если вы женаты или у вас есть дети. А теперь представьте, как неэффективно подходить к телевизору, чтобы нажимать каждую из этих кнопок. Что за трата времени!

Содержание

  1. Метод выбора
    медленный, подвержен ошибкам и чрезмерно используется
  2. Не беспокойся, это не твоя вина!
  3. Нам не нужно выбирать
    диапазоны перед большинством действий (методы)
  4. Метод выбора вызывает
    больше ошибок
  5. Как еще мы можем
    избежать выбора метода?

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

Метод выбора в VBA может быть таким же трудоемким для Excel, как для вас, чтобы встать с дивана и сменить канал.

Метод Select используется для выбора объектов в Excel, таких
как рабочие таблицы, диаграммы, фигуры и диапазоны. Вот несколько примеров.

Worksheets("Sheet2").Select
Range("B5").Select

За этими строками кода обычно следует другая общая команда для копирования / вставки значений, форматирования ячейки, вставки формул и т.д.

Проблема с методом Select в том, что он действительно может замедлить ваш макрос. Когда мы используем метод Select, VBA заставляет приложение Excel обновлять экран с изменением выбора (отображать новый рабочий лист, выделять диапазон / ячейку и т.д.). Это обновление экрана занимает дополнительное время и обычно не требуется.

Хорошей новостью является то, что в 99% случаев, метода Select можно избежать.

Не беспокойся, это не твоя вина!

Мы часто видим метод Select в коде VBA, и это в основном
вина регистратора макросов. Не поймите меня неправильно, макро-рекордер —
отличный инструмент, но код, который он производит, может быть довольно
неэффективным.

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

Программа записи макросов выдает следующий код, когда я
записываю свои действия, чтобы скопировать диапазон B2: B8 на листе 1 и
вставить его на листе 2.

Sub Macro_Recorder_Copy_Paste()
    Sheets("Sheet1").Select
    Range("B2:B8").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveSheet.Paste
End Sub

Метод
Select используется 4 раза в
коде выше. Свойство Selection также используется
один раз, так что это действительно 5 ссылок на выделение для простой вставки
копии.

Этот макрос можно сделать намного эффективнее!

Нам не нужно выбирать
диапазоны перед большинством действий (методы)

Нам не нужно выбирать листы или диапазоны для этого простого
действия копирования и вставки. Следующий макрос выполняет ту же задачу без
метода Select и требует только одну строку кода.

Sub Simple_Copy_Paste()
    Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Range("B2")
End Sub

Ознакомьтесь с моей статьей и серией видео о 3 способах копирования и вставки с помощью VBA, чтобы получить больше примеров кода и пояснений.

Какая разница между макросами?

Допустим, вы хотите изменить телеканал с 2 на 7. В первом
примере с макро-рекордером требуется пять раз нажать кнопку «канал +».
Телевизор должен проделать дополнительную работу для отображения каждого канала
на экране, когда вы несколько раз нажимаете кнопку.

Упрощенный макрос требует, чтобы вы нажали кнопку 7 на
пульте дистанционного управления один раз, чтобы перейти непосредственно к
каналу 7. Гораздо быстрее и проще! 🙂

Метод выбора вызывает
больше ошибок

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

Worksheets("Sheet2").Range("A2").Select

Возвращает
следующую ошибку:

Run-time error '1004'
Select method of the Range class failed

Это просто правило в VBA, которое не позволяет нам выбирать лист и диапазон в одной строке кода. Если вы действительно хотите выбрать лист и диапазон, то нам нужно разбить его на две строки кода.

Worksheets("Sheet2").Select
Range("A2").Select

Метод Select также может быть подвержен множеству других ошибок. Важно правильно квалифицировать код, сначала выбрав родительские объекты в иерархии, такие как рабочие книги и рабочие таблицы, прежде чем выбирать диапазон.

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

Как еще мы можем
избежать выбора метода?

Мы хотим, чтобы наши макросы были максимально эффективными, чтобы мы могли экономить время и ​​получать дополнительные бонусные баллы в обзорах производительности! А отказ от метода Select — один из простых способов действительно улучшить наши макросы.

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

Если вы считаете, что в макросе есть какие-то ненужные методы Select, но вы не знаете, как от них избавиться, оставьте комментарий ниже со своим кодом.

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

Макрос для выделения ячеек со снятой защитой на листе Excel

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

Таблица шаблона отчета выглядит так:

По умолчанию в Excel нет инструмента для автоматического выделения незащищенных ячеек. А проверять установлена ли защита для каждой ячейки или нет – это потребует много сил и времени. По умолчанию каждая ячейка защищена при включении защиты листа. А чтобы снять защиту с ячейки нужно выбрать: «ГЛАВНАЯ»-«Формат»-«Формат ячеек». После перейти на закладку «Защита» и снять галочку на против опции «Защищаемая ячейка». Затем если включить защиту листа, выбрав инструмент: «РИЦЕНЗИРОВАНИЕ»-«Изменение»-«Защитить лист», то в незащищенную ячейку все равно можно вводить и редактировать значения данных. Поэтому лучшим решением будет – написать свой VBA макрос, который сам найдет и выделит каждую ячейку со снятой защитой в таблице Excel.

Для написания своего кода макроса открываем редактор Visual Basic (ALT+F11):

Sub SeeLock()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
   For i = 1 To diapaz1.Count
     If diapaz1(i).Locked = False Then
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub

Затем в этом же редакторе создаем новый модуль выбрав инструмент: «Insert»-«Module». И в созданный модуль вводим код представленный ниже на листинге:

Теперь если мы хотим найти и выделить все незащищенные от редактирования ячейки в таблице отчета на листе с включенной защитой выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«SeeLock»-«Выполнить». После запуска макроса выделиться несмежный диапазон всех незащищенных ячеек в таблице отчета.

Основной принцип действия макроса основывается на тем, что с помощью цикла каждая ячейка проверяется установлено ли для ее свойства Locked (галочка – «защищаемая ячейка») значение False. Если да, значит ячейка незащищенная от редактирования, когда будет включена защита рабочего листа Excel. В таком случае ячейка со снятой защитой дополняет несмежный диапазон в переменной diapaz2.



Полезные советы:

  1. Если нужно сделать так чтобы этот макрос наоборот выделял только защищенные ячейки, тогда достаточно лишь изменить значение False на True.

    2. If diapaz1(i).Locked = True Then

  2. Если нужно выделить все ячейки, для которых отключена защита на целом листе, а не только в границах таблицы тогда сделает изменить код в строке, где описано создание экземпляра объекта в переменной diapaz1 на:

    3. Set diapaz1 = Selection

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

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

Как выбрать ВСЕ ячейки на листе с помощью кода VBA

Главная > VBA > Как выбрать ВСЕ ячейки на листе с помощью кода VBA

VBA для выбора всех ячеек

Примечания

Лист Должно быть активировано

Связанные учебные пособия

В VBA есть свойство CELLS, которое можно использовать для выбора всех ячеек на листе.

 Cells. Выберите

VBA для выбора всех ячеек

  1. Сначала введите свойство CELLS, чтобы ссылаться на все ячейки на листе.
  2. После этого введите точку (. ).
  3. На этом этапе у вас будет список методов и свойств.
  4. В этом списке выберите «Выбрать» или введите «Выбрать».

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

Примечания

  • Свойство CELLS работает точно так же, как вы используете сочетание клавиш Control + A для выбора всех ячеек.
  • Когда вы запускаете этот код VBA, он выберет все ячейки, даже если лист защищен, а некоторые ячейки заблокированы.
  • Также будут выбраны скрытые ячейки.

Лист должен быть активирован

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

Допустим, вы хотите выделить все ячейки из «Листа1». Если вы используете приведенный ниже код, вы получите сообщение об ошибке. Сначала вам нужно активировать «Лист1», а затем использовать свойство «Ячейки», чтобы выбрать все ячейки.

 Рабочие листы ("Лист1"). Активировать
Cells.Select

Теперь, когда вы запустите это, он сначала активирует «Лист1», а затем выберет все ячейки. Эта вещь дает вам небольшое ограничение: вы не можете выбрать весь лист, если этот лист не активирован.

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

 Sheets.Add.Name = "mySheet"
Cells.Select

Что такое VBA

Связанные руководства

  • Подсчет строк с использованием VBA в Excel
  • Шрифт Excel VBA (цвет, размер, тип и жирность)
  • Excel VBA Скрытие и отображение столбца или строки
  • Диапазон Excel VBA — работа с диапазоном и ячейками
  • Применение границ ячейка с помощью VBA в Excel
  • Найти последнюю строку, столбец и ячейку с помощью VBA в Excel
  • Вставить строку с помощью VBA в Excel
  • Объединить ячейки в Excel с помощью кода VBA
  • Выбрать диапазон/ячейку с помощью VBA в Excel
  • ActiveCell в VBA в Excel
  • Метод специальных ячеек в VBA в Excel
  • Свойство UsedRange в VBA в Excel
  • Автоподбор VBA (строки, столбцы или весь лист)
  • VBA ClearContents (из ячейки, диапазона или всего листа)
  • VBA Копировать диапазон на другой лист + рабочую книгу
  • VBA Ввести значение в ячейку (установить, получить и изменить)
  • VBA Вставить столбец (одиночный и множественный)
  • VBA Именованный диапазон | (статический + из выбора + динамический)
  • Смещение диапазона VBA
  • Диапазон сортировки VBA | (По убыванию, Несколько столбцов, Ориентация сортировки
  • Перенос текста VBA (ячейка, диапазон и весь лист)
  • Проверка VBA, если ячейка пуста + несколько ячеек

Выбор рабочего листа на основе значения ячейки в Excel VBA

Я пытаюсь выбрать конкретную страницу на основе значения ячейки U2. Я использую внутренние имена Excel, а не отображаемые имена для листов. «Лист11» — это лист, к которому я сейчас пытаюсь подключиться. Я пробовал следующие коды, но получаю ошибку времени выполнения 9, вне диапазона.

Что я могу попробовать дальше?

Спасибо

 '#1
Dim ws As рабочий лист
ws = Диапазон ("U2")
Установите ws = ActiveSheet
'#2
(Диапазон ("U2"). Активировать
'#3
Sheet11.Активировать
Работает, но нет переменной
'#4
Листы(Диапазон("U2").Текст).Активировать
'#5
Листы(Диапазон("U2").Значение).Активировать
'#6
Dim GetString как строка
ПолучитьСтроку = Диапазон("U2")
GetString.Активировать
  • Excel
  • ВБА

3

Это то, что вы ищете?

 Листы(ActiveSheet.Range("U2").Value).Выбрать

1

Ссылка на рабочий лист по его кодовому имени 

 Опция Явный
Sub RefByCodeName()
    
    ' Запишите кодовое имя в строковую переменную ("wscName").
    ' Настройте рабочий лист!
    Dim wscName As String: wscName = CStr(Sheet1. Range("U2").Value)
    
    ' Используя функцию 'RefWorksheetByCodeName', попытайтесь сослаться
    ' рабочий лист ("ws") по его кодовому имени.
    Dim ws As Worksheet: установите ws = RefWorksheetByCodeName (wscName, ThisWorkbook)
    
    'Проверить рабочий лист.
    Если ws ничего, то
        MsgBox "Рабочий лист с кодовым именем '" & wscName & "' не найден.", _
            vbCritical
        Выйти из подпрограммы
    Конец, если
    
    'Продолжить, например:
    'MsgBox "Имя: " & ws.Name & vbLf & "Кодовое имя:" & ws.CodeName, _
        vbИнформация
    ' Убедитесь, что рабочая книга активна.
    Если Not ThisWorkbook Is ActiveWorkbook, то ThisWorkbook.Activate
    ' Выберите рабочий лист.
    ws.Select
Конец сабвуфера
'''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
' Назначение: в рабочей книге ("wb") ссылается на рабочий лист
' по его кодовому имени ("WorksheetCodeName").
'''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
Функция RefWorksheetByCodeName( _
    ByVal WorksheetCodeName как строка, _
    ByVal wb As Workbook) _
Как рабочий лист
    Dim ws As рабочий лист
    
    Для каждого ws в wb. Worksheets
        Если StrComp(ws.CodeName, WorksheetCodeName, vbTextCompare) = 0 Тогда
            Установите RefWorksheetByCodeName = ws
            Выход из функции
        Конец, если
    Следующий мс
Конечная функция

1

Листы имеют 3 способа обращения к ним:

  1. Имя (то, что вы видите на вкладке внизу)
  2. Индекс (число, обозначающее положение листа в книге, т. е. оно изменится, если вы измените положение листа)
  3. CodeName (это то, что вы называете внутренним именем Excel)

Вы можете выбрать лист, используя любой из них. Если бы у меня была рабочая книга с вкладками, названными в честь месяцев в порядке с января по декабрь, и я не изменил кодовые имена, код для выбора ноября с использованием каждого из них был бы следующим:
1.Имя Рабочие листы ("Ноябрь"). Выберите
2. Указатель рабочих листов (11). Выберите
3.Кодовое имя Sheet11.Select
В отличие от имени и индекса, вы можете напрямую ссылаться на кодовое имя, поскольку оно является объектом.

Следующий код использует индекс для циклического перебора кодовых имен листов, сравнивая их со значением в U2, когда оно совпадает с U2, он выбирает лист.

 Dim lCodeName как строка
Dim lWSCount, lCount как целое число
lCodeName = ActiveSheet.Range("U2").Value
lWSCount = ActiveWorkbook.Worksheets.Count
Для lCount = 1 Для lWSCount
Если Рабочие Листы(lCount).CodeName = lCodeName Тогда
Рабочие листы(lCount).Выбрать
Еще
Конец, если
Следующий

0

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.