VBA Do Until Loops

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
If you enjoyed this post and would like to comment or share improvements please send your comments Here.  
If you would like to say hello and buy me a cup of coffee Click here.
Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here|
If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Comments are closed.