2021/04/11

セル変更履歴をコメントに残す

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• ４．ワークブックモジュール（ThisWorkbook）
• 　４－１．変数宣言・設定部
• 　４－２．システム実行部
• 　　４－２－１．複数シート選択時の処理
• 　　４－２－２．対象セルか否かの判定
• 　　４－２－３．追加文字列の作成
• 　　４－２－４．コメントの追加・作成
• 　　よりみち
• ５．最後に

１．背景

１つのExcelファイル上でセルの値を何回も変更する作業は当然であり、そういう修正を加えながら完成させていくものだと思います。個人での修正もありますし、複数人で作業する場合もあると思います。
しかし「どの場所を、いつ、どのように修正したか」を全て記憶している人はそう多く無く、私などはキリの良いところで「別名で保存」を繰り返し、戻りたくなったら前のファイルを見直す、というようなことをやっています。実に非効率です。

一方、Excelには「ブックの共有」という機能があります（最近はクラウド上にファイルを置く「ブックの共同編集」機能の方が推奨されているようです）。この機能を使用すると図１－１のように「変更履歴」を残すことが可能になります。

図１－１

ただし、この機能はマクロ付きファイル（拡張子が.xlsm）では使えないという制限がありますし、またブック共有にすると「ファイルが重くなる」というデメリットもあります。

そこで今回はブック共有の変更履歴に似た形で、「変更履歴をコメントに記録」するマクロを紹介します。
なお、あくまで「コメント」なので、ユーザー側での編集や削除も可能です。悪意のある場面では信頼性が乏しくなることを御承知下さい。

２．システム概要

今回システムは「特定のセル範囲（プログラム内での設定が必要）」での値変更（含：データ削除）を検知し、その経緯をコメントとして記録する　ものです。
記録の内容は、今回は「変更者の名前（ユーザー名）」「変更日時」「変更後の内容」「数式（数値や文字列の場合は無し）」の４つとしています。

例えば複数人で作業するプロジェクトがあり、その進捗の管理を図２－１のような１つのExcelファイルで行っているとします。
変更が加えられるのは、この中の薄いオレンジ色の「D4セル」「D5セル」「D6セル」「C7～D7（データはC7セル）」ですので、このセル範囲を「対象セル」としてプログラム内に登録しておきます。
すると、対象セルの値を変更するたびにコメントとして経緯が記録され、図２－１の右側のように「セルにマウスを当てれば経緯が表示される」ことになります。

図２－１

記録されるセル内容は以下の通りです。
　・数値・文字列のような値
　・数式の場合は、その結果＋数式自体（数式はカッコ内に表示）
　・数式がエラーになった場合は、エラー内容＋数式（数式はカッコ内に表示）

また複数シートを選択した状態でセル入力操作をした場合には、図２－２のようにメッセージを出し、コメントへの記録はしません。（セル内容の変更は、通常操作の通り、選択した各シート上で変更されます。）
これはExcelが「複数シート選択時は、コメントの操作が出来ない」仕様のためです。コメント記録のために「単一シート選択にしてからコメント記録し、また複数シート選択にする」ことも不可能では無いと思いますが、面倒そうなので「記録せず」としました。

図２－２

その他として、コメント枠の大きさは内容に合わせて大小します。また今回システムには「コメントを消去」する機能はありませんので、履歴が不要になったら、手動でマウス右ボタンから「コメントの削除」で消去して下さい。また、手動でのコメント編集も可能です。

３．プログラムの流れ

シート上の値を変更すると「Workbook_SheetChangeイベント」が発生します。値を変更したセル位置はイベントの中で取得できますので、まず「変更したセルが履歴保存の対象セルか否か」を確認します。
対象セルだった場合には、ユーザー名・日時・セルの値・数式（数式だった場合）の情報を、追加する文字列として組み立てます。
次にそのセルに既存のコメントがあるか否かを確認し、存在するのであれば既存コメントの先頭に追加コメントを結合します。無ければコメントを作成し、追加コメントをコメント内容とします。

図３－１

４．ワークブックモジュール（ThisWorkbook）

今回のマクロはワークブックモジュールのみです。
１つのシートしか使わないのであればシートモジュールにコードを作れば良いのですが、複数のシートを使う場合は、各シートに同じようなコードを作ることになってしまうので、今回はブックモジュールにまとめて作っています。

４－１．変数宣言・設定部

ブックを開く際、共通で使用する変数の宣言と設定をしているのが図４－１です。

1. '==========　⇩(1)　変数宣言・設定部　============
2. Dim ComZone As Variant 　　　 '←履歴を残すセル範囲を格納する配列（Comment Zone の略）
3. Dim ShCnt As Long 　　　　　 '←複数ｼｰﾄ選択状態でｾﾙ入力をした時､MsgBoxを1回のみ表示させる変数
5. Private Sub Workbook_Open()
7. 　ComZone = Array( _
8. 　　　　　　　　　　Sheet1.Range("D4:D6,C7"), _
9. 　　　　　　　　　　Sheet2.Range("D4:D6,C7") _
10. 　　　　　　　　　)
12. 　ShCnt = 1
14. End Sub

