In Excel Video 434 we’ll work through how to capture user input from Message Boxes. Notice how the syntax is slightly different when we need the user’s answer. We define a variable as equal to the Message Box and the Message Box function now needs parentheses. The...

Excel Video 433 takes Message Boxes a step further by customizing the message buttons. The trick is that instead of using a default like vbOKCancel or vbInformation, try using a number instead. Here’s the URL for the page I reference in the video:...

Excel Video 432 is the first of three videos on how to use Message Boxes in Excel. Today we’ll build a basic message box with the text inside the box, the button choices inside the box, and the title of the box. There are a whole list of choices for buttons. We’ll...

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...

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...

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...

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...

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...

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....

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...

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...

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...

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....

Tables are a great way to manage Excel data. Excel Video 421 shows how to reference and change a Table using VBA. As we continue our discussion about how to reference data using VBA, it’s worth a few minutes to understand how to work with Tables. If you aren’t...

There are three very useful shortcuts in Excel Video 420. I’ll show you how to quickly comment and uncomment lines of code. For those of you who don’t write a lot of code, by comment I mean to have VBA skip over the line without running the code. You can use comments...

If you’d like to use a macro in one spreadsheet to change another spreadsheet, watch Excel Video 419. Today we’ll review the syntax to reference another workbook from VBA. The first thing you’ll notice is that the second workbook has to be open for the VBA code to...

In Excel Video 418 we’ll identify ranges on another worksheet in the same workbook. Everything in Excel Video 417 assumed we wanted to select ranges in the worksheet we were in when we ran the code, which is called the active worksheet. By referencing the worksheets...

Excel Video 417 starts our discussion on identifying ranges for our VBA code to work on. In the Macro Recorder videos we were very concerned about which cell we started recording in. Once we can identify ranges of cells, it’s not as important which cell we’re in when...

Excel Video 416 showcases another way to track values in your code and watch when they change. The Watch Window takes a little setup, but once you add watches, you can watch variables, values, and other aspects of your code throughout the macro. As the macro changes...

Excel Video 415 introduces the Immediate Window, one way to keep track of how locations and values in your spreadsheet change as your macro or VBA code runs. Watch for two ways to open the Immediate Window, two commands to get information in the Immediate Window, and...