VBA Excel. Выход из вложенных циклов

Досрочный выход из вложенных циклов в процедурах VBA Excel, когда стандартных конструкций «Exit For» и «Exit Do» не достаточно.

Содержание

Когда требуется выход из всех циклов?

При разработке макросов и пользовательских функций в VBA Excel часто возникает необходимость прервать выполнение вложенных циклов досрочно — например, при нахождении искомого значения или при возникновении ошибки. Стандартная конструкция Exit For или Exit Do позволяет выйти только из текущего внутреннего цикла, но не из всех уровней вложенности.

Представьте, что вы перебираете двумерный массив или таблицу, и как только находите нужное значение, дальнейший поиск теряет смысл. Если циклы вложены (например, строки и столбцы), то Exit For внутри внутреннего цикла прервёт только его, а внешний продолжит работу. Чтобы остановить все циклы, нужно использовать один из описанных ниже подходов, кроме первого, где применён, в качестве примера, Exit For для выхода только из одного внутреннего цикла.

Использование Exit For для внутреннего цикла

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

Sub ExampleExitFor()
    Dim i As Long, j As Long
    For i = 1 To 10
        For j = 1 To 10
            If i * j = 50 Then
                Debug.Print "Найдено: i=" & i & ", j=" & j
                Exit For   ' выходит только из цикла по j
            End If
        Next j
        ' внешний цикл продолжается, i увеличивается
    Next i
End Sub

Sub ExampleExitFor()

Dim i As Long, j As Long

For i = 1 To 10

For j = 1 To 10

If i * j = 50 Then

Debug.Print "Найдено: i=" & i & ", j=" & j

Exit For ' выходит только из цикла по j

End If

Next j

' внешний цикл продолжается, i увеличивается

Next i

End Sub

Как видите, после Exit For управление передаётся на строку Next i и внешний цикл продолжается. Это не то, что нам нужно, если мы хотим полностью прекратить поиск.

Но в данном примере использование выхода только из внутреннего цикла оправдано — были найдены и вторые значения i и j, произведение которых равно 50:

Найдено: i=5, j=10
Найдено: i=10, j=5

Использование флага (булевой переменной)

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

Sub ExitWithFlag()
    Dim i As Long, j As Long
    Dim found As Boolean
    found = False
    
    For i = 1 To 10
        For j = 1 To 10
            If i * j = 50 Then
                Debug.Print "Найдено: i=" & i & ", j=" & j
                found = True
                Exit For   ' выходим из внутреннего
            End If
        Next j
        
        If found Then Exit For   ' выходим из внешнего
    Next i
    
    If found Then
        Debug.Print "Поиск завершён досрочно."
    Else
        Debug.Print "Значение не найдено."
    End If
End Sub

Sub ExitWithFlag()

Dim i As Long, j As Long

Dim found As Boolean

found = False

For i = 1 To 10

For j = 1 To 10

If i * j = 50 Then

Debug.Print "Найдено: i=" & i & ", j=" & j

found = True

Exit For ' выходим из внутреннего

End If

Next j

If found Then Exit For ' выходим из внешнего

Next i

If found Then

Debug.Print "Поиск завершён досрочно."

Else

Debug.Print "Значение не найдено."

End If

End Sub

Результат работы кода:

Найдено: i=5, j=10
Поиск завершён досрочно.

Плюсы: код легко читается, не требует GoTo или сложных конструкций.
Минусы: нужно добавлять проверку после каждого внутреннего цикла.

Использование GoTo для безусловного перехода

Оператор GoTo позволяет передать управление на метку вне всех циклов. Это один из самых прямых способов.

Sub ExitWithGoTo()
    Dim i As Long, j As Long
    
    For i = 1 To 10
        For j = 1 To 10
            If i * j = 50 Then
                Debug.Print "Найдено: i=" & i & ", j=" & j
                GoTo ExitAll
            End If
        Next j
    Next i
    
ExitAll:
    Debug.Print "Циклы прерваны."
End Sub

Sub ExitWithGoTo()

Dim i As Long, j As Long

For i = 1 To 10

For j = 1 To 10

If i * j = 50 Then

Debug.Print "Найдено: i=" & i & ", j=" & j

GoTo ExitAll

End If

Next j

Next i

ExitAll:

Debug.Print "Циклы прерваны."

End Sub

Результат работы кода:

Найдено: i=5, j=10
Циклы прерваны.

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

Выход с помощью Exit Sub или Exit Function

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

Sub ExitWithExitSub()
    Dim i As Long, j As Long
    
    For i = 1 To 10
        For j = 1 To 10
            If i * j = 50 Then
                Debug.Print "Найдено: i=" & i & ", j=" & j
                Exit Sub   ' немедленный выход из процедуры
            End If
        Next j
    Next i
    
    Debug.Print "Это сообщение не будет выведено, если выход произошёл."
End Sub

Sub ExitWithExitSub()

Dim i As Long, j As Long

For i = 1 To 10

For j = 1 To 10

If i * j = 50 Then

Debug.Print "Найдено: i=" & i & ", j=" & j

Exit Sub ' немедленный выход из процедуры

End If

Next j

Next i

Debug.Print "Это сообщение не будет выведено, если выход произошёл."

End Sub

Результат работы кода:

Найдено: i=5, j=10

