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
原創
377

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


分享