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
```

**Table of Contents**Expand

**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 **VBA UBound **functionÂ 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 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
```

**â§ Output:**

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

**Read More: **How to Use VBA Average Function in Excel

**Calculate the Average of an Array with VBA: 3 Examples**

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 **VBA Split function**.

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.

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

**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 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
```

**â§ 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 contains the average of the marks.

Now, weâ€™ll insert our **Range_Average** function in the empty cell.

Click **ENTER**. And itâ€™ll contain the average of the marks. That is 61.54.

**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**.

**â§ª Step 2: Dragging Tools from the Toolbox**

*UserForm1 *will be opened in the editor. Along with the **UserForm**, youâ€™ll get a **Toolbox** containing all the tools available for use.

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**.

**â§ª Step 3: Putting Code for ListBox1**

Double-click on the *ListBox1*. A private sub procedure 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
```

**â§ª Step 4: Inserting Code for TextBox1**

Similarly, double-click on *TextBox1*. Another private sub procedure 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
```

**â§ª Step 5: Entering Code for CommandButton1**

Then double-click on the *CommandButton1*. Another sub procedure 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
```

**â§ª Step 6: Inserting Code for Running the UserForm**

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

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*.

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.

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

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**. A message box will display 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.

**Download Practice Workbook**

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

**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.

**Related Articles**

- How to Calculate VLOOKUP AVERAGE in Excel
- How to Find Average with OFFSET Function in Excel
- How to Average Values Greater Than Zero in Excel
- How to Add Average Line to Excel Chart

**<< Go Back to Calculate Average in Excel | How to Calculate in ExcelÂ | Learn Excel**