Работа с таблицами с использованием Excel VBA (с примерами)

Помимо ячеек и диапазонов, работа с листами - еще одна область, о которой вам следует знать, чтобы эффективно использовать VBA в Excel.

Как и любой объект в VBA, с листами связаны различные свойства и методы, которые можно использовать при автоматизации работы с VBA в Excel.

В этом уроке я подробно расскажу о «Рабочих листах», а также покажу вам несколько практических примеров.

Итак, приступим.

Все коды, которые я упоминаю в этом руководстве, необходимо поместить в редактор VB. Перейдите в раздел «Где разместить код VBA», чтобы узнать, как это работает.

Если вы заинтересованы в изучении VBA простым способом, ознакомьтесь с моими Онлайн-обучение по Excel VBA.

Разница между листами и листами в VBA

В VBA у вас есть две коллекции, которые иногда могут немного сбивать с толку.

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

В Excel VBA:

• Коллекция «Рабочие листы» будет относиться к коллекции всех объектов рабочего листа в книге. В приведенном выше примере коллекция Worksheets будет состоять из трех рабочих листов.
• Коллекция «Таблицы» будет относиться ко всем рабочим таблицам, а также к таблицам диаграмм в книге. В приведенном выше примере у него будет четыре элемента - 3 листа + 1 лист диаграммы.

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

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

Таблицы = Рабочие листы + Таблицы диаграмм

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

Поэтому, если вам нужно ссылаться только на листы, используйте коллекцию «Рабочие листы», а если вам нужно ссылаться на все листы (включая листы диаграмм), используйте коллекцию «Листы».

В этом уроке я буду использовать только коллекцию «Рабочие листы».

Ссылка на рабочий лист в VBA

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

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

Использование имени рабочего листа

Самый простой способ сослаться на рабочий лист - использовать его имя.

Например, предположим, что у вас есть книга с тремя листами - Лист 1, Лист 2, Лист 3.

И вы хотите активировать Лист 2.

Вы можете сделать это с помощью следующего кода: Sub ActivateSheet () Worksheets ("Sheet2"). Активировать End Sub

Приведенный выше код просит VBA обратиться к Sheet2 в коллекции Worksheets и активировать его.

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

Sub ActivateSheet () Sheets ("Sheet2"). Активировать End Sub

Использование порядкового номера

Хотя использование имени листа - это простой способ сослаться на лист, иногда вы можете не знать точное имя листа.

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

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

Предположим, у вас в книге есть следующие листы:

Приведенный ниже код активирует Sheet2:

Sub ActivateSheet () Рабочие листы (2) .Activate End Sub

Обратите внимание, что мы использовали номер индекса 2 в Задания (2). Это будет относиться ко второму объекту в коллекции рабочих листов.

Что происходит, когда вы используете 3 в качестве порядкового номера?

Он выберет Sheet3.

Если вам интересно, почему он выбрал Sheet3, ведь это явно четвертый объект.

Это происходит потому, что лист диаграммы не является частью коллекции листов.

Поэтому, когда мы используем номера индексов в коллекции Worksheets, они будут ссылаться только на рабочие листы в книге (и игнорировать листы диаграмм).

Напротив, если вы используете Sheets, Sheets (1) будет ссылаться на Sheets1, Sheets (2) будет ссылаться на Sheet2, Sheets (3) будет ссылаться на Chart1, а Sheets (4) будет ссылаться на Sheet3.

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

Примечание: порядковый номер идет слева направо. Итак, если вы переместите Sheet2 влево от Sheet1, тогда Worksheets (1) будет ссылаться на Sheet2.

Использование кодового имени рабочего листа

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

И если имя листа было изменено, ваш код не будет работать, пока вы также не измените имя листа в коде VBA.

Чтобы решить эту проблему, вы можете использовать кодовое имя рабочего листа (вместо обычного имени, которое мы использовали до сих пор). Кодовое имя может быть назначено в редакторе VB Editor и не меняется при изменении имени листа из области рабочего листа.

Чтобы присвоить листу кодовое имя, выполните следующие действия:

1. Щелкните вкладку Разработчик.
2. Щелкните кнопку Visual Basic. Это откроет редактор VB.
3. Выберите в меню пункт «Просмотр» и нажмите «Окно проекта». Это сделает видимой панель «Свойства». Если панель «Свойства» уже отображается, пропустите этот шаг.
4. Щелкните имя листа в проводнике проекта, который вы хотите переименовать.
5. На панели «Свойства» измените имя в поле перед (Имя). Учтите, что в названии не должно быть пробелов.

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

