こんにちは！なるーらぼです。なんとか続いております…３回目です。
今回は「変更されたセルを知る」方法についてお話ししていきたいと思います。

管理表でよくあること

たとえば、このような数表で毎日購入するような食品の値段をシコシコと管理していたとします。

そして値段の部分を変更したら、履歴のC列へ日付と前の値段を書いておきたいとします。実際はこんな管理の仕方はしないと思いますが…

ではまず「りんご」がいま100円ですが、120円になったとします。
そうしたら値段を120円に変更しますよね。

変更したあと、マクロで履歴を更新したいとすると、直接的に書いてしまうなら以下の感じでしょうか。

Sub note_mu()
　With Sheet1
　　If .Range("B2").Value <> 100 Then
　　　.Range("C2").Value = Format(Now, "yyyy-mm-dd") & " に100円から変更されました。"
　　End If
　End With
End Sub

とりかえずこれで思った通りにはなりました。

しかし、大問題があります。

このコード、すべての食品について１つずつ書かなければなりませんよね。さらに、変更された値段をすべてコードに更新していかなければならなくなります。

対策その１

さすがに毎回更新するのはつらいですし、できれば更新された商品の履歴だけをこのようにしたいですよね。そこで、まずは変更されたセルだけを知りたいと思います。
ワークシートに変更があったときに発生するイベントというものがあります。「Worksheet_Change」イベントです。

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

このイベントは引数にRangeオブジェクトをとります。ということは、この引数に変更されたセルが含まれている可能性が高いですね！さっそく調べてみましょう。

Private Sub Worksheet_Change(ByVal Target As Range)
　Debug.Print Target.Address
End Sub

このようにして、「りんご」の値段を変更してみてください。すると、「イミディエイトウィンドウ」に次のように出力されていると思います。

この「$B$2」は変更されたセルのアドレスです。ということは、どのセルが変更されたかはわかるようになりました。あとは元のセルの値をどうするか、そして変更された値を使って履歴に更新をするということです。

ここで恐ろしいことに注意しなければなりません。

というのも、変更イベントの中でさらに変更をするということは再度の変更イベントが発生してしまうということです。ということは、どこかで変更が止まらなければ無限に更新していってマクロが停止しないということが発生してしまいます。

まずは変更が発生したときに相手にするセルを限定しましょう。ここでは2列目の変更しか相手にする必要がありません。ですから、イベントの引数にわたされたRangeオブジェクトから変更されたセルはどの列なのかを取得して、対象以外の列であればイベントを終了させるようにしましょう。

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Column <> 2 Then
　　Exit Sub
　End If
　Debug.Print "変更されたセル：" & Target.Address
End Sub

これでひとまずB列だけに限定して処理をすることができるようになりました。

対策その２

次はとりあえず変更されたことだけを履歴に記入するようにします。得られているRangeオブジェクトから、一つ右のセルに書き込めばいいので、元の値段はともかくとして日付と定型テキストを書き込むことにします。

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Column <> 2 Then
　　Exit Sub
　End If
　With Target.Offset(, 1)
　　.Value = Format(Now, "yyyy-mm-dd") & " に更新されました。"
　End With
End Sub

これで実現したいことの半分くらいは完成したでしょうか。あとは元の値段ですね…

元の値段を保存しておくにはいくつかの方法が考えられます。１つはワークシート内のどこかへセルの変更イベント時に書き込んでおくというものです。そうすれば履歴へ書き込むときに先にその値を取得しておいて、それからその値もマクロから更新するということです。

例として、監視対象の列から５つ右のセルへ書き込んでおくことにしたとすると…

うまくいったように見えますが、この方法には問題があります。最初にB列と同じ値が５つ右のセルには入っていないということです。とすると、元の値は空っぽのセルの値ですので、暗黙的にゼロに変換されて「0円から更新されました」ということになってしまいます。

あるいは、知らずにもしくは「忘れて」ゴミだと思って削除してしまうかもしれません。そうすると機能しなくなってしまいます。

もちろん対処する方法がないわけではありません。

例えばワークブックが開かれたときのイベントである「WorkBook_Open」イベントでB列の値を５つ右のセルにも入れていくという方法があるでしょう。

対策その３

ワークシート内に書き込んでしまうと削除されてしまう可能性があります。ですから、ブックを開いたときなどにメモリ内に記憶させるという方法もあるでしょう。

少しExcelの動作が鈍くなってもよければ選択されたものが変更されたという「Worksheet_SelectionChange」イベントで毎回決まった変数へ状態を読み込んでおくということも１つの方法です。範囲が決まっているのであれば、その範囲を配列として確保しておけばそことの比較をすることで実現できるでしょう。

まず選択セルの変更イベントに配列としてB列を取り出す命令を書いておきます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　oldvalues = Sheet1.Range("B2:B9")
End Sub

この「oldvalues」は標準モジュールに定義しておく必要があります。このとき、数値だからといって厳密にIntegerにしてはいけません。セルの値には何が入るかわかりませんし、配列が入るのでVariantにしておきましょう。

Public oldvalues As Variant

あとは更新イベントを以下のように書き換えれば実現できます。

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Column <> 2 Then
　　Exit Sub
　End If
　With Target.Offset(, 1)
　　.Value = Format(Now, "yyyy-mm-dd") & " に" & oldvalues(Target.Row - 1, 1) & "円から更新されました。"
　End With
End Sub

しかし、これもまた問題があります。

例えばB列の任意のセルを選択したまま保存して、次回開いたときに一度も選択位置を動かさずにB列のセルを更新するとエラーが発生します。これは、まだ値を取り込んでいないoldvaluesを参照したためであり、一度選択しているセルを動かすと発生しなくなるというやっかいな問題があります。とはいえ、その程度であればブックが開いたときのイベント処理に記述すれば回避することはできます。

それとは別に問題が潜在してしまうのが、変数「oldvalues」です。publicになっている変数はどのマクロからもアクセスすることができるため、配列の中身を削除されてしまうとひとたまりもありません。

ではどうしたらいいんだ？ということになりますが…まだマシな方法を考えると、Excelの別シートをデータベースのようにしておいて、どの食品がその時点でいくらだったかを記録しておくのがよいでしょう。値段を変更したら、そちらから直近の値段を参照してきて履歴列へ書き込み、最新の値段自体はデーターベースのようになっているシートへ日付とともに書き込むということです。

結局それかよ…と思うかもしれませんが、これでも問題はあります。Excelですので気軽にシートの削除をすることができるでしょうし、列や行の削除もなされることを考えると万全ではないでしょう。

だからこそ、データベースファイルやデーターベースサーバーというものがあるのだと思っていただけるとよいかと。

最後に

今回は変更された位置を知ることと、変更される前の値をどうやって扱うかについてちょっとだけ考えてみました。こういうことがあるので、わざわざ入力フォームを準備している会社も多いことでしょう。