How to Average a Column in VBA

Written by alexander gokhfeld
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Average a Column in VBA
VBA programs allow users to perform mathematical calculations in Access databases. (Jupiterimages/ Images)

The Microsoft Office Access database program includes a set of aggregate functions, allowing users to perform mathematical calculations. These include averages, counting the number of items in a column and finding the minimum or maximum value in a column of data. You can use these functions in the database queries or in Visual Basic for Applications (VBA) code for the calculation of a single value, such as a sum or column average. The column must contain numeric, currency or date/time data. You need to define a range of data in order to calculate the average of a column in VBA program.

Skill level:


  1. 1

    Click the "Start" button in Windows and select "Microsoft Office," "Microsoft Visual Basic for Applications."

  2. 2

    Click "Open," "New" to create the VBA script for calculating a column average in your Access database.

  3. 3

    Enter the following code in the window:

    Sub ColumnAvg()

    Dim Rng As Range

    Dim i As Long

    Set Rng = Range("B1:B3")

    For i = 3 To 12

    Cells(i, "C").Value = ColumnFunction.Round _
                        (ColumnFunction.Sum(Rng) / 3, 0)
    Set Rng = Rng.Offset(1, 0)

    Next i

    End Sub

  4. 4

    Click "File," "Save" to save the VBA script for calculating an average of the "B" column.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.