Home and Learn - Free Excel VBA Course

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.

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.

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 >