Vba пример excel: Макросы для Excel с исходными кодами и описанием функций

Содержание

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

Содержание

  • Использование кода из примеров макросов Excel
  • Как запустить макрос
  • Примеры макросов Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

'Этот код отобразит все листы в книге Sub UnhideAllWoksheets () Dim ws As Worksheet для каждого ws в ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub

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

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

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

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

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

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

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

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

'Этот код защитит все листы за один раз. Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' замените Test123 нужным паролем. For Each ws In Worksheets () ws.Unprotect password: = password Next ws Конец подписки

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

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

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

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

'Этот код отобразит все строки и столбцы в подменю рабочего листа UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

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

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

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

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

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

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

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

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

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

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

'Этот код сохранит файл с меткой времени в его имени Sub SaveWorkbookWithTimeStamp () Тусклая метка времени в виде строки 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 для каждого ws в рабочих таблицах 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 () с ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub

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

Защитить все рабочие листы в книге

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

'Этот код будет защищать все листы в книге Sub ProtectAllSheets () Dim ws As Worksheet для каждого ws In Worksheets ws.Protect Next ws End Sub

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

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

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

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

'Этот код вставит строку после каждой строки в выбранном Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Вставьте ActiveCell.Offset (2, 0). Выберите Next i End Sub

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

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

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

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

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

'Этот код вставит метку времени в соседнюю ячейку Private Sub Worksheet_Change (ByVal Target As Range) При ошибке GoTo Handler Если Target.Column = 1 And Target.Value "" Тогда Application.EnableEvents = False Target.Offset (0, 1) = Format (Now (), «дд-мм-гггг чч: мм: сс») Application.EnableEvents = True End If Handler: End Sub

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

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

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

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

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

'Этот код будет выделять альтернативные строки в выделении Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Если следующий 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 считает орфографической ошибкой. Во многих случаях он также выделяет названия или названия брендов, которых он не понимает.

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

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

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

Вы можете узнать больше об обновлении сводных таблиц здесь.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub

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

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

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

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

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

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

Ниже приведен код, который сортирует данные по нескольким столбцам:

Sub SortMultipleColumns () с ключом ActiveSheet. Sort .SortFields.Add: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlYes. Применить End With End Sub

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

Результат будет примерно таким, как показано ниже:

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

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

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

Что-то вроде того, что показано ниже:

Ниже приведен код VBA, который создаст функцию для извлечения числовой части из строки:

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

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

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

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

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

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

Приемы :: Планета Excel

Сортировка:
дата создания
дата изменения
просмотры
комментарии

5 примеров использования новой функции IMAGE в Excel

Разбираем на примерах варианты использования новой функции ИЗОБРАЖЕНИЕ (IMAGE) для вставки изображений по ссылкам на лист Excel + делаем собственный аналог на VBA для вставки изображений из локальных файлов.

ChatGPT и Excel

Примеры использования искусственного интеллекта Open AI ChatGPT для решения повседневных задач в Microsoft Excel: ввод формул, создание макросов и М-функций в Power Query.

Поиск отличий в 2 текстовых строках

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

Основы работы в Microsoft Power BI

Пошаговый разбор всего процесса и главных принципов работы в Microsoft Power BI — от сырых файлов с исходными данными до опубликованного в облако интерактивного аналитического отчёта.

Диаграмма выполнения плана

Ещё один наглядный вариант визуализации выполнения плана с помощью столбчатой гистограммы с накоплением и «усов» погрешностей в Microsoft Excel.

Решение системы уравнений в Excel

Как решить систему уравнений (линейных и нелинейных) в Microsoft Excel с помощью обратной матрицы Крамера и подбором в надстройке Поиск решения (Solver).

Регистро НЕ чувствительность в Power Query (и Power BI)

Как победить регистрочувствительность в Power Query при фильтрации, удалении дубликатов и объединении таблиц с помощью функции Comparer. OrdinalIgnoreCase

Склейка диапазонов новыми функциями VSTACK и HSTACK

Как склеить (друг под другом или друг рядом с другом) несколько таблиц (в том числе с нескольких листов) с помощью новых функций Office 365 — ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).

Динамический «Топ-N + Другие» в сводной таблице

Как при помощи запроса Power Query реализовать в сводной таблице вывод динамического «Топ-N + Другие».

Быстрое прогнозирование в Microsoft Excel

Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.

Путь к текущей книге и новая функция LET

