From the course: Excel: Learning VBA

Create a calculation using mathematical operators - Microsoft Excel Tutorial

From the course: Excel: Learning VBA

Start my 1-month free trial Buy for my team

Create a calculation using mathematical operators

“

- [Lecturer] Elsewhere in this course, I have demonstrated mathematical calculations in Excel VBA. In this movie, I will give you more accurate and complete descriptions of the operators that you can use to create those calculations. My sample file is 02_05_Operators, and you can find it in the chapter two folder of the exercise files collection. This workbook does contain a worksheet with some data, but what I really want you to look at is the VBA code. So I'll press ALT F11 to move to the Visual Basic Editor. And here, you see I have a code module with a single subroutine called arithmetic operators. And you can see that I have defined a whole lot of variables. So I've declared everything that I want to use in this subroutine. You don't need to worry about them, you'll see them as we go along. I'll start with assigning values to two variables, and those are the values of the first order and the second order. And then, the total is the first order plus the second order. And you can see that the plus sign is used for addition. And when we run the subroutine, you see that we will get a message box with the total. You can also find the difference between the two orders by using subtraction. And on this line, you can see that the minus sign, which you type using the same key as the hyphen, is used to subtract one value from another. And once again, we will get our message box. Below that, we're calculating sales tax, and we do that through multiplication. So you can see here that we are reusing the plus sign for addition. And then, over to the side, we have multiplication, which is done using an asterisk. So that will be the first order multiplied by the sales tax, which I defined here as 0.9, and that will be displayed as well. Just as you can do multiplication, you can also do division. And here, we can calculate a profit ratio by dividing the bottle price, that is what we sell it for in the store, by the cost to make it. And so we'll have 18 divided by seven, and we'll round that to two decimal points and display the result. You can also use exponentiation. So for example, two to the third power is eight. So you would have two times two times two. And you indicate an exponent by using the caret, and that is that upward pointing arrow or chevron that you see there. And here's the formula for calculating the value, assuming compounded interest. And we'll see that result in a message box as well. And finally, you have two related operations, and those are integer division and modular division. Integer division, as the name implies, finds the whole number value from a division operation. So here, we have 81 divided by 13. And the most that you can do with 13s going into 81 is six because six times 13 is 78. So integer division, which is indicated using a backslash as opposed to a forward slash, would return the value of six, and that'll be displayed. And finally, you have modular division, which instead of the integer component takes the remainder. So the number of bottles remaining, we have 81 minus 78, would be three. There is not a separate operator, single character operator, that you use for modular division. Instead, in Excel VBA, you just use the mod keyword. So that's a lot to take in. Let's go ahead and run the subroutine to see all of our results. So I'll press F5, and the total is 412.94. Again, that was with the plus sign. The difference with the minus sign is 207.56, and the price with tax, which we calculated using multiplication and addition, is that value there. Profit ratio, which we calculated using division, is 2.57. The value of the investment after 10 years, which we calculated using an exponent, is the value you see here, just over $148,000. The number of cases available is six, and we calculated that through integer division. And finally, the remainder, which we calculated through modular division, is three. So there will be three bottles after we create cases of 13 bottles a piece. The operators that you see here are similar to those that you would use creating formulas in an Excel workbook. The only two you might not have been familiar with were integer division and modular division, but I think you'll find that all of them are very useful.

Contents