More than 1 year has passed since last update.

Excelで行の削除を契機としたイベントを拾う

Posted at 2023-02-05

前振り

vbaはほとんど門外漢です
wshやるならjavascript派です
いつもはC#を使い込んでいます

Excelファイルを操作するなら、C#からEPPlus使うかpythonでOpenPyxlかな？
という感覚で、充分だと思ってました（個人的には―――　勿論今もそう思ってます）

でも、Excelマクロ使わないといけないお仕事が降ってきてしまったので、悪戦苦闘の日々を送っている状況でー

行が削除されたタイミングで、と或る処理を起動させたいな

と思ったのですが、
なかなかこれが…

てな中で、「ま、こんな感じなら取り敢えず許せるかなー」と云う所に行きついたので、ここに纏めてみたい

まずは、先人達の知恵を借りる

Worksheet_SelectionChangeとWorksheet_Changeを抱き合わせて行が削除された事を知る、事ができるよ
と云うのが、大勢の意見だった
流れとしては―

選択領域が変化した時に、着目カラムの有効最終行を覚えておく
⇒Worksheet_SelectionChangeを利用
行が削除された際にはWorksheet_Changeイベントが発動するので、
着目しているカラムの有効最終行が減っていれば、行削除したと思って良い

と云う感じかな
イベントハンドラにはRangeが渡されるので、どの行が削除されたかは推して知るべし

なんですけどねー
ちょっと引っかかる

どこら辺が引っかかるか

気持ちは分かるんですけど、

行の削除を監視したいのに、裏で握っているデータが有効最終行番号
だったり
有効最終行を判定するのに、特定のカラムだけが監視対象
だったり
ま、そこら辺は監視対象を増やしゃ良いのかも知れませんけど、個別の対応甚だしい
有効最終行以降の行を削除した場合には、行削除の判定をしてくれない
この仕様の正否については微妙
有効最終行の監視対象カラムのデータを消すと、行削除と判定されたり…

色々きちんと「個別に」実装していかないと、「あれ？」みたいな目にあいそう

それでは対応方針をば

Worksheet_SelectionChangeとWorksheet_Changeを駆使することに異論はないとして―

C#erとしてはObject-orientedな実装を目指したい
やはり行の削除を監視するのだから、裏で握るべきは「行番号」ではなく、対象の行を特定できる「Range(≒Object)」でしょう

汎用性を求めて、ある特定のカラム位置に依存することなく、あくまでも「行の削除」というアクションに反応したい
となるとやはりイベントハンドラの様なものに仕上げられるだろうか？

ちゅー事は「行削除」イベントなので、有効最終行以降の行が削除されたとて、きちんと反応して欲しい様な気がする
（対象となる行が有効であるか無効なのか、はユーザが判断する事だからね）

はじめの一歩

ユーザ操作として、行全体の選択⇒行の削除、の流れなのでー

Worksheet_SelectionChangeで受け取ったtargetが選択行そのものを表している筈で、それをそのまま握る
次に削除操作で起こったWorksheet_Changeで受け取ったtargetと上で握ったRangeの違いを見てみよう

取り敢えず、３行目を選択してみたところだが、

target・selrowともに$3:$3で、３行目を表している
で、そのまま３行目を削除すると…

selrowの方はAddressにアクセスできなくなっている
つまり、握っておいたRangeが無効になっているという事だろう
しかし

selrow自体はRange型を維持している様で中身だけが無効になっている、と云うややこしい状態だ
いっその事Nothingにしてくれればいいのに…

本当に行が削除されたのか？

それはさておき、Worksheet_Changeイベントは、シート上の「どこか」が変更された、と云う状況を表しているに過ぎない
先に握っているRangeが無効になったからと云って、それが「行」の削除を表しているかは不確定である
しかも、「行」を表していたかどうかは、Worksheet_Changeイベントを受け取ってしまってからでは分からない
何故なら、嘗て選択範囲を表していたselrow自体が無効になってしまっているからだ

なので、現在の状態から一昔前の選択状態を推測するしか手立てはない
幸いExcelの特性から

削除前後で選択範囲は変わらない

と云う事が分かっているので、削除範囲はWorksheet_Changeに渡されたtargetから得られる、と云う結論に落ち着く