Как с помощью формул получить в ячейке листа обновляемый путь к текущей книге.

Подробнее…

Риск безопасности и блокировка макросов

Как разблокировать макросы при появлении сообщения РИСК БЕЗОПАСНОСТИ, когда файл была скачана из интернета.

Слушаем интернет-радио в Excel

Как за пару минут реализовать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.

Горизонтальная фильтрация столбцов в Excel

Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.

Переключение вычислений в сводной таблице срезами

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

Копировать сумму выделенных ячеек в Буфер обмена

Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)

Как правильно разворачивать вложенные таблицы в Power Query

Как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.

Моделирование лотереи в Excel

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

Столбец из примеров — искуственный интеллект в Power Query

Подробный разбор инструмента Столбец из примеров (Column from Examples), автоматически подбирающего формулу для преобразования ваших данных по заданному образцу.

Диаграмма по выделенной ячейке

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

Сборка разноформатных таблиц с одного листа в Power Query

Как собрать несколько разноформатных простых (не умных!) таблиц с одного листа Excel, а затем развернуть их в плоские (нормализовать) и соединить в единое целое с помощью Power Query.

Подробнее…

Нарастающий итог в Excel

Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.

LAMBDA — новая суперфункция Excel

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

Сохранение истории обновлений запросов Power Query

Как при обновлении запросов Power Query сохранять старые значения для истории, а не заменять их на новые.

Деление слипшегося текста функцией ФИЛЬТР.XML

Быстрый способ разделить слипшиеся данные на несколько ячеек с помощью функции ФИЛЬТР.XML, превратив исходный текст в XML-код.

Обновляемый курс валют в Excel

Как использовать функции ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) для загрузки мгновенно обновляемого курса любой валюты на любой интервал дат.

Система учета заказов на Google Calendar и Excel

Как вести учет заказов в Google-календаре и загружать потом оттуда данные в Excel с обновлением через Power Query.

Динамические гиперссылки между таблицами

Как сделать удобные динамические гиперссылки, чтобы быстро переходить из одной таблицы в другую — к заданному значению в строке и столбце. Что-то типа ВПР, но возвращающая ссылку, а не значение.

Поиск ключевых слов в тексте

Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.

Сборка таблиц с разными шапками из нескольких книг

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

Разделение таблицы по листам

Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа — с обновлением (через Power Query) и без (только VBA).

Одновременная фильтрация нескольких сводных таблиц

Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.

Картинки как подписи на диаграмме

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

Преимущества сводной по Модели Данных

Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?

Поиск последнего вхождения (инвертированный ВПР)

Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т. д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?

Парсинг нетабличных данных с сайтов

Импорт нетабличных данных, которые обычно «не видит» Power Query, на примере парсинга цен и товаров с сайта Wildberries.

Распределяем список по наборам

Как разделить список по наборам неодинакового размера — разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.

Сводная по таблице с многострочной шапкой

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

Выпадающий список с показом изображений

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

Как правильно использовать стили в Excel

Зачем (на самом деле!) нужны Стили в Excel и как правильно их использовать для быстрого форматирования ваших данных.

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

Три способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.

Вафельная диаграмма в Excel

2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.

Массовая замена текста в Power Query функцией List.Accumulate

Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.

Массовая замена текста формулами

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

Регулярные выражения (RegExp) в Power Query

Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.

Производственный календарь в Excel

Как при помощи Power Query сделать в Excel «вечный» производственный календарь — автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.

Обманчивая простота функции ПОСЛЕД (SEQUENCE)

Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) — генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.

Импорт данных из OneDrive и SharePoint в Power Query / BI

Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.

ВПР и числа-как-текст

Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются «числа-как-текст», что приводит к ошибкам #Н/Д.

План-факт анализ в сводной таблице с Power Pivot и Power Query

Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями «многие-ко-многим», простых мер на DAX и допиливании входных данных с помощью Power Query.

Библиотека кодов Excel VBA — полезные макросы для начинающих

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

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

Эти коды идеально подходят для начинающих, которые раньше не использовали Excel VBA или только начинают.

Давайте сначала опишем, что такое код макроса и как вы можете начать его использовать. Затем мы перейдем к самой интересной части и рассмотрим примеры макросов Excel в этом списке.

Изучите лучшие сочетания клавиш Excel!

Загрузите нашу распечатанную таблицу ярлыков для ПК и Mac

Введите адрес электронной почты

Что такое код макроса?

Код макроса относится к коду VBA (Visual Basic для приложений) для макроса.

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

Самостоятельное написание этого кода VBA или макроса дает вам гораздо больший контроль над вашим макросом.

Код VBA может быть простым и выполнять базовый шаг форматирования или замораживать панели. Или он мог бы сделать что-то большее, например, экспортировать все листы книги в отдельный PDF-файл.

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

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

Показать вкладку «Разработчик»

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

Если на вашей ленте нет вкладки «Разработчик», выполните следующие действия, чтобы включить ее.

  1. Щелкните Файл > Параметры .
  2. Выберите категорию «Настроить ленту», а затем установите флажок для вкладки «Разработчик».
  1. Нажмите «ОК».

Как использовать код макроса

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

  1. Щелкните Разработчик > Visual Basic или нажмите Alt + F11 .

Открывается редактор Visual Basic. Окно Project Explorer показано слева. В нем перечислены открытые книги и другие проекты.

  1. Щелкните в рабочей книге, куда вы хотите вставить код VBA, щелкните Вставить > Модуль .
  1. Скопируйте и вставьте код из этой статьи в окно кода.

После этого макрос будет доступен для запуска в окне макросов.

  1. Нажмите Разработчик > Макросы . Выберите макрос из списка и нажмите Выполнить .

В этой библиотеке кодов VBA есть категория с именем Events . Эти макросы будут храниться не в модулях, а в объекте, где происходит событие.

Это будет объяснено, когда мы представим код. Большинство макросов в этом списке хранятся в модулях и следуют описанной выше процедуре.

Добавление кнопки для запуска ваших макросов

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

Кнопки можно вставлять на панель быстрого доступа (где находятся кнопки «Сохранить» и «Отменить»), на ленту или на рабочий лист.

Подход зависит от того, что делает макрос и когда вы хотите его запустить.

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

  1. Щелкните стрелку Настроить панель быстрого доступа и щелкните Дополнительные команды .
  1. Щелкните стрелку списка Популярные команды и выберите Макросы . Выберите нужный макрос и нажмите Добавить .
  1. Нажмите кнопку Изменить , чтобы изменить изображение и отображаемое имя кнопки.

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

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

Примеры кода Excel VBA

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

Общие задачи Excel

  • Автоподбор ширины столбца
  • Скопируйте и вставьте
  • Очистить все гиперссылки на листе
  • Формат ячеек с формулами
  • Преобразование формул в значения

Коды рабочих листов

  • Показать все столбцы
  • Защита рабочего листа
  • Перебрать все листы книги

Коды рабочих книг

  • Показать все рабочие листы
  • Защита книги
  • Открытие и закрытие книги
  • Электронная почта активной книги с Outlook

Файлы и папки

  • Экспорт каждого рабочего листа в виде одного файла PDF
  • Экспорт активного листа в формате PDF
  • Экспорт нескольких листов в один файл PDF
  • Перебрать все файлы папки
  • Выбор файла с помощью FileDialog

Полезные функции Excel

  • Сортировка столбцов
  • Фильтруйте данные
  • Создать диаграмму

События

  • Переход к определенному рабочему листу при открытии
  • Выполнить действие при смене ячейки

Резюме!

Общие задачи Excel

Эти коды VBA позволяют быстро выполнять общие задачи Excel.

Автоподбор ширины столбцов

Этот код автоматически подгонит ширину столбцов для всех столбцов рабочего листа.

  Подпрограмма AutofitAllColumns()

Cells.EntireColumn.AutoFit

Конец суб

Этот код автоматически подгонит ширину определенных столбцов на листе. В данном примере это столбцы D и F.

  Подпрограмма AutofitSpecificColumns()

Диапазон("D:D,F:F").EntireColumn.AutoFit

End Sub
Копировать и вставить

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

Следующий код копирует диапазон A1:B6 в A1 другого листа.

  Sub CopyAndPaste()

Диапазон("A1:B6").Копировать рабочие листы("Лист2").Range("A1")

End Sub

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

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

  Sub CopyAndPaste()

Range("A2").CurrentRegion.Copy Worksheets("Архив").Range("A1").End(xlDown).Offset(1, 0)

Конец суб

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

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

  Sub CopyAndPasteValues()

Диапазон("A1:B6").Копировать
Рабочие листы ("Лист2"). Диапазон ("A1"). Специальная вставка: = xlPasteValues

