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

Макрос для объединения пустых ячеек в строках таблицы Excel

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

Как объединить пустые ячейки в строках с помощью макроса

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

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

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

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

Откроем редактор Visual Basic (ALT+F11):

И вставим новый модуль с помощью инструмента в редакторе: «Insert»-«Module». После запишем в модуль код VBA-макроса:

Sub JoinEmpty()
Dim i As Long
For i = 1 To Selection.Columns.Count
  If Selection.Cells(1, i) = "" Then
  ActiveSheet.Range(Selection.Cells(1, i - 1), Selection.Cells(1, i)).Merge
  End If
Next
Selection. HorizontalAlignment = xlVAlignCenter
End Sub


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

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

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

Selection.HorizontalAlignment = xlHAlignCenter

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



Как объединить все пустые ячейки во всех строках таблицы Excel

Что если в нашей таблице потребуется выполнить аналогичное объединение пустых ячеек сразу в нескольких строках – одновременно? Для этого нужно добавить несколько строк в код нашего макроса. Но сначала подготовим исходную таблицу.

Допустим нам для последних трех отделов необходимо разбить данные на 2 полугодия. Исходный вид таблицы будет выглядеть следующим образом:

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

Dim j As Long

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

For j = 1 To Selection.Rows.Count

Так же не забудем добавить конец нового цикла:

Next

Во всех экземплярах объекта Cells, для второго аргумента вместо числа 1 введем переменную j:

Selection. Cells(i,j)

Полная версия модифицированного кода макроса:

Sub JoinEmpty()
Dim i As Long
Dim j As Long
For j = 1 To Selection.Rows.Count
  For i = 1 To Selection.Columns.Count
    If Selection.Cells(j, i) = "" Then
    ActiveSheet.Range(Selection.Cells(j, i - 1), Selection.Cells(j, i)).Merge
    End If
  Next
Next
Selection.HorizontalAlignment = xlVAlignCenter
End Sub


Теперь, нам осталось только лишь выделить диапазон B1:L12 и запустить новую версию макроса:

Читайте также: макрос для объединения пустых ячеек в столбцах таблицы Excel

Внимание! Если первая ячейка (в верхнем левом углу) выделенного диапазона – пуста, то она будет объедена со смежной ячейкой слева от выделенного диапазона.

Поиск на листе Excel, примеры использования Find на VBA

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

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Sheets("Данные"). Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:

1-я строка: Выбираем в книге лист «Данные»;

2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;

3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

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

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application. FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c. Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = .Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т. к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

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

Свойство FindFormat можно задавать разными способами, например, так:

With Application. FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Поиск последней заполненной ячейки с помощью Find

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

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

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

Поиск по шаблону (маске)

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

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

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:

* — для обозначения любого количества любых символов;

? — для обозначения одного любого символа;

~ — для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т. е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

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

• Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells.Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

VBA Для каждого примера (краткий справочник)

В этой статье

• Для каждого цикла
• Для каждого: основные примеры
  • Сквозные ячейки
  • Сквозные листы Рабочие тетради
  • Цикл по фигурам
  • Цикл Сквозные диаграммы
  • Цикл по сводным таблицам
  • Цикл по таблицам
  • Цикл по элементам массива
  • Цикл по числам
• Для каждого построителя циклов
• Для каждой – Если
  • Для каждой ячейки в диапазоне – Если
• Для каждой Общие примеры
  • Закрыть все книги
  • Скрыть все листы
  • Показать все листы
  • Защитить все листы
  • Снять защиту со всех листов
  • Удалить все фигуры на всех листах
  • Обновить все сводные таблицы
• Использование для каждого в Access VBA

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

Цикл для каждого

Цикл для каждого цикла позволяет перебрать каждый объект в коллекции:

• Все ячейки в диапазоне
• Все рабочие листы в рабочей книге
• Все открытые книги
• Все фигуры на листе
• Все элементы массива
• и больше!

For Each: основные примеры

В этих примерах показано, как настроить циклы For Each для циклического перебора различных типов объектов.

Цикл по ячейкам

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

 Подпрограмма ForEachCell()
    Затемнить ячейку как диапазон
    
    Для каждой ячейки в листах («Лист1»). Диапазон («A1: A10»)
        Cell.Offset(0, 1).value = Cell.value
    Следующая ячейка
    
End Sub 

Циклический просмотр листов

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

 Подпрограмма ForEachSheets()
    Dim ws As рабочий лист

    Для каждого ws в листах
        ws.Visible = Истина
    Следующий мс

End Sub 

Цикл по рабочим книгам

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

 Подпрограмма ForEachWorkbooks()
    Dim wb как рабочая книга
    
    Для каждого ББ в рабочих книгах
        wb.Закрыть
    Следующий бб
    
End Sub 

Цикл по фигурам

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

 Подпрограмма ForEachShape()
    Дим Шп Ас Шейп
    
    Для каждого листа в листах ("Лист1"). Формы
        Шп.удалить
    Следующая Шп
    
End Sub 

Кодирование VBA стало проще

Остановить поиск кода VBA в сети. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области программирования и множеством функций, позволяющих сэкономить время для всех пользователей!

Узнать больше

Сквозные диаграммы

Эта процедура будет перебирать каждую диаграмму на листе 1, удаляя каждую.

 Подпрограмма ForEachCharts()
    Dim cht As ChartObject
    
    Для каждого чт в листах("Лист1").ChartObjects
        чт.Удалить
    Следующий чт
    
End Sub 

Цикл по сводным таблицам

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

 Sub ForEachPivotTables()
    Dim pvt как сводная таблица
    
    Для каждого pvt в листах («Лист1»). Сводные таблицы
        pvt.ClearTable
    Следующий пвт
    
Конец суб 

Циклический просмотр таблиц

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

 Подпрограмма ForEachTables()
    Dim таблица как ListObject
    
    Для каждой таблицы в листах ("Лист1").ListObjects
        табл.Удалить
    Следующая таблица
    
End Sub 

Программирование VBA | Генератор кода работает на вас!

Циклический просмотр элементов массива

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

 Подпрограмма ForEachItemInArray()
    Dim arrValue как вариант
    Затемнить элемент как вариант
    arrValue = Array("Элемент 1", "Элемент 2", "Элемент 3")
    
    Для каждого элемента в arrValue
        Элемент MsgBox
    Следующий элемент
    
End Sub 

Цикл по номерам

Эта процедура будет перебирать все числа в массиве, отображать каждое значение в msgbox,

 Sub ForEachNumberInNumbers()
    Dim arrNumber (от 1 до 3) как целое число
    Dim num как вариант
    
    номер(1) = 10
    число(2) = 20
    номер(3) = 30
    
    Для каждого числа In arrNumber
        номер почтового ящика
    Следующий номер
    
Конец суб 

Для каждого построителя циклов

Примеры в этой статье были созданы с помощью построителя циклов в нашей надстройке VBA: AutoMacro .

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

Для каждого — If

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

Для каждой ячейки в диапазоне — If

 Sub If_Loop()
Затемнить ячейку как диапазон
 
  Для каждой ячейки в диапазоне («A2: A6»)
    Если Ячейка.Значение > 0 Тогда
      Cell.Offset(0, 1).Value = "Положительное"
    ИначеЕсли Cell.Value < 0 Тогда
      Cell.Offset(0, 1).Value = "Отрицательное"
    Еще
      Ячейка.Смещение(0, 1).Значение = "Ноль"
    Конец, если
  Следующая ячейка
 
End Sub 

Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Для каждого из общих примеров

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

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

 Sub CloseAllWorkbooks()
    
    Dim wb как рабочая книга
    
    Для каждого ББ в рабочих книгах
        wb.Close SaveChanges: = True
    Следующий бб
    
End Sub 

Скрыть все листы

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

 Подпрограмма HideAllSheets()
    Dim ws As рабочий лист

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

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

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

 Sub UnhideAllSheets()
    Dim ws As рабочий лист

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

Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Защитить все листы

Эта процедура защитит все рабочие листы.

 Sub ProtectAllSheets()
    Dim ws As рабочий лист

    Для каждого ws в листах
        ws.Protect Password:=". .."
    Следующий мс
    
Конец суб 

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

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

 Sub UnprotectAllSheets()
    Dim ws As рабочий лист

    Для каждого ws в листах
        ws.Unprotect Password:="..."
    Следующий мс
    
End Sub 

Удалить все фигуры на всех листах