図４－１

今回、変更履歴を記録する対象セル範囲は「セル範囲（Range型）の配列」の形で保存します。そのため２行目ではVariant型で宣言をしています。
７～１０行目で対象セル範囲を変数ComZoneに保存します。サンプルファイルでは「D4セル」「D5セル」「D6セル」「C7～D7（データはC7セル）」をユーザー入力セルとしていますので、８行目の「Sheet1.Range("D4:D6,C7")」ように設定します。
Sheet2にも同様の入力範囲があるため、９行目の「Sheet2.Range("D4:D6,C7")」で設定を行っています。この２つのシートのセル範囲を７行目の「Array」関数で配列に格納しています。

１２行目の「ShCnt = 1」は、複数シート選択時に「『コメントに記録しません』のメッセージを１回だけ表示」させるために使うものです。詳細は、図４－２の２３～３３行目で説明します。

４－２．システム実行部

シート上のセル値が変更されると、図４－２のWorkbook_SheetChangeイベントが発生します。
引数として「Sh」と「Target」の２つを受け取ります。文字通り「Sh」は「変更されたシート」、「Target」は「変更されたセル範囲」です。

16. '==========　⇩(2)　システム実行部　============
17. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
19. 　Dim NewCom As String 　　　 '←記録する文字列
20. 　Dim r As Range 　　　　　　 '←変更範囲内の１セル
21. 　Dim i As Long 　　　　　　　 '←カウンタ変数（範囲の数）
23. 　If ShCnt = 1 Then
24. 　　ShCnt = ActiveWindow.SelectedSheets.Count
26. 　　If ShCnt > 1 Then
27. 　　　MsgBox "複数シート選択時は、セル入力履歴が保存できません"
28. 　　　Exit Sub
29. 　　End If
30. 　Else
31. 　　ShCnt = ShCnt - 1
32. 　　Exit Sub
33. 　End If
35. 　For i = 0 To UBound(ComZone, 1)
36. 　　If Sh.CodeName = ComZone(i).Parent.CodeName Then
38. 　　　For Each r In Target
39. 　　　　If Not Intersect(ComZone(i), r) Is Nothing Then
41. 　　　　　If IsError(r.Value) = True Then
42. 　　　　　　NewCom = r.Text
43. 　　　　　ElseIf r.Value = "" Then
44. 　　　　　　NewCom = "Del"
45. 　　　　　Else
46. 　　　　　　NewCom = r.Value
47. 　　　　　End If
49. 　　　　　NewCom = "[" & Format(Now(), "yyyy/mm/dd hh:mm") & "]" & NewCom
51. 　　　　　NewCom = Application.UserName & ":" & NewCom
53. 　　　　　If r.HasFormula = True Then
54. 　　　　　　NewCom = NewCom & "(" & r.Formula & ")"
55. 　　　　　End If
57. 　　　　　If TypeName(r.Comment) = "Comment" Then
58. 　　　　　　r.Comment.Text Text:=NewCom & vbLf & r.Comment.Text
59. 　　　　　Else
60. 　　　　　　r.AddComment
61. 　　　　　　r.Comment.Text Text:=NewCom
62. 　　　　　End If
64. 　　　　　r.Comment.Shape.TextFrame.AutoSize = True
66. 　　　　End If
67. 　　　Next r
69. 　　End If
70. 　Next i
72. End Sub

図４－２

４－２－１．複数シート選択時の処理

２３～３３行目は、複数シート選択時に、「コメントに記録しません」のメッセージを『１回だけ』表示させるコードです。
『１回だけ』にこだわらずに「選択したシート枚数分のメッセージを表示」でしたら、図４－３のようなコードで充分なのですが、たとえば１０枚のシートを選択していると、１つのセルを操作するたびに「１０個のメッセージボックスに対してOKボタンをクリック」する必要が出てくるのです。
それでは不親切だと思い「操作１回に付きメッセージボックスも１回」になるようにしました。もちろん単一シートで作業している時にはメッセージボックスは出ません。

• 　　If ActiveWindow.SelectedSheets.Count > 1 Then
• 　　　MsgBox "複数シート選択時は、セル入力履歴が保存できません"
• 　　Exit Sub

図４－３

まずWorkbook_SheetChangeイベントは「変更されたシートごと」に発生します。そのため複数シート選択時には、選択したシート枚数分のWorkbook_SheetChangeイベントが発生することになります。
Workbook_SheetChangeイベントで受け取る引数「Sh」「Target」について説明します。例えばSheet1とSheet2を選択した状態でA1セルを操作した時は、「Sh=Sheet1、Target=A1」の引数を持つWorkbook_SheetChangeイベントと、「Sh=Sheet2、Target=A1」の引数を持つWorkbook_SheetChangeイベントの２つが発生するのです。（もちろん引数TargetはRange型ですので、親であるSheetの情報も持っています）

そのため、図４－３のようなコードでは、選択シート枚数分のメッセージが発生してしまうのです。
このような状況下でメッセージを１つで終わらすためには、Workbook_SheetChangeプロシージャの外に変数（ShCnt）を置き、その変数に選択シート枚数を代入しつつ、１つずつ減らしていく　という手法を考えました。

