包括工作表单元格值测试在内的for循环中的类型不匹配

Question

我在我的VBA宏中收到一个类型错配错误。下面是我的代码的基本部分：

Public Function CalculateSum(codes As Collection, ws As Worksheet) As Double

On Error GoTo ErrorHandler

If ws Is Nothing Then
    MsgBox ("Worksheet is necessery")
    Exit Function
End If


Dim balanceColumnIndex, codesCulumnIndex As Integer
Dim searchStartRow, searchEndRow As Integer
balanceColumnIndex = 17
codesColumnIndex = 4
searchStartRow = 7
searchEndRow = ws.Cells(ws.Rows.Count, codesColumnIndex).End(xlUp).Row

Dim result As Double
result = 0#

For counter = searchStartRow To searchEndRow
    If Len(ws.Cells(counter, codesColumnIndex)) > 0 And Len(ws.Cells(counter, balanceColumnIndex)) > 0 And _
    IsNumeric(ws.Cells(counter, codesColumnIndex).Value) And IsNumeric(ws.Cells(counter, balanceColumnIndex).Value) Then
        If Contains(codes, CLng(ws.Cells(counter, codesColumnIndex).Value)) Then
            result = result + ws.Cells(counter, balanceColumnIndex).Value
            ''' ^^^ This line throws a type-mismatch error
        End If
    End If
Next counter


CalculateSum = result

ErrorHandler:
Debug.Print ("counter: " & counter & "\ncode: " & ws.Cells(counter, codesColumnIndex).Value & "\namount: " & ws.Cells(counter, balanceColumnIndex).Value)

End Function

现在发生的情况是，在将当前行余额添加到result的行中发生类型错配错误，尽管如此：

• searchEndRow = 129，计数器等于130
• 当前地址下的单元格是空的，但不知何故它们通过了长度和数字值的测试(此时我停止调试，IsNumeric(ws.Cells(counter, codesColumnIndex).Value)返回true！

现在我很困惑，我不知道该怎么做。请帮帮忙。

Answer 1

正如评论者所指出的，Cells(...).Value是一个Variant。这意味着运算符可能不会以您预期的方式应用于.Value。对于使用Len或其他字符串操作的测试，明确转换为字符串。例如，不要使用Len(ws.Cells(...))，而是尝试Len(CStr(ws.Cells(...).Value))。这样，您就会知道Len给了您预期的结果。

类似地，在添加到result的地方，请使用result = result + CDbl(ws.Cells(...).Value)确保将Double值一起添加。

要回答您关于不同计算机上不同发生的错误的问题，我最常体验的是，这是所讨论的特定数据。正如一位评论者所指出的，Empty确实是数字的，因为它隐式地转换为0！因此，IsNumeric(Empty)是True。在代码中使用CStr可以防止这一点，因为IsNumeric(CStr(Empty)) = IsNumeric("") = False。使用IsNumeric(CStr(...))可以防止您试图添加0# + ""，这是类型不匹配。因此，也许用户有一个测试数据中没有的空单元格，这就造成了问题。这不是唯一的可能，只是我遇到的最有可能的可能性。