Excel VBA需求：跨工作簿单元格更新时添加时间戳并计算差值

Let’s break down how to implement your requested functionality with VBA, tailored to your setup of WorkBook1 (31 sheets named (01)-(31)) and WorkBook2 as your mirror/log workbook.

Core Goal Recap #

• Monitor changes to columns B and C in any sheet of WorkBook1
• Record a timestamp of each change in WorkBook2
• Calculate the time difference between the updated B and C values (assuming these columns store time-formatted data)

Step 1: Add Change Detection Code to WorkBook1 #

Open WorkBook1, press Alt + F11 to launch the VBA Editor. Double-click the ThisWorkbook module in the left Project Explorer pane, then paste this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Only react to single-cell changes in columns B or C
    If Not Intersect(Target, Sh.Range("B:C")) Is Nothing And Target.Cells.Count = 1 Then
        Dim wb2 As Workbook
        Dim logSheet As Worksheet
        Dim timestamp As Date
        Dim timeDiff As Variant
        Dim wsName As String, cellAddr As String
        Dim bVal As Variant, cVal As Variant
        
        ' Capture current date/time of the change
        timestamp = Now()
        
        ' Get context from WorkBook1
        wsName = Sh.Name
        cellAddr = Target.Address
        bVal = Sh.Range("B" & Target.Row).Value
        cVal = Sh.Range("C" & Target.Row).Value
        
        ' Calculate time difference (only if both cells are valid times)
        If IsDate(bVal) And IsDate(cVal) Then
            timeDiff = Abs(cVal - bVal) ' Absolute value to avoid negatives
            timeDiff = Format(timeDiff, "hh:mm:ss") ' Format for readability
        Else
            timeDiff = "Invalid time values"
        End If
        
        ' Locate WorkBook2 (update the filename/extension if needed)
        On Error Resume Next
        Set wb2 = Workbooks("WorkBook2.xlsx")
        On Error GoTo 0
        
        If wb2 Is Nothing Then
            MsgBox "WorkBook2 isn't open! Please open it first to log changes.", vbExclamation
            Exit Sub
        End If
        
        ' Set up or access the log sheet in WorkBook2
        On Error Resume Next
        Set logSheet = wb2.Sheets("ChangeLog")
        On Error GoTo 0
        
        If logSheet Is Nothing Then
            ' Create log sheet if it doesn't exist
            Set logSheet = wb2.Sheets.Add(After:=wb2.Sheets(wb2.Sheets.Count))
            logSheet.Name = "ChangeLog"
            ' Add header row
            logSheet.Range("A1:F1").Value = Array("Timestamp", "Sheet Name", "Cell Changed", "B Value", "C Value", "Time Difference")
            logSheet.Range("A1:F1").Font.Bold = True
            logSheet.Columns("A:F").AutoFit
        End If
        
        ' Write data to the next empty row
        Dim nextRow As Long
        nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1
        
        logSheet.Cells(nextRow, "A").Value = timestamp
        logSheet.Cells(nextRow, "B").Value = wsName
        logSheet.Cells(nextRow, "C").Value = cellAddr
        logSheet.Cells(nextRow, "D").Value = bVal
        logSheet.Cells(nextRow, "E").Value = cVal
        logSheet.Cells(nextRow, "F").Value = timeDiff
        
        ' Format timestamp column for clarity
        logSheet.Cells(nextRow, "A").NumberFormat = "yyyy-mm-dd hh:mm:ss"
        logSheet.Columns("A:F").AutoFit
    End If
End Sub

Step 2: Customize for Your Setup #

• WorkBook2 Filename: Update Workbooks("WorkBook2.xlsx") to match your actual file (use .xlsm if WorkBook2 has macros).
• Log Sheet Name: Change "ChangeLog" to your preferred sheet name in WorkBook2.
• Time Difference Unit: To calculate differences in minutes/hours instead of hh:mm:ss, replace the timeDiff line with:

  timeDiff = DateDiff("n", bVal, cVal) ' Difference in minutes
  

Step 3: Enable Macros #

• Save WorkBook1 as a macro-enabled workbook (.xlsm format) to keep the code.
• Enable macros when opening WorkBook1 (adjust Excel’s macro security settings if needed).

Alternative: Log to Matching Sheets in WorkBook2 #

If you want to log changes directly in the corresponding sheet (e.g., changes in WorkBook1's (01) go to WorkBook2's (01)), replace the log sheet section with this code:

' Replace the central log sheet code with this:
Dim targetSheet As Worksheet
On Error Resume Next
Set targetSheet = wb2.Sheets(wsName)
On Error GoTo 0

If targetSheet Is Nothing Then
    MsgBox "Sheet " & wsName & " doesn't exist in WorkBook2. Can't log this change.", vbExclamation
    Exit Sub
End If

' Find next empty row in column A (adjust column as needed)
nextRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1

' Write data to the matching sheet
targetSheet.Cells(nextRow, "A").Value = timestamp
targetSheet.Cells(nextRow, "B").Value = cellAddr
targetSheet.Cells(nextRow, "C").Value = bVal
targetSheet.Cells(nextRow, "D").Value = cVal
targetSheet.Cells(nextRow, "E").Value = timeDiff

Troubleshooting Tips #

• WorkBook2 Not Found: Add Set wb2 = Workbooks.Open("C:\Full\Path\To\WorkBook2.xlsx") after the "If wb2 Is Nothing" check to auto-open it.
• Invalid Time Values: Ensure columns B and C in WorkBook1 are formatted as time (Home tab > Number Format > Time).
• Macro Not Triggering: Double-check the code is in WorkBook1’s ThisWorkbook module, not a regular module.

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