Formula Macro

Automating calculations using formula macro

The training video describes a process of automating calculations using formula macro. You learn how to enter data in specific spreadsheet cells and get the result using a formula in a specific cell in a macro.

  • Open a new Excel worksheet
  • Click on Tools in the menu bar, select Macro and from the drop down menu select Visual Basic Editor
  • In the Visual Basic Editor window, click on Insert and select Module
  • In the workspace type sub and follow it with an appropriate macro name like ‘calculation’ and press enter
  • The ‘End Sub’ line is added automatically by the Visual Basic Editor in Excel
  • Between the above lines write your Excel macro code to put data into specified cells as shown in the training video
  • Range(“A1”), for example, accesses cell address A1 (upper or lower case does not matter).
  • To access the value in cell address A1 you can write Range(“A1”) or Range(“A1”).Value. The latter is precise.
  • To tell VBA that you are writing a formula in cell address C1 you write the code Range(“C1”).Formula. Did you notice how the VBA editor offers a menu once you write ‘Range(“C1”).’. This happens only if the syntax or grammar for accessing the cell address is correct
  • Finally define the formula to calculate the total of the data entered as shown. The formula for adding values is wrtten as “=sum(a1:b1)” in quotes and denotes that values from cell address a1 upto values in cell address b1 have to be added. If you had data in cells a1, b1, c1, d1, e1 and wanted to apply the formula in f1 you would write the formula “=sum(a1:e1)”. The main aim of this Excel training video is to make you familiar with the syntax writing a formula in an Excel macro.

Further reading:

Use Excel Formulas/Functions In VBA Macro Code

2 thoughts on “Formula Macro

  1. supriya

    Hello ,

    I have a problem with excel vba will you help me. My problem is I have a sheet which consists of different names which may repeat again also. I need to perform operations on that names. I want a loop which perform that particular operations at the same time the names also should change. Thank you in advance.

  2. vijay

    i have basic income of employee for a year , over which increment is to be added @3% every year , also promotion bonus increment is to be added in gape of every 10 year,

    i want to generate a excel program for such ,

    please guide


Leave a Reply

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