そこで、行が削除されたか否かは、targetが「行」を表しているかどうかの判定に掛かってくる
ここら辺は、先人の知恵を掘り起こすと、target.Address = target.EntireRow.Addressであれば行選択状態である事が判定できる、との事である

ここまでの纏め

ここで全体をコード化してみよう

option Explicit

Dim SelRange As Range

Private Property Get IsSelRangeInvalid() As Boolean              //  SelRangeが無効であるか否かを判定する
    On Error Resume Next
    IsSelRangeInvalid = True
    IsSelRangeInvalid = Not (SelRange.Address <> vbNullString)
End Property

Private Sub Worksheet_Activate()
    Set SelRange = Selection
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Set SelRange = target
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
    If target.Address = target.EntireRow.Address Then            //  行の選択状態であった場合
        If IsSelRangeInvalid Then                                //  嘗ての選択範囲が無効であるなら、行削除された
            Debug.Print(target.Address & "の行が削除されました")
        End If
    End If
End Sub

有効最終行に関係なく、とにかく行が削除されたならば反応する様になりました(^^)

もうちょっと…

さて、
行削除の判定はうまくいったのだが、もうちょい汎用的にならんものか？
カラムの削除とか、行・列の挿入時にも何とか対応したい…

とか思うと、全てのコードがWorksheet_ChangeSub内に集まっているのは頂けない
と云う事で、ひねくり出したのが以下のコード

Private Sub Worksheet_Change(ByVal target As Range)
    On Error Resume Next
    If target.Address = target.EntireRow.Address Then            //  行の選択状態であった場合
        If IsSelRangeInvalid Then                                //  嘗ての選択範囲が無効であるなら、行削除された
            Application.Run CodeName & ".UserEvent_RowDelete", target
        End If
    End If
    On Error GoTo 0
End Sub

Private Sub UserEvent_RowDelete(ByVal target As Range)
    Debug.Print(target.Address & "の行が削除されました")
End Sub

通常のワークシートイベントの様に「UserEvent_RowDelete」なる名前を持ったSubを定義してあると、行削除の際にそこが呼び出され、定義されていなければ何もしない、と云う仕掛け（イベントハンドラチックでしょ？）

ただ未定義の場合、行削除の都度On Errorに引っ掛かるのが如何なものか？
感は否めないが、ま、そこら辺気になるなら工夫次第で何とかしてね、という含みを持たせて一応完了、と致します

最後の仕上げ

行削除だけではなく、行・列の削除・挿入に対するユーザイベントも処理できるようにしてみました
考え方は行削除の水平展開って感じですので、ここでは詳細言及しませんが理解いただけると思います

ご活用頂ければ幸いで御座います―

option Explicit

Dim SelRange As Range

Private Property Get IsSelRangeInvalid() As Boolean              //  SelRangeが無効であるか否かを判定する
    On Error Resume Next
    IsSelRangeInvalid = True
    IsSelRangeInvalid = Not (SelRange.Address <> vbNullString)
End Property

Private Sub Worksheet_Activate()
    Set SelRange = Selection
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Set SelRange = target
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
    On Error Resume Next
    If target.Address = target.EntireRow.Address Then            //  行の選択状態であった場合
        If IsSelRangeInvalid Then                                //  嘗ての選択範囲が無効であるなら、行削除された
            Application.Run CodeName & ".UserEvent_RowDelete", target
        ElseIf target.Address <> SelRange.Address Then           //  選択範囲がずれているので、行挿入です
            Application.Run CodeName & ".UserEvent_RowInsert", target
        End If
    End If
    If target.Address = target.EntireColumn.Address Then         //  列の選択状態であった場合
        If IsSelRangeInvalid Then                                //  嘗ての選択範囲が無効であるなら、列削除された
            Application.Run CodeName & ".UserEvent_ColumnDelete", target
        ElseIf target.Address <> SelRange.Address Then           //  選択範囲がずれているので、列挿入です
            Application.Run CodeName & ".UserEvent_ColumnInsert", target
        End If
    End If
    On Error GoTo 0

    ：  通常のWorksheet_Changeハンドラの記述はここら辺で…

End Sub

以上

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up