问Excel每200 in计算一次增值

EN

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    ' add reference to Microsoft Scripting Runtime via Menu - Tools - References

    Const Scope = "C2:C5" ' monitoring area
    Const DX = 1 ' horizontal result offset
    Const DY = 0 ' vertical result offset
    Const Buf = 0 ' FIFO buffer size

    Static oData(0 To Buf) As New Dictionary
    Static oIndex As New Dictionary
    Dim rCells As Range
    Dim oCell
    Dim i As Long

    Set rCells = Application.Intersect(Target, Target.Parent.Range(Scope))
    If Not rCells Is Nothing Then
        For Each oCell In rCells
            With oCell
                i = oIndex(.Address)
                .Offset(DY, DX).Value = .Value - oData(i)(.Address)
                oData(i)(.Address) = .Value
                i = i + 1
                If i > Buf Then i = 0
                oIndex(.Address) = i
            End With
        Next
    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Const Scope = "C2:C5" ' monitoring area

    Static oData As New Dictionary
    Dim rCells As Range
    Dim oCell
    Dim dDelta

    Set rCells = Application.Intersect(Target, Target.Parent.Range(Scope))
    If Not rCells Is Nothing Then
        For Each oCell In rCells
            With oCell
                dDelta = .Value - oData(.Address)
                If dDelta <> 0 Then
                    .Offset(0, 1).Value = dDelta
                    oData(.Address) = .Value
                End If
            End With
        Next
    End If

End Sub