Basic Math Operators – Subtraction

Basic Description

 

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:

subtraction

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:

subtraction result

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

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