How to use Do While Loop in Excel VBA?

Do While Loop – Do While loop repeats a set of statements as long as the condition is True.

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 While Loop

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

                   Do While Num <= 10

                             Syntax

                   Loop

     

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

                   Do

                             Syntax

                   Loop While Num <= 10

 

Difference between Do While and Loop While

Do While -> Do While checked condition as False at first time then the loop must exit.

                   Num = 1

                   Do While Num <= 0

                             MsgBox “Num Value : “ & Num

                             Num = Num + 1

                   Loop

 

Loop While -> Loop While must run once if the first condition as False 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 While Num <= 0

      

Do While Example

(A).    Sub Do_While()

                   Dim Num As Integer

                   Num = 1

                   Do While 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 While Loop and apply condition at Beginning of Loop. Do While 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

How to use Do While Loop in Excel VBA?

 

Loop While Example

(A).    Sub Loop_While()

                   Dim Num As Integer

                   Num = 1

                   Do

                             MsgBox “Num Value : “ & Num

                             Num = Num + 1

                    Loop While 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 While Loop and apply condition at end of Loop. Loop While 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

How to use Do While Loop in Excel VBA?

Download Workbook

Leave a Reply

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