VBA - 运行时错误 424：需要对象

VBA - run time error 424: object required

提问by Pak Ho Cheung

I got an error of run time error 424: object required. I am stuck in creating a formula to calculate standard deviation. I guess I did something wrong by defining type of range. Any suggestions?

我收到运行时错误 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

采纳答案by Mrig

There are some issues with code.

代码有一些问题。

1. getRangeByYearreturns Variantwhile parameter passed to sdExcludesZerois Excel.Range(accordingly, cell.Valuechanges to cell)
2. Instead of Application.sqrtuse Sqr

1. getRangeByYear返回Variant传给sdExcludesZerois 的参数Excel.Range（相应地，cell.Value更改为cell）
2. 而不是Application.sqrt使用Sqr

See the code below.

请参阅下面的代码。

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

I've commented lines that needs to be changed and added new line below it. Let me know if anything is not clear.

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

Suggestion:Instead of celluse any other variable name.

建议：不要cell使用任何其他变量名。

---

---

EDIT :You just have to change the return type of function getRangeByYearto Range. Hence use,

编辑：您只需将函数的返回类型更改getRangeByYear为Range. 因此使用，

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

instead of

代替

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

Another change will be to replace

另一个变化将是替换

sdExcludesZero = Application.sqrt(sumOfSquareDiff / count)

with

和

sdExcludesZero = Sqr(sumOfSquareDiff / count)

See the complete code below.

请参阅下面的完整代码。

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