Excel VBA自定义函数costo返回#VALUE!错误排查求助

核心问题原因 #

出现#VALUE!错误的主要原因有3个：

• 数组索引越界：VBA中Array()函数创建的是0基数组（索引从0开始），你的d数组包含12个元素（索引0~11），但循环里i从1到12，当i=12时访问d(i)会超出数组范围，触发运行时错误，反映到工作表就是#VALUE!。
• 参数x的访问方式不严谨：从工作表传入单元格区域时，x是Range对象，直接用x(i)可能因隐式类型转换问题导致错误，需明确取值。
• 三角函数的角度单位不匹配：Excel的Sin/Cos/Asin函数使用弧度计算，如果工作表中输入的d1、p1是角度值，未转换为弧度会导致计算结果超出Asin的有效参数范围（[-1,1]），进而触发错误。

修正后的代码 #

Function costo(x As Range, d1 As Double, p1 As Double) As Double
    Dim d As Variant
    ' 0基数组，对应索引0~11
    d = Array(Array(129, 90), Array(129, 98), Array(142, 81), Array(133, 98), _
             Array(139, 102), Array(156, 144), Array(125, 127), Array(137, 222), _
             Array(213, 241), Array(145, 229), Array(206, 118), Array(152, 167))
    Dim c As Double
    c = 0
    Dim i As Long
    ' 循环索引改为0~11，匹配d数组的0基索引
    For i = 0 To 11
        ' 明确获取x区域中第i+1个单元格的值（Range是1基）
        c = c + 50 * x.Cells(i + 1).Value * distanza(d1, p1, d(i)(0), d(i)(1))
    Next i
    costo = c
End Function

Function distanza(ByVal d1 As Double, ByVal p1 As Double, ByVal d2 As Double, ByVal p2 As Double) As Double
    Dim r As Double
    r = 6371
    ' 若传入的d1/p1/d2/p2是角度，先转换为弧度
    d1 = WorksheetFunction.Radians(d1)
    p1 = WorksheetFunction.Radians(p1)
    d2 = WorksheetFunction.Radians(d2)
    p2 = WorksheetFunction.Radians(p2)
    
    Dim sinVal As Double
    sinVal = Sqr(WorksheetFunction.Power(Sin((d1 - d2) / 2), 2) + _
             Cos(d1) * Cos(d2) * WorksheetFunction.Power(Sin((p1 - p2) / 2), 2))
    ' 确保Asin的参数在[-1,1]范围内，避免报错
    If sinVal > 1 Then sinVal = 1
    If sinVal < -1 Then sinVal = -1
    
    distanza = 2 * r * WorksheetFunction.Asin(sinVal)
End Function

额外注意事项 #

• 调用函数时，x参数需选择连续的12个单元格区域（比如A1:L1），d1和p1选择单个单元格。
• 如果你的d数组中的数值是角度，同样需要在distanza函数中转换为弧度（上面的代码已处理）；如果本身就是弧度，可移除Radians转换代码。
• 调试时可在函数中加入错误捕获，方便定位问题：

  On Error Resume Next
  ' 原有代码
  If Err.Number <> 0 Then
      costo = Err.Description ' 调试时返回错误信息，正式使用可改为返回0或其他标识
      On Error GoTo 0
      Exit Function
  End If
  On Error GoTo 0
  

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