24 полезных примера макросов Excel для начинающих VBA (готовых к использованию)

Использование макросов Excel может ускорить вашу работу и сэкономить много времени.

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

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

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

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

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

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

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

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

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

• Откройте книгу, в которой вы хотите использовать макрос.
• Удерживая клавишу ALT, нажмите F11.Это откроетсяVB-редактор.
• Щелкните правой кнопкой мыши любой объект в Project Explorer.
• Перейдите в Вставка -> Модули.
• Скопируйте и вставьте код в окно кода модуля.

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

После вставки кода в книгу его необходимо сохранить с расширением .XLSM или .XLS.

Как запустить макрос

После копирования кода в редакторе VB шаги для запуска макроса следующие:

• Перейдите на вкладку разработчика.
• Щелкните Макрос.

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

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

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

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

Примечание. Вы найдете много апострофов ('), за которыми следует одна или две строки.Это комментарии, которые игнорируются при выполнении кода и размещаются как комментарии для себя/читателя.

Если вы обнаружите какие-либо ошибки в статье или коде, сообщите мне об этом.

Пример макроса Excel

В этой статье описаны следующие примеры макросов:

Показать все рабочие листы сразу

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

Вот код, чтобы отобразить все листы в книге.

"Этот код покажет все листы в рабочей книге Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

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

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

Скрыть все рабочие листы, кроме активного рабочего листа

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

'Этот макрос скроет весь рабочий лист, кроме активного листа Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

Сортировка листа по алфавиту с помощью 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 Если Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Защитите все рабочие листы одновременно

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

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

'Этот код защитит все листы за один раз Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'замените Test123 на нужный пароль. Для каждого ws In Worksheets ws.Protect password:=password Next ws Конец сабвуфера

Снять защиту сразу со всех листов

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

'Этот код защитит все листы за один раз Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'замените Test123 на пароль, который вы хотите. Для каждого ws In Worksheets ws.Unprotect password:=password Next ws Конец сабвуфера

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

Показать все строки и столбцы

Этот код макроса покажет все скрытые строки и столбцы.

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

'Этот код покажет все строки и столбцы в Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Разъединить все объединенные ячейки

Объединение ячеек в одну — обычная практика.Пока это работает, вы не сможете сортировать данные при объединении ячеек.

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

'Этот код разъединит все объединенные ячейки Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Обратите внимание, что я рекомендую использовать параметр «Центрировать выделение» вместо параметра «Объединить и центрировать».

Сохранить книгу с отметкой времени в имени

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

Хорошей практикой является сохранение файла с отметкой времени.

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

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

'Этот код сохранит файл с отметкой времени в его имени. Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" и отметка времени End Sub

Вам нужно указать расположение папки и имя файла.

В приведенном выше коде «C: UsersUsernameDesktop — это папка, которую я использую.Вам нужно указать папку, в которой вы хотите сохранить файл.Кроме того, я использовал общее имя «WorkbookName» в качестве префикса имени файла.Вы можете указать контент, связанный с вашим проектом или компанией.

Сохраняйте каждый рабочий лист как отдельный PDF-файл

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

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

Вот код VBA, который сохраняет каждый рабочий лист в виде отдельного PDF-файла.

'Этот код сохранит каждый рабочий лист как отдельный PDF-файл Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

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

Обратите внимание, что этот код работает только для рабочих листов (не для листов диаграмм).

Сохраняйте каждый рабочий лист как отдельный PDF-файл

Вот код для сохранения всей книги в формате PDF в указанной папке.

'Этот код сохранит всю книгу в формате PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

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

Преобразование всех формул в значения

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

'Этот код преобразует все формулы в значения Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

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

Защитить/заблокировать ячейки с формулами

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

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

'Этот код макроса заблокирует все ячейки с формулами Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Связанный учебник: Как заблокировать ячейки в Excel.

Защитить все листы в книге

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

'Этот код защитит все листы в рабочей книге Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

Этот код будет проходить по всем листам один за другим и защищать их.

Если вы хотите снять защиту со всех листов, используйте в своем коде ws.Unprotect вместо ws.Protect.

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

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

'Этот код будет вставлять строку после каждой строки в выборе. Вставьте ActiveCell.Offset(1, 2).Выберите Next i End Sub

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

Автоматически вставлять дату и временную метку в соседние ячейки

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

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

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

'Этот код вставит временную метку в соседнюю ячейку. 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

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

Кроме того, этот код работает, когда ввод данных выполняется в столбце A (обратите внимание, что в коде есть строка Target.Column = 1).Вы можете изменить его соответствующим образом.

Выделите альтернативные строки в выделении

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

Вот код, который сразу выделяет альтернативные строки в выборе.

'Этот код будет выделять альтернативные строки в выборе. Если следующий Myrow End Sub

Обратите внимание, что в коде я указал цвет как vbCyan.Вы также можете указать другие цвета (например, vbRed, vbGreen, vbBlue).

Выделите ячейки с ошибками

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

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

'Этот код выделит ячейки, содержащие слова с ошибками Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

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

Обновить все сводные таблицы в книге

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

'Этот код обновит всю сводную таблицу в рабочей книге Sub RefreshAllPivotTables() Dim PT As PivotTable для каждого PT в ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Подробнее об обновлении сводных таблиц можно прочитать здесь.

Изменить регистр букв выбранных ячеек на верхний регистр

Хотя в Excel есть формулы для изменения регистра букв текста, он позволяет делать это в другом наборе ячеек.

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

'Этот код изменит выбор на верхний регистр Sub ChangeCase() Dim Rng As Range для каждого Rng в Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

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

Выделить все ячейки с комментариями

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

'Этот код будет выделять ячейки с комментариями` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

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

Выделите пустые ячейки с помощью VBA

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

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

Вот код макроса VBA:

'Этот код выделит все пустые ячейки в наборе данных Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

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

Как отсортировать данные по одному столбцу

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

Подзаголовок сортировки данных () 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

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

Здесь также используются три ключевых параметра:

• Key1 — это ключ, по которому вы хотите отсортировать набор данных.В приведенном выше примере кода данные будут отсортированы на основе значений в столбце A.
• Order1 — Здесь вам нужно указать, хотите ли вы сортировать данные по возрастанию или по убыванию.
• Заголовок — здесь вам нужно указать, есть ли у ваших данных заголовок или нет.

Узнайте больше о том, как сортировать данные в Excel с помощью VBA.

Как отсортировать данные по нескольким столбцам

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

Вот код для сортировки данных на основе нескольких столбцов:

Sub SortMultipleColumns() с ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

Обратите внимание, что здесь я указал сначала сортировать по столбцу A, а затем по столбцу B.

Вывод будет выглядеть следующим образом:

Как получить только числовую часть строки в Excel

Если вы просто хотите извлечь из строки числовую или текстовую часть, вы можете создать пользовательскую функцию в VBA.

Затем вы можете использовать эту функцию VBA на своем листе (как обычную функцию Excel), и она будет извлекать из строки только числовую или текстовую часть.

Как показано ниже:

Вот код VBA, который создаст функцию, извлекающую числовую часть из строки:

'Этот код VBA создаст функцию для получения числовой части строки Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

Вам нужно поместить код в модуль, а затем вы можете использовать функцию =GetNumeric на листе.

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

Аналогично, приведенная ниже функция будет получать только текстовую часть строки в Excel:

'Этот код VBA создаст функцию для получения текстовой части из строки. 1))) Затем результат = результат и середина (CellRef, i, 1) Далее я GetText = функция завершения результата

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