Home and Learn - Free Excel VBA Course

When you store numbers inside of variables, one of the things you can do with them is mathematical calculations. We'll start with simple addition.

Return to your code from the previous section.
Set up another Sub and call it **Add_Numbers**. Inside of **Sub** and
**End Sub** type the following code:

Dim Number_1 As Integer

Dim Number_2 As Integer

Number_1 = 10

Number_2 = 20

Worksheets(1).Range("A1").Value = "Addition
Answer"

Worksheets(1).Range("B1").Value = Number_1 + Number_2

Your coding window will then look like this:

Return to Excel. Select any cells that have numbers in them, then press the
Delete key on your keyboard to get rid of them. Now add a new button. From the
Assign Macro dialogue box select your **Add_Numbers** Sub. Change the button
text to **Addition**. Click away from the button to deselect it. Now click
the button again. The result should be this (you'll need to widen the **A**
column):

We've done two things here: we've used VBA to add some text inside of cell A1, and we've put the result of our addition into cell B1. The line that puts the text into cell A1 is this:

Worksheets(1).Range("A1").Value = "Addition Answer"

This time after the equal sign we have some direct text. If you want direct
text to the right of the equal sign you need to enclose it between double quotation
marks. We have the text **Addition Answer** between double quotes. The **Value**
of **Range A1** on **Worksheets(1)** is then set to this new text.

The part that does the adding up is this line:

Worksheets(1).Range("B1").Value = Number_1 + Number_2

To the right of the equal sign we now have this:

Number_1 + Number_2

We have already stored a value of 10 inside of the variable called **Number_1**.
Inside of the variable called **Number_2** we have stored a value of 20.
Just like in ordinary mathematics, the plus symbol is used to add up. VBA will
add the two values for you and store the answer into the cell B1.

You can add up more than two numbers, of course. If we had a third variable, we could have done this:

Worksheets(1).Range("B1").Value = Number_1 + Number_2 + Number_3

You can add up numbers that aren't inside of variables. So this is perfectly fine:

Worksheets(1).Range("B1").Value = Number_1 + Number_2 + 30

Or this:

Worksheets(1).Range("B1").Value = 10 + 20 + 30

Or you can store your addition in just one variable:

Number_1 = 10 + 20 + 30

Worksheets(1).Range("B1").Value = Number_1

In the VBA programming language, the minus sign (-) is used to subtract one value from another. Again, you can use actual values, values stored in variables, or a combination of the two.

Go back to your code. Set up another Sub and call it **Subtract_Numbers**.
Add the following lines of code:

Dim Number_1 As Integer

Dim Number_2 As Integer

Number_1 = 450

Number_2 = 387

Worksheets(1).Range("A2").Value = "Subtraction
Answer"

Worksheets(1).Range("B2").Value = Number_1 - Number_2

Your coding window will then look like this:

Notice that we're using the same variable names, here: **Number_1** and
**Number_2**. This is perfectly acceptable as both are enclosed within their
own **Sub** and **End Sub**. The thing you can't do is to set up two variables
with the same name between the same and **Sub** and **End Sub** lines.
But if they are in two different Subs, that's OK. (This is known as Variable
Scope.)

Return to your spreadsheet and add a new button. From the Assign Macro dialogue
box select your **Subtract_Numbers** Sub. Change the button text to **Subtraction**.
Test it out and you should see a new line appear on your spreadsheet:

So we set up two variables and stored values of 450 and 387 in them. We added some direct text to cell A2, and used the following subtraction for cell B2:

Worksheets(1).Range("B2").Value = Number_1 - Number_2

The only difference between this and the addition code (apart from the B2 cell reference) is the use of the subtraction symbol (-) in place of the addition symbol (+). When the code is run, VBA looks at the values in the two variables. It then deducts one from the other. The answer is then stored as the Value for the Range on Worksheets(1).

Just like addition, you can use more than one variable, a mixture of variables, or no variables at all, as in the following lines:

Number_1 - Number_2 - Number_3

Number_1 - 10

300 - 200

You can also mix the addition and subtraction. Amend the code for your **Subtract_Numbers**
to this (the new or amended 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 = 50
Number_2 = 40
Number_3 = 30**

**Answer = Number_1 + Number_2 - Number_3**

Worksheets(1).Range("A2").Value = "Subtraction
Answer"

**Worksheets(1).Range("B2").Value = Answer**

We've set two new variables here:

Dim Number_3 As Integer

Dim Answer As Integer

After setting up the variables, we have this:

Number_1 = 50

Number_2 = 40

Number_3 = 30

Answer = Number_1 + Number_2 - Number_3

The first three lines just store the numbers into the variables. The fourth line is where we perform a calculation. The first thing we do is add the value stored in Number_1 (50) to the value stored in Number_2 (40). Once this addition is performed (50 + 40), we deduct the value stored in the variable called Number_3 (30).

Return to your spreadsheet and click your button. You should see a value of 60 appear in cell B2. The reason it does so is because of this line:

Worksheets(1).Range("B2").Value = Answer

The answer to our calculation has been stored in the variable called Answer. We then use this variable as the Value for the Range B2 in Worksheets(1).

But what we're really doing is just this:

Dim Answer As Integer

Answer = 50 + 40 - 30

Worksheets(1).Range("B2").Value = Answer

In the next lesson, you'll see how to multiply and divide with Excel VBA.

**Next Lesson:** 2.5 Multiply and Divide >