End Sub
Удалить все гиперссылки на листе

Следующий код макроса удалит все гиперссылки на листе.

  Sub ClearHyperlinks()

ActiveSheet.Гиперссылки.Удалить

End Sub
Форматирование ячеек с формулами

Форматирование ячеек, содержащих формулы, упрощает их идентификацию на листе. Этот код макроса отформатирует ячейки формулы желтым цветом заливки ( ColorIndex = 6 ).

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

  Подформатформулы()

Dim rng As Range

Для каждого кольца в Cells.SpecialCells(xlCellTypeFormulas)

rng.Interior.ColorIndex = 6

Следующий круг

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

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

  Sub ConvertFormulastoValues()

Dim rng As Range

Для каждого кольца в Cells.SpecialCells(xlCellTypeFormulas)

rng.Formula = rng.Value

Следующий круг

Конец суб

Коды рабочих листов

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

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

Скрытие столбцов позволяет уменьшить беспорядок на листе и защитить данные. Этот код макроса покажет все скрытые столбцы одним нажатием кнопки.

  Sub UnhideAllColumns()

Столбцы.EntireColumn.Hidden = Ложь

End Sub
Защита рабочего листа

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

  Sub ProtectWS()

ActiveSheet.Protect

End Sub

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

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

  Sub ProtectWS()

Пароль ActiveSheet.Protect: = «Excel», AllowInsertingRows: = True

Конец суб

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

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

  Sub ProtectFormulas()

С ActiveSheet

.Снять защиту
.Cells.Locked = Ложь
.Cells.SpecialCells(xlCellTypeFormulas).Locked = Истина
.Защищать

Конец с

Конец суб
Цикл по всем листам рабочей книги

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

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

  Sub LoopAllSheets()

Dim ws As рабочий лист

Для каждого ws в ActiveWorkbook.Worksheets
ws.Protect
Следующий мс

Конец суб

Коды рабочих книг

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

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

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

  Sub UnhideAllWorksheets()

Dim ws As рабочий лист

Для каждого ws в ActiveWorkbook. Worksheets
ws.Visible = кслшитвисибле
Следующий мс

End Sub
Защита книги

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

  Sub ProtectWorkbook()

Пароль ThisWorkbook.Protect: = "Excel"

End Sub
Открытие и закрытие книги

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

Присваивает активную книгу переменной, затем открывает Северная рабочая книга , чтобы скопировать в нее данные из диапазона A1:C250. Затем эта рабочая книга сохраняется и закрывается.

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

  Sub OpenCloseWorkbooks()

Dim wbk как рабочая книга
Установите wbk = ActiveWorkbook

Workbooks.Open "C:\Users\Admin\OneDrive\Desktop\Sales\North.xlsx"
wbk. Sheets("Лист1").Range("A1:C250").Назначение копирования:=Range("A1")

ActiveWorkbook.Close SaveChanges:=True

Конец суб
Электронная почта активной книги с помощью Outlook

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

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

  Субаттачтоемаил()

Dim OutApp как объект
Затемнить OutMail как объект
Установите OutApp = CreateObject("Outlook.Application")
Установить OutMail = OutApp.CreateItem(0)

С исходящей почтой
.to = "[email protected]"
.Subject = "Великолепная электронная таблица"
.Body = "Здравствуйте! Надеюсь, вам понравится великолепная таблица, прикрепленная к этому письму. "
.Attachments.Add ActiveWorkbook.FullName
.отображать
Конец с

Установите OutMail = Ничего
Установить OutApp = Ничего

Конец суб

Файлы и папки

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

Экспорт каждого рабочего листа в виде отдельного PDF-файла

Этот код экспортирует все рабочие листы в отдельный PDF-файл. Имя листа используется в качестве имени файла PDF, и они сохраняются в папку, назначенную переменной FolderPath . Просто измените этот путь на то, что вам нужно.

  Sub ExportAsPDF()

Dim FolderPath как строка
Dim ws As рабочий лист

FolderPath = "C:\Users\Computergaga\Desktop\Sales"

Для каждого ws в ActiveWorkbook.Worksheets

ws.ExportAsFixedFormat Тип: = xlTypePDF, имя файла: = FolderPath & "\" &
ws.Name, openafterpublish:=False

Следующий

MsgBox "Все файлы PDF успешно экспортированы."

Конец суб
Экспорт активного листа в формате PDF

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

  Sub ExportAsPDF()

