新建一个模块,然后把下面的代码复制进去,保存模块Module1。
使用方法:新建一个文本框,在控件来源写上 =MinOfList(5, -3, Null, 0, 2) 结果会返回“-3”
代码:
Function MinOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMin As Variant 'Smallest value found so far.
varMin = Null 'Initialize to null
For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) or IsDate(varValues(i)) Then
If varMin <= varValues(i) Then
'do nothing
Else
varMin = varValues(i)
End If
End If
Next
MinOfList = varMin
End Function
Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
varMax = Null 'Initialize to null
For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next
MaxOfList = varMax
End Function