用 VBA 讓 Excel 像 Git 一樣記錄修改歷程

1. 前言

Git 版本控制能記錄每次修改的內容、時間與作者，讓你日後追溯任何一次變更。對工程報表或重要試算表來說，這種追蹤能力同樣很有價值，尤其是需要留存修改歷程供查驗的文件。

本文記錄如何用 Excel VBA 實現這個概念，自動偵測儲存格修改，在每次存檔時將詳細記錄寫入專屬工作表，並附加手動備註欄供補充說明。

2. 系統運作原理

系統由三個階段組成：

開啟活頁簿時：對所有工作表的現有資料拍下「快照」(snapshot)，記住每格目前的值。
每次修改儲存格時：即時比對新值與快照中的舊值，若不同則暫存到緩衝區 (buffer)。
按下 Ctrl+S 存檔時：將緩衝區的所有變更一次寫入「修訂記錄」工作表，並自動遞增版本號。

3. 程式碼架構

系統分兩個部分：

位置	負責內容
`ThisWorkbook` 模組	核心邏輯：快照、比對、寫入記錄、自動建立 sheet
各工作表模組	攔截儲存格修改事件，轉交 `ThisWorkbook` 處理

ThisWorkbook 內包含以下 Sub / Function：

名稱	觸發時機	功能
`Workbook_Open`	開啟活頁簿時	初始化 buffer 與 snapshot 字典
`TakeSnapshot`	由 `Workbook_Open` 呼叫	掃描所有工作表，記錄每格目前的值
`LogChange`	由各工作表 Change 事件呼叫	比對新舊值，有變動則暫存到 buffer
`Workbook_BeforeSave`	每次存檔前	將 buffer 寫入「修訂記錄」sheet
`GetOrCreateLogSheet`	由 `BeforeSave` 呼叫	自動建立「修訂記錄」工作表（若不存在）

4. 安裝步驟

4.1. 步驟一：開啟 VBE

在 Excel 按下 Alt + F11，開啟 Visual Basic for Applications 編輯器。若左側「專案總管」視窗未顯示，按 Ctrl + R 開啟。

4.2. 步驟二：找到 ThisWorkbook

在專案總管左側，展開目前活頁簿下的「Microsoft Excel 物件」資料夾（點左邊的 + 展開），雙擊「ThisWorkbook」。

注意：ThisWorkbook 在「Microsoft Excel 物件」資料夾裡面，不是在「模組」資料夾。兩者是不同的位置。

4.3. 步驟三：貼入 ThisWorkbook 程式碼

清空原有內容，貼入以下完整程式碼：

' ============================================================
' 修訂記錄系統 - ThisWorkbook 模組
' ============================================================

Private mChangeBuffer As Collection
Private mSnapshots As Object

' ---------- 開啟活頁簿時初始化 ----------
Private Sub Workbook_Open()
    Set mChangeBuffer = New Collection
    Call TakeSnapshot
End Sub

' ---------- 拍快照：記錄所有工作表目前的值 ----------
Private Sub TakeSnapshot()
    Set mSnapshots = CreateObject("Scripting.Dictionary")
    Dim ws As Worksheet
    Dim cell As Range
    For Each ws In Me.Worksheets
        If ws.Name <> "修訂記錄" Then
            For Each cell In ws.UsedRange
                Dim key As String
                key = ws.Name & "!" & cell.Address(False, False)
                mSnapshots(key) = CStr(cell.Value)
            Next cell
        End If
    Next ws
End Sub

' ---------- 供各工作表呼叫的記錄方法 ----------
Public Sub LogChange(ws As Worksheet, target As Range)
    If ws.Name = "修訂記錄" Then Exit Sub
    If mChangeBuffer Is Nothing Then Set mChangeBuffer = New Collection
    If mSnapshots Is Nothing Then Call TakeSnapshot

    Dim cell As Range
    For Each cell In target
        Dim key As String
        key = ws.Name & "!" & cell.Address(False, False)

        Dim oldVal As String
        oldVal = ""
        If mSnapshots.Exists(key) Then oldVal = mSnapshots(key)

        Dim newVal As String
        newVal = CStr(cell.Value)

        If oldVal <> newVal Then
            Dim record(1 To 6) As String
            record(1) = CStr(Now())
            record(2) = Environ("USERNAME")     ' Windows 登入帳號
            record(3) = ws.Name
            record(4) = cell.Address(False, False)
            record(5) = oldVal
            record(6) = newVal
            mChangeBuffer.Add record
            ' 同步更新快照，避免重複記錄
            mSnapshots(key) = newVal
        End If
    Next cell
End Sub

' ---------- 存檔時將暫存記錄寫入修訂記錄 sheet ----------
' ============================================================
' 修訂記錄系統 - ThisWorkbook 模組
' ============================================================

Private mChangeBuffer As Collection
Private mSnapshots As Object

