【Excel VBA】セル内の値が変更された時点で、プログラムを実行する方法

皆さんこんにちは、エンジニアのIMMRです。

VBAのプログラムって、普通のプログラムのように全て値等のデータをセットして、それから

プログラムを実行して、出力を得る。というものだけでないんです。

「セルの値を変更」したり、「新しいシートを作成」したり、「右クリック」をしたり、と一定の動作を実施したことで

プログラムを実行させることができるんです。

それが「イベントプロシージャー」です。その方法について説明します。

先ほども説明しましたが、「イベントプロシージャー」は、さまざまな動作において起こるイベントです。

ここでは、その中の１つである「セル内の値が変更された時点」でプログラムが動作する方法を説明します。

サンプルのブックとしては、下記を使用して、

B列に値が入るごとにC列に結果を表示させるようにします。

８０点以上なら・・・青文字で”合格”と表示

それ以外格なら・・・赤文字で”不合格”と表示

することにします。

図１　サンプルブック

１．イベントプロシージャーの設定方法

イベントプロシージャーは、普通のプログラムを作成する時とは、違う方法で行います。

その方法をまず、説明します。

①「Visual　Basic」をクリックします。

図２　設定方法１

↓

②「ThisWorkbook」をダブルクリックします。　※モジュールを作成するわけではありません。

図３　設定方法２

↓

③赤枠部分のv（矢印）部分をクリックします。

図４　設定方法３

↓

④「Workbook」をクリックします。

図５　設定方法４

↓

⑤赤枠部分のv（矢印）部分をクリックします。

図６　設定方法５

↓

⑥イベントで動作するためのリストが出てきます。ここで内容を選択します。

図７　設定方法６

↓

⑦今回は「SheetChange」を選択します。

図８　設定方法７

↓

⑧赤枠のところが、セルの値が変化した度に実行されるところです。

図９　設定方法８

赤枠のところに今回の処理を行うためのプログラムを記載していきます。

２．サンプルプログラムの設定

先ほどの手順でイベントプロシージャーを作成すると

「Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)」

が作成されます。

そして、「Sh」や「Target」を使ってどこのセルが変更されたかを確認することができます。

例えば、

シート名を確認したい場合　Sh.Name　　※シート「テスト」で実施した場合、「テスト」が格納されます

変更したセルのアドレス　Target.Address　※セル（B4）で実施した場合「＄B＄４」が格納されます。

変更したセルの行　Target.Row　※セル（B4）で実施した場合「４」が格納されます。

変更したセルの列　Target.Column　※セル（B4）で実施した場合「２」が格納されます。

以上のことより下記のサンプルプログラムを作成しました。

サンプルプログラム

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Cells(Target.Row, 2) > 80 Then
        Cells(Target.Row, 3) = "合格"
        Cells(Target.Row, 3).Font.Color = RGB(0, 0, 255)
    Else
         Cells(Target.Row, 3) = "不合格"
         Cells(Target.Row, 3).Font.Color = RGB(255, 0, 0)
    End If

End Sub

これは、値が設定された（セル）の左から２つ目の列（B列）の値が

８０より大きい場合「合格」という文字を３つ目の列（C列）にセットしてフォントの色を青にする。

それ以外（８０以下）の場合「不合格」という文字を３つ目の列（C列）にセットしてフォントの色を赤にする。

といったプログラムです。

３．サンプルプログラムの実行

では、プログラムを実行していきます。

実行とは言っても、いままでのようにプログラム自信を実行させるのではなく、

イベントの内容に沿った作業を実行します。（ここでは、セルの値変更）

図１０のようにセル（B4）に「９０」という値を入力します。
そして、Enterキーを押して決定します。

図１０　プログラム実行前１

↓

すると、プログラムを実行してないのにも関わらず、

図１１のようにセル（C4）に青文字で”合格”と表示されます。

図１１　プログラム実行後１

続いて、図１２のようにセル（B5）に「７０」という数値を入力して、

同じようにEnterキーを押して決定します。

図１２　プログラム実行前２

↓

すると、同様に自動で図１３のようにセル（C５）に赤文字で”不合格”と表示されます。

図１３　プログラム実行後２

これで予定どおり、数値を入力してメッセージを表示することができました。
・８０点以上なら・・・青文字で”合格”と表示
・それ以外格なら・・・赤文字で”不合格”と表示

で問題なく、出来ているように見えます。

しかし、いくつか不具合な点（バグ）が含まれているのです。

その１つを説明します。

４．サンプルプログラムの不具合な点について

不具合な点の１つがセルの範囲です。

例えば、図１４のように
全く関係のない場所（セルのF6）に値を入れて同じようにEnterキーを押して決定します。

図１４　プログラム実行前３

↓

図１５のようにC列の箇所に”不合格”をいう文字が表示されてしまいました。

図１５　プログラム実行後３

これは、セル（F６）に値を入力した時点で

セルの値を変更したのでプログラムが動いてしまい、

何も入力していないセル（B6）の値をみて、ブランク（値が何もない）すなわち、８０以下と

判断して、”不合格”という文字が表示されてしまったからです。

今回の場合、B列のセルに値を入力された場合のみ、プログラムを実行するという条件を追加する必要があります。

５．サンプルプログラムの不具合修正

以下のサンプルプログラム２のように

B列の場合のみ、プログラムを実行するという設定が必要になります。

考え方はIF文を使って、値が変更された左から２つ目のセルだけを対象にします。

これにより、どのセルの値変更を行ってもプログラムは動作するが

左から２つ目、つまりB列でなければ以降の作業は行われなくなります。

サンプルプログラム２

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If (Target.Column = 2) Then　　　　　　　　　　　　　　　　　　　　　　　・・・追加箇所
    If Cells(Target.Row, 2) > 80 Then
        Cells(Target.Row, 3) = "合格"
        Cells(Target.Row, 3).Font.Color = RGB(0, 0, 255)
    Else
         Cells(Target.Row, 3) = "不合格"
         Cells(Target.Row, 3).Font.Color = RGB(255, 0, 0)        
    End If
End If　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・・・追加箇所　

End Sub

６．サンプルプログラムの不具合修正の実行

先ほどと同様に関係のないセルに値を入れて行ってみます。

セル（F6）に値を入力して、

同じようにEnterキーを押して決定します。

図１６　プログラム実行前４

↓

図１７を確認すると今度は、B列以外のセルに値を入力してもC列にメッセージは表示されません。

無事に不具合部分を改修することができました。

図１７　プログラム実行後４

このようにして、不具合部分の修正は完了です。

まだ、上記プログラムには修正しないといけない部分（バグ）がありますが

今回の説明はこれで終了とさせていただきます。

セル内の値が変更された時点で、プログラムを実行することが出来ました。