Эта процедура удалит все фигуры в книге.

 Sub DeleteAllShapesOnAllWorksheets()
    Dim Sheet As Worksheet
    Дим Шп Ас Шейп
    

    Для каждого листа в листах
        Для каждой шп в Sheet.Shapes
            Шп.удалить
        Следующая Шп
    Следующий лист
    
Конец суб 

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

Эта процедура обновит все сводные таблицы на листе.

 Sub RefreshAllPivotTables()
    Dim pvt как сводная таблица
    
    Для каждого pvt в листах («Лист1»). Сводные таблицы
        pvt.RefreshTable
    Следующий пвт
    
End Sub 

Использование For Each в Access VBA

Цикл For Each работает в Access VBA так же, как и в Excel VBA. В следующем примере будут удалены все таблицы в текущей базе данных.

 Sub RemoveAllTables()
  Dim tdf как TableDef
  Dim dbs как база данных
  Установите dbs = CurrentDb
  Для каждого tdf в dbs.TableDefs
      DoCmd.DeleteObject tdf.Name
  Петля
  Установите dbs = Ничего
End Sub 

VBA С | Как использовать оператор With в Excel VBA?

В этой статье мы увидим схему Excel VBA С. With — это оператор в языках программирования. Это один из наиболее часто используемых операторов, который используется с оператором END. Объект, на который делается ссылка, упоминается после ключевого слова with, и весь процесс завершается оператором End With. Оператор VBA With не может быть без конца оператором, поэтому обязательно закрывать серию операторов. Ключевая особенность оператора with заключается в том, что мы можем получить доступ к свойствам объекта, не обращаясь к объекту несколько раз. Метод доступа к свойствам объекта заключается в ключевом слове точка. Когда мы ставим ключевое слово с точкой, мы видим несколько вариантов доступа к свойствам объекта. Давайте узнаем, как использовать оператор With в некоторых примерах ниже.

Как использовать с оператором в Excel VBA?

Мы узнаем, как использовать оператор With в Excel, используя код VBA.

Вы можете скачать этот шаблон VBA с Excel здесь — VBA с шаблоном Excel

VBA с — пример № 1

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

Шаг 1: Вставьте новый модуль в редактор Visual Basic (VBE). Нажмите на вкладку Insert > выберите Module .

Шаг 2:  Определите новую подпроцедуру во вставленном модуле, которая может содержать ваш макрос.

Код:

 Подпример1()

End Sub 

Шаг 3:  Поскольку мы начинаем с самого простого оператора with.

Код:

 Подпример1()

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

Конец суб 

Шаг 4: Когда мы набираем точку (.), мы видим ряд различных функций, связанных с объектом.

Код:

 Подпример1()

С диапазоном ("A1")
.Значение = 15

End Sub 

Шаг 5:  Всегда не забывайте закрывать оператор with оператором End.

Код:

 Подпример1()

С диапазоном ("A1")
.Значение = 15
Конец с

Конец суб 

Шаг 6: Теперь запустите этот код, нажав кнопку «Выполнить» или клавишу F5.

VBA With – Пример №2

В этом примере мы увидим, как изменить один из шрифтов или цвет свойства объекта. У меня есть некоторые данные на листе 2 в ячейках от A1 до C3 обычного черного цвета, и я хочу изменить шрифт текста на синий, используя With Statement. Во-первых, давайте посмотрим текущие данные на листе 2 следующим образом. Для этого выполните следующие действия:

Шаг 1:  Теперь в том же модуле объявите еще одну подпроцедуру следующим образом.

Код:

 Подпример2()

End Sub 

Шаг 2: Кроме того, теперь у нас есть более одного листа в этой книге, поэтому давайте активируем лист 2 с помощью следующего кода.

Код:

 Подпример2()

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

End Sub 

Шаг 3: Теперь объявите переменную с типом данных Range, в которой будет храниться диапазон для наших данных, как показано ниже, и установите диапазон для диапазона, в котором есть наши данные на листе 2.

Код:

 Sub Example2()

Рабочие листы("Лист2").Активировать
Dim Rng As Range
Установить Rng = диапазон («A1: C3»)

End Sub 

Шаг 4:  Получите доступ к свойству Font переменной Rng с помощью инструкции with со следующим кодом, показанным ниже.

