在VBA代码中添加逐元素减法计算行的技术修改需求

针对需求，已在指定代码行下方新增逐元素差值计算逻辑，完整代码如下（新增部分已用注释标注）：

Dim RangeValues As Object 'global (or could use Static inside the event handler)
Dim startTime

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim elapsedTime As Double ' Stores the elapsed time
    Dim logSheet As Worksheet
    Dim oldValues As Variant, newValues As Variant, diffValues As Variant
    Dim rowNum As Long, numColumns As Long
    Dim i As Long, LogRanges, el
   

    'assuming all ranges to be logged are single-row >1 cell...
    LogRanges = Array("N20:FB20", "N21:FB21", "N22:FB22", "N24:FB24", "N26:FB26", "N30:FB30", "N31:FB31", "N32:FB32", "N33:FB33", "N34:FB34", "N35:FB35", "N38:FB38", "N40:FB40", "N42:FB42", "N44:FB44", "N48:FB48", "N49:FB49", "N50:FB50")

    Select Case Target.Address(False, False)
        Case "G11" 'G11 was selected
            ' Start the stopwatch and capture old values
            startTime = Now
            Set RangeValues = CreateObject("scripting.dictionary")
            For Each el In LogRanges 'capture current values from each range
                With Me.Range(el)
                    RangeValues(.Address) = .Value
                End With
            Next el
        Case "H11" 'H11 was selected
            ' Stop the stopwatch, capture new values, and log the information
            Set logSheet = ThisWorkbook.Sheets("Log - Ratio")
            rowNum = logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Row + 3

          ' Assuming the target sheet name is "TargetSheet" (replace with actual sheet name)

          ' Set targetSheet = ThisWorkbook.Sheets("Ratios sommaire")


          For Each el In LogRanges
                With Me.Range(el)
                    oldValues = RangeValues(.Address)
                    newValues = .Value
                End With
                numColumns = UBound(oldValues, 2)
                        ' Add a new column for the subtraction results


                With logSheet.Rows(rowNum)
                    .Cells(1).Value = startTime
                    .Cells(1).Offset(1).Value = Now
                    .Cells(2).Resize(2).Value = Application.UserName
                    .Cells(3).Resize(2).Value = el 'range address
                    .Cells(4).Value = "OLD"
                    .Cells(5).Value = .Cells(Me.Range(el).Row, "J").Value  ' Retrieve value from row N and column 10
                    .Cells(6).Resize(1, numColumns).Value = oldValues
                    .Cells(4).Offset(1).Value = "NEW"
                    .Cells(5).Offset(1).Value = .Cells(Me.Range(el).Row, "J").Value  ' Retrieve value from row N and column 10
                    .Cells(6).Offset(1).Resize(1, numColumns).Value = newValues
                    
                    ' --- 新增差值计算逻辑开始 ---
                    .Cells(4).Offset(2).Value = "DIFF (NEW-OLD)"
                    .Cells(5).Offset(2).Value = .Cells(Me.Range(el).Row, "J").Value ' 同步对应行J列的值
                    ' 逐元素计算newValues与oldValues的差值
                    ReDim diffValues(1 To 1, 1 To numColumns)
                    For i = 1 To numColumns
                        diffValues(1, i) = newValues(1, i) - oldValues(1, i)
                    Next i
                    .Cells(6).Offset(2).Resize(1, numColumns).Value = diffValues
                    ' --- 新增差值计算逻辑结束 ---
                    
                End With
                rowNum = rowNum + 4 ' 新增一行后调整行偏移量
            Next el
    End Select
End Sub

修改说明 #

1. 新增diffValues变量存储差值数组
2. 在newValues写入日志后，添加了差值计算与写入逻辑：
   • 标注差值行标识为DIFF (NEW-OLD)
   • 同步对应行的J列值
   • 循环逐元素计算newValues - oldValues的结果
   • 将差值数组写入日志表的下一行
3. 调整rowNum增量从+3改为+4，适配新增的差值行

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