VBA: UserForm如何使用数组从第二列的列表框中计算平均值

Question

我很难找到一种方法，从列表框中获取平均值，然后在用户表单上显示该平均值。我知道你应该使用一个数组，但我很困惑，目前如何数组列表框上的第二列。下面是一个例子，文本框中的数字需要平均起来，然后显示在圆圈中，我有

Answer 1

这可能有助于澄清，它添加了一个点击事件方法到列表框…所以点击列表框中的条目，消息就会告诉你它们对它的了解

Private Sub ListBox1_Click()
MsgBox Me.ListBox1.Value
MsgBox Me.ListBox1.ListIndex
MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 0)  ' The Value of Column 1
MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 1)  ' The Value of Column 2
End Sub

或者，如果您想知道总体平均值，只需将所有第2列条目相加并除以条目数

Private Sub ListBox1_Click()
Dim i As Long
Dim iMax As Long
Dim SubTot As Double

SubTot = 0
iMax = UBound(Me.ListBox1.List)
For i = 0 To iMax
SubTot = SubTot + Me.ListBox1.List(i, 1)
Next i
MsgBox "The Average of ALL people is " & Round((SubTot / (iMax + 1)), 2)
End Sub

Answer 2

获取列表框列的平均值

• 这个例子在工作表上使用了一个Active-X列表框和标签。当然，您将设法将此应用到您的用户表单案例。
• 它使用循环将列表框的第二列的数字写入数组，Application.Average从数组中检索平均值并将其写入标签。

Sub ListBoxAverage()

Dim Players() As Variant: Players = VBA.Array("Tim", "Jon", "Sue")
Dim Numbers() As Variant: Numbers = VBA.Array(1, 2, 4)

Dim rUpper As Long: rUpper = UBound(Players)

Dim Arr() As Double ' Array to Hold the Numbers
Dim r As Long ' List Box Row Counter
Dim n As Long ' Array Element Counter

With Sheet1.ListBox1
' Populate.
.Clear
.ColumnCount = 2
For r = 0 To rUpper
.AddItem
.List(r, 0) = Players(r) ' 1st column
.List(r, 1) = Numbers(r) ' 2nd column
Next r
' Write the values from the 2nd column to a Double array.
ReDim Arr(0 To rUpper)
Dim rValue As Variant
For r = 0 To rUpper ' .ListCount - 1
rValue = .List(r, 1) ' 2nd column
If IsNumeric(rValue) Then ' it's a number
Arr(n) = CDbl(rValue)
n = n + 1
'Else ' it's not a number; do nothing
End If
Next r
End With

With Sheet1.Label1
' Write the average to the label.
If n = 0 Then
.Caption = "No average."
Else
If n < r Then
ReDim Preserve Arr(0 To n - 1)
End If
.Caption = Format(Application.Average(Arr), "0.0")
End If
End With

End Sub