Home and Learn - Free Excel VBA Course
2.5 Use variables to multiply and divide in Excel VBA
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 it Multiply_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 select Multiply_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 (/).
Try out some division for yourself. Return to your coding window and add a new Sub. Call it Divide_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
Your coding window will then look like this:
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 using As Integer in our code. When you use the As 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
What we're doing here is mixing some addition with division. Our sum is really this:
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 called operator 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.
With operator 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.
One last thing about the division symbol: don't confuse it with the backslash character (\), which is used for something called Integer Division. This is when you take the whole number answer and throw away any "point something" at the end. For example, try this code:
Dim answer As Double
answer = 11 / 2
MsgBox (answer)
As you expect, the answer in the message box is 5.5, because 11 divide by 2 is 5.5. However, change the forward slash into a backslash:
answer = 11 \ 2
Now the answer you get in the message box is just 5: the point 5 at the end has been chopped off. So just be aware that the backslash in Excel VBA gets you Integer Division, not regular division.
In the next part, you'll learn about some more variable types in Excel VBA.
Next Lesson: 2.6 More Variable Types >