2.4 Use variables to add and subtract in Excel VBA

 

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

 

Subtraction

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.

<< Excel VBA Course Menu

 

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney