# vba 自定义function返回值_Excel VBA解读（133）： 编写高效的Function过程——让代码运行更快的技术...

excelperfect

Function AverageTol(theRange,dTol)

For Each Thing In theRange

If IsNumeric(Thing) Then

If Abs(Thing) > dTol Then

AverageTol = AverageTol + Thing

lCount = lCount + 1

End If

End If

Next Thing

AverageTol = AverageTol / lCount

End Function

Function AverageTolA(theRange AsRange, dTol As Double)

Dim oCell As Range

Dim lCount As Long

For Each oCell In theRange

If IsNumeric(oCell) Then

If Abs(oCell) > dTol Then

AverageTolA = AverageTolA +oCell

lCount = lCount + 1

End If

End If

Next oCell

AverageTolA = AverageTolA / lCount

End Function

Function AverageTolC(theRange AsRange, dTol As Double)

Dim vArr As Variant

Dim v As Variant

Dim lCount As Long

On Error GoTo FuncFail

'将单元格区域转换为variant型数组

vArr = theRange

For Each v In vArr

If IsNumeric(v) Then

If Abs(v) > dTol Then

AverageTolC = AverageTolC + v

lCount = lCount + 1

End If

End If

Next v

AverageTolC = AverageTolC / lCount

Exit Function

FuncFail:

AverageTolC = CVErr(xlErrNA)

End Function

vArr = theRange

vArr = theRange.Value2

Dim d As Double

Dim r As Double

On Error GoTo skip

For Each v In vArr

d = CDbl(v)

If Abs(d) > dTol Then

r = r + d

lCount = lCount + 1

End If

skip:

Next v

02-11 6086
04-11 1万+
09-28