In this screencast, I'll be going over VBA syntax and expression entry. Whenever the Visual Basic Editor is performing calculations, there's a certain order of operations. Exponentiation comes first, thus this carrot or this circumflex here, then negation, then multiplication and division, and then plus and minus. Formulas are evaluated left to right. I'll show you how this works momentarily unless parentheses are used to group calculations. If there's parentheses present, that overrides or trumps any of this left-to-right evaluation. Again, I'll show you a couple of examples of this. Let's take a look at an example here where we have 1 plus 2 times 3 in the numerator, and we have 4 times 5 in the denominator. In VBA syntax, we would write this as 1 plus 2 times 3 in parentheses. This sets it apart in the numerator, and then we divide in the denominator by 4 times 5. Now, it's really important that when you have denominators like this, that you're specifying with these parentheses, numerator which is on top, and the denominator which is on bottom. If this is going way over your head, that's entirely fine. In fact, this screencast is really the only screencast in this entire course that has a little bit of math in it. The way that VBA interprets this is it calculates the 2-by-3 result. It's going to take a look at the parentheses on the left-hand side, first, is going to go left to right. It's going to multiply 2 times 3 to get a result. Then it's going to compute the 1 plus the 6 to get 7 in the numerator or the left parentheses there. Now, it's going to work to the right. It goes left to right. It's going to compute the 4 times 5 results in that second set of parentheses to get 20, and then it's going to divide the numerator result, which was 7 by the denominator result of 20 to get the final result of 0.35. That's how the Visual Basic Editor performs mathematical manipulations. When we do that, we get 0.35 as the solution to that expression. Now, I want to show you a couple of incorrect ways to write this in the Visual Basic Editor or in VBA syntax. This is not the same as 1 plus 2 times 3 divided by 4 times 5 in parentheses. What this really is, the way I've written it here is 1 plus 2 times 3 divided by 4 times 5, which is 1.3. That is not the same as 0.35 above. You always have to specify with parentheses. You have to separate the numerator from the denominator. Another incorrect way to write this would be 1 plus 2 times 3 divided by 4 times 5. What this really is is 1 plus 2 times 3 divided by 4. We're only taking 1 plus 2 times 3 and dividing by 4. We're not dividing by 5 as well. We're actually multiplying by 5 here, and this gives us 8.75, which also is incorrect. These two ways are incorrect ways of writing this expression above. Now, one more correct way to write this is 1 plus 2 times 3 divided by 4, divided by 5. What we're really doing, here again, we're going left to right is we're taking 1 plus 2 times 3, dividing by 4, we're getting a new result and we're dividing by 5. This is an alternative, correct way to write this. Let's go over another example. The present value of an amount given its future value can be obtained using this formula. We take the future value and we divide by 1 plus i, i is the interest rate here, and that's going to be in parentheses, and we take it to the n number of years. We're performing exponentiation here. If we wanted $10,000 in 10 years from now, n would be 10. Our future value is 10,000. Let's just say the interest rate is 0.05. I think that's quite generous. I think the interest rate is actually quite a bit lower than that these days. But we can calculate how much money or the present value we would have to invest today to have $10,000 in 10 years from now. In VBA syntax, if we had these variables FV, i, n, and PV, this is how we would write this. I've actually got this in a starter file. We can calculate PV. PV is equal to FV divided by in parentheses 1 plus i, n parentheses. Then we take it to the exponent of n. We use our carrot or the circumflex there to exponentiate, The way that the Visual Basic Editor does this, it calculates the 1 plus i result. Again, it starts in parentheses, so it's going to get 1.05. It's then going to exponentiate first because that's first in order of operations. It's going to take 1.05 to the 10th power, and that's 1.629. Finally, it's going to divide the numerator of 10,000 by the denominator result of 1.629 to get the final answer, which is 6,139. You would have to invest $6,139 today in order to get $10,000, 10 years from now, if the interest rate we five percent. I've got this calculation in a file called VBA syntax examples that you can download and work along with me. This is in a subroutine called present value. We're dimming these four variables. Future value is 10,000, i is 0.05, n is 10. We can perform our calculation here. You'll learn a lot more about this in the next module. But if I wanted to put the result, our present value into range A1, that's cell A1 of the worksheet, I could use this statement here. Let's go ahead and run through this. Press F8 the locals window down here is really important because you can see what the Visual Basic Editor thinks the different variable values are. i is 0.05, the present value. We can run that line. Present value, we're getting 6,139. Then we're going to place that value, that PV value into range A1. If I go over here to the worksheet, you'll see that it's placed that into cell A1, and then we can finish that. This is one example of how we can write complicated mathematical syntax in the Visual Basic Editor. I'm going to be resuming this screencast in a Part 2 that you can watch, and we'll talk more about the various syntax in VBA.
