如果区域中的所有单元格都是数字，则对区域求和，否则返回错误

Question

注意:我正在传递一系列单元格给函数。区域中的每个单元格都包含一个公式，该公式返回数字或字符串。下面的代码总是返回一个错误。我认为代码正在返回一个错误，因为它正在查看单元格中的公式，而不是公式的结果。我希望代码测试每个单元格中的结果(数字或字符串)，而不是测试产生结果的公式。我认为。value属性会照顾到这一点，但它显然没有。

Function SmartSum(sumRange As Range) As Variant
'returns the sum of the the passed range IFF all elements in the range are numeric
'returns error if any of the elements in the range are not numeric, e.g., a text string
Dim ary As Variant
Dim i   As Long
ary = sumRange
SmartSum = 0
'sum all cells in range if they are numeric
'return an error if they are not
For i = LBound(ary, 1) To UBound(ary, 1)
    If IsNumeric(ary(i, 1)) Or ary(i, 1) = "" Then
        SmartSum = SmartSum + ary(i, 1)
    Else
        SmartSum = CVErr(xlErrValue)
    End If
Next
End Function

Answer 1

空字符串不是数字，所以当您尝试使用+操作符添加SmartSum时，您会得到类型不匹配错误。udf中的所有错误都报告为#VALUE!错误，因此您无法判断是设计返回错误还是实际发生了错误。

也，你试图返回xlErrValue，但这不是真正发生的事情。当代码遇到非数字时，它会将SmartSum更改为错误值。然后，它尝试将下一个值添加到错误值中，这会导致错误，并且函数返回#VALUE!(因为内部错误，而不是因为CVError()函数)。

如果将xlErrValue更改为xlErrNA，函数仍将返回#VALUE!。如果你想返回一个不同的错误，你可以这样重写你的函数。

Function SmartSum(sumRange As Range) As Variant
'returns the sum of the the passed range IFF all elements in the range are numeric
'returns error if any of the elements in the range are not numeric, e.g., a text string
Dim ary As Variant
Dim i   As Long
Dim vReturn As Variant
ary = sumRange
vReturn = 0
'sum all cells in range if they are numeric
'return an error if they are not
For i = LBound(ary, 1) To UBound(ary, 1)
    If IsNumeric(ary(i, 1)) Or Len(ary(i, 1)) = 0 Then
        vReturn = vReturn + ary(i, 1)
    Else
        vReturn = CVErr(xlErrNA)
        Exit For
    End If
Next i
SmartSum = vReturn
End Function

你当然不想这样做，它只是说明正在发生的事情。对于上面的函数，空字符串将导致#VALUE!错误(函数中的错误)，但非数字字符串将导致#N/A!错误(函数返回的错误)。

考虑到空字符串，您可以使用Val()函数强制该值为数字。您已经检查了IsNumeric()，因此您将"强制"的唯一事情是空字符串-其他所有内容都将愉快地转换为数字。

Function SmartSum(sumRange As Range) As Variant
'returns the sum of the the passed range IFF all elements in the range are numeric
'returns error if any of the elements in the range are not numeric, e.g., a text string
Dim ary As Variant
Dim i   As Long
Dim vReturn As Variant
ary = sumRange
vReturn = 0
'sum all cells in range if they are numeric
'return an error if they are not
For i = LBound(ary, 1) To UBound(ary, 1)
    If IsNumeric(ary(i, 1)) Or Len(ary(i, 1)) = 0 Then
        vReturn = vReturn + Val(ary(i, 1))
    Else
        vReturn = CVErr(xlErrValue)
        Exit For
    End If
Next i
SmartSum = vReturn
End Function