In the last lesson, you used variables to add and subtract with Excel VBA code. In this lesson, you'll learn how to multiply and divide.
Multiplication
In programing languages, the multiplication sign is the asterisk (*). So if you want to multiply 10 by 5 in VBA you could do it like this:
Dim Number_1 As Integer Dim Number_2 As Integer Dim Answer As Integer Number_1 = 10
Number_2 = 5
Answer = Number_1 * Number_2
Worksheets(1).Range("A3").Value = "Multiplication Answer" Worksheets(1).Range("B3").Value = Answer
Try it out for yourself. Return to your coding window. Add another Sub and call itMultiply_Numbers. In between Sub and End Sub type the code above.
The code is more or less the same as before. The only differences are the cell references (A3 and B3) and the multiplication sign (*). Your coding window should look like this:
Once you have added the code, return to your spreadsheet. Add a new button and
selectMultiply_Numbers from the Assign Macro dialogue box. Change the text on the button as before. When you click your button, you should see a new line added:
As with Addition and Subtraction, you can use more than two numbers or variables in your calculations. So these are fine:
Answer = Number_1 * 10
Answer = Number_1 * Number_2 * Number_3 Answer = Number_1 * Number_2 * 10
You can mix the Addition, Subtraction and Multiplication, but you need to take care. For example, what is the correct answer to the sum below?
Answer = 10 * 2 + 5
If you do the sum from left to right you'd first multiply the 10 and the 2 to get 20. Now add the 5 to get and answer of 25. However, if you work form right to left, you'd first add the 5 and the 2 to get 7. Multiply 7 by 10 and you'd get 70, a totally different answer!
VBA works things out from left to right. But you can force the answer you need by using round brackets:
Answer = 10 * (2 + 5)
The round brackets above surround the 2 + 5. VBA takes this to mean you want to add these two numbers first. Once it has an answer it will then do the rest of the calculation. It's a good idea to use round brackets to avoid any confusion.
Division
The symbol to use when you want to divide numbers is the forward slash (/). (Quite bizarrely, though, you can also use the back slash (\) without getting any error messages. You may get errors in your calculations, though)
Try out some division for yourself. Return to your coding window and add a new Sub. Call itDivide_Numbers. In between Sub and End Sub, type the following code:
Dim Number_1 As Integer Dim Number_2 As Integer Dim Answer As Integer Number_1 = 10
Number_2 = 5
Answer = Number_1 / Number_2
Worksheets(1).Range("A4").Value = "Division Answer" Worksheets(1).Range("B4").Value = Answer
Return to Excel and add a new button to your spreadsheet. From the Assign Macro dialogue box select your Divide_Numbers Sub. Change the text on the button. When you click your
new button, you should see a new line appear:
Now go back to your code. Change Number_2 from 5 to 4:
Number_1 = 10 Number_2 = 4
So we're now dividing 10 by 4. Return to Excel and click your Division button. What answer do you get? Instead of the expected 2.5 you still get 2! The reason VBA has chopped off the .5 at the end is because we're usingAs Integer in our code. When you use theAs
Integer variable type you only get whole numbers, not fractions. To get a "point something" you need to use a different variable type. You'll learn more about the different variable types shortly. For now, change your code to this (the new lines are in bold):
Dim Number_1 As Integer Dim Number_2 As Integer
Dim Number_3 As Integer
Dim Answer As Integer
Number_1 = 8
Number_2 = 8 Number_3 = 4
Answer = Number_1 + Number_2 / Number_3
Worksheets(1).Range("A4").Value = "Division Answer" Worksheets(1).Range("B4").Value = Answer
Answer = 8 + 8 / 4
You may think that this says "first add 8 and 8 then divide by 4". The answer you'd be expecting is 16 / 4, which is 4. However, try out the code by clicking the button on your spreadsheet and you'll find that the answer you actually get is not 4 but 10! So what's going on?
The reason you get 10 and not 4 is because of something calledoperator precedence. All this means is which of the mathematical operators (+, -, * /) has priority. VBA sees division as more important than addition, so it does the dividing first. Replace the / symbol with the * symbol and you'll find that multiplication is also more important than addition. So the answer to this sum:
Answer = 8 + 8 * 4
is 40, according to VBA, and not 64.
Withoperator precedence you have to take into account the following:
Division and Multiplication are done before addition and subtraction
Division and Multiplication have equal priority and so are calculated from left to right, as
long as there's no addition and subtraction to do
Addition and subtraction have equal priority and so are calculated from left to right, as long
as there's no division and multiplication to do
If the above is somewhat confusing, just remember to use round brackets to make it clear to VBA what you want to do:
Answer = (8 + 8) / 4
In the above sum, VBA will now add 8 to 8, because of the round brackets. The answer to whatever is between the round brackets will then get divided by 4.
In the next part, you'll learn about some more variable types in Excel VBA.