Okay, we're moving along to two way if then statements in VBA. So in this case for two way, you come into this logical condition just like you would a one way, but you do something if that condition is true, and you do something if the condition is false. The general form in VBA has if condition then you have your statements if that's true, else statements if the condition is false. And then you have an end if, and this is by far the most common if then structure that's used in VBA programs. Let's look at an example, a lot of times in engineering and math you'll have piecewise functions. Here we have a piecewise function for the friction factor as a function of the Reynolds number which is R, so this is used in fluid dynamics. Basically, the function has a different value if the Reynolds number is less than 2,000 as when the Reynolds number is greater than 2,000. We can draw just the quick flow diagram element for this. We come into this, we have a conditional. Is R less than 2,000? If that's true then we're going to calculate the output of the function in this case, we're going to make a function that's 16 divided by the Reynolds number. Otherwise if that's false, if it's not less than 2,000, then it's greater than or equal to 2,000. Then we calculate the friction factor using this other piece of the function. So in VBA, if we were to create a function then it would look like this. The name of our function is fluid, it has an argument which is the Reynolds number R. If R less than 2,000 then fluid, remember the output of a function is always the name of a function. Fluid equals 16 divide by R. Otherwise fluid is the second part of the function here, and we end if and end function. And then we can just use this in Excel. We could just type in fluid, because it's a function on the different Reynolds numbers and we will get the output of the function which is the friction factor. Let's work through another example, how would you create a VBA sub that would add 20 to a cell's value if the number were positive and subtract 20 from the cell's value if the number were not positive? Let's make a quick flow chart for this. We checked to see if the active cell value is greater than zero. If that's true then the new active cell value is equal to the old active cell value plus 20. Otherwise the new active cell is equal to the old active cell value minus 20 and then we reconvene and we end. So I'm going to create my sub routine in the editor. So I type in the first line and when I press Enter for any sort of if statement, I like to put, if this is a two-way I'll put the else. And I always like to add or include the end if so I don't forget it. So, if that's positive, if the active cell is greater than zero then the new active cells, the old active cell plus 20. Otherwise, the new active cell is the old active cell minus 20. It's always nice to set up positive and negative controls, so let's go ahead and step through this using F8. If the active cell is greater than zero, seven should be, we bump into that true option there, and we increase the active cell value by 20. Otherwise, if we click in negative four and we run through this, you see that we skip over that part, the true part of the two way up then into the false part and we subtract 20. Let's do another more advanced example here. We want to make a function, so a VBA function, not a sub, that's going to output biz if the corresponding number that we put into the function is divisible by 3. We're going to assume we're working with positive integers, buz if the number is divisible by 5 and bizbuz if the number is divisible by both 3 and 5. So a flow chart for this is shown here. You notice that it starts out with a two way if then, and it comes back together, that two way if then comes back together, but on each arm of the two way if then, we have another two way if then. So we really have four options here, x can be divisible by 3 and 5. In that case, we output bizbuz, x can be divisible by 3 but not be divisible by 5, and in that case we output biz. X can not be divisible by 3 but be divisible by 5, in that case we output buzz. And finally x is not divisible by either 3 nor 5, and in that case we don't do anything. So let's go ahead and put this into a module. Again, this is going to be a function and we're going to call it bizbuz. The argument is going to be x and it's going to be an integer. We've already taken into account the enter x because that's taken into account when we input that as an argument to the function. The next thing we do is we have a two way if then, so I'd like to just sort of frame it out. It will give you a warning because you don't have anything after the if, and if the first condition is false, we go into a one way if then which is shown here in the original else statement. So to see if x is divisible by 3, we can use the mod function. If x mod 3 + 0 then we see if x is divisible by 5. And if both of those are true, then we're going to output bizbuz. The output of a function is always the name of the function. So we write bizbuz is equal to, in quotations, bizbuz. Otherwise, so now we're looking at the left arm of the right side of the first conditional. We're going to output biz. So bizbuz is equal to biz. So now let's look at the left arm of the original two way if then, if x is divisible by three. If that's false, then we have to see if x divisible by five. And if x is divisible by five but not divisible by three we're going to output buz. And if neither of those are true we're not going to do anything. So that's why this second embedded if then structure here is just a one way. All right, we are ready to go. The way you troubleshoot or debug a function, as you recall, is to put a break point in the first line of executable code. One thing I forgot to do is write bizbuz as a string. So we put our break point there, I'm going to go to cell B1 and type in the function with the argument, just the cell next to it. And you see that I jumped into the function because I had the break point, and now I can step through it. 1 is not divisible by 3. Oops, and this is why you step through, because I forgot to put a 0 there, equals 0. So let's go ahead and reset this. Go back into Excel, press Enter. We go back into debug mode and we skipped over both. So it's not divisible by either of those, and you see it just left a blank. Now I'm quite certain it's working. I'm going to remove that break point and I'm going to take that function that I put into B1 and I'm going to drag it all the way down. You see this is what we would expect. 3 is divisible by 3 so we get biz, 5 divisible, and so on and the first one that's divisible by both. It's just the product of three and five which is bizbuz. So that hopefully shows you a more complex example of using embedded two way if thens inside of another two way if then. Thanks for watching.