' ---------- 開啟活頁簿時初始化 ----------
Private Sub Workbook_Open()
    Set mChangeBuffer = New Collection
    Call TakeSnapshot
End Sub

' ---------- 拍快照：記錄所有工作表目前的值 ----------
Private Sub TakeSnapshot()
    Set mSnapshots = CreateObject("Scripting.Dictionary")
    Dim ws As Worksheet
    Dim cell As Range
    For Each ws In Me.Worksheets
        If ws.Name <> "修訂記錄" Then
            For Each cell In ws.UsedRange
                Dim key As String
                key = ws.Name & "!" & cell.Address(False, False)
                mSnapshots(key) = CStr(cell.Value)
            Next cell
        End If
    Next ws
End Sub

' ---------- 供各工作表呼叫的記錄方法 ----------
Public Sub LogChange(ws As Worksheet, target As Range)
    If ws.Name = "修訂記錄" Then Exit Sub
    If mChangeBuffer Is Nothing Then Set mChangeBuffer = New Collection
    If mSnapshots Is Nothing Then Call TakeSnapshot

    Dim cell As Range
    For Each cell In target
        Dim key As String
        key = ws.Name & "!" & cell.Address(False, False)

        Dim oldVal As String
        oldVal = ""
        If mSnapshots.Exists(key) Then oldVal = mSnapshots(key)

        Dim newVal As String
        newVal = CStr(cell.Value)

        If oldVal <> newVal Then
            Dim record(1 To 6) As String
            record(1) = CStr(Now())
            record(2) = Environ("USERNAME")     ' Windows 登入帳號
            record(3) = ws.Name
            record(4) = cell.Address(False, False)
            record(5) = oldVal
            record(6) = newVal
            mChangeBuffer.Add record
            ' 同步更新快照，避免重複記錄
            mSnapshots(key) = newVal
        End If
    Next cell
End Sub

' ---------- 存檔時將暫存記錄寫入修訂記錄 sheet ----------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' 保險：確保快照與 buffer 都已初始化
    If mSnapshots Is Nothing Then Call TakeSnapshot
    If mChangeBuffer Is Nothing Then Set mChangeBuffer = New Collection

    If mChangeBuffer.Count = 0 Then Exit Sub

    Dim logWs As Worksheet
    Set logWs = GetOrCreateLogSheet()

    Dim lastRow As Long
    lastRow = logWs.Cells(logWs.Rows.Count, "A").End(xlUp).Row

    Dim lastVer As Double
    If lastRow < 2 Then
        lastVer = 0
    Else
        lastVer = Val(Replace(logWs.Cells(lastRow, 1).Value, "v", ""))
    End If

    Dim newVer As String
    newVer = "v" & Format(lastVer + 0.1, "0.0")

    Dim i As Integer
    For i = 1 To mChangeBuffer.Count
        Dim rec As Variant
        rec = mChangeBuffer(i)

        Dim writeRow As Long
        writeRow = logWs.Cells(logWs.Rows.Count, "A").End(xlUp).Row + 1

        logWs.Cells(writeRow, 1).Value = newVer
        logWs.Cells(writeRow, 2).Value = rec(1)
        logWs.Cells(writeRow, 3).Value = rec(2)
        logWs.Cells(writeRow, 4).Value = rec(3)
        logWs.Cells(writeRow, 5).Value = rec(4)
        logWs.Cells(writeRow, 6).Value = rec(5)
        logWs.Cells(writeRow, 7).Value = rec(6)
        logWs.Cells(writeRow, 8).Value = ""
        logWs.Cells(writeRow, 2).NumberFormat = "yyyy/mm/dd hh:mm:ss"
    Next i

    Set mChangeBuffer = New Collection
End Sub

' ---------- 自動建立修訂記錄 sheet（若不存在）----------
Private Function GetOrCreateLogSheet() As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Me.Worksheets("修訂記錄")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = Me.Worksheets.Add(After:=Me.Worksheets(Me.Worksheets.Count))
        ws.Name = "修訂記錄"

        ' 建立標題列（含備註欄）
        ws.Cells(1, 1).Value = "版本"
        ws.Cells(1, 2).Value = "修改時間"
        ws.Cells(1, 3).Value = "修改人"
        ws.Cells(1, 4).Value = "工作表"
        ws.Cells(1, 5).Value = "儲存格"
        ws.Cells(1, 6).Value = "修改前"
        ws.Cells(1, 7).Value = "修改後"
        ws.Cells(1, 8).Value = "備註"

        ' 標題列格式
        With ws.Range("A1:H1")
            .Font.Bold = True
            .Interior.color = RGB(68, 114, 196)
            .Font.color = RGB(255, 255, 255)
            .HorizontalAlignment = xlCenter
        End With

        ws.Columns("A:H").AutoFit
    End If

    Set GetOrCreateLogSheet = ws
End Function


