How To Do Calculation In VBA in Microsoft Excel 2010

In this article, you will learn how to perform calculation in Excel using VBA code.
 
Let us understand with an example:

We have some random numbers which we want to multiply & then add with some numbers for each cell.
 
img1
 
If you have to perform calculation on the selected cells, then you need to follow the below steps:

Click on Developer tab

From Code group, select Visual Basic
 
img2
 
Click on Insert, and then Module
 
img3
 
This will create new module.

Enter the following code in the Module

Sub Calculation()

x = InputBox("Multiply by what?", Default:=1)

y = InputBox("Add what?", Default:=0)

 

For Each cell In Selection

cell.Value = cell.Value * x + y

Next cell

End Sub
 
img4
 
Press ALT + F8 shortcut key for opening Macro window & then select the macro.
 
img5
 
Alternatively, you can press F5 to run the code in VBA screen.

After executing the macro, we will get the following input box to enter the number “Multiply by what”
 
img6
 
After entering the value (2 in our example), the next input box will appear in front of you to enter the number to add (2 in our example). Refer below snapshot
 
img7
 
We will get the output. Refer below snapshot
 
img8
 
In this way, we can make calculations according to the requirement of the user.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.