office交流网--QQ交流群号

Access培训群:792054000         Excel免费交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

Access VBA自定义求平均值函数MyAvg (适用于Excel VBA, VB6)

2020-04-15 08:00:00
zstmtony
原创
1318

Access自带了DAvg聚合函数求平均值,但只能对表中字段求平均值,无法对提供的多个数字参数求平均值

所以自己做了一个求平均值的自定义函数,同样适用于Access VBA, Excel VBA, VB6


'MyAvg 求平均值

Public Function MyAvg(Optional dblNum1 As Variant, Optional dblNum2 As Variant, Optional dblNum3 As Variant, Optional dblNum4 As Variant, Optional dblNum5 As Variant) As Double


Dim i As Integer
Dim dblSum As Double
If Not IsMissing(dblNum1) Then
i = i + 1
dblSum = dblSum + Val(dblNum1)
End If

If Not IsMissing(dblNum2) Then
i = i + 1
dblSum = dblSum + Val(dblNum2)
End If


If Not IsMissing(dblNum3) Then
i = i + 1
dblSum = dblSum + Val(dblNum3)
End If

If Not IsMissing(dblNum4) Then
i = i + 1
dblSum = dblSum + Val(dblNum4)
End If


If Not IsMissing(dblNum5) Then
i = i + 1
dblSum = dblSum + Val(dblNum5)
End If

If i > 0 Then
MyAvg = dblSum / i
Else
MyAvg = 0
End If


End Function


分享