How to Use a Do-While Loop in Excel VBA

Video Transcript

"Hi, my name is Dave Andrews. Today I'm going to show you how to use a do while loop in Excel VBA. Let's open up Excel. Now we need to access Excel's Visual Basic editor, the VBA. So, hold down the alt key on your keyboard and press F eleven. This opens up the VBA. So, what we're going to do is put a little function in to where when the sheet is activated, it's going to fill in some values using a do while loop. So, let's open up sheet one. Next to the general, let's select a little drop down that says worksheet and lets change our function to activate. So this code is going to run when a worksheet is activated. Let's type in our code. As you can see, I typed in code here. Still inside this worksheet activate sub-routine, I've created a variable WS that's going to be a worksheet object, a row that's going to be an integer, and a column that's going to be an integer. I initialize row and col to one. If I don't do that, my code will crash out because I'm going to be changing cells within the workbook and you can't set a cell to the zero, so, the first thing I do is I set the worksheet variable equal to our active workbook sheets, sheet one, because that's the sheet that we're going to be affecting. Here's our do while loop. Simply start it by just saying do and then say loop, and then say while. Now what's here after the word while are the conditions that have to stay true for this loop to continue executing. Now within our code here, I just set the cell for the current row and column to say hello, and I increment row and column, because if I don't, this loop will continue endlessly. You have to make sure that your do while loops are going to have some sort of end condition that's possible. Now in this case, we're going to loop until while, which means as long as row is less than five and column is less than five. Because I'm incrementing the both of them here, that will eventually close out. So lets close our VBA and we're going to switch to a different sheet, saying sheet two. Now as we switch back to sheet one, our code should activate. As you can see, the row and column have incremented until they're less than five, that's why we only have four hello's and it's filled in the text and the sheet. My name's Dave Andrews and I've just shown you how to use the do while loop in Excel VBA."