March 1, 2014

Calculations in Excel Using VBA

Calculations using macros in Microsoft Excel
Once you access the Excel cells using the range or cells property, you can easily perform calculations using macros based on your knowledge of the domain, example finance, in which you may be working. You can add, subtract, average, multiply or divide according to the needs of the situation. Of course, you can use the Excel inbuilt functions or your own functions to perform different calculations. The main idea here is to automate the calculations and hide the formulas from prying eyes.

  • Click on the developer tab
  • Select Visual Basic from the Code group
  • In the Microsoft Visual basic Window click on insert in the menu bar
  • Select Module
  • In the workspace enter the code using the range or cell property and apply an appropriate formula
  • Click on ‘Run’ to view the result

Of course, using the cells or range property you could have also added the labels

The VBA code is given below:

Sub calculate()
Range(“A4”) = “Tom”
Range(“B4”) = 5000
Range(“C4”) = Range(“B4”) * 0.5
Range(“D4”) = Range(“C4”) + Range(“B4”)
End Sub

Watch the video below to see the implementation.

6 thoughts on “Calculations in Excel Using VBA

  1. Superb..I have a question. Each time of calculation, we have to do in vba code section or any automatic one is available?c
    Thanks in advance


  2. Its too good. If we need to apply vlookup formula in 60,000 rows, it gives result in more than 10 mins. how to overcome this?

    Thank you very much

  3. Dear dinesh,
    Am a vba writer… Am currently working on a particular project on vba that has to do with loan given. I want to make my code to be able to deduct customers loan amount to be paid per interval from the customer available balance automatically…. Time duration for the loan payment is there, e.g the customer fill a tenure textbox of 4 i.e loan will be paid in duration of 4months or weeks….. At each month or week interval I want my program to deduct the amount agreed on to be paid at each interval from the available balance.

Comments are closed.