Do While Loop VBA Function in Microsoft 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.