FolderPath = "C:\Users\Computergaga\Desktop\Sales"

ActiveSheet.ExportAsFixedFormat Тип:=xlTypePDF, имя файла:=FolderPath & "\" &
Activesheet.Name, openafterpublish:=False

End Sub
Экспорт нескольких листов в один PDF-файл

Вы можете экспортировать рабочие листы в один файл PDF. Этот код экспортирует рабочие листы с именами London и Berlin в один файл PDF.

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

.
  Sub ExportAsPDF()

Dim FolderPath как строка

FolderPath = "C:\Users\Trainee1\Desktop\PDFs"

Листы(Массив("Лондон", "Берлин")).Выбрать
ActiveSheet.ExportAsFixedFormat Тип:=xlTypePDF, Имя файла:=FolderPath & "\Sales", openafterpublish:=False, ignoreprintareas:=False

MsgBox "Все файлы PDF успешно экспортированы."

Конец суб
Циклический просмотр всех файлов папки

Следующий код VBA будет выполнять циклический просмотр каждого файла в папке Sales на рабочем столе.

Каждый файл открывается, число 20 вводится в ячейку A1 Листа 1, затем сохраняется и закрывается. Это простая операция, но этот код можно заменить любыми действиями, которые вы хотите выполнить с каждым файлом в папке.

Для этого процесса используется функция Dir . Первый экземпляр Dir — это когда указан путь к файлу. Он захватывает имя файла первого файла в папке.

Затем он снова используется в конце цикла для захвата имени следующего файла.

  Sub LoopAllFiles()

