2021/04/11

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




1.背景

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

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

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

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

2.システム概要

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

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

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

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

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

3.プログラムの流れ

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

4.ワークブックモジュール(ThisWorkbook)

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

4-1.変数宣言・設定部

ブックを開く際、共通で使用する変数の宣言と設定をしているのが図4-1です。
  1. '========== ⇩(1) 変数宣言・設定部 ============
  2. Dim ComZone As Variant     '←履歴を残すセル範囲を格納する配列(Comment Zone の略)
  3. Dim ShCnt As Long       '←複数シート選択状態でセル入力をした時、MsgBoxを1回のみ表示させる変数
  4. Private Sub Workbook_Open()
  5.  ComZone = Array( _
  6.           Sheet1.Range("D4:D6,C7"), _
  7.           Sheet2.Range("D4:D6,C7") _
  8.          )
  9.  ShCnt = 1
  10. End Sub
図4-1

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

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

4-2.システム実行部

シート上のセル値が変更されると、図4-2のWorkbook_SheetChangeイベントが発生します。
引数として「Sh」と「Target」の2つを受け取ります。文字通り「Sh」は「変更されたシート」、「Target」は「変更されたセル範囲」です。
  1. '========== ⇩(2) システム実行部 ============
  2. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  3.  Dim NewCom As String     '←記録する文字列
  4.  Dim r As Range        '←変更範囲内の1セル
  5.  Dim i As Long         '←カウンタ変数(範囲の数)
  6.  If ShCnt = 1 Then
  7.   ShCnt = ActiveWindow.SelectedSheets.Count
  8.   If ShCnt > 1 Then
  9.    MsgBox "複数シート選択時は、セル入力履歴が保存できません"
  10.    Exit Sub
  11.   End If
  12.  Else
  13.   ShCnt = ShCnt - 1
  14.   Exit Sub
  15.  End If
  16.  For i = 0 To UBound(ComZone, 1)
  17.   If Sh.CodeName = ComZone(i).Parent.CodeName Then
  18.    For Each r In Target
  19.     If Not Intersect(ComZone(i), r) Is Nothing Then
  20.      If IsError(r.Value) = True Then
  21.       NewCom = r.Text
  22.      ElseIf r.Value = "" Then
  23.       NewCom = "Del"
  24.      Else
  25.       NewCom = r.Value
  26.      End If
  27.      NewCom = "[" & Format(Now(), "yyyy/mm/dd hh:mm") & "]" & NewCom
  28.      NewCom = Application.UserName & ":" & NewCom
  29.      If r.HasFormula = True Then
  30.       NewCom = NewCom & "(" & r.Formula & ")"
  31.      End If
  32.      If TypeName(r.Comment) = "Comment" Then
  33.       r.Comment.Text Text:=NewCom & vbLf & r.Comment.Text
  34.      Else
  35.       r.AddComment
  36.       r.Comment.Text Text:=NewCom
  37.      End If
  38.      r.Comment.Shape.TextFrame.AutoSize = True
  39.     End If
  40.    Next r
  41.   End If
  42.  Next i
  43. End Sub
図4-2

4-2-1.複数シート選択時の処理

23~33行目は、複数シート選択時に、「コメントに記録しません」のメッセージを『1回だけ』表示させるコードです。
『1回だけ』にこだわらずに「選択したシート枚数分のメッセージを表示」でしたら、図4-3のようなコードで充分なのですが、たとえば10枚のシートを選択していると、1つのセルを操作するたびに「10個のメッセージボックスに対してOKボタンをクリック」する必要が出てくるのです。
それでは不親切だと思い「操作1回に付きメッセージボックスも1回」になるようにしました。もちろん単一シートで作業している時にはメッセージボックスは出ません。
  •   If ActiveWindow.SelectedSheets.Count > 1 Then
  •    MsgBox "複数シート選択時は、セル入力履歴が保存できません"
  •   Exit Sub
図4-3

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

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

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

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

