How to use Do Until Loop in Excel VBA?

Do Until Loop – Do Until loop repeats a set of statements as long as the condition is False.

Suppose we have to do lots of work in Excel i.e consolidate data from multiple worksheets in a single worksheet, import data into excel from SQL Server, import data into excel from MS Access, Creating Word Document, Import multiple files Name into Excel from folders and subfolders, Rename folder files, Insert object into excel, and lots of works to do then we use loop for work fast.

There are two techniques to apply the condition for Do Until Loop

(A). We can apply the condition at beginning of Loop.

                   Do Until Num > 10

                             Syntax

                   Loop

     

(B). We can apply the condition at end of Loop.

                   Do

                             Syntax

                   Loop Until Num > 10

 

Difference between Do Until and Loop Until

Do Until -> Do Until checked condition as True at first time then the loop must exit..

                   Num = 1

                   Do Until Num > 0

                             MsgBox “Num Value : “ & Num

                             Num = Num + 1

                   Loop

 

Loop Until -> Loop Until must run once if the first condition as True because the condition is checking in end of the Loop so middle of the statement must execute at once.

                   Num = 1

                   Do

                             MsgBox “Num Value : “ & Num ‘This line must execute once

                             Num = Num + 1

                   Loop Until Num > 0

      

Do Until Example

(A).    Sub Do_Until()

                   Dim Num As Integer

                   Num = 1

                   Do Until Num > 10

                             MsgBox “Num Value : “ & Num

                             Num = Num + 1

                   Loop

          End Sub

 

Steps Follow ­ ­–>

(1). Declare Variable – We need to Declare Variable for Numeric using Integer Data Type. Dim Num as Integer. Integer is a Datatype and Num is a Variable Name

(2). Assign Value – After that, we have to assign the value into Variable Name. Num = 1

(3). Apply Condition – After that, we use Do Until Loop and apply condition at Beginning of Loop. Do Until Num > 10

(4). Show Result – We show the variable Value through MsgBox.

(5). Increasing Value – We increase the Value into the variable by one. Num = Num + 1. Each declared Numeric variable have 0 zero value.

  • Num = Num + 1 : Num = 1
  • Num = Num + 1 : Num = 2
  • Num = Num + 1 : Num = 3
  • Num = Num + 1 : Num = 4
  • Num = Num + 1 : Num = 5
  • Num = Num + 1 : Num = 6
  • Num = Num + 1 : Num = 7
  • Num = Num + 1 : Num = 8
  • Num = Num + 1 : Num = 9
  • Num = Num + 1 : Num = 10

(6).Loop – After that, we use loop keyword for repeating task.

Code Work

When we run this code, The MsgBox display ten times display Num value and each time must increase value into Num variable.

  • MsgBox display : 1
  • MsgBox display : 2
  • MsgBox display : 3
  • MsgBox display : 4
  • MsgBox display : 5
  • MsgBox display : 6
  • MsgBox display : 7
  • MsgBox display : 8
  • MsgBox display : 9
  • MsgBox display : 10

do until loop in vba

How to use Do Until Loop in Excel VBA?

Loop Until Example

(A).    Sub Loop_Until()

                   Dim Num As Integer

                   Num = 1

                   Do

                             MsgBox “Num Value : “ & Num

                             Num = Num + 1

                    Loop Until Num > 10

           End Sub

 

(1). Declare Variable – We need to Declare Variable for Numeric using Integer Data Type. Dim Num as Integer. Integer is a Datatype and Num is a Variable Name

(2). Assign Value – After that, we have to assign the value into Variable Name. Num = 1

(3). Do – Starting Loop

(4). Show Result – We show the variable Value through MsgBox.

(5). Increasing Value – We increase the Value into the variable by one. Num = Num + 1. Each Declared Numeric variable have 0 zero value.

  • Num = Num + 1 : Num = 1
  • Num = Num + 1 : Num = 2
  • Num = Num + 1 : Num = 3
  • Num = Num + 1 : Num = 4
  • Num = Num + 1 : Num = 5
  • Num = Num + 1 : Num = 6
  • Num = Num + 1 : Num = 7
  • Num = Num + 1 : Num = 8
  • Num = Num + 1 : Num = 9
  • Num = Num + 1 : Num = 10

(6). Apply Condition – After that, we use Do Until Loop and apply condition at end of Loop. Loop Until Num > 10 

Code Work

When we run this code, The MsgBox display ten times Num value and each time must increase value into Num variable.

  • MsgBox display : 1
  • MsgBox display : 2
  • MsgBox display : 3
  • MsgBox display : 4
  • MsgBox display : 5
  • MsgBox display : 6
  • MsgBox display : 7
  • MsgBox display : 8
  • MsgBox display : 9
  • MsgBox display : 10

do until loop in vba macro

How to use Do Until Loop in Excel VBA?

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *