Basic Math Operators – Multiplication
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.