Что такое код VBA и как его использовать? Примеры кода VBA

Автор: Дмитрий Соловьев 28 Nov, 24 ·

Что такое код VBA и как его использовать? Примеры кода VBA

Многие пользователи Windows знакомы с пакетом Microsoft Office и его многочисленными мощными приложениями, используемыми в повседневных задачах. Однако не все они осведомлены о возможностях Excel. Несмотря на это, Excel имеет функции, такие как макросы и коды VBA, которые могут значительно упростить ваши повседневные задачи.

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

В этой статье

Что такое код VBA?
Как использовать коды макросов в Excel
20 Базовых примеров кода Excel VBA для начинающих
🚀 [Бонус] Как обеспечить безопасность данных при использовании кодов VBA
Заключение

Что такое код VBA?

Прежде чем мы приступим к рассмотрению примеров кода VBA, давайте более подробно рассмотрим, что такое VBA и зачем он нужен. VBA, или Visual Basic for Applications, это язык программирования, который используется в приложениях Microsoft Office, таких, как Excel, для создания, сохранения и запуска макросов, которые также известны как автоматизированные сценарии.

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

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

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

Запустите Microsoft Excel и перейдите к Файлу > Параметрам > Настройка ленты.
В разделе Основные вкладки, установите флажок рядом с Разработчик, чтобы включить функцию, которая скрыта по умолчанию.
Сохраните настройки, нажав ОК, а затем перейдите на вкладку Разработчик.
Выберите опцию Записать макрос.
Назовите свой макрос и кратко опишите, что он делает, чтобы вы могли узнать его позже. Вы также можете добавить ярлыки для вызова макроса.
После ввода сведений о макросе, нажмите ОК и выполните действия, которые вы хотите записать в макрос. Приложение Excel запишет и сохранит эти действия внутри созданного макроса.
Нажмите на Остановить запись, как только вы завершите свои действия, и ваш макрос будет выполнен.
Когда вам потребуется повторить эти действия, вы можете нажать определенную комбинацию клавиш для вызова макроса, которую вы установили. Как альтернативу, вы можете перейти в раздел Разработчик > Макросы > Просмотр макросов и нажмите Выполнить на нужном макросе.

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

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

Как упоминалось выше, VBA расшифровывается как Visual Basic Applications, язык, который понимает Excel. Любые макросы, которые вы создадите с помощью вышеупомянутых действий, запишут ваши действия с электронными таблицами, преобразуют их в читаемый в Excel код VBA и сохранят в том виде, который Excel сможет распознать и понять.

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

Вставка нескольких столбцов
Вставка нескольких строк
Автоматическая подгонка столбцов
Автоматическая подгонка строк
Разъединение ячеек
Выделение значений Больше чем
Выделение значений Меньше чем
Выделение отрицательных чисел
Выделение ячеек с ошибками в написании
Показ рабочих листов
Удаление всего, кроме активных рабочих листов
Удаление пустых листов
Сохранение рабочих листов в формате PDF
Закрытие рабочей книги
Обновление сводных таблиц
Добавление диаграммы в виде изображений
Вставка изображения
Удаление символов из строк
Обратный текст
Замена пустых ячеек нулями

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

Вставка нескольких столбцов

Sub InsertMultipleColumns()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireColumn.Select
On Error GoTo Last