まず、手持ちのコマを確認します。図４－４のように、初期値である「ShCnt=1」（通常は単一シートでの作業だから、初期は１と仮置きしました）、選択シート数を数える「ShCnt=選択シート数」、１つずつカウントを減らしていく「ShCnt=ShCnt-1」、そして「メッセージ表示」の４つです。

図４－４

この４つのコマをどのように並べれば、複数（２つ）のシートを選択した時（図４－５）と単一のシートを選択した時（図４－６）の両方に成立するかを考えます。
コードは上から下に、また左から右に進みますので、薄い赤線のカミナリ線（ジグザグ線）は、コードが進む順番を表していることになります。

図４－５


図４－６

まず初期段階（＝ブックが立ち上がった直後）は、図４－１の１２行目で仮設定したように「ShCnt=1」として考えます。
次にセルが操作されて「メッセージを表示させる工程」に入って来た時、その時点ではまだ「ShCnt=1」のままです。この時点で、今操作している状態は「複数シート選択状態か、単一シート選択状態か」を判定することはできません。
ですので図４－５、図４－６の②の位置にメッセージ表示を持ってくることは出来ませんし、「ShCnt=ShCnt-1」を持ってきても状況は変わらないので、「ShCnt=選択シート数」を②の位置に持ってくることになります。

②の位置に「ShCnt=選択シート数」を持ってくることで、複数シート選択時はShCntの値が２以上になるので、その値を元に「メッセージ表示」をするか否かを判断することが出来ます。

次に「ShCnt=ShCnt-1」を考えます。図４－５の１シート目の④の場所で「ShCnt=ShCnt-1」を実行してしまうと、図４－５の２シート目の「ShCnt値」は１つ減って「１」となり、１シート目の入口状態と区別が出来なくなります。ですので、「ShCnt=ShCnt-1」を実行する条件としては「メッセージを表示」しない時、つまりShCnt＞1の時となります。
且つ、「ShCnt=ShCnt-1」を実行する時に「ShCnt=選択シート数」を実行してしまったら、またメッセージを表示してしまうことになりますので、「ShCnt=ShCnt-1」を実行する場所は「ShCnt=選択シート数」とは異なる場所である必要があることも分かります。

結局、図４－５で「ShCnt=ShCnt-1」を実行するのは、１か所になりますので、初期と終了時とで同じShCnt値にするためには、初期にShCnt＝1と置くことで良さそうです（＝最初のShCnt＝1の仮置きが正しかった）。
以上のような条件を踏まえて作ったIf文が、２３～３３行目になります。図４－７に再掲します。

23. 　If ShCnt = 1 Then
24. 　　ShCnt = ActiveWindow.SelectedSheets.Count
26. 　　If ShCnt > 1 Then
27. 　　　MsgBox "複数シート選択時は、セル入力履歴が保存できません"
28. 　　　Exit Sub
29. 　　End If
30. 　Else
31. 　　ShCnt = ShCnt - 1
32. 　　Exit Sub
33. 　End If

図４－７

まず、２３行目でShCnt値が１か否かで分岐させます（図４－５の①の部分）。次に、ShCnt値が１だった場合には２４行目で選択シート数を計算し、その値が１より大きい（２以上）だった場合（２６行目）には、２７行目メッセージを表示させます（図４－５の②③の部分）。
最後に、ShCnt値が１より大きい（２以上）だった時のみ、３１行目で「ShCnt=ShCnt-1」を実行させます（図４－５の④の部分）。
なお、これより下の「コメントへの作業工程」に進んでしまうと、複数シート選択時にエラーが発生しますので、２８行目・３２行目の「Exit Sub」でイベントプロシージャを抜け出させます。

４－２－２．対象セルか否かの判定

３５行目の「For i = 0 To UBound(ComZone, 1)」は、対象セル範囲の配列である「ComZone」の次数（≒シート数）分だけ、Forで回し「１つのシート上の対象範囲」に絞っています。この対象範囲は必ず「単一のシートのセル範囲」になっています。
３６行目の「If Sh.CodeName = ComZone(i).Parent.CodeName Then」では、そのセル範囲の『親（＝シート）』の名前を、引数の「Sh（シート）」の名前と同じか否かを調べています。

この段階で「イベントが発生しているシート ＝ 対象となっているシート」にしておかないと、３９行目の「Intersect(ComZone(i), r)」の処理でエラーが発生します。Intersectメソッドの引数に異なるシートのセル範囲が混ざっているとエラーが発生するからです。

３８行目の「For Each r In Target」では、変更されたセル範囲を１つ１つのセルに分解し、３９行目の「If Not Intersect(ComZone(i), r) Is Nothing Then」で、その１つ１つのセル範囲が設定した対象セル範囲に入っているかを確認しています。

４－２－３．追加文字列の作成

４１～５５行目で、今回コメントに追加する文字列を組み立てています。今回は、４つの項目を下記のような順番に並べています。このコメントの文字列を使って履歴の自動解析もできるように、項目間は「コロン」「カギカッコ」「丸カッコ」で分けています。