Excelの変更履歴を記録するVBA

Excelにも昔、変更履歴の記録する機能があったはずなのですがいつのまにか消えているような気がします。

色々な所とやり取りする上で変更履歴が記録できないと色々不便だったりします。

変更してくださいねってお願いして戻ってきたブックと初めのブックの違いを抽出することは以下の記事で紹介しています。

でもはじめから変更したのはここだよと変更者も意識して分かるようになった方が間違いも少なくなるのではないかなと思ったのです。

そこでブックに対して変更したらそこが記録されるようにしたいと思います。

そのためのVBAがこちら。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bfval
Dim afvalx()
Dim cnt As Long
Dim mes As String
Dim nowcom As String
Application.EnableEvents = False
For cnt = 1 To Target.Count
  ReDim Preserve afvalx(cnt)
  afvalx(cnt) = Target(cnt).Formula
Next
Application.Undo
For cnt = 1 To Target.Count
  bfval = Target(cnt).Formula
  Target(cnt).Formula = afvalx(cnt)
  If afvalx(cnt) <> bfval Then
    mes = "を「" & bfval & "」から「" & afvalx(cnt) & "」に変更"
    If TypeName(Target(cnt).Comment) = "Comment" Then
      nowcom = Target(cnt).Comment.Text
      Target(cnt).Comment.Delete
    Else
      nowcom = ""
    End If
    Target(cnt).AddComment _
      nowcom & vbCrLf & "===" & vbCrLf & _
      Replace( _
        Format(Date, "yyyy/mm/dd") & "[" & Application.UserName & "]" & _
        Replace(Target.Address, "$", "") & mes, _
        "「」", "空白")
  End If
Next
Application.EnableEvents = True
End Sub

これを VBEを開いて、変更履歴を付けたい「ブック」のVBAとして貼り付けます。

予想以上に大きくなってしまったのですが、それを説明するためにはちょっと込み入った話をしなければいけません。

まず、変更しているときに範囲選択しているセルの値をすべてafvalxという配列に読み込みます。

その後、元に戻す操作をして、選択している範囲に対してひとつずつ変更前の値と比較して行って変更されていたら変更のメッセージをメモ機能（ちょっと前までコメント機能と言っていた機能）を追加します。でも、前にメモがそのセルに書いてあるとエラーになってしまうのでメモを削除する前に今のメモ内容を記憶してからメモを削除して、新しくメモを作成して今までのメモ内容と合わせて今回の変更のメッセージを入れ、セルの値を変更後の値に変更します。

全体的に何度もセルの値を入れ替えるので、その都度シートを変更したという形で認識されてしまってこのVBAが反応してしまうのでそれを防ぐために、EnableEventsをFalseにして、処理が終わったらTrueにしています。

本当は標準テンプレートのPersonal.xltxのブックに書き込めば全てのブックで動いてくれると良かったのですがそれができないので、変更したい度にそのブックにこれを貼り付けてから動かす方法しかないようです。逆にそうしないと普段ブックにデータを入れているだけでこれが動いてしまうので、面倒ですが一回一回貼ったほうがいいでしょうね。