Making Calculations - It Doesn’t Get Any Simpler Than That!
Let’s switch to the “Calculations" worksheet. Now, type “Value 1" to A2, “Value 2" to A3, “Product" to A5 and “Sum" to A6. In B2, we will enter the value of “Value1", in B3 the value of “Value 2" and we will make Excel calculate the sum and product automatically, and write the result of the product to B5 and sum to B6.
To do this, we first go to the Visual Basic Editor and right click on the “Microsoft Excel Objects" and select Insert -> Module. Under the tree structure, you will see “Module1." Now go to Insert at the menu and select “Procedure." Make sure that “Sub" is selected under “Type", “Public" is selected under “Scope" and “All local variables as Statics" is unchecked. Type something under the “Name" box as you prefer. I chose “Calculate."
Between the Public Sub Calculate() and End Sub enter the following code:
Range("B5").Value = Range("B2").Value * Range("B3").Value
Range("B6").Value = Range("B2").Value + Range("B3").Value
If you are with me so far, you already saw that we have selected the “Calculations" sheet and defined values for the cells. It is important that you use the .Value notation, otherwise “Range" may return text, which means that you may end up with two numbers put together instead of being added; the sum of 3 and 5 may return 35 instead of 8.
After this point, it is up to you to enter as many calculations as you want. At this point, I suggest you assign variables to cell values to save typing time. For our code, it is better to go like this:
Value_1 = Range("B2").Value
Value_2 = Range("B3").Value
Range("B5").Value = Value_1 * Value_2
Range("B6").Value = Value_1 + Value_2
Homework: Go for a more complex sheet with more calculations.