Код:

 Подпример2()

Рабочие листы("Лист2").Активировать
Dim Rng As Range
Установить Rng = диапазон («A1: C3»)
С Rng.Font

End Sub 

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

Код:

 Подпример2()

Рабочие листы("Лист2").Активировать
Dim Rng As Range
Установить Rng = диапазон («A1: C3»)
С Rng.Font
.Цвет = vbBlue

Конец суб 

Шаг 6:  Теперь давайте завершим оператор with оператором END WITH следующим образом.

Код:

 Подпример2()

Рабочие листы("Лист2").Активировать
Dim Rng As Range
Установить Rng = диапазон («A1: C3»)
С Rng.Font
.Цвет = vbBlue
Конец с

End Sub 

Шаг 7:  Когда мы выполняем код, мы получаем следующий результат на листе 2.

VBA With — пример № 3

В этом очень простом примере мы увидим еще одну функцию шрифта, которой можно управлять с помощью оператора With. В этом примере у нас есть аналогичный текст в ячейках от B2 до D5, и они набраны обычным шрифтом. Мы хотим изменить шрифт на жирный и курсивный, используя оператор With. Во-первых, давайте посмотрим на данные на листе 3 следующим образом. Для этого выполните следующие действия:

Шаг 1: Теперь в том же модуле объявите еще одну подпроцедуру следующим образом.

Код:

 Подпример3()

End Sub 

Шаг 2: Кроме того, теперь у нас есть более одного листа в этой книге, поэтому давайте активируем лист 3 с помощью следующего кода.

Код:

 Подпример3()

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

End Sub 

Шаг 3:  Теперь объявите переменную как тип данных Range, в которой будет храниться диапазон наших данных, как показано ниже, и установите диапазон для диапазона, который содержит наши данные на листе 3.

Код:

 Подпример3()

Рабочие листы("Лист3"). Активировать
Dim Rng As Range
Установить Rng1 = Диапазон ("B2: D5")

End Sub 

Шаг 4:  Получите доступ к свойству Font переменной Rng с помощью оператора with.

Код:

 Подпример3()

Рабочие листы("Лист3").Активировать
Dim Rng As Range
Установить Rng1 = Диапазон ("B2: D5")
С Rng1.Font

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

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

Код:

 Подпример3()

Рабочие листы("Лист3").Активировать
Dim Rng As Range
Установить Rng1 = Диапазон ("B2: D5")
С Rng1.Font
.Жирный = Истина
.Курсив = Истина

End Sub 

Шаг 6:  Давайте закончим операторы, используя оператор End, как показано ниже.

Код:

 Подпример3()

Рабочие листы("Лист3").Активировать
Dim Rng As Range
Установить Rng1 = Диапазон ("B2: D5")
С Rng1. Font
.Жирный = Истина
.Курсив = Истина
Конец с

End Sub 

Шаг 7: Когда мы выполним приведенный выше код, мы увидим результат на листе 3 следующим образом.

Как и в приведенных выше примерах, мы можем видеть, насколько важен оператор with в нашем повседневном программировании. Кроме того, он удаляет повторяющиеся шаги, когда мы пытаемся получить доступ к свойствам объекта. Мы также можем использовать операторы Loop в операторах With, чтобы определить критерии, к которым мы хотим получить доступ для объекта.

Как использовать VBA с (реальный пример)?

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

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

Код:

 Подпример4()

Dim ws As рабочий лист
Dim FoundCell как диапазон
Dim Name как String, FindThis как String, Age как Integer, k как Integer
Установите ws = Рабочие листы («Лист4»)
Имя = InputBox("Введите имя")
Возраст = InputBox("Введите возраст")
НайтиЭто = Имя
Установите FoundCell = ws.Range("A:A").Find(What:=FindThis)
k = FoundCell.Row
С ВС
Ячейки (k, 2). Значение = Возраст
Конец с

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

Когда мы выполняем код, мы получаем следующие два всплывающих окна:

Когда мы нажимаем OK для возраста, мы видим результат, отображаемый на листе 4 следующим образом.

Теперь давайте разберем код шаг за шагом:

1. Когда мы просим пользователя ввести имя, код находит его в столбце A.
2. Когда значение, предоставленное пользователем, найдено, строка для значения сохраняется в переменной.