VBA basics
17. Iterative processing (Do Loop)

Excel macro basics and applications, introduction to Excel VBA
last updated:08-16-2019

17. Iterative processing (Do Loop)

Some of this topic was processed by machine translation. Original


As a repetitive process, we performed For ~ Next, this time Do ~ Loop.
The frequency of use is lower than that of For-Next,
This is something that you must remember.



For-Next was to specify the number of repetitions in advance,
Do ~ Loop is not a repeat count but a repeat condition.

Do ~ Loop syntax

Do [{While | Until} condition]
  [statements]
  [Exit Do]
  [statements]

Loop
Alternatively, you can use the following syntax.
Do
  [statements]
  [Exit Do]
  [statements]

Loop [{While | Until} condition]



condition optional.
Specifies an expression that evaluates to true or false, or a string expression.
If the value of the argument condition is a null value, the argument condition is assumed to be false.
statements statements are executed repeatedly while the argument condition is true, or until the argument condition is true,
Write a statement with any number of lines.

This is an excerpt from the Excel help.
If you read this and understand it, you won't have a hard time and you won't see this blog.
I will explain in more detail.
If you delete the extras and rewrite them easily,

Do [While condition]
  ... processing ...
Loop
または
Do [Until condition]
  ... processing ...
Loop

There are also the following ways of writing, but you don't have to remember them.
Do
  [statements]
  [Exit Do]
  [statements]

Loop [{While | Until} condition]

condition

Specifies a formula or string expression that evaluates to true or false.

A formula is a combination of a numeric value and a comparison / logical operator,
A string expression is a combination of a string and a comparison / logical operator.

Comparison operator Meaning
= Equal
<> Not equal
> Greater than
>= More
< Less than
<= Below

Logical operator Meaning
And And
Or Or
Xor exclusive OR
Eqv logical equivalence
Imp logic inclusion

Do not use logical operators other than And and Or.

Do Loop syntax

An example of Do Loop.

When 1 is inserted from A1 cell to A10 cell.



Sub practice1()
  Dim i
  i = 1
  Do While i <= 10
    Cells(i, 1) = 1
    i = i + 1
  Loop
End Sub

Or

Sub practice2()
  Dim i
  i = 1
  Do Until i > 10
    Cells(i, 1) = 1
    i = i + 1
  Loop
End Sub

While is a condition
Until
Therefore,
The While and Until conditions are complementary.
That means
Not (While condition) = Until condition, While condition = Not (Until condition)
This is the relationship.

Also,
i = 1
This puts 1 in the variable i.
And
i = i + 1
This adds 1 to variable i and puts it back in variable i.
That is, the variable i is incremented by one.
If you write a description line by line

Sub practice1()
  Dim i        'Declares variable i
  i = 1         'put 1 in variable i
  Do While i <= 10  'Continue processing while variable i is 10 or less
    Cells(i, 1) = 1 '1 is put in i row of column A
    i = i + 1     'Increase variable i by 1
  Loop        'Do processing range is up to here
End Sub


Make sure you understand the macro VBA code and comments.

If you want to process every other line as in Step 2 of For ~ Next,
i = i + 2
This will quickly understand what you need to do.

You can use either While or Until.
However, basically, use either one.
If you use them together, the program will be difficult to see.

Exit Do

Exit Do exits the loop from Do to Loop.

Before the specified conditions are met,
Use this when you want to end loop processing from Do to Loop.
As a frequent use,

Do
When you want to finish the process, Exit Do
Loop

In this way, without writing the end condition in Do,
In this loop, the end condition is judged and the process is terminated.

Do ~ Loop nesting

When 1 is put in 10 rows and 10 columns.

Sub practice3()
  Dim i, j
  i = 1
  Do Until i > 10
    j = 1
    Do Until j > 10
      Cells(i, j) = 1
      j = j + 1
    Loop
    i = i + 1
  Loop
End Sub

Run and check.

Do ~ Loop nesting should be up to 3 levels.

Last word

Do ~ Loop is less frequently used and more difficult to use than For ~ Next.
However, as VBA improves and begins to write various processes, the number of repetitions is unknown.

For example, when processing all files in a folder,
It becomes essential when waiting in VBA until external input is completed.
When you need them, you can learn the details again,
First, understand the basic sentence pattern.



Articles with the same theme "VBA basics"

14. Character concatenation and continuation line
15. Four arithmetic operations and annotations (comments)
16. Iterative processing (For Next)
17. Iterative processing (Do Loop)
18. Get last row (End, Rows.Count)
19. General practice question 1
20. Conditional branch (If)
21. Conditional branch (ElseIf)
22. Conditional branch (Select Case)
23. Message box (MsgBox function)
24. Input box (InputBox function)



  • >
  • >
  • 17. Iterative processing (Do Loop)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text