Join Curt Frye for an in-depth discussion in this video Creating a calculation using mathematical operators, part of Learning VBA in Excel (2010).
As I've shown you in movies throughout this course, you can perform mathematical calculations within Excel VBA. You use the same operators as you would find in the Excel client program. In this movie, I'll show you a number of examples of how to use arithmetic operators in your code. So I'll open up the VBA, the Visual Basic Editor. The first mathematical operator I'll discuss is the equal to sign. Use it here to assign a value to a variable, so the currency total of the first order is = 310.25, and of the second it's 102.69.
You can use the equal sign in other ways, but I'll show you those in later movies. The second operator is addition, and that is the plus sign here, so we have the current total is equal to the FirstOrder plus the SecondOrder. Next, you have the minus sign, and we use that here to find the difference between two orders, in this case it would be the FirstOrder minus the value of the SecondOrder. Next step is multiplication, which I have here. It is the asterisk, or a capital 8 if you type the way that I do. What this code does is it finds the total the FirstOrder, multiplies the FirstOrder's value by the SalesTax, which is 9%, and then rounds off that value to 2 decimal points.
Next step is division, and this is standard division, or decimal division. Let's say the Two Trees wanted to find out the profit ratio for each bottle, in other words the amount they sell it for versus the amount they cause them to make. They can calculate that by using division. They just the bottle price that they sell it for by the cost per bottle, and then they round that off to 2 digits. Next you have exponentiation, or raising something to a power. You don't use it very often in standard business transactions, but you do when you calculate interest. So let's say that Two Trees wanted to find an investment where their interest was compounded continuously; for that you do need to use exponentiation.
So let's say that you invested $100,000, and the rate was 4%, and the number of years, or the number of periods, was 10. To calculate the future value of the investment, you would take the investment and multiply it by 1, plus the rate, which is 0.04, and raise that value to the exponent, which is the number of periods, which I have made 10, and then the Round function rounds the value off to two decimal places. Now I've already covered decimal division, which allows a remainder, but let's talk about two other types of division: integer division and modular division.
Integer division divides one number by another and discards the remainder. Modular division divides one number by another and keeps the remainder only. If Two Trees wanted to know how many individual bottles they would have left over after putting together the 13 bottle baker's dozen cases, they could use modular division. And now I have a code routine here that walks through each of these calculations, so I'll press F5 and show you how it works. So first, we have the total. This is arithmetic addition. Click OK. This is subtraction, the minus sign. The difference between the two order totals is $207.56.
Next, we have the price with tax of $338.17. That's multiplication. Then we have division, which was the profit ratio. On each bottle that's 2.57. Then we have the investment, which at 4% interest over ten years, $100,000, would be worth $148,024.43. Based on 81 bottles, you can make 6 baker's dozen cases, each of which contain 13, and finally, at the end you would have 3 bottles left over. The mathematical operators in Excel VBA let you calculate any values you need.
You might not use integer division and modular division as often as you use the other operations, but they are handy tools to have at your disposal.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures