excel - VBA - 运行时错误 424 : object required

Question

我收到运行时错误 424:需要对象的错误。我一直在创建一个计算标准差的公式。我想我通过定义范围类型做错了什么。有什么建议吗？

Sub result()
    ' I can see the average
    MsgBox Application.Average(getRangeByYear(2, year))
    ' Error is caused in here
    MsgBox sdExcludesZero(getRangeByYear(2, year))
End sub

Function meanExcludesZero(r As Excel.Range)
    Dim count As Double
    Dim sum As Double
    For Each cell In r
        If cell.Value <> 0 Then
            count = count + 1
            sum = sum + cell.Value
        End If
    Next cell
    meanExcludesZero = sum / count
End Function

Function sdExcludesZero(r As Excel.Range)
    Dim mean As Double
    mean = meanExcludesZero(r)
    Dim sumOfSquareDiff As Double, count As Double
    For Each cell In r
        If cell.Value <> 0 Then
            count = count + 1
            sumOfSquareDiff = sumOfSquareDiff + (cell.Value - mean) * (cell.Value - mean)
        End If
    Next cell
    sdExcludesZero = Application.sqrt(sumOfSquareDiff / count)
End Function

Function getRangeByYear(column As Integer, year As Integer)
    ...
    ...
    getRangeByYear = Range(Cells(startIndex, column), Cells(endIndex, column))
End Function

Answer 1

代码存在一些问题。

1. getRangeByYear 返回 Variant，而传递给 sdExcludesZero 的参数是 Excel.Range(因此，cell .Value 更改为单元格)
2. 使用 Sqr 而不是 Application.sqrt

请参阅下面的代码。

Sub result()
    ' I can see the average
    MsgBox Application.Average(getRangeByYear(2, year))
    ' Error is caused in here
    MsgBox sdExcludesZero(getRangeByYear(2, year))
End Sub

'Function meanExcludesZero(r As Excel.Range)
Function meanExcludesZero(r As Variant)
    Dim count As Double
    Dim sum As Double
    For Each cell In r
        'If cell.Value <> 0 Then
        If cell <> 0 Then
            count = count + 1
            'sum = sum + cell.Value
            sum = sum + cell
        End If
    Next cell
    meanExcludesZero = sum / count
End Function

'Function sdExcludesZero(r As Excel.Range)
Function sdExcludesZero(r As Variant)
    Dim mean As Double
    mean = meanExcludesZero(r)
    Dim sumOfSquareDiff As Double, count As Double
    For Each cell In r
        'If cell.Value <> 0 Then
        If cell <> 0 Then
            count = count + 1
            'sumOfSquareDiff = sumOfSquareDiff + (cell.Value - mean) * (cell.Value - mean)
            sumOfSquareDiff = sumOfSquareDiff + (cell - mean) * (cell - mean)
        End If
    Next cell
    'sdExcludesZero = Application.sqrt(sumOfSquareDiff / count)
    sdExcludesZero = Sqr(sumOfSquareDiff / count)
End Function

Function getRangeByYear(column As Integer, year As Integer)
    '...
    '...
    getRangeByYear = Range(Cells(startIndex, column), Cells(endIndex, column))
End Function

我已经注释了需要更改的行并在其下方添加了新行。如果有任何不清楚的地方请告诉我。

建议:使用任何其他变量名称代替cell。

<小时/>

编辑:您只需将函数 getRangeByYear 的返回类型更改为 Range 即可。因此使用，

Set getRangeByYear = Range(Cells(startIndex, column), Cells(endIndex, column))

而不是

getRangeByYear = Range(Cells(startIndex, column), Cells(endIndex, column))

另一个变化是替换

sdExcludesZero = Application.sqrt(sumOfSquareDiff / count)

与

sdExcludesZero = Sqr(sumOfSquareDiff / count)

请参阅下面的完整代码。

Sub result()
    ' I can see the average
    MsgBox Application.Average(getRangeByYear(2, year))
    ' Error is caused in here
    MsgBox sdExcludesZero(getRangeByYear(2, year))
End Sub

Function meanExcludesZero(r As Excel.Range)
    Dim count As Double
    Dim sum As Double
    For Each cell In r
        If cell.Value <> 0 Then
            count = count + 1
            sum = sum + cell.Value
        End If
    Next cell
    meanExcludesZero = sum / count
End Function

Function sdExcludesZero(r As Excel.Range)
    Dim mean As Double
    mean = meanExcludesZero(r)
    Dim sumOfSquareDiff As Double, count As Double
    For Each cell In r
        If cell.Value <> 0 Then
            count = count + 1
            sumOfSquareDiff = sumOfSquareDiff + (cell.Value - mean) * (cell.Value - mean)
        End If
    Next cell
    'sdExcludesZero = Application.sqrt(sumOfSquareDiff / count)
    sdExcludesZero = Sqr(sumOfSquareDiff / count)
End Function

Function getRangeByYear(column As Integer, year As Integer) As Range
    '...
    '...
    Set getRangeByYear = Range(Cells(startIndex, column), Cells(endIndex, column))
End Function