На изображении выше имя листа - «SheetName», а кодовое имя - «CodeName». Даже если вы измените имя листа на листе, кодовое имя останется прежним.

Теперь вы можете использовать коллекцию Worksheets для ссылки на рабочий лист или использовать кодовое имя.

Например, обе строки активируют рабочий лист.

Рабочие листы («Имя листа»). Активировать CodeName.Activate

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

Ссылаясь на лист в другой книге

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

Например, если у вас есть книга с именем Примеры и вы хотите активировать Sheet1 в книге примеров, вам необходимо использовать приведенный ниже код:

Sub SheetActivate () Рабочие книги ("Examples.xlsx"). Рабочие листы ("Sheet1"). Активировать End Sub

Обратите внимание, что если книга была сохранена, вам необходимо использовать имя файла вместе с расширением. Если вы не знаете, какое имя использовать, обратитесь за помощью в Project Explorer.

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

Добавление рабочего листа

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

Sub AddSheet () Worksheets.Add End Sub

Он принимает имя по умолчанию Sheet2 (или любое другое число в зависимости от того, сколько листов уже есть).

Если вы хотите, чтобы рабочий лист был добавлен перед определенным листом (скажем, Sheet2), вы можете использовать приведенный ниже код.

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

Приведенный выше код сообщает VBA о необходимости добавить лист, а затем использует оператор «До», чтобы указать рабочий лист, перед которым следует вставить новый рабочий лист.

Точно так же вы также можете добавить лист после рабочего листа (скажем, Sheet2), используя приведенный ниже код:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

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

Sub AddSheet () Dim SheetCount как целое число SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Удаление рабочего листа

Приведенный ниже код удалит активный лист из книги.

Sub DeleteSheet () ActiveSheet.Delete End Sub

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

Если вы не хотите видеть предупреждение, используйте следующий код:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Если для Application.DisplayAlerts установлено значение False, предупреждение не будет отображаться. Если вы его используете, не забудьте вернуть значение True в конце кода.

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

Если вы хотите удалить конкретный лист, вы можете сделать это с помощью следующего кода:

Sub DeleteSheet () Worksheets ("Sheet2"). Удалить End Sub

Вы также можете использовать кодовое имя листа, чтобы удалить его.

Sub DeleteSheet () Sheet5.Delete End Sub

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

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

Следующий код изменит имя Sheet1 на «Сводка».

Sub RenameSheet () Worksheets ("Sheet1"). Name = "Summary" End Sub

Вы можете комбинировать это с методом добавления листов, чтобы получить набор листов с определенными именами.

Например, если вы хотите вставить четыре листа с именами2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 и 2021-2022 Q4, вы можете использовать приведенный ниже код.

Sub RenameSheet () Уменьшить количество таблиц в виде целых таблиц Countsheets = Worksheets.Count для i = от 1 до 4 рабочих листов. Добавить после: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i). Name = "2018 Q" & i Next i Конец подписки

В приведенном выше коде мы сначала подсчитываем количество листов, а затем используем цикл For Next для вставки новых листов в конце. По мере добавления листа код также переименовывает его.

Назначение объекта рабочего листа переменной

При работе с листами вы можете назначить лист переменной объекта, а затем использовать эту переменную вместо ссылок на лист.

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

Вот код, который добавит 2021-2022 в качестве префикса ко всем именам рабочего листа.

Sub RenameSheet () Dim Ws As Worksheet для каждой Ws в рабочих таблицах Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

В приведенном выше коде в качестве типа рабочего листа объявляется переменная Ws (с использованием строки «Dim Ws As Worksheet»).

Теперь нам не нужно подсчитывать количество листов, чтобы просмотреть их. Вместо этого мы можем использовать цикл «Для каждой буквы W в листах». Это позволит нам просмотреть все листы в коллекции листов. Неважно, 2 листа или 20 листов.

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

В приведенном ниже коде мы назначаем переменную Ws для Sheet2 и используем ее для доступа ко всем свойствам Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

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

Обратите внимание, что код объявляет объект Ws как переменную типа Worksheet (используя строку Dim Ws как Worksheet).

Скрыть рабочие листы с помощью VBA (скрытый + очень скрытый)

Скрытие и отображение листов в Excel - простая задача.

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

Но что, если вы не хотите, чтобы они могли отображать лист (ы).

Вы можете сделать это с помощью VBA.

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

Sub HideAllExcetActiveSheet () Dim Ws As Worksheet для каждой Ws в ThisWorkbook.Worksheets Если Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

В приведенном выше коде свойство Ws.Visible изменено на xlSheetVeryHidden.

• Если для свойства Visible задано значение xlSheetVisible, лист отображается в области рабочего листа (как вкладки рабочего листа).
• Если для свойства Visible задано значение xlSheetHidden, лист скрыт, но пользователь может отобразить его, щелкнув правой кнопкой мыши на любой вкладке листа.
• Если для свойства Visible задано значение xlSheetVeryHidden, лист скрыт и не может быть отображен из области рабочего листа. Вам нужно использовать код VBA или окно свойств, чтобы отобразить его.

Если вы хотите просто скрыть листы, которые можно легко отобразить, используйте следующий код:

Sub HideAllExceptActiveSheet () Dim Ws As Worksheet для каждой Ws в ThisWorkbook.Worksheets Если Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Приведенный ниже код отобразит все рабочие листы (как скрытые, так и очень скрытые).

Sub UnhideAllWoksheets () Dim Ws As Worksheet для каждой Ws в ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Связанная статья: Показать все листы в Excel (за один раз)

Скрыть листы на основе текста в нем

Предположим, у вас есть несколько листов с названиями разных отделов или лет, и вы хотите скрыть все листы, кроме тех, в которых указан год 2021-2022.

Вы можете сделать это с помощью функции VBA INSTR.

Приведенный ниже код скроет все листы, кроме тех, на которых есть текст 2021-2022.

Sub HideWithMatchingText () Dim Ws As Worksheet для каждой Ws в рабочих таблицах Если InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0, то Ws.Visible = xlSheetHidden End If Next Ws End Sub

В приведенном выше коде функция INSTR возвращает позицию символа, в которой она находит соответствующую строку. Если строка не найдена, возвращается 0.

Приведенный выше код проверяет, есть ли в имени текст2021-2022. Если это так, ничего не происходит, иначе рабочий лист скрыт.

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

Сортировка листов в алфавитном порядке

Используя VBA, вы можете быстро отсортировать листы по их именам.

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

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets (j) .Name < Sheets (i) .Name Then Sheets (j) .Move before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Обратите внимание, что этот код хорошо работает с текстовыми именами, а также в большинстве случаев с годами и числами. Но это может дать вам неправильные результаты, если у вас есть имена листов 1,2,11. Он отсортирует и выдаст вам последовательность 1, 11, 2. Это потому, что он выполняет сравнение как текст и считает, что 2 больше 11.

Одновременная защита / снятие защиты со всех листов

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

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

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'замените Test123 нужным паролем. For Each ws In Worksheets ws.Protect password: = password Next ws End Sub

Следующий код снимет защиту со всех листов за один раз.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'замените Test123 на пароль, который вы использовали при защите For Each ws In Worksheets ws.Unprotect password: = password Next ws End Sub

Создание оглавления всех рабочих листов (с гиперссылками)

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

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Name & "! A1", _ TextToDisplay: = Worksheets (i) .Name Next i End Sub

Приведенный выше код вставляет новый рабочий лист и называет его Index.

Затем он просматривает все рабочие листы и создает гиперссылки для всех рабочих листов на индексном листе.

Куда поместить код VBA

Хотите знать, где находится код VBA в вашей книге Excel?

В Excel есть серверная часть VBA, называемая редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.

Вот как это сделать:

1. Перейдите на вкладку Разработчик.
2. Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
3. На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
4. Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
5. Скопируйте и вставьте код в окно модуля.

Вам также могут понравиться следующие руководства по Excel VBA:

• Работа с книгами с использованием VBA.
• Использование операторов IF Then Else в VBA.
• Для следующего цикла в VBA.
• Создание пользовательской функции в Excel.
• Как записать макрос в Excel.
• Как запустить макрос в Excel.
• События Excel VBA - простое (и полное) руководство.
• Как создать надстройку в Excel.
• Как сохранить и повторно использовать макрос с помощью личной книги макросов Excel.