All right. We are now getting into the meat of programming. All programs do one or more of the following; sequence, selection or repetition. Sequence is pretty straightforward. All program code has sequence. It's really just one thing after another. The encompassing structure of all programs essentially has sequence in it. You begin and then you end. So, let's talk now about the next program structure, which is selection. Selection is program branching based on decisions. So, you'll come into this from some other place in your program or flowchart, and you have to make a logical condition. So, this is basically a test. If it's true, then you're going to do one thing, if it's false, you may or may not do another thing. So, the program does one thing or another depending upon whether that logical condition evaluates to true or false. There are several specific structures that provide for selection, in particular we have a One-way If-Then, which I'm covering in this screen cast and a two-way If-Then, in a subsequent screen cast. So, One-way If-Then looks like this. The flow chart elements, you come into it, you make a decision, if that decision is true, then you do something. If that logical condition is false, you do nothing. You just move along and both of these come together at the end. In VBA code, it looks like this. It has this general form. We have a condition here, so, we write if condition. So, condition is just some sort of conditional statement that has to be true or false. If conditions, so, if that's true then we do this. So, we're going to have something. An example is if Xval greater than 10 then. So, Xval, if it's greater than 10, then we're going to bump down and we're going to set Yval equal to 20. Otherwise, you notice we're not doing anything. So, in the flowchart element over here to the left, we wouldn't be doing anything on the left side. We would just move along. By the way, this is really useful. You can write these One-way If-Then statements on one line. These three lines of code here, are equivalent to this single line of code here. If Xval greater than 10 then, Yval equal 20, and you notice that you don't need the End If. So, let's work through two short examples. We have an example here. We want to create a VBA subroutine that asks the user for an integer. If the integer is even, the sub tells the user so. So, this is an input box. In this first example, the user inputs a seven which obviously is not divisible by two. It is not even, so nothing happens. If the user inputs an even number in the second example of 12, then a message box is going to tell them that their number is even. Now importantly, there's a good way to do this in VBA to determine if an integer is even, and that is using the mod function, MOD. So, if I wrote five mod three, this is just the remainder when five is divided by three. So, this equals one plus two thirds. The mod function gives you this. It gives you the remainder. Another example would be, well, we could do the two. So, if seven mod two, that's seven divided by two equals three plus one half. So, the remainder is one. So, we can check to see if the remainder is zero. We can write x mod two, if that equals zero, then we know that that number is divisible by two, and it's even otherwise it is not. Let's make a quick flowchart. So, we're going to start here. We're going to start and then we are going to obtain x from the user. Next, we are going to determine if x is divisible by two using the mod function. If that is true, if x mod two equals zero, then we're going to have a message box, but if this is false, then we're not going to do anything. Then, we end. So, that's a nice flow chart. Now, let's implement that in VBA code. So, I've got my sub here. I've named it MessageIfEven. I'm dimming x as an integer. We're assuming that the user inputs an integer. Next, we obtain x in an input box. I set up my condition here. So, if x mod two equals zero then, and a lot of times when I put an if, I automatically put an end if even before putting the code in here. Another thing I like to do is, tab. I'd like to indent here. So, I tab that out and write my code. So, if this is true, then we're going to message box, your number is even. That's all we're doing. If this is not true, then we're not doing anything. So, that's what's shown on this flow chart over here. So, let's go through this. I'm going to put my cursor in there, and I'm going to press F8 so we can run through this. Please enter a number. We enter seven, and when I look at the locals window, we've picked up seven. I run this next line, you see that it just bumped over the contents of the if structure because that wasn't true, and then we end if and we end sub. So, nothing happened. However, if we run it now and enter something that's even like 12, then I do that, you see that it goes into the if then statement. The remainder when 12 is divided by two is equal to zero. We see then the message box, your number is even. Let's do another quick example. Create a VBA subroutine that replaces a blank cell with a zero. So, if I have my active cell in a blank cell it's going to replace it with a zero. The flowchart for this example is going to be very similar to this. It's going to be a One-way If-Then. We won't obtain X because we don't need any values to act on, but instead we're just going to go straight into One-way If-Then. We're going to check to see if that active cell is blank, and then if it is, we're going to replace it with a zero. The name of my sub is ReplaceBlankWithZero. I don't have any variables that I am defining, so I don't have to dim anything. So, I have my If then statement, if ActiveCell equals. Now, there's a couple ways to do this. The way I'm going to show you first, is just to empty quotations. That means, if it's blank, there's nothing in it, then, we want to replace the active cell with zero. So, it is simple as this. When I run that, make sure that cell B2 here is my active cell, and I am running through this. You see that it's blank, so we jump into the One-way If-Then and we replace with a zero. Now, there's another way to do this. Instead of using if ActiveCell equals empty quotations, we can use the IsEmpty function that's built into VBA. If IsEmpty of the active cell, then active cell equals zero. So, let's make sure that I make that empty, and let's run this. You see it does the same exact thing. I wanted to just show you how you can convert if you have a One-way If-Then, you can make it a one-liner. So, in this example, I can just take this argument that's in there and just put it at the end of the line, and in that case, you can't have an end if, you actually have to remove it. This does the exact same thing then. All right. That's it for One-way If-Then programming structures in VBA.
