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/Photos.com/Getty 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:
Easy

Other People Are Reading

Instructions

  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

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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