﻿ VBA for Excel 2007 tutorial-loop to compute average of values

# VBA for Excel 2007 tutorial-loop to compute average of values

## VBA loop: compute average of values

In this VBA example, you will learn to use VBA loop(while loop) to calculate the average of values in Excel cells. The result of the calculation will be placed immediately below the range of values that are populated in to the cells automatically by VBA code.

To run this example VBA code, you will need a Form and a CommandButton.

VBA code for this example:

Private Sub CommandButton1_Click()
Dim s As Long
Dim i As Integer
i = 1
s = 0
'Populate values in Excel cells
Range("A1") = 34
Range("A2") = 20
Range("A3") = 40
Range("A4") = 50
Range("A5") = 98
Range("A6") = 87
Range("A7") = 90
Range("A8") = 78

Range("A1:A8").Select 'Select the range of values to sum
While i <= Selection.Count 'Sum values in Excel cells using while loop
s = s + Range("A" & i) ' then the average can be calculated
i = i + 1
Wend

'Show average result in the Excel cell below the range of values
Range("A" & Selection.Count) = "Average: " & s / Selection.Count
Range("A" & Selection.Count).Font.Bold = True 'Make the font bold
Range("A" & Selection.Count).Font.Color = RGB(100, 10, 255) ' Coloring the result
Range("A:A").Columns.AutoFit 'Make the column width fit the text

End Sub