Excel Help Home ExcelHelp.com BBB Business Review
Do While Loop Excel VBA Function

 

Do While Loop in Excel

 

The Excel Do while loop function is another great Excel function to know. The Excel Do While Loop function is used to loop through a set of
defined instructions/code while a specific condition is true.

 

 

The Basics:

 

The condition to be evaluated if another loop is to be performed is listed after the While keyword. For example:

 

Do While (Condition is still evaluated as true)

 

'Code to be executed

 

Loop

 

Now, using this function allows you to add some powerful functionality to your Excel application.
Let’s assume you want to remove all the spaces in a string in the active cell in the active worksheet.
You can achieve this by combining other Excel functions into your Do While loop.

 

Dim StringToBeProcess As String ‘String variable to process
Dim Position As Long  ‘Long variable holds position of the character string found

 

‘For this example we are going to assume the active cell contains the following text, namely:
“Excel Help is a great site!”

 

StringToBeProcess = ActiveCell
Position = instr(StringToBeProcess,” “)

 

Do While Position > 0  ‘The instr function will return the position of the first
 space found working from left to right

 

StringToBeProcess = Left(StringToBeProcess, Position - 1) & 
 Mid(StringToBeProcess, Position + 1,Len(StringToBeProcess) - Position)
Position = InStr(StringToBeProcess, " ")
 ‘Update the Position variable to find the next possible space in the string 
  being processed

 

Loop

 

Msgbox StringToBeProcess

 

The Excel Do While loop allowed us to cycle through the string in the active cell until we have processed all

the available spaces.

 
 
Sitemap | Privacy | Terms
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation.
Copyright 2011 ExcelHelp.com All rights reserved
More Info (+)