Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)

While working with arrays, we often need to calculate the average of an array in VBA. In this article, I’ll show you how you can calculate the average of an array in VBA with proper examples and illustrations.


VBA Code to Calculate the Average of an Array (Quick View)

Sub Average_of_Array()

Dim MyArray() As Variant
MyArray = Array(11, 34, 79, 77, 21, 40, 136, 99, 81)

Dim Sum As Variant
Sum = 0

For i = LBound(MyArray) To UBound(MyArray)
    Sum = Sum + MyArray(i)
Next i

Dim Average As Variant
Average = Sum / (UBound(MyArray) + 1)

MsgBox "The Average of the Array is: " + Str(Average)

End Sub

Quick View to Calculate the Average an Array in VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


VBA Code to Calculate the Average of an Array (Step-by-Step Procedure)

Here we have an array consisting of the numbers 11, 34, 79, 77, 21, 40, 136, 99, and 81.

Let’s see how we can calculate the average of the array step by step.

⧪ Step 1: Declaring the Array

To calculate the average of the array, first, we’ve to declare the array.

Dim MyArray() As Variant
MyArray = Array(11, 34, 79, 77, 21, 40, 136, 99, 81)

⧪ Step 2: Adding All the Elements

Then we’ve to find out the sum of all the elements of the array. We’ll accomplish this by iterating through a for-loop using the LBound function and the UBound function of VBA.

Dim Sum As Variant
Sum = 0

For i = LBound(MyArray) To UBound(MyArray)
    Sum = Sum + MyArray(i)
Next i

⧪ Step 3: Calculating the Average

Finally, we’ve to divide the sum by the total number of elements of the array. We’ll use the UBound function of VBA for this purpose.

Dim Average As Variant
Average = Sum / (UBound(MyArray) + 1)

⧪ Step 4 (Optional): Displaying the Average

In the end, you can add a Message Box to display the average.

MsgBox "The Average of the Array is: " + Str(Average)

So the complete VBA code will be:

⧭ VBA Code:

Sub Average_of_Array()

Dim MyArray() As Variant
MyArray = Array(11, 34, 79, 77, 21, 40, 136, 99, 81)

Dim Sum As Variant
Sum = 0

For i = LBound(MyArray) To UBound(MyArray)
    Sum = Sum + MyArray(i)
Next i

Dim Average As Variant
Average = Sum / (UBound(MyArray) + 1)

MsgBox "The Average of the Array is: " + Str(Average)

End Sub

Quick View to Calculate the Average an Array in VBA

⧭ Output:

Run this code. And it’ll show the average of the given array, that is, 64.22.

Average of an Array with VBA in Excel

Read More: How to Calculate Average in Excel (Including All Criteria)


3 Examples Involving the Average of an Array with VBA (Macro, UDF, and UserForm)

We’ve seen how we can calculate the average of an array in VBA. Now, let’s see some examples involving the average of an array.


Example 1: Developing a Macro to Calculate the Average of a User-Input Array with VBA

Let’s develop a Macro to calculate the average of a user-input array with VBA.

First, we’ll use an InputBox asking the user to input the array.

By default, VBA will grant the input as a string. To convert it to an array, we’ve to use the Split function of VBA.

Then we’ll use the mentioned code to calculate the average of the array and show it in a Message Box.

So, the complete VBA code will be:

⧭ VBA Code:

Sub Average_of_User_Input_Array()

MyArray = InputBox("Enter the Array: ")
MyArray = Split(MyArray, ",")

Dim Sum As Variant
Sum = 0

For i = LBound(MyArray) To UBound(MyArray)
    Sum = Sum + MyArray(i)
Next i

Dim Average As Variant
Average = Sum / (UBound(MyArray) + 1)

MsgBox "The Average of the Array is: " + Str(Average)

End Sub

⧭ Output:

Run the code. First, it’ll ask the user to input the array. I’ve entered 11,23,45,67,79,77,99,34,78,15,23.

Input Box to Calculate the Average of an Array with VBA

Then click OK. It’ll display the output of the array in a Message Box. It is 50.09.

Read More: How to Calculate Average in Excel Excluding 0 (2 Methods)


Example 2: Creating a User-Defined Function to Calculate the Average of a Range of Cells with VBA

Now, let’s create a user-defined function to calculate the average of a range of cells with VBA.

First, we’ll declare the function with a range of cells as the argument.

Then we’ll convert the range to an array.

After that, we’ll calculate the average of the array with the mentioned code.

Finally, we’ll set the average as the output of the function.

So, the complete VBA code will be:


⧭ VBA Code:

Function Range_Average(Rng As Range)

Dim MyArray() As Variant
MyArray = Application.Transpose(Rng)

Dim Sum As Variant
Sum = 0

For i = LBound(MyArray) To UBound(MyArray)
    Sum = Sum + MyArray(i)
Next i

Dim Average As Variant
Average = Sum / (UBound(MyArray) + 1)

Range_Average = Average

End Function

VBA Code to Calculate the Average of an Array with VBA

⧭ Output:

Here we’ve got a data set with the names of some students and their marks in an examination. Below the marks, there is an empty cell that’ll contain the average of the marks.

Data Set to Calculate the Average of an Array with VBA

Now. we’ll insert our Range_Average function in the empty cell.

Entering User-Defined Function to Calculate the Average of an Array with VBA

Click ENTER. And it’ll contain the average of the marks. That is 61.54.

Related Content: How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)


Similar Readings:


Example 3: Developing a UserForm to Calculate the Average of an Array with VBA

We’ve created a Macro to calculate the average of a user-input array.

Also, we’ve created a user-defined function to calculate the average of a range of cells.

