VBA - loop with multiple conditional levels

Question

I am trying to create a loop that tests for multiple levels of conditions, and tests that fail to go onto the next item, and those that pass go to the next level test. How would do this? Specifically it goes:

ITEM: test condition 1

Is condition 1 met?
    no: next ITEM
    yes: test condition 2

Is condition 2 met?
    no: next ITEM
    yes: test condition 3

Is condition 3 met?
    no: next ITEM
    yes: run block of code, next item

Here is an illustration of it

enter image description here

Thanks in advance for the help

Peter Albert · Accepted Answer · 2014-01-31T07:34:37.777

There are multiple ways to solve this - I'd recommend the last option, listed below:

The classical way is this way:

Sub ClassicalVersion()

    For Each x In YourCollectionY  'or use For i = LBound(array) to Ubound(array)
        If Condition1 Then
            If Condition2 Then
                If Condition3 Then
                    'Your code here
                End If
            End If
        End If
    Next

End Sub

This is sometimes referred to as the arrow-head antipattern and should be avoided imo, as it makes code hard to read.

Instead, you could go for a GoTo statement:

Sub GoToVersion()

    For Each x In YourCollectionY  'or use For i = LBound(array) to Ubound(array)
        If Not Condition1 Then GoTo NextElement
        If Not Condition2 Then GoTo NextElement
        If Not Condition3 Then GoTo NextElement

        'Your code here
NextElement:
    Next

End Sub

A lot of people despise the use of GoTo, but imo this would be a feasible solution to your problem and is already better to the classical version.

The best approach imo however is to separate the loop from your conditions (and according to the Single Responsibility Principle, maybe even the code from your condition check) using a sub routine and the Exit Substatement:

Sub SRPVersion()
    For Each x In YourCollectionY  'or use For i = LBound(array) to Ubound(array)
        If RunChecks Then
            CallToSubWithYourCode
        End If
    Next

End Sub

Function RunChecks(x as ...) as Boolean
    If Not Condition1 Then Exit Function
    If Not Condition2 Then Exit Function
    If Not Condition3 Then Exit Function
    RunChecks = True
End Function

It looks like you and I are thinking very similar thoughts, so I'm upvoting. — Floris, Jan 30 '14 at 20:35
@Floris: Agree - looks similar! And I really miss the `Continue` statement (and the logical short-circuiting) in VBA! — Peter Albert, Jan 30 '14 at 21:49
I'm going to ignore suggested protocol and say "+1, nice answer." — Doug Glancy, Jan 31 '14 at 04:21

score 1 · Answer 2 · answered Jan 30 '14 at 20:26

You can do this with a Do loop:

Do While True
  If Not condition1 Then Exit Do
  If Not condition2 Then Exit Do
  If Not condition3 Then Exit Do
  Call code
  Exit Do
Loop

Or, if you do this whilst looping over a bunch of items:

For Each Thing in bunchOfItems

  If Not condition1 Then GoTo nextItem
  If Not condition2 Then GoTo nextItem
  If Not condition3 Then GoTo nextItem

  Call code

nextItem:

Next Thing

Most languages have a continue statement to short circuit the rest of the For loop. VBA, unfortunately, doesn't.

VBA - loop with multiple conditional levels

2 Answers2