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

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

**â§ Output:**

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

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

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

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

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

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

**Similar Readings:**

**How to Calculate Percentage above Average in Excel (3 Easy Ways)****Excel VBA to Read CSV File into Array (4 Ideal Examples)****How to Calculate Centered Moving Average in Excel (2 Examples)****Running Average: How to Calculate Using Excelâ€™s Average(â€¦) Function****How to Name a Table Array in Excel (With Easy Steps)**

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

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

**â§Ş 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
```

**â§Ş 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
```

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

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

**How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)****Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)****Excel VBA: Remove Duplicates from an Array (2 Examples)****How to Sort Array with Excel VBA (Both Ascending and Descending Order)****Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)**