Как видим, сообщение перед End Sub не выведено — произошёл выход из процедуры.

Плюсы: очень просто и эффективно.
Минусы: метод подходит только если после циклов не нужно выполнять никакой код (или его можно перенести в другое место).

Комбинированный подход с вложенными проверками

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

Sub ExitWithDoWhile()
    Dim i As Long, j As Long
    Dim found As Boolean
    i = 1
    Do While i <= 10 And Not found
        j = 1
        Do While j <= 10 And Not found
            If i * j = 50 Then
                Debug.Print "Найдено: i=" & i & ", j=" & j
                found = True
            End If
            j = j + 1
        Loop
        i = i + 1
    Loop
End Sub

Sub ExitWithDoWhile()

Dim i As Long, j As Long

Dim found As Boolean

i = 1

Do While i <= 10 And Not found

j = 1

Do While j <= 10 And Not found

If i * j = 50 Then

Debug.Print "Найдено: i=" & i & ", j=" & j

found = True

End If

j = j + 1

Loop

i = i + 1

Loop

End Sub

Результат работы кода:

Найдено: i=5, j=10

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

Пример реального использования

Мне впервые понадобился выход из вложенных циклов при сравнении 2 больших таблиц Excel.

Таблица 1 — список населённых пунктов разных стран мира (58967 строк):

Таблица 2 — список населённых пунктов стран СНГ с кодом страны (7027 строк):

«Таблица 1» расположена на листе «Список», обращение к нему — Me, так как код размещён в его модуле. «Таблица 2» расположена на листе «Лист2».

Необходимо произвести следующие действия:

Пройтись циклом 1 уровня (внешним, охватывающим) по первому столбцу «Таблицы 1» (наименования населённых пунктов).
Пройтись циклом 2 уровня (вложенным) по третьему столбцу «Таблицы 2» (Column4).
Пройтись циклом 3 уровня вложенности по элементам списка каждой ячейки, выбранной циклом второго уровня, чтобы найти совпадение наименования из первой таблицы с наименованием в списке из этой ячейки.
Из строки, где найдено совпадение наименований, скопировать наименование, транслитерацию и код страны на лист «Список новый».
Выйти из вложенных циклов (внешний цикл продолжается до полного завершения).

Для этих целей был использован следующий код:

Private Sub СравнениеСписокЛист2()
Debug.Print Format(Now, "hh:mm")
Application.ScreenUpdating = False
Dim arr1(), arr2(), arr3() As String, i1, i2, i3, n
Dim skipToNextI As Boolean

arr1 = Me.Range("A1").CurrentRegion.Value
arr2 = Worksheets("Лист2").Range("A1").CurrentRegion.Value
n = 2

    With Worksheets("Список новый")
        For i1 = 2 To UBound(arr1)
        skipToNextI = False ' Сбрасываем флаг на каждой итерации
            For i2 = 2 To UBound(arr2)
            arr3 = Split(CStr(arr2(i2, 3)), ",")
                For i3 = LBound(arr3) To UBound(arr3)
                    If arr3(i3) = arr1(i1, 1) Then
                        .Cells(n, 1) = arr3(i3)
                        .Cells(n, 2) = arr2(i2, 2)
                        .Cells(n, 3) = arr2(i2, 4)
                        n = n + 1
                        skipToNextI = True ' Включаем флаг
                        Exit For ' Выходим из третьего цикла в текущий (второй)
                    End If
                Next i3
            ' Если флаг поднят, выходим и из второго цикла тоже
            If skipToNextI Then Exit For
            Next i2
        Next i1
    End With

Application.ScreenUpdating = True
Debug.Print Format(Now, "hh:mm")
MsgBox "Обработка завершена"
End Sub

Private Sub СравнениеСписокЛист2()

Debug.Print Format(Now, "hh:mm")

Application.ScreenUpdating = False

Dim arr1(), arr2(), arr3() As String, i1, i2, i3, n

Dim skipToNextI As Boolean

arr1 = Me.Range("A1").CurrentRegion.Value

arr2 = Worksheets("Лист2").Range("A1").CurrentRegion.Value

n = 2

With Worksheets("Список новый")

For i1 = 2 To UBound(arr1)

skipToNextI = False ' Сбрасываем флаг на каждой итерации

For i2 = 2 To UBound(arr2)

arr3 = Split(CStr(arr2(i2, 3)), ",")

For i3 = LBound(arr3) To UBound(arr3)

If arr3(i3) = arr1(i1, 1) Then

.Cells(n, 1) = arr3(i3)

.Cells(n, 2) = arr2(i2, 2)

.Cells(n, 3) = arr2(i2, 4)

n = n + 1

skipToNextI = True ' Включаем флаг

Exit For ' Выходим из третьего цикла в текущий (второй)

End If

Next i3

' Если флаг поднят, выходим и из второго цикла тоже

If skipToNextI Then Exit For

Next i2

Next i1

End With

Application.ScreenUpdating = True

Debug.Print Format(Now, "hh:mm")

MsgBox "Обработка завершена"

End Sub

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

Резюме

Выход из вложенных циклов в VBA Excel — частая задача, и для её решения есть несколько проверенных подходов. Самыми надёжными и читаемыми являются метод с булевым флагом и аккуратное использование GoTo.

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