VB.NET Excel互操作自定义函数遇数值参数报#Value错误求助

错误原因 #

当参数y为数值时，原代码会触发类型不匹配异常，导致Excel返回#Value!错误。

原代码中第一个判断分支是If y.Value2 = "*" And IsNumeric(x.Value2)：

• 当y是数值类型时，y.Value2是数值，和字符串"*"直接做相等比较，VB.NET会尝试进行跨类型转换，这个过程会抛出类型不匹配的异常。
• Excel自定义函数一旦捕获到未处理的异常，就会返回#Value!错误。

原代码 #

Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Public Function EachSubTotal(ByVal x As Excel.Range, ByVal y As Excel.Range) As Double
    Dim ReturnValue As Double
    Dim Done As Boolean
    Done = False
    ReturnValue = -1

    If y.Value2 = "*" And IsNumeric(x.Value2) Then
        ReturnValue = x.Value2 / 1.1025
        Done = True
    ElseIf (y.Value2 >= 0) And IsNumeric(x.Value2) Then
        ReturnValue = x.Value2 * (1 - y.Value2)
        Done = True
    ElseIf (y.Value2 < 0) And IsNumeric(x.Value2) Then
        ReturnValue = x.Value2 * (1 + y.Value2)
        Done = True
    End If

    If Not Done And String.IsNullOrEmpty(x.Value2) Then
        ReturnValue = -2
    End If

    Return Math.Round(ReturnValue, 2)

End Function

修复后的代码及逻辑说明 #

修正后的代码通过先区分参数类型，彻底避免了跨类型比较的问题：

1. 先通过IsNumeric(y.Value2)判断y是否为数值，再处理数值的正负计算逻辑
2. 非数值类型时，再判断是否为"*"并执行对应计算

修复后的代码：

Public Function EachSubTotal(ByVal x As Excel.Range, ByVal y As Excel.Range) As Double
    Dim ReturnValue As Double
    Dim Done As Boolean
    Done = False
    ReturnValue = -1

    If IsNumeric(y.Value2) Then
        If (y.Value2 >= 0) And IsNumeric(x.Value2) Then
            ReturnValue = x.Value2 * (1 - y.Value2)
            Done = True
        Else
            ReturnValue = x.Value2 * (1 + y.Value2)
            Done = True
        End If
    Else
        If y.Value2 = "*" And IsNumeric(x.Value2) Then
            ReturnValue = x.Value2 / 1.1025
            Done = True
        End If
    End If

    If Not Done And String.IsNullOrEmpty(x.Value2) Then
        ReturnValue = -2
    End If

    Return Math.Round(ReturnValue, 2)

End Function

内容的提问来源于stack exchange，提问作者stevie