Excel VBA Do Loops

In this brief tutorial I demonstrate two examples of Excel VBA Do Loops

SYNTAX  Excel VBA Do Loop
Do {While | Until } condition
[statements]
[Exit Do]
[statements]
Loop
– or –
Do 
[statements]
[Exit Do]
[statements]
Loop {While | Until } condition

If you study the two ways you can write a VBA Do loop on the left, you note you can have the condition tested at the start or after the first statement is executed.  Probably the most common VBA Do Loop (see example #1 below) runs down a list of rows and executes some action (statement) until you hit an empty space (the condition). 
The statement components in example #1 below makes the current line upper case and then moves the cursor down one row. 

Hint: The exit do provides another way to break out of the loop -adding this code: 
If activecell.row >5000 Then Exit Do

It will avoid having the code run until you hit the last row in Excel (that’s over a million rows)  

Sub Do_Loop_Example1()
'*************************************************************
' Subject       Do Loop Example #1
' Date          12/25/2018
' Developer:    Ray Mills
' Purpose       Demonstrate do loops
'               Change all lines to Caps
'*************************************************************
' start in the right place ...
Range("A1").Select
' run through the list ...

Do Until ActiveCell.Value = Empty 
    'do something here
    ActiveCell.Value = UCase(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
' move back to starting pt ...
Range("A1").Select

End Sub

Do loops probably fall just behind the if then statements in use as the fundamental code building blocks when you develop VBA code, particularly in the beginning.  Here I’ve created a very basic Do loop.  Here’s what it does:
1. It moves the cursor to cell A1
2. It will move the cursor down a line at a time until it hits the first empty cell
3. It will make the activecell contents uppercase 
4. It will return to cell A1

Note: There is another Do Loop variant the Do while Loop.  It works exactly the same – i.e. you might code: 
Do while x < 20000.  
Once X is 20,000 or greater the loop stops. 

Before Code Was Run
After Code Was Run
Sub Do_Loop_Example2()

'*************************************************************
' Subject       Do Loop Example #2
' Date          2/20/2018
' Developer:    Ray Mills
' Purpose       Demonstrate do loops
'               SHOW A PROGRESS F0RM
'*************************************************************
Dim x As Integer

'load the counterform & set the counter label to 0 ...Load frmCounter
frmCounter.lbCounter.Caption = "0"


' show the form
frmCounter.Show

x = 0
Do Until x = 10
    Application.Wait (Now + TimeValue("0:00:01"))
    x = x + 1
    frmCounter.lbCounter.Caption = x
    frmCounter.Frame2.Width = (x * 23)
    frmCounter.Repaint
Loop

frmCounter.Hide
Unload frmCounter

End Sub

VBA Do Loop Status Form Example

In addition to the code above, I’ve added a form frmCounter with three components
1.  a label name lbCounter (the 11 shown)
2.  two frames no captions the inner one (Frame 2) has the back color set to Red  

Watch the attached video so you can see how I used a VBA Do loop to automate the form and make it dynamic.

Do Loop Example #3
The third example is an extract from another of my posts about improving your VBA code’s efficiency (e.g. making your code run faster).  You can see it HERE. I have changed the For Next loop in that example to a Do Loop, both will work.

With the cursor at cell A2 and  Cell A1 has a value of 1 the program will have the cursor move down each row until it reaches row 5000.  

Sub MoveExample()
' How to improve execution time
' this simple example moves to each cell ...


Dim TimeStart As Date, TimeEnd As Date
Dim i As Integer
Dim sMsg As String

TimeStart = Now
Do until activecell.row = 5001
ActiveCell.Value = 1 + ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(1, 0).Select
loop
TimeEnd = Now

i = DateDiff("s", TimeStart, TimeEnd)
sMsg = "Elapse time = " & i & " seconds"
MsgBox sMsg, vbInformation, sMsg


End Sub

Excel and VBA Craftsman offers experienced VBA Development: Complete GUI Driven Applications, Automated Reconciliations, VBA ODBC Database Data Mining, VBA Excel, Access, Outlook and Word Customization. VBA Custom Functions, VBA Concept Assessment, VBA integration of MS office applications into powerful single seamless solutions, Excel Help, Automate Excel, Excel Automation