Basic Math Operators – Division

Basic Description

 

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 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:

division

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:

division result

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment