【Access小品】最值及平均值---函数的可变参数

卡西达

　有版友问能否如同Excel一样横向的求字段的平均值（其中如果字段值为空这不在平均值计算范围内），对于这个问题需要用自定义函数解决。在自定义函数中，要用到可变参数概念，也就是说需要适应带入的参数个数变化。这一方法是在事先无法确定参数数量情况下常用的一种方法。

1. 
   
2. Function DDMax(ParamArray A() As Variant) As Single
   
3. '示例:select *,DDmax(字段1,字段2,字段3,字段4) as 最大值 from 表1
   
4. Dim i As Long
   
5. Dim Mx As Single
   
6. Mx = -1 * 10 ^ 10
   
7. For i = 0 To UBound(A, 1)
   
8.         If IsNumeric(A(i)) = True Then
   
9.               If A(i) > Mx Then Mx = A(i)
   
10.         End If
    
11. Next
    
12. DDMax = Mx
    
13. End Function
    
14. 
    
15. Function DDMin(ParamArray A() As Variant) As Single
    
16. '示例:select *,DDmin(字段1,字段2,字段3,字段4) as 最小值 from 表1
    
17. Dim i As Long
    
18. Dim Mn As Single
    
19. Mn = 10 ^ 10
    
20. For i = 0 To UBound(A, 1)
    
21.         If IsNumeric(A(i)) = True Then
    
22.               If A(i) < Mx Then Mx = A(i)
    
23.         End If
    
24. Next
    
25. DDMin = Mn
    
26. End Function
    
27. 
    
28. Function DDAvg(ParamArray A() As Variant) As Single
    
29. '示例:select *,DDAvg(字段1,字段2,字段3,字段4) as 平均值 from 表1
    
30. Dim i As Long
    
31. Dim S As Single
    
32. Dim C As Long
    
33. S = 0: C = 0
    
34. For i = 0 To UBound(A, 1)
    
35.         If IsNumeric(A(i)) = True Then
    
36.               S = S + A(i)
    
37.               C = C + 1
    
38.         End If
    
39. Next
    
40. If C <> 0 Then
    
41.         DDAvg = S / C
    
42. Else
    
43.         DDAvg = 0
    
44. End If
    
45. End Function
    

复制代码