i = InputBox("Введите количество столбцов для вставки", "Вставьте столбцы"

For j = 1 To i
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightorAbove
Next j
Last: Exit Sub
End Sub

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

Sub InsertMultipleRows()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireRow.Select
On Error GoTo Last
i = InputBox("Введите количество строк для вставки", "Вставьте строки")
For j = 1 To i
Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
Next j
Last: Exit Sub
End Sub

Автоматическая подгонка столбцов

Sub AutoFitColumns()
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Автоматическая подгонка строк

Sub AutoFitRows()
Cells.Select
Cells.EntireRow.AutoFit
End Sub

Разъединение ячеек

Sub UnmergeCells()
Selection.UnMerge
End Sub

Выделение значений Больше чем

Sub HighlightGreaterThanValues()
Dim i As Integer
i = InputBox("Enter Greater Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(31, 218, 154)
End With
End Sub

Выделение значений Меньше чем

Sub HighlightLowerThanValues()
Dim i As Integer
i = InputBox("Enter Lower Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLower, _
Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(217, 83, 79)
End With
End Sub

Выделение отрицательных чисел

Sub highlightNegativeNumbers()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsNumber(Rng) Then
If Rng.Value < 0 Then
Rng.Font.Color= -16776961
End If
End If
Next
End Sub

Выделение ячеек с ошибками в написании

Sub HighlightMisspelledCells()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=rng.Text) Then
rng.Style = "Bad"
End If
Next rng
End Sub

Показ рабочих листов

Sub UnhideAllWorksheet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Удаление всего, кроме активных рабочих листов

Sub DeleteWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.name <> ThisWorkbook.ActiveSheet.name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub

Удаление пустых листов

Sub deleteBlankWorksheets()
Dim Ws As Worksheet
On Error Resume Next
Application.ScreenUpdating= False
Application.DisplayAlerts= False
For Each Ws In Application.Worksheets
If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0 Then
Ws.Delete
End If
Next
Application.ScreenUpdating= True
Application.DisplayAlerts= True
End Sub

Сохранение рабочих листов в формате PDF

Sub SaveWorkshetAsPDF()
Dimws As Worksheet
For Each ws In Worksheets
ws.ExportAsFixedFormat _
xlTypePDF, _
"ENTER-FOLDER-NAME-HERE" &; _
ws.Name & ".pdf"
Next ws
End Sub

Закрытие рабочих книг

Sub CloseAllWorkbooks()
Dim wbs As Workbook
For Each wbs In Workbooks
wbs.Close SaveChanges:=True
Next wb
End Sub

Обновление сводных таблиц

Sub vba_referesh_all_pivots()
Dim pt As PivotTable
For Each pt In ActiveWorkbook.PivotTables
pt.RefreshTable
Next pt
End Sub

Добавление диаграммы в виде изображений

Sub ConvertChartToPicture()
ActiveChart.ChartArea.Copy
ActiveSheet.Range("A1").Select
ActiveSheet.Pictures.Paste.Select
End Sub

Вставка изображения

Sub Image nameedPicture()
Selection.Copy
ActiveSheet.Pictures.Paste
End Sub

Удаление символов из строк

Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
End Function

Обратный текст

Public Function rvrse(ByVal cell As Range) As String
rvrse = VBA.strReverse(cell.Value)
End Function

Замена пустых ячеек нулями

Sub replaceBlankWithZero()
Dim rng As Range
Selection.Value = Selection.Value
For Each rng In Selection
If rng = ““ Or rng = “ “ Then
rng.Value = "0"
Else
End If
Next rng
End Sub

🚀[Бонус] Как обеспечить безопасность данных при использовании кодов VBA

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

Однако не стоит паниковать, даже если вы уже применяли макросы для удаления данных с помощью VBA, поскольку существуют решения, которые могут восстановить ваши файлы Excel. Программы для восстановления данных могут справиться с такими ситуациями, и одним из примеров такого инструмента является Wondershare Recoverit.

Скачать бесплатно для Windows Vista/7/8/10/11

Скачать бесплатно для macOS X 10.10 или более поздней версии

Без разницы, имеете ли вы дело с потерянными таблицами Excel, недавно не сохраненными изменениями, удаленными данными или случайным удалением информации через коды VBA, данное приложение поможет. Его эффективный и продвинутый алгоритм поиска и восстановления позволяет быстро восстанавливать потерянные файлы в любой ситуации потери данных, поддерживая более 500 форматов файлов. Пользоваться им очень просто, и ниже прилагаются инструкции:

Откройте Wondershare Recoverit и выберите опцию Жесткие диски и местоположения.
Выберите дисковод, на котором вы храните файлы XLS – приложение немедленно просканирует его на наличие потерянных данных.
Добавьте фильтры файлов и ключевые слова для поиска конкретных файлов Excel.
Wondershare Recovery позволит вам просмотреть файлы Excel, когда они будут найдены. Если предварительный просмотр вас удовлетворил, вы можете приостановить или остановить сканирование и нажать Восстановить, чтобы сохранить обнаруженные данные на вашем компьютере.

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

Вы можете использовать это приложение на различных устройствах хранения данных, включая внутренние диски, внешние накопители, NAS, USB-накопители и многие другие, так как Wondershare Recoverit поддерживает более 2000 устройств хранения. Оно идеально подходит для восстановления данных в случае утери, вызванной вредоносными программами, сбоями системы, прерванными передачами файлов, поврежденными дисками и другими ситуациями, что делает его незаменимым инструментом для обеспечения безопасности данных.

Заключение

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

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

Скачать Бесплатно для Windows Vista/7/8/10/11

Скачать Бесплатно для macOS X 10.10 или более поздней версии

Часто задаваемые вопросы

Сложен ли код VBA?

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

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

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

Редактировать существующие макросы так же просто, как создавать новые. Вот как это сделать:

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

2.Выберите макрос, который вы хотите изменить, и нажмите Редактировать.

3.Измените код VBA в редакторе Visual Basic и сохраните свой макрос.

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