Примеры циклов For… Next

Заполняем десять первых ячеек первого столбца активного листа Excel цифрами от 1 до 10:

Sub test1()
Dim i As Long
  For i = 1 To 10
    Cells(i, 1) = i
  Next
End Sub

В предыдущий цикл добавлен оператор Step со значением 3, а результаты записываем во второй столбец:

Sub test2()
Dim i As Long
  For i = 1 To 10 Step 3
    Cells(i, 2) = i
  Next
End Sub

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

Sub test3()
Dim i As Long
  For i = 0 To -9 Step -1
    Cells(i + 10, 3) = i + 10
  Next
End Sub

Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа Excel:

Sub test4()
Dim i As Long
  For i = 0 To -9 Step -3
    Cells(i + 10, 4) = i + 10
  Next
End Sub

Вложенный цикл:

Sub test5()
Dim i1 As Long, i2 As Long
  For i1 = 1 To 10
'Пятой ячейке в строке i1 присваиваем 0
    Cells(i1, 5) = 0
      For i2 = 1 To 4
        Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2)
      Next
  Next
End Sub

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

Выход из цикла
В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For… Next:

Sub test6()
Dim i As Long
  For i = 1 To 10
    Cells(i, 6) = Choose(i, "Медведь", "Слон", "Жираф", "Антилопа", _
    "Крокодил", "Зебра", "Тигр", "Ящерица", "Лев", "Бегемот")
  Next
End Sub

Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».

Sub test7()
Dim i As Long
  For i = 1 To 10
    If Cells(i, 6) = "Крокодил" Then
      Cells(i, 7) = "Он съел галоши"
      Exit For
        Else
      Cells(i, 7) = "Здесь был цикл"
    End If
  Next
End Sub

Результат работы циклов представлен на изображении ниже.

Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For… Next.

Цикл For Each… Next

Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Предусмотрен принудительный выход из цикла с помощью оператора Exit For.

Синтаксис

For Each element In group
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ element ]

#VBA #ExcelVBA #ForEach #ForEachNext #ЦиклForEach #Цикл

Если цикл For Each… Next используется для прохождения элементов коллекции (объект Collection) или массива, тогда переменная element должна быть объявлена с типом данных Variant, иначе цикл работать не будет.

Примеры циклов For Each… Next

Цикл для диапазона ячеек
На активном листе рабочей книги Excel выделите диапазон ячеек и запустите на выполнение следующую процедуру:

Sub test1()
Dim element As Range, a As String
  a = "Данные, полученные с помощью цикла For Each... Next:"
    For Each element In Selection
      a = a & vbNewLine & "Ячейка " & element.Address & _
      " содержит значение: " & CStr(element.Value)
    Next
  MsgBox a
End Sub

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

Цикл для коллекции листов
Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:

Sub test2()
Dim element As Worksheet, a As String
  a = "Список листов, содержащихся в этой книге:"
    For Each element In Worksheets
      a = a & vbNewLine & element.Index _
      & ") " & element.Name
    Next
  MsgBox a
End Sub

Информационное окно MsgBox выведет список наименований всех листов рабочей книги Excel по порядковому номеру их ярлычков, соответствующих их индексам.

Цикл для массива
Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.

Sub test3()
Dim element As Variant, a As String, group As Variant
group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь")
'или можно присвоить массиву значения диапазона ячеек
'рабочего листа, например, выбранного: group = Selection
a = "Массив содержит следующие значения:" & vbNewLine
  For Each element In group
    a = a & vbNewLine & element
  Next
MsgBox a
End Sub

Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.

Sub test4()
Dim element As Variant, a As String, group As Variant
group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь")
'или можно присвоить массиву значения диапазона ячеек
'рабочего листа, например, выделенного: group = Selection
a = "Массив содержит следующие значения:" & vbNewLine
  For Each element In group
    element = "Попугай"
    a = a & vbNewLine & element
  Next
MsgBox a
End Sub

Цикл для коллекции подкаталогов и выход из цикла
В этом примере мы будем добавлять в переменную a названия подкаталогов на диске C вашего компьютера. Когда цикл дойдет до папки Program Files, он добавит в переменную a ее название и сообщение: «Хватит, дальше читать не буду! С уважением, Ваш цикл For Each… Next.».

