excel - ＃价值！用户编写的时间加权平均函数返回的错误

Question

我正在尝试在 VBA 中编写一个计算时间加权平均值的函数，但是#VALUE!当我尝试在工作表中使用该函数时出现错误。

在尝试调试时，我已经能够CurRPM通过迭代CurOffset和使用CurRng = rng.Offset(CurOffset, 0). 更改的效果CurRng是向下迭代 RPM 列，检查下面的单元格CurRng是否为空、与 相同CurRPM或非空且与 不同CurRPM。

Public Function TIMEAVERAGE(rng As Range) As Long

'Initializing Variables
Dim CurTime As Integer
Dim CurRPM As Integer
Dim CurSum As Integer
Dim CurOffset As Integer
CurOffset = 0
CurSum = 0

'The input cell is the first RPM
'The cell to the left of the input cell is the second RPM
CurTime = rng.Offset(0, -1)
CurRPM = rng

'Keep calculating as long as the cell below CurRng isn't empty
While IsEmpty(CurRng.Offset(1, 0)) = False
    'If the cell below has the same RPM, look at next cell
    If CurRng.Offset(1, 0) = CurRPM Then
        CurOffset = CurOffset + 1
        CurRng = rng.Offset(CurOffset, 0)
    Else:
        'Otherwise, add the previous RPM's contribution to the average
        'CurTime, as shown, is the start of the previous RPM
        CurSum = CurSum + CurRPM * (CurRng.Offset(0, -1) - CurTime)
        'Change CurRPM and CurTime for the new RPM
        CurRPM = CurRng.Offset(1, 0)
        CurTime = CurRng.Offset(0, -1)
        CurOffset = CurOffset + 1
        CurRng = rng.Offset(CurOffset, 0)
    End If

Wend

'Add the contributions of the final RPM
CurSum = CurSum + CurRPM * (CurRng.Offset(0, -1) - CurTime)

'Return the final TIMEAVERAGE
TIMEAVERAGE = CurSum / CurRng.Offset(0, -1)

End Function

如果用户输入下面的单元格，则此程序的预期结果将返回 150 =TIMEAVERAGE(B2)。相反，#VALUE!会返回一个错误。

       A       B        
 ________________    
1|    Time | RPM
2|    0    | 100
3|    1    | 100
4|    2    | 100
5|    3    | 200
6|    4    | 200
7|    5    | 200
8|    6    | 200

标签： excelvba