Macros & VBA:Looping Playlist
Excel Video 422 Looping to Dynamically Define a Range
There’s a way to programmatically define a range of cells to work with in Excel Video 422. I’m introducing loops, a way to repeat code a set number of times, in Excel Video 422. I’m also adding a few new functions as well, so I hope this isn’t too much for one video. Hopefully the idea of being able to use code to dynamically define a range makes sense and will be helpful for you in your macros and VBA.
Watch for the code that tells Excel how many rows to work with in our example. That bit of code will come in handy in future projects. Also watch for how to resize a range. Once we find a blank column, we expand the range to five columns to shade all five at once.
Stay tuned. We’ll slow down a bit and talk more about looping in the next series of Excel Videos. Thanks for watching.
Excel Video 423 Introducing Loops
We’ll start discussing loops in Excel Video 423. Loops are an important concept in programming, and there are several different ways to create a loop in VBA. We’ll start with a basic For…Next loop that runs code a set number of times. To keep things simple, we’ll use one variable that I’ll call A, and we’ll use it several times throughout the macro. Watch how the macro runs until A hits the limit of 8 that we define in our loop.
We’ll make the macro a little more complicated in the next Excel Video. Thanks for watching.
Excel Video 424 Loops with Steps
Excel Video 424 shows you how to make your loop skip rows as it processes your code. Today’s example is a list of patients where I need to test a quality measure for every third patient. Watch how I can use Step to count by threes (1,4,7,10…) instead of shading every row. Also notice how I use the variable b to not just shade the first column, but to put text in the second column as well.
Stepping is great, but wouldn’t it be nice to not have to count how many times you want the loop to run before running the loop each time? Next time we’ll count the number of rows to automatically figure out how many times the loop needs to run.
Excel Video 425 Looping to the End of the Data
Excel Video 425 has one line of code that makes your macros much easier to run as the size of your data changes. Watch for how we create the FindEnd variable that finds the bottom row of your data, and then watch for how we use FindEnd to make our loop work. If the size of the data you want to run your macro against changes, the 4 minutes it will take you to watch Excel Video 425 will be repaid time and time again. I hope you find the code helpful. Thanks for watching.
Excel Video 426 Nesting Loops
Excel Video 426 is a practical example of nesting a loop inside another loop. Before I start, here’s a plug for MGMA’s Annual Conference in Las Vegas in October. You’ll find tons of real world examples of best practices from medical practices throughout the country. It’s a great opportunity to network, and be sure to go to my Custom Reporting session Monday afternoon. It will change the way you look at your data.
I’ll be the first to admit that my nesting loops example has nothing to do with medicine, but it is a very practical example that I hope will make nesting loops easier to understand. Watch how I create an a loop, a b loop that runs inside the a loop, and how b is reset to 1 each time the b loop gets to 20. The one line of code that’s inside both the a and the b loops ends up being run 400 times.
Finally, notice my comment out to the side of my code. The default property of Cells(a,b) is value, so Cells(a,b) is the same thing as Cells(a,b).Value. Either way works. Whatever syntax is easier for you.
Excel Video 427 Exiting Loops
If you find a problem as you’re looping through data, watch Excel Video 427 to learn how to stop the loop so you can fix the problem. Notice how we use an IF statement to check for blank statement dates. If we find a blank statement date, we use a message box to tell the user about the problem and the row with the missing date. As long as the patient has a statement date, the logic and the loop continues. There’s a bunch more things we can do with message boxes that I’ll review once we survive looping in VBA. In the meantime, I hope you’re finding these videos on looping helpful. Thanks for watching.
Excel Video 428 Looping Through Workbook Objects
We’ve spent several Excel Videos looping through rows and columns in a worksheet. Excel Video 428 has a way to loop through Excel objects in your workbook. It’s easy to see the five worksheet names in this Excel workbook, but if you had 20 or 30 tabs, here’s a way to count the number of worksheet tabs and then loop through each tab to get the name of the worksheet. We’ll put the results in another message box, similar to the last Excel Video.
For...Next is not the only way to loop in Excel. Stay tuned. Next time I’ll show you another way to loop through data with VBA.
Excel Video 429 Do While Loops
Excel Video 429 introduces another way to loop through data with Do While Loops and Do Loops While. The difference between those two types of loops is where the While condition is. A Do While Loop may never run if the condition is never met, but a Do Loop While will always run at least once before the condition is checked.
My example is very basic, but you can use this principle to run code while there is data, while there are rows and columns, or while a statement is true or false. Be careful as you play with loops. You can easily send Excel off into a never-ending loop. If you do, CTRL+BREAK will get you out of the endless loop.
There’s a similar kind of loop called a Do Until Loop, with a related Do Loop Until. I’ll discuss those in the next Excel Video. Thanks for watching.
Excel Video 430 Do Until Loops
If you watched Excel Video 429, Excel Video 430 will make a lot of sense as an extension of Do loops. The Do Until Loop has the condition at the top of the loop so the loop may never run, as in our first example. If you put the condition at the bottom of the loop, as in a Do Loop Until, your code will run at least once.
In the next Excel Video, I’ll give you a more practical way to use loops in a more involved example. I look forward to seeing you then.
Excel Video 431 Looping Example
Excel Video 431 wraps up our discussion of looping with an example. We’re going to replace all of the underscore characters with spaces in a set of 6 cells. The first thing you’ll say is, “Nate, I can do that much easier with Find and Replace.” No question. To keep the example simple, we’ll just replace the underscore with a space, but with similar code you could do a custom find and replace. For example, every time you find something in cell C1, replace it with something in cell B1 but every time you find something in C2, replace it with what’s in B2, and so on.
There are two loops in the code. The For Each…Next loop will loop through each cell in our range of C1 to C6. While in each cell, the Do While…Loop checks to see if there’s still an underscore. If it finds an underscore, the code replaces the underscore with a space. If not, the code finishes the Do While…Loop and move to the next cell in the For Each…Next loop.
I hope the Excel Videos on looping have given you some helpful ideas. The next couple Excel Videos will use message boxes to allow your code to interact with the user. Thanks for watching.