' ---------- 自動建立修訂記錄 sheet（若不存在）----------
Private Function GetOrCreateLogSheet() As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Me.Worksheets("修訂記錄")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = Me.Worksheets.Add(After:=Me.Worksheets(Me.Worksheets.Count))
        ws.Name = "修訂記錄"

        ' 建立標題列（含備註欄）
        ws.Cells(1, 1).Value = "版本"
        ws.Cells(1, 2).Value = "修改時間"
        ws.Cells(1, 3).Value = "修改人"
        ws.Cells(1, 4).Value = "工作表"
        ws.Cells(1, 5).Value = "儲存格"
        ws.Cells(1, 6).Value = "修改前"
        ws.Cells(1, 7).Value = "修改後"
        ws.Cells(1, 8).Value = "備註"

        ' 標題列格式
        With ws.Range("A1:H1")
            .Font.Bold = True
            .Interior.Color = RGB(68, 114, 196)
            .Font.Color = RGB(255, 255, 255)
            .HorizontalAlignment = xlCenter
        End With

        ws.Columns("A:H").AutoFit
    End If

    Set GetOrCreateLogSheet = ws
End Function

4.4. 步驟四：為每張工作表加上 Change 事件

在專案總管，依序雙擊每一張需要追蹤的工作表（例如 Sheet1、計算頁、填寫面積等），各別貼入：

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.LogChange Me, Target
End Sub

「修訂記錄」工作表本身不需要貼，系統會自動排除它。

4.5. 步驟五：編譯與存檔

VBE 上方選單點「偵錯 > 編譯 VBAProject」，確認沒有錯誤。
回到 Excel，另存新檔，格式選「Excel 啟用巨集的活頁簿 (*.xlsm)」。
關閉後重新開啟檔案，讓 Workbook_Open 執行初始化。

必須存成 .xlsm，存成 .xlsx 會導致 VBA 程式碼被自動移除。

5. 修訂記錄欄位說明

欄位	說明	範例
版本	每次存檔遞增 0.1	v1.0、v1.1、v2.0
修改時間	儲存格被修改的時間	2026/04/16 14:23:01
修改人	Windows 登入帳號	zongboa
工作表	被修改的工作表名稱	計算頁
儲存格	被修改的儲存格位址	C5
修改前	修改前的原始值	1200
修改後	修改後的新值	1350
備註	手動填寫的補充說明	依甲方要求修正單價

備註欄由系統留空，每筆記錄對應獨立一列，填入備註後不會因為新增記錄而跑位。

6. 遇到的問題與解決方式

6.1. 問題一：出現「找不到方法或資料成員」

原因：VBE 內找不到 LogChange 這個 Public Sub，通常是 ThisWorkbook 模組的程式碼未正確貼入。

解決步驟：

確認 VBE 左側有展開「Microsoft Excel 物件」，且 ThisWorkbook 裡面確實有 Public Sub LogChange 這段。
確認各工作表的呼叫寫法是 ThisWorkbook.LogChange Me, Target。
VBE 選單「偵錯 > 編譯 VBAProject」重新編譯，看是否還有其他錯誤提示。

6.2. 問題二：修改後存檔，修訂記錄沒有任何記錄

依序確認以下四點：

檔案格式：確認是 .xlsm，而非 .xlsx。
重新開啟檔案：貼完程式碼後必須關閉再重開，讓 Workbook_Open 執行才能建立初始快照，否則系統沒有舊值可以比對。
工作表有沒有掛事件：每張要追蹤的工作表都要個別貼上 Worksheet_Change。
巨集安全性設定：「檔案 > 選項 > 信任中心 > 信任中心設定 > 巨集設定」，確認不是「停用所有巨集且不通知」。

6.3. 問題三：不確定 ThisWorkbook 在哪裡

專案總管的「模組」資料夾裡放的是一般模組 (Module1、Module2 等)，而 ThisWorkbook 是在「Microsoft Excel 物件」資料夾裡。兩個資料夾是不同的，要注意展開正確的那個。

7. 注意事項

本系統只追蹤手動輸入造成的儲存格變動。由公式重算產生的值變化不會被記錄。
TakeSnapshot 會掃描所有工作表的 UsedRange，若資料量非常大（數萬列以上），開啟速度可能略慢。
修訂記錄工作表會持續累積，長期使用後可定期手動清理較舊的版本，或另存備份。
若想改為顯示自訂名稱而非 Windows 帳號，把 Environ("USERNAME") 改為固定字串即可，例如：record(2) = "Zongboa"。

8. 結論

這套系統的核心概念是「事件驅動 + 快照比對」，開啟時拍快照、修改時比對、存檔時才批次寫入，既不影響正常操作流程，也不會產生多餘的效能負擔。備註欄採每筆記錄獨立一列的設計，填入備註後永遠跟著對應的修改記錄，不受後續新增記錄影響。

對於工程報表、財務計算或任何需要留存修改歷程的 Excel 文件來說，這個方式比依賴 OneDrive 版本歷程更直觀，也比手動維護修訂日誌更省事。