Finally, we’ll develop a VBA UserForm to calculate the average of both a user-input array and a range of cells.

⧪ Step 1: Opening the UserForm

Press ALT+F11 to open the Visual Basic editor in Excel. In the Visual Basic editor, go to Insert > UserForm to insert a new UserForm.

Opening UserForm to Calculate the Average of an Array with VBA

⧪ Step 2: Dragging Tools from the Toolbox

A UserForm called UserForm1 will be opened in the editor. Along with the UserForm, you’ll get a Toolbox containing all the tools available for use in the UserForm.

Move your cursor over the Toolbox and drag a ListBox (ListBox1) to the top left corner of the UserForm.

Similarly, drag two Labels (Label1 and Label2) to the right of the ListBox and two TextBoxes (TextBox1 and TextBox2) to the right of the Labels.

Finally, drag a CommandButton (CommandButton1) to the bottom right corner of the UserForm.

Change the display of the Labels as Range and Array respectively.

Also, change the display of the CommandButton to OK.

Inserting Tools to Calculate the Average of an Array with VBA

⧪ Step 3: Putting Code for ListBox1

Double-click on the ListBox1. A private subprocedure called ListBox1_Click will open. Insert the following code there:

Private Sub ListBox1_Click()

If UserForm1.ListBox1.Selected(0) = True Then
    UserForm1.Label1.Visible = True
    UserForm1.TextBox1.Visible = True
    UserForm1.Label2.Visible = False
    UserForm1.TextBox2.Visible = False   

ElseIf UserForm1.ListBox1.Selected(1) = True Then
    UserForm1.Label2.Visible = True
    UserForm1.TextBox2.Visible = True
    UserForm1.Label1.Visible = False
    UserForm1.TextBox1.Visible = False   

Else
    MsgBox "Select At Least One.", vbExclamation

End If

End Sub

Read More: Excel VBA to Create Data Validation List from Array

⧪ Step 4: Inserting Code for TextBox1

Similarly, double-click on TextBox1. Another private subprocedure called TextBox1_Change will open. Enter the following code there.

Private Sub TextBox1_Change()

On Error GoTo Message

Range(UserForm1.TextBox1.Text).Select

Message:
    x = 6

End Sub

VBA Code to Calculate the Average of an Array with VBA

⧪ Step 5: Entering Code for CommandButton1

Then double-click on the CommandButton1. Another subprocedure called CommandButton1_Click will open. Enter the following code there.

Private Sub CommandButton1_Click()

If UserForm1.ListBox1.Selected(0) = True Then

    Dim MyArray() As Variant
    MyArray = Application.Transpose(Range(UserForm1.TextBox1.Text))

    Dim Sum As Variant
    Sum = 0

    For i = LBound(MyArray) To UBound(MyArray)
        Sum = Sum + MyArray(i)
    Next i

    Dim Average As Variant
    Average = Sum / (UBound(MyArray) + 1)

    MsgBox "The Average of the Array is: " + Str(Average)

ElseIf UserForm1.ListBox1.Selected(1) = True Then

    MyArray2 = UserForm1.TextBox2.Text
    MyArray2 = Split(MyArray2, ",")

    Dim Sum2 As Variant
    Sum2 = 0

    For i = LBound(MyArray2) To UBound(MyArray2)
        Sum2 = Sum2 + MyArray2(i)
    Next i

    Dim Average2 As Variant
    Average2 = Sum2 / (UBound(MyArray2) + 1)

    MsgBox "The Average of the Array is: " + Str(Average2)   

Else
    MsgBox "Select At Least One.", vbExclamation

End If

End Sub

VBA Code to to Calculate the Average of an Array with VBA

Read More: VBA to Transpose Array in Excel (3 Methods)

⧪ Step 6: Inserting Code for Running the UserForm

Finally, go to the Insert > Module option in the VBA toolbar and enter a new module.

Inserting Module to to Calculate the Average of an Array with VBA

Enter the following code in the module.

Sub Run_UserForm()

UserForm1.Caption = "Average of Array"

UserForm1.TextBox1.Text = Selection.Address

UserForm1.Label1.Visible = False
UserForm1.Label2.Visible = False

UserForm1.TextBox1.Visible = False
UserForm1.TextBox2.Visible = False

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.AddItem "Range of Cells:"
UserForm1.ListBox1.AddItem "User-Input Array:"

Load UserForm1
UserForm1.Show

End Sub

⧪ Step 7: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Select the range of cells from the worksheet whose average you want to calculate (C4:C17), then run the Macro called Run_UserForm.

Running Macro to to Calculate the Average of an Array with VBA

A UserForm called Average of Array will be opened. It contains a ListBox with two options: Range of Cells and User-Input Array.

If you select Range of Cells, a TextBox will open asking you to enter the range whose average you want to calculate. By default, it’ll contain the address of the selected range. But you can change it if you want.

Running UserForm to to Calculate the Average of an Array with VBA

Then click OK. A message box will display the average of the input range, which is 61.54 here.

Displaying Output to to Calculate the Average of an Array with VBA

Similarly, if you select User-Input Array the Range TextBox will disappear and a new TextBox will appear asking you to enter the array. Enter the array there.

Here I’ve entered 11,21,79,67,87,157,131,66.

Click OK. And a message box will display you the average of the array. That is 77.375


Things to Remember

In Example 2, we created an Excel function that takes a range of cells as the input and returns the average as the output. But there’s already a built-in function in Excel called the AVERAGE function that does the same.


Conclusion

So, this is the way to calculate the average of an array with VBA in Excel. Hope you’ve gone through all the examples in detail and understood how the code works. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo