Worksheet_Changeイベントの引数Target(Excel)

Worksheet_Changeイベントの引数Target

Worksheet_Changeイベントについては、イベントを起こすセル範囲を限定するのによく使う。

引数「Target」に関する注意事項

Worksheet_Changeイベントが発生したときに、プロシージャに渡される引数Targetについて、ちょっと気をつけておいた方が良いことに気づいたので、備忘録的に記しておく。

まず、Worksheet_Changeのイベントプロシージャとして、次のコードを書いておく。

Private Sub Worksheet_Change(ByVal Target As Range)
  Debug.Print "引数TargetのCountプロパティ：" & Target.Count
End Sub

引数TargetのCountプロパティをイミディエイト・ウインドウに表示するだけのプロシージャ。

フィルハンドルでドラッグしたとき

こんなふうに、ドラッグしてコピーしたときの引数Targetは、

これでお分かりのように、ドラッグした範囲全てである。

行ごと削除した場合

こんなふうに、行を丸ごと選択して、削除する。

147456!!!!!!!!

すさまじい数のRangeオブジェクトが渡されている。

行ごと／列ごと削除の場合に何もせずにExitする

Targetの中身を調べて、その中身次第でイベントプロシージャの処理を実行するかどうかを分岐したいとき、列ごと削除や行ごと削除された日には、すさまじい回数の計算が生ずることになる。かといって、通常の操作におけるセルの上限個数なんて決められない場合がある。

たとえば、フィルハンドルで値をコピーしたときにはそれぞれのセルの値に応じて処理をしたい、というようなとき、ドラッグする範囲の上限なんて決められない。そんなときに、【列ごと削除した】とか【行ごと削除した】ようなときにはイベント処理をしない、というふうにできれば良い。

次のようなコードを考えた。

リスト１

With Target
  If .Count Mod Rows.Count = 0 Or _
     .Count Mod Columns.Count = 0 Then Exit Sub
End With

こいつをWorksheet_Changeプロシージャの先頭に入れる。

要するに、TargetのCountプロパティがRows.CountとかColumns.Countで割り切れる場合は、行ごと／列ごと変化したとみなして処理を飛ばすわけだ。

行ごと削除してみると、TargetのCountプロパティが98304になっていることが分かる。

Columns.Countの値（１行あたりのセルの総数）は16384。

ご覧のように、98304は16384で割り切れるので、何もせずにExitすることになる。

おわりに

Targetに複数のセルが渡されたときは、Target.Valueとか書いているとエラーになるので、注意が必要。

追記

よく考えたら、

TargetのCountプロパティがRows.CountとかColumns.Countで割り切れる場合は、行ごと／列ごと変化したとみなして処理を飛ばす

というのは余りにも乱暴なやり方だった。

値を書き換えたセル範囲のセルの数（Countプロパティ）がたまたまRows.CountとかColumns.Countの倍数だったりすると、行・列削除だとみなされてしまうことになる（まあ、そんなことは滅多にないだろうけれど）。

それはいくらなんでも、いくらなんでもそれはご勘弁願いたい。というわけで、コードを書き換えてみた。

っていうか、ついでにセル範囲が行または列全体かどうかを判定するFunctionを作ってみた。

リスト１改

Public Function isWholeRowORColumn(ByVal targetRange As Range) As Boolean
  With targetRange
    If .Rows.Count = Rows.Count Or _
       .Columns.Count = Columns.Count Then _
      isWholeRowORColumn = True: Exit Function
  End With
  isWholeRowORColumn = False
End Function

引数で渡されたセル範囲targetRangeの縦幅（targetRange.Rows.Count）がシート全体の縦幅（Rows.Count）と等しかったら列全体、targetRangeの横幅（targetRange.Columns.Count）がシート全体の横幅（Columns.Count）と等しかったら行全体が変化したとみなす。

問題は、列全体または行全体に値が書き込まれた場合だな……。