Sub test5()
Dim FSO As Object, myFolders As Object, myFolder As Object, a As String
'Создаем новый FileSystemObject и присваиваем его переменной "FSO"
Set FSO = CreateObject("Scripting.FileSystemObject")
'Извлекаем список подкаталогов на диске "C" и присваиваем
'его переменной "myFolders"
Set myFolders = FSO.GetFolder("C:\")
a = "Папки на диске C:" & vbNewLine
'Проходим циклом по списку подкаталогов и добавляем в переменную "a"
'их имена, дойдя до папки "Program Files", выходим из цикла
  For Each myFolder In myFolders.SubFolders
    a = a & vbNewLine & myFolder.Name
    If myFolder.Name = "Program Files" Then
      a = a & vbNewLine & vbNewLine & "Хватит, дальше читать не буду!" _
      & vbNewLine & vbNewLine & "С уважением," & vbNewLine & _
      "Ваш цикл For Each... Next."
  Exit For
    End If
  Next
Set FSO = Nothing
MsgBox a
End Sub

Информационное окно MsgBox выведет список наименований подкаталогов на диске C вашего компьютера до папки Program Files включительно и сообщение цикла о прекращении своей работы.

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

Цикл Do While… Loop

Цикл Do While… Loop в VBA Excel предназначен для повторения блока операторов до тех пор, пока выполняется заданное условие (возвращается значение True). Этот цикл позволяет проверять условие как до, так и после выполнения операторов. Предусмотрен принудительный выход из цикла с помощью оператора Exit Do

Синтаксис
Синтаксис цикла Do While… Loop существует в двух вариантах, определяющих, когда проверяется условие.

Условие проверяется до выполнения операторов:

Do While condition
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop

Условие проверяется после выполнения операторов:

Do
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop While condition

В квадратных скобках указаны необязательные атрибуты цикла Do While… Loop.

#VBA #ExcelVBA #DoWhile #DoWhileLoop #ЦиклDoWhile #Цикл

Exit Do - очень полезный оператор для цикла Do While… Loop, так как при некоторых обстоятельствах этот цикл может стать бесконечным. Если такой риск существует, следует предусмотреть возможность выхода из бесконечного цикла с помощью оператора Exit Do.

Примеры циклов Do While… Loop

Цикл Do While… Loop с условием до исполняемых операторов:

Sub test1()
Dim a As Byte
  Do While a < 10
    a = a + 1
  Loop
MsgBox a
End Sub

Цикл Do While… Loop с условием после исполняемых операторов:

Sub test2()
Dim a As Byte
  Do
    a = a + 1
  Loop While a < 10
MsgBox a
End Sub

Бесконечный цикл и Exit Do:

Sub test3()
Dim a As Byte, n As Long
  Do While a < 10
  a = a + 1
  n = n + 1
    If a = 9 Then
      a = 0
    End If
    If n = 1000 Then
      Exit Do
    End If
  Loop
MsgBox n
End Sub

Когда число итераций цикла дойдет до 1000, он будет завершен, и информационное окно MsgBox выведет на экран число повторений цикла Do While… Loop из этого примера.

Цикл Do Until… Loop

Цикл Do Until… Loop в VBA Excel предназначен для повторения блока операторов пока не выполняется заданное условие (возвращается значение False). Этот цикл позволяет проверять условие как до, так и после выполнения операторов. Предусмотрен принудительный выход из цикла с помощью оператора Exit Do.

Синтаксис
Синтаксис цикла Do Until… Loop существует в двух вариантах, определяющих, когда проверяется условие.

Условие проверяется до выполнения операторов:

Do Until condition
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop

Условие проверяется после выполнения операторов:

Do
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop Until condition

В квадратных скобках указаны необязательные атрибуты цикла Do Until… Loop.

#VBA #ExcelVBA #DoUntil #DoUntilLoop #ЦиклDoUntil #Цикл

Exit Do - очень полезный оператор для цикла Do Until… Loop, так как при некоторых обстоятельствах этот цикл может стать бесконечным. Если такой риск существует, следует предусмотреть возможность выхода из бесконечного цикла с помощью оператора Exit Do.

Примеры циклов Do Until… Loop

Цикл Do Until… Loop с условием до исполняемых операторов:

Sub test1()
Dim a As Byte
  Do Until a > 9
    a = a + 1
  Loop
MsgBox a
End Sub

Цикл Do Until… Loop с условием после исполняемых операторов:

Sub test2()
Dim a As Byte
  Do
    a = a + 1
  Loop Until a > 9
MsgBox a
End Sub

Бесконечный цикл и Exit Do:

Sub test5()
Dim a As Byte, n As Long
  Do Until a > 10
  a = a + 1
  n = n + 1
    If a = 10 Then
      a = 0
    End If
    If n = 1000 Then
      Exit Do
    End If
  Loop
MsgBox n
End Sub

Когда число итераций цикла дойдет до 1000, он будет завершен, и информационное окно MsgBox выведет на экран число повторений цикла Do Until… Loop из этого примера.

Цикл While… Wend

Цикл While… Wend в VBA Excel предназначен для выполнения блока операторов до тех пор, пока выполняется заданное условие (возвращается значение True). Этот цикл позволяет проверять условие только до выполнения операторов. Принудительный выход из цикла с помощью оператора Exit Do не предусмотрен.

Синтаксис цикла While… Wend

While condition
    [ statements ]
Wend

#VBA #ExcelVBA #While #WhileWend #ЦиклWhile #Цикл

*Если не использовать в цикле свой код VBA, смысл применения цикла теряется.

Пример цикла While… Wend

Sub test1()
Dim a As Byte
  While a < 100
    a = a + 7
  Wend
MsgBox a
End Sub

Информационное сообщение MsgBox выведет число 105.

✨ Функции для работы с буфером обмена

🛠 Функция записи текста в буфер обмена:

Function SetClipBoardText(ByVal Text As Variant) As Boolean
    SetClipBoardText = CreateObject("htmlfile").parentWindow.clipboardData.SetData("Text", Text)
End Function

🛠 Функция вставки текста из буфера обмена:

Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("htmlfile").parentWindow.clipboardData.GetData("Text")
End Function

🛠 Функция очистки буфера обмена:

Function ClearClipBoardText() As Boolean
    ClearClipBoardText = CreateObject("htmlfile").parentWindow.clipboardData.clearData("Text")
End Function

✅ Пример использования функций для работы с буфером обмена:

Sub Primer()
Dim s As String
    s = "Копирование текста из переменной в буфер обмена"
    'Копируем текст в буфер обмена
    SetClipBoardText (s)
    'Вставляем текс из буфера обмена в ячейку "A1"
    Range("A1") = GetClipBoardText
    'Очищаем буфер обмена, если это необходимо
    ClearClipBoardText
End Sub

#VBA #ExcelVBA #БуферОбмена #ClipBoard

Ячейка в VBA Excel

Ячейка в VBA Excel - это частный случай объекта Range (диапазона), состоящего из одной ячейки. Cells - это свойство объекта Range, возвращающее объект Range, состоящий из одной ячейки.

#VBA #ExcelVBA #Range #Cells #Ячейка #Ячейки

Обращение к ячейке
Все известные мне способы обращения к ячейке в VBA Excel.

Обращение к ячейке по адресу

Обращение к ячейке "C5" на активном листе:

Range("C5")
Cells(5, 3)
Cells(5, "C")
[C5]

Обращение к активной (выбранной) ячейке на активном листе:

ActiveCell

Обращение к ячейке "C5" на неактивном листе книги с кодом:

'по основному имени листа
Лист2.Range("C5")
Лист2.Cells(5, 3)
Лист2.Cells(5, "C")
Лист2.[C5]
'по имени ярлыка
Sheets("Имя ярлыка").Range("C5")
Sheets("Имя ярлыка").Cells(5, 3)
Sheets("Имя ярлыка").Cells(5, "C")
Sheets("Имя ярлыка").[C5]

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.

Обращение к ячейке "C5" на листе "Лист2" другой открытой книги из книги с кодом:

Workbooks("Книга2.xlsm").Sheets("Лист2").Range("C5")
Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, 3)
Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, "C")
Workbooks("Книга2.xlsm").Sheets("Лист2").[C5]

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

#VBA #ExcelVBA #ОбращениеКЯчейке #Range #Cells

Обращение к ячейке по индексу

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

Для примера, Cells(4) - это та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel количество строк и столбцов на рабочем листе может быть разным.

По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range("A1:C3"):

Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2").