Dim fileName как вариант
fileName = Dir("C:\Users\admin\OneDrive\Desktop\Sales\")

Делать, пока имя_файла <> ""

    Workbooks.Открыть имя файла
    Рабочие листы ("Лист1"). Диапазон ("А1"). Значение = 20
    ActiveWorkbook.Close savechanges:=True

    имя_файла = Каталог

Петля

Конечный переходник

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

  имя_файла = Dir("C:\Users\admin\OneDrive\Desktop\Sales\*.xlsx")
Выбор файла с помощью диалогового окна FileDialog

При работе с файлами диалоговое окно FileDialog в Excel VBA очень полезно. Это среда, с которой пользователь мгновенно знакомится.

Существуют FileDialogs для открытия файла, сохранения, выбора и выбора папки. В этом примере используется открытый диалог. Это известно как мсофиледиалогопен .

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

В этом коде VBA манипулируются различными свойствами FileDialog, поэтому он используется с конструкцией With .

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

  Sub Использование FileDialog()

Dim имя файла как строка

С Application. FileDialog(msoFileDialogOpen)
.AllowMultiSelect = Ложь
.Title = "Выберите книгу для использования"
.Показывать
Имя файла = .SelectedItems(1)
Конец с

Workbooks.Open имя файла
Рабочие листы ("Лист1"). Диапазон ("А1"). Значение = 20
ActiveWorkbook.Close SaveChanges:=True

MsgBox "Книга обновлена"

End Sub

Полезные функции Excel

Эти коды VBA упростят использование некоторых наиболее полезных функций Excel.

Сортировка столбцов

Следующий код сортирует диапазон A1:K250 по одному столбцу. Предполагается, что диапазон имеет заголовки в первой строке.

Аргумент Key1 указывает столбец для сортировки. В этом примере аргументу присвоено значение C1, поэтому диапазон сортируется по столбцу C в порядке убывания.

  Sub SortSingleColumn()

Диапазон ("A1: K250"). Ключ сортировки 1: = Диапазон ("C1"), Order1: = xlDescending

Конец суб

В следующем коде макроса определенное имя Продажи используется в качестве диапазона для сортировки. Определенное имя не включает заголовки.

На этот раз диапазон отсортирован по столбцу B в порядке возрастания.

  Sub SortSingleColumn()

Range("Продажи").Sort Key1:=Range("B1"), Order1:=xlAscending

End Sub

Дополнительные аргументы Key и Order могут быть добавлены для сортировки по нескольким столбцам.

В этом примере заданное имя 9В качестве диапазона использовался 0089 Продажи . Диапазон сортируется сначала по столбцу D в порядке возрастания, а затем по столбцу J в порядке убывания.

  Sub SortMultipleColumns()

Диапазон ("Продажи"). Сортировка Key1: = Range ("D1"), Order1: = xlAscending, Key2: = Range ("J1"), Order2: = xlDescending

End Sub

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

В этом примере таблица называется Продажи и таблица отсортирована по полю Страна в порядке возрастания.

  Подтаблица сортировки()

С ActiveSheet. ListObjects("Продажи").Sort
   .SortFields.Clear
   .SortFields.Add Key:=Range("Sales[Country]"), Order:=xlAscending
   .Применять
Конец с

End Sub
Фильтруйте данные

Следующий код макроса можно использовать для включения функции автофильтра. Он применяется к активному рабочему листу и использует текущую область для диапазона A1.

  Sub TurnFilterOn()

Диапазон("A1").АвтоФильтр

Конец суб

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

  Sub TurnFilterOff()

ActiveSheet.AutoFilterMode = Ложь

End Sub

В этом примере фильтр применяется к столбцу D ( Field:=4 ), чтобы отобразить записи только для Дании.

  Sub FilterByText ()

Диапазон ("A1"). Поле автофильтра: = 4, Criteria1: = "Дания"

End Sub

Для фильтрации по более чем одному текстовому значению 9Можно использовать оператор 0089 xl или . Здесь данные фильтруются, чтобы показать строки для Дании и Великобритании.

  Sub FilterByText ()

Диапазон ("A1"). Поле автофильтра: = 4, критерии 1: = "Дания", оператор: = xlOr, критерии 2: = "Великобритания"

End Sub

Существует множество доступных для использования операторов, таких как xlFilterIcon и xlTop10Items .

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

  Sub FilterByNumber()

Диапазон ("A1"). Поле автофильтра: = 8, Criteria1: = "> 5", Оператор: = xlAnd, Criteria2: = "<20"

End Sub

В следующем примере список фильтруется по нескольким столбцам. Он фильтрует столбец D только для Дании и столбец H для чисел больше 5 и меньше 20.

  Sub FilterByTwoColumns()

С диапазоном ("A1")

Поле .AutoFilter:=4, Criteria1:="Дания"
Поле .AutoFilter:=8, Criteria1:=">5", Оператор:=xlAnd, Criteria2:="<20"

Конец с

Конец суб

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

  Sub ClearFilters()

Если ActiveSheet.FilterMode = True Тогда

ActiveSheet.ShowAllData

Конец, если

End Sub

Автофильтр — не единственный фильтр в Excel. Узнайте, как использовать расширенный фильтр с VBA в Excel.

Создать диаграмму

Вы можете использовать Excel VBA для создания диаграмм одним нажатием кнопки.

Этот код VBA создаст столбчатую диаграмму (тип диаграммы по умолчанию) из диапазона C3:D8. Он использует переменную объекта диаграммы, поэтому параметры задаются для размера и положения области диаграммы. Затем данные устанавливаются для диаграммы.

  Подпрограмма CreateChart()

Затемнение MChart как ChartObject

Установите MyChart = ActiveSheet.ChartObjects.Add (Верх: = 50, Слева: = 100, Ширина: = 450, Высота: = 250)
Диапазон MyChart.Chart.SetSourceData("C3:D8")

Конец суб

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

  Sub ChangeChartType()

Dim MyChart как диаграмма
Установить MyChart = ActiveSheet.ChartObjects(1).Chart

MyChart.Chart.ChartType = xlLine

End Sub

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

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

  Sub EditChartElements ()

Dim MyChart как диаграмма
Установить MyChart = ActiveSheet.ChartObjects(1).Chart

MyChart.HasTitle = Истина
MyChart.ChartTitle.Text = "Продажи товаров"
MyChart.SetElement msoElementDataLabelOutSideEnd

Конец суб

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

События

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

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

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

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

  1. В редакторе Visual Basic дважды щелкните объект книги в окне Project Explorer, с которым вы хотите использовать код.
  1. Выберите Workbook из списка объектов.
  2. Выберите Открыть из списка процедур.
  3. Вставьте требуемый код в процедуру, показанную в окне кода.

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

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

  Частная подпрограмма Workbook_Open()

Рабочие листы("Оглавление").Выбрать
Диапазон("A2").Выбрать

Конец суб

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

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

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

Для создания Worksheet_Change процедура:

  1. Дважды щелкните рабочий лист, который вы хотите использовать, в окне Project Explorer, чтобы открыть окно его кода.
  2. Выберите Рабочий лист из списка объектов, измените из списка процедур, и процедура появится в окне кода.

В этом коде измененная ячейка проверяется с помощью оператора If , чтобы проверить, находилась ли измененная ячейка в столбце 5 и было ли результатом изменения значение .Да .

Переменная Target предоставляется событием изменения рабочего листа. Это измененная ячейка.

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

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

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

  Private Sub Worksheet_Change (ByVal Target As Range)

Если Target. Column = 5 и Target = "Да", тогда

Приложение.EnableEvents = Ложь
Target.EntireRow.Copy Worksheets("Лист2").Range("A1").End(xlDown).Offset(1, 0)
Target.Interior.ColorIndex = 6

Конец, если

Приложение.EnableEvents = Истина

End Sub

Что дальше?

Эти коды VBA значительно экономят время, но это только начало.

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

Запишитесь на наш курс Excel Macros и VBA, чтобы начать свое путешествие сегодня.

Готов стать сертифицированным ниндзя Excel?

Пройдите курс GoSkills «Макросы и VBA» сегодня!

Начать бесплатную пробную версию

формул VBA Excel | SuperExcelVBA

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

VBA Формула

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

 Диапазон ("F2"). Формула = "=СУММ(B2:C7)"
    Диапазон ("F3"). Формула = "=СУММ($B$2:$C$7)"

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

Несколько формул

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

"Перетаскивание" вручную:

"Перетаскивание" с помощью VBA:

 Диапазон ("D2:D7"). Формула = "=СУММ(B2:C2)"

Другой способ выполнить то же действие — использовать метод FillDown.

 Диапазон ("D2"). Формула = "=СУММ(B2:C2)"
    Диапазон("D2:D7").FillDown

VBA FormulaLocal

FormulaLocal добавляет предопределенные формулы Excel на рабочий лист. Эти формулы, однако, должны быть написаны на местном языке Excel (в случае Бразилии — на португальском языке).

 Диапазон("F2").FormulaLocal = "=SOMA(B2:C7)"

Как и свойство Formula, FormulaLocal можно использовать для создания нескольких формул .

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

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

В обозначениях:

  • R относится к рядам , в случае вертикального смещения
  • C относится к столбцам , в случае горизонтального смещения
  • N символизирует целое число , которое указывает, на сколько должно быть сдвинуто число строк и/или столбцов
  • Относительное обозначение : Используйте в качестве ссылки диапазон, который его назвал

Формат относительной формулы: R[ N ]C[ N ]:R[ N ]C[ N ].

 Range("F2").FormulaR1C1 = "=SUM(R[0]C[-4]:R[5]C[-3])" 'Равно нижней строке
    Диапазон ("F2"). ФормулаR1C1 = "=СУММ(RC[-4]:R[5]C[-3])"

Если N опущено, предполагается значение 0.

В примере RC[-4]:R[5]C[-3] приводит к "B2: C7" . Эти ячейки получаются путем: отступления 4 столбца влево RC[-4] из Range("F2") для получения "B2" ; и 5 строк вниз и 3 столбца влево R[5]C[-3] из Range("F2") для получения "C7" .

  • Абсолютное обозначение : Используйте начало электронной таблицы в качестве ссылки

Формат относительной формулы: R N C N :R N C N .

 Range("F2").FormulaR1C1 = "=СУММ(R2C2:R7C3)" 'Результаты в "$B$2:$C$7"

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

Две записи (относительная и абсолютная) могут быть объединены.

 Range("F2").FormulaR1C1 = "=СУММ(RC[-4]:R7C3)" 'Результаты в "B2:$C$7"

VBA WorksheetFunction

К формулам Excel также можно получить доступ с помощью методов объекта WorksheetFunction.

 Диапазон ("F2") = WorksheetFunction.Sum (Диапазон ("B2: C7"))

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

Все формулы объекта WorksheetFunction на английском языке.

Одним из больших преимуществ такого доступа к формулам Excel является возможность более легкого их использования в среде VBA.

 MsgBox (WorksheetFunction.Sum(3, 4, 5))
    Расход=4
    MsgBox (WorksheetFunction.Sum(3, 4, 5,-расход))

Чтобы получить список доступных формул Excel в этом формате, просто введите WorksheetFunction. автоматически появится меню опций со всеми формулами:

Закрепление вашего обучения

Предлагаемое упражнение

Текущий регион

  • Предыдущая страница
  • Следущая страница
  • сообщить об ошибке/отзыв

SuperExcelVBA.