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 for Excel: vba loops to calculate average of values in Excel cells





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

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.