単一シート選択時の流れ
図4-6

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

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

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

結局、図4-5で「ShCnt=ShCnt-1」を実行するのは、1か所になりますので、初期と終了時とで同じShCnt値にするためには、初期にShCnt=1と置くことで良さそうです(=最初のShCnt=1の仮置きが正しかった)。
以上のような条件を踏まえて作ったIf文が、23~33行目になります。図4-7に再掲します。
  1.  If ShCnt = 1 Then
  2.   ShCnt = ActiveWindow.SelectedSheets.Count
  3.   If ShCnt > 1 Then
  4.    MsgBox "複数シート選択時は、セル入力履歴が保存できません"
  5.    Exit Sub
  6.   End If
  7.  Else
  8.   ShCnt = ShCnt - 1
  9.   Exit Sub
  10.  End If
図4-7

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

4-2-2.対象セルか否かの判定

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

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

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

4-2-3.追加文字列の作成

41~55行目で、今回コメントに追加する文字列を組み立てています。今回は、4つの項目を下記のような順番に並べています。このコメントの文字列を使って履歴の自動解析もできるように、項目間は「コロン」「カギカッコ」「丸カッコ」で分けています。
  ユーザー名: [ 日時  セルの値  数式  

まず41~47行目では、セルの値を決めています。
41行目の「If IsError(r.Value) = True Then」では、セル値がエラーの場合の分岐です。例えば「=10/0」の様な数式を入力してしまった時です。エラーの場合の処理は最初に行い、エラー値を取り除いておかないと、その後の処理の中でエラーが発生してしまうため重要です。
エラーの場合にセルに表示される「#DIV/0!」等の文字は「Textプロパティ」で取得できますので、42行目の「NewCom = r.Text」で「文字としてエラーを記録」します。
なお「エラーは記録しないで無視する」のでしたら、この代わりに「Exit For」とし、内側のFor~Next(38~67行目)を抜けて下さい。

43行目の「ElseIf r.Value = "" Then」は、数値を削除した時の分岐です。なお「Delキー」等でセル値を削除した時以外にも、何も値の無いセルを編集モードにし、そのままEnterキーで確定した時も「削除した」ことで記録されます。本当の削除か否かは、その前後の履歴での判断が必要です。
削除した時には、44行目の「NewCom = "Del"」で、セルの値として「Del」という文字列にしました。なお、もし文字列として「Del」をセルに入力する可能性があるのであれば、エラーっぽく「#Del!」みたいにするのも良いと思います。但し「このような文字列もセルには入力可能」ですので100%区別できるわけではありません。

最後に45行目の「Else」で、その他(通常のセル値は、ここに入ってくれると思っています)の処理をします。46行目の「NewCom = r.Value」でセル値を変数NewComに代入します。なお数式の場合も、その計算結果が入ります。

49行目の「NewCom = "[" & Format(Now(), "yyyy/mm/dd hh:mm") & "]" & NewCom」では、「日時」を追加しています。
日時は「Now()」で得られますのでFormatで書式をわざわざ揃える必要は無い(「NewCom = "[" & Now() & "]" & NewCom」でも記録される)のですが、図1-1で分かるように「ブック共有」の変更履歴が「分の単位まで」でしたので「秒を削る」ためにFormatで書式を作っています。
また、日時はカギカッコで囲むことにしていますので、文字列としてカギカッコも追加しています。

51行目の「NewCom = Application.UserName & ":" & NewCom」は、ユーザー名を追加しています。区切り文字列のコロンを間に挟んでいます。
なお、職場によっては「ユーザー名は皆同じ」場合もあるかと思います。そのような場合は、誰が入力したのか分からなくなりますので、他の情報を使う必要があります。
アンケートの回収と集計方法」の「個有情報の取得」にいくつか紹介してあるので参考にして下さい。

最後に、セルに入れたものが数式だった場合は、53行目の「If r.HasFormula = True Then」が成立しますので、54行目の「NewCom = NewCom & "(" & r.Formula & ")"」で数式を文字列として追加します。また丸カッコで囲みます。
これで、新規に追加する文字列が完成です。

4-2-4.コメントの追加・作成

57~62行目で、コメントを追加します。
57行目の「If TypeName(r.Comment) = "Comment" Then」は「対象セルにコメントが存在するか否か」を確認しています。TypeName(セル範囲.Comment)が戻す値は、図4-8のように2通りあります。
なお「セル範囲」に複数のセル(例えば「Range("D4:D6")」)を指定した場合は、必ず「Nothing」が戻りますので注意が必要です。
コメント有無TypeName(セル範囲.Comment)の値
有りComment
無しNothing
図4-8

コメントがある場合は、文字列「Comment」が得られますので、58行目の「r.Comment.Text Text:=NewCom & vbLf & r.Comment.Text」が実行されます。
式の内容としては、41~55行目で作成した今回のコメント(変数NewCom)を既存のコメント(r.Comment.Text)の先頭に追加します。見易くするため改行(vbLf)を間に入れます。

コメント内で改行するは「vbCrLf」「vbLf」のどちらでも改行となります(vbCrでは改行になりません)が、実際にコメント内でEnterキーで改行をしてみると、それは「vbLf」として記録されていました。ですので「手入力したコメントと同じ」である「vbLf」を改行に使いました。

また58行目の代わりに、Comment.TextメソッドのOverwrite引数を利用すると、以下のようなコードでも同じ結果となります。
 「r.Comment.Text Text:=NewCom & vbLf, Overwrite:=False」
この引数の内容については、下記「よりみち」を参照下さい。

一方で、コメントの無いセルの場合には、60行目の「r.AddComment」で新たにコメントを追加し、61行目の「r.Comment.Text Text:=NewCom」で今回の履歴内容をコメント文としています。

最後に64行目の「r.Comment.Shape.TextFrame.AutoSize = True」で、コメントのサイズを自動化にし、履歴が増えても「全ての履歴が見える」ようにしています。
なお、一度「AutoSize = True」の設定をすれば、そのコメントのサイズはその後も自動で拡大してくれるみたいなので、新規にコメントを作成した時のみ実行(61行目の下で実行)するのもOKです。
但し、手動で作成したコメントに対しては自動化されなくなってしまいますので、今回は「新規時も追記時も毎回実行」するようにしました。

寄り道
Comment.Textメソッドは「コメントの文字列を設定」するもので、3つの引数については図4-9のように他サイトでも説明されています。
引数内容備考
Text追加するテキストを指定
Start文字列を配置する位置番号を指定省略すると既存文字列は削除
OverwriteTrue=テキストを上書き
False=テキストを挿入
既定値=True
図4-9

しかし、今回色々組み合わせて試してみると、ちょっとニュアンスが違う気がします。試した結果は図4-10です。
Textメソッドの引数の組合せ
図4-10

この結果を見る限り、引数の説明は図4-11のような感じになると思われます。
引数内容備考
Text追加するテキストを指定省略時、及び長さゼロの文字列の指定時にはコメント変更されず
Start文字列を配置する位置番号を指定
省略すると先頭を指定した事になる
Overwrite文字列挿入はFalseを指定
0を含む正数を指定すると指定文字位置までを置換
省略時は置換となる
図4-11



5.最後に

もし、セル内容の「変更の経緯」が重要であれば、例えば日毎に進捗を入力するような表の方が一般的だと思います。しかし、経緯よりも現在の結果さえ分かれば良いという場合は、今回サンプルのような「データをどんどん上書き」していく管理法もあると思います。
その場合でも図2-1の「室長コメント」のところで、以前の内容を確認したい時もあると思いますし、急に上司の気が変わって「一週間でどれだけ進んだのか」という急な質問が来る可能性もあります。

そんな時、今回のようにデータの変更履歴が簡単に追うことができる機能があれば、すぐに対応が出来るかもしれません。


セル変更履歴をコメントに残す(it-055.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。