Loop without do ошибка vba

Can anyone tell me why I get an error message «loop without do»? for this code

If you have a broken construct (IF without END IF, Do without Loop, For without Next) and they are nested the error always seems to be the outer nest and not the construct with the actual problem. So the message always seems wrong.
In this case it is broken because you have a FOR without a Next inside the DO Loop. You would expect the error to be FOR WITHOUT NEXT, not LOOP Without DO. Only after seeing this a few times do you know what to look for.

I’ve always wondered why vba shows the wrong message. I think this clarifies my doubt.

On the other hand, I think it’s actually giving you the correct message. Of course, I am only guessing, but I believe the VBA interpreter/compiler is reading your code from top to bottom and probably went like this:

Do While Not ... (compiler: Ah, we're starting a Do loop, need to keep an eye out for the closing Loop keyword)
    For j = ... (compiler: Ah, now we're starting a For/Next loop, need to keep an eye out for the closing Next keyword)
        If rs2.Fields ... (compiler: Ah, this time we're starting an If/Then block, need to keep an eye out for the closing End If statement)
            store = store & ... (compiler: nothing wrong here, just move on)
        Else (compiler: I haven't seen the End If yet, so this Else statement is okay)
        End If (compiler: Ah, here's the end of the If/Then block)
        
        rs2.MoveNext (compiler: This should be legal within the For/Next loop, proceed)
        Loop (compiler: Wait a minute, I didn't see a beginning Do statement inside the For/Next loop, this must be a mistake. Show error message)
[here's the rest of the code that's missing from the original post]
    Next
Loop

See how the indentation helps see the problem? I certainly hope so. Cheers!

@FahadTiger — I will explain your error message. In that explanation, I SHOULD use code tags but I can’t colorize them so I’ll do something else.

Do While Not rs2.EOF
For j = LBound(MYarray) To UBound(MYarray)
If rs2.Fields(«ISSUE»).Value = MYarray(j) Then
store = store & «,» & rs2.Fields(«ENGINEER»).Value
Else
End If
rs2.MoveNext
Loop

The DO WHILE … LOOP construct is almost OK — except that the FOR loop construct is incomplete. More specifically, the FOR J=… loop is unterminated. The correct termination would be to put a NEXT J just below the rs2.MoveNext instruction. You would NOT use a simple NEXT statement because that is not the correct termination. The syntax of VBA FOR «stepping» loops requires that you not only have the stepping variable in the FOR statement but ALSO in the NEXT statement.

This error is due to the «code block» concept. I colorized to show the different blocks. The IF/THEN/ELSE/END IF block in PURPLE is perfectly legal and properly terminated (though you could omit the ELSE and it wouldn’t change anything.) This is not the cause of the problem.

The GREEN block is unterminated because of not having the NEXT J in place. Therefore, the LOOP statement doesn’t have a corresponding DO statement

in the same code block

. My colors show what you probably intended, but using the code block concept, that LOOP statement appears (to VBA) to be in the GREEN block even though the corresponding DO WHILE was in the RED block. Thus, in the block where it appears, you have a LOOP without a DO. Adding the NEXT J before the LOOP statement would close out the GREEN block and leave you back in the RED block where the LOOP would properly terminate the DO.

Just one more side note: rs2.Fields(«ISSUE»).Value is a bit torturous. You could have used rs2![ISSUE] just as easily. Ditto for the [Engineer] field. Turns out that for anything that HAS a .Value property, it is the default property and thus does not need to be expressed. AND if that recordset has a field named ISSUE or one called ENGINEER, then the rs2![fieldname] syntax gets you there without quite as much typing. Again, you can omit the FIELDS() element because the default property for a recordset IS the FIELDS collection.

Code:

Sub test()
     
    Loop---compiler looks back as far as the last 'loop start' and can't see a Do
End Sub

Sorry but I can’t understand this. You have a Do on line 2. Why compiler can’t see it?

If I was the compiler I would think this way:

Sub test()
    Do While Not f = 0---compiler says OK starting a loop
        For j = 1 To 10---compiler says OK  starting a loop
            code
    
    Loop---compiler says I reached the loop for Do on line 2 but there's no next. 
                                       Do and For are overlapping. Send a message For without Next
End Sub--

The correct termination would be to put a NEXT J just below the rs2.MoveNext instruction. You would NOT use a simple NEXT statement because that is not the correct termination. The syntax of VBA FOR «stepping» loops requires that you not only have the stepping variable in the FOR statement but ALSO in the NEXT statement

This is not correct. VBA is very loose on this construct. No variable is needed in the NEXT. The below runs fine.

Public Sub TestLoop()
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim j As Integer
  Dim fld As DAO.Field
 
  Set rs = CurrentDb.OpenRecordset("01Schools", dbOpenDynaset)
 
  For Each fld In rs.Fields
    For i = 1 To 3
      For j = 1 To 4
        Debug.Print fld.Name & " I: " & i & " J:" & j
      Next
    Next
  Next
End Sub

and now you have the problem — there are two for/next loops and the developer has missed the first next. So when the compiler reaches a next — which for is it applied to?

I think I understand your logic now.
But think it this way : In the following line,

Debug.Print Left(myvar, Len(Replace(var2, «_», «») — Len(var2))) & Mid(var2, Len(var2) — Len(var1))

the compiler has an eye on all opening and closing parentheses and quotation marks.
Not only it counts the number of closing and opening parentheses, but also checks for matching pairs and weather they are in the right position.

Why the compiler can not do the same for loops?

If my memory serves me well (which recently doesn’t) long long time ago, when I wrote php, the compiler was able to spot which inner loop has not a matching pair and highlighted the block. Then I was using Zend editor. I wonder why VBA behaves the way you explained.

I wonder why VBA behaves the way you explained.

More correctly: «I wonder why VBA (mis)behaves the way you explained.»

MajP said: This is not correct. VBA is very loose on this construct. No variable is needed in the NEXT. The below runs fine.

From the Microsoft VBA Language Specification published 04-30-2014, top of page 77 (in the PDF version thereof), and in section 5.4.2.3 For Statement I offer a couple of excerpts.

for-statement = simple-for-statement / explicit-for-statement
simple-for-statement = for-clause EOS statement-block "Next"
explicit-for-statement = for-clause EOS statement-block ("Next" / (nested-for-statement ",")) bound-variable-expression
nested-for-statement = explicit-for-statement / explicit-for-each-statement
for-clause = "For" bound-variable-expression "=" start-value "To" end-value [stepclause]
start-value = expression
end-value = expression
step-clause = Step" step-increment
step-increment = expression

The <bound-variable-expression> within the <for-clause> of an <explicit-for-statement> must resolve to the same variable as the <bound-variable-expression> following the <statement-block>. The declared type of <bound-variable-expression> must be a numeric value type or Variant.

I didn’t pluck my comment out of thin air. The presence of code tags means I cannot colorize or change much else, so I must simply refer you to the definition for the explicit-for-statement and the for-clause. According to the strict definition of a FOR statement, you have the «FOR variable = …» part followed by the statement block followed by Next and, if there is no intervening loop, the bound variable expression as in «NEXT variable» — and further, the variable with NEXT must resolve to the same variable as was used in the FOR statement.

I think we must at least agree that the FOR loop lacked proper termination. Whether by NEXT or NEXT J, it needed something it didn’t have, and the rest of my explanation (about implied language blocks) was correct. @MajP, you may indeed be correct that newer versions of VBA are looser than used to be the case. However, if the OP is using Access before 2016, this standard should apply.

NG said: Doc is reminiscing about his BASIC days…

Actually, no. As it happens, I am quoting the strict statement that describes VBA syntax, and I have named the reference. It is too big for me to upload in its entirety and besides, you can look it up yourself if you have an urgent need to do so. The following link gives you a page to download PDF or WORD versions from 2014 through 2021.

I looked at the 2021 version for the FOR statement and the language definition has not changed. It does not indicate that the bound variable of the NEXT is optional. More precisely, the syntax of the language definition doesn’t include a «leave it blank» option.