ワークシートのイベントプロシージャ

カテゴリー名：　［ExcelVBAとOLE　ワークシートの操作］

2016/09/03

イベントプロシージャは、何らかの出来事(イベント)が発生した時に呼び出される手続き(プロシージャ)のことです。

イベントプロシージャには、ワークシートに関するものとワークブックに関するものがあります。ここではワークシート関連を取り上げます。

《このページの目次》

　　　　

1.　はじめに

　イベント(出来事)には、たとえば「該当のワークシートがアクティブになったとき」とか、「ワークシートの選択セルが変更されたとき」などいくつかあります。

　Excelのユーザーがマウスやキーボードを操作すると、操作の種類によってはイベントが発生することになります。

　イベントが発生した場合に、それに応じて実行されるプロシージャをシートモジュールに登録しておくと、どのタイミングで発生するか分からないイベントに対応できるようになります。

　　　　

　ワークシートのイベントプロシージャは、標準モジュールやクラスモジュールとは違うところに組み込みます。シートモジュールというコンポーネントに組み込みます。

　新規のワークブックには Sheet1, Sheet2, Sheet3 の三つのワークシートがあります。それぞれにシートモジュールが用意されています。つまり、シートモジュールが三つあるわけです。

　ワークシートのイベントプロシージャを組み込むときは、この三つのうちのどれに組み込むかを決める必要があります。

　　　　

　ここでは、VBScript(または JScript)によってシートモジュールを登録する方法を採ります。VBEを立ち上げてコードを入力する方法ではありません。

　そのため、Excelのマクロセキュリティの設定をあらかじめ変更しておく必要があります。ExcelをGUI操作して、マクロのセキュリティに関して次の設定変更を行います。

　　　　

　イベントプロシージャは、全自動一括処理で扱っても意味がないので、「OLEを利用するVBScript」は掲げません。その代わりといっては何ですが、簡単なイベントプロシージャの例をいくつか取り上げようと思います。

目次に戻る

　　　　

2.　Worksheet_Activate

　Worksheet_Activateというプロシージャ名は、該当のワークシートがアクティブシートになった時に実行されるものです。該当のワークシートというのは、イベントプロシージャが組み込まれているワークシートです。

　まず、イベントプロシージャとしては最も簡単と思われるもの ＝
Sheet2がアクティブシートになった時に、そのA1セルに現在時刻を書き込む、
という処理を取り上げます。

　Sheet2がアクティブシートになる度にA1セルに書き込みが行われるので、アクティブシートになった時点の時刻がA1セルに表示されることになります。

　プロシージャ部分だけ掲げると次のとおり。

△　event01.txt

1' ワークシートがアクティブシートになった時に、そのA1セルに現在時刻を書き込む
2Private Sub Worksheet_Activate()
3    Range("A1").Value = "=NOW()"
4    Range("A1").NumberFormatLocal = "HH:MM:SS"
5End Sub

　　　　

　Worksheet_Activateというプロシージャ名は、規定の名前です。

　標準モジュールの場合は Macro1 とか SetValue など自分の好みの名前をつけることができますが、イベントプロシージャの場合は使える名前が決まっています。

　その辺の事情を詳しく知りたい方は、たとえば下のサイトを参照して下さい。

エクセルExcel大事典 VBAマクロ イベントプロシージャ Open Target Cancel EnableEvents Volatile

　　　　

　次に、イベントプロシージャを組み込む VBScript を掲げます。

△　event01.vbs

 1' Sheet2へのイベントプロシージャの組み込み: Worksheet_Activate
 2Option Explicit
 3Dim FSO, BookPath
 4Dim EXLapp, WBobj, CPobj
 5Const xlWorkbookNormal = -4143
 6
 7Set FSO = CreateObject("Scripting.FileSystemObject")
 8BookPath = FSO.GetAbsolutePathName("Book1.xls")
 9If (FSO.FileExists(BookPath) = True) Then FSO.DeleteFile(BookPath)
10Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
11EXLapp.Visible = True  ' Excelを見える状態に
12Set WBobj = EXLapp.Workbooks.Add()  ' Workbookの新規作成
13Set CPobj = WBobj.VBProject.VBComponents("Sheet2")  ' シートモジュールの選択
14CPobj.CodeModule.AddFromFile(FSO.GetAbsolutePathName("event01.txt"))
15WBobj.SaveAs BookPath, xlWorkbookNormal
16EXLapp.quit

　上のVBScriptを実行して生成される Book1.xls をExcelで開いてGUI操作するとき、

　Sheet2をアクティブシートにする度に、そのA1セルを書き換えて現在時刻を表示します。

　　　　

　イベントプロシージャの組み込みを行っているのは下の２行です。

Set CPobj = WBobj.VBProject.VBComponents("Sheet2")
CPobj.CodeModule.AddFromFile(FSO.GetAbsolutePathName("event01.txt"))

　上の "Sheet2" を "Sheet3" とすれば、Sheet3にプロシージャを組み込むことになります。

　　　　

　同じ処理をする JScript は下のとおり。

△　event01.js

 1// Sheet2へのイベントプロシージャの組み込み: Worksheet_Activate
 2var fso, exlApp, wb, cp;
 3var xlWorkbookNormal = -4143;
 4
 5fso = WScript.CreateObject("Scripting.FileSystemObject");
 6var bookPath = fso.GetAbsolutePathName("Book1.xls");
 7if (fso.FileExists(bookPath)) fso.DeleteFile(bookPath);
 8exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
 9exlApp.Visible = true;  // Excelを見える状態に
10wb = exlApp.Workbooks.Add();  // Workbookの新規作成
11cp = wb.VBProject.VBComponents("Sheet2");  // シートモジュールの選択
12cp.CodeModule.AddFromFile(fso.GetAbsolutePathName("event01.txt"));
13wb.SaveAs(bookPath, xlWorkbookNormal);  // ブックの保存
14exlApp.Quit();  // Excel終了

目次に戻る

　　　　

3.　Worksheet_SelectionChange

　SelectionChangeは、該当のワークシート内において選択セルが変更された時呼び出されるプロシージャです。

　カーソルを動かして、注目のセルをA1からB1に移したような場合にSelectionChangeが呼び出されます。

　下のプロシージャをシートモジュールSheet1に登録したとしましょう。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.value = 123
End Sub

　この場合、選択セルをA1からB1に移すと、B1に123が書き込まれます。更にC1に移れば、C1にも123が書き込まれます。

　この 123 の書き込みは、あくまでSheet1においてです。Sheet2, Sheet3 では発生しません。

　Worksheet_SelectionChange() のパラメータ Target は、イベントプロシージャが呼び出された段階での注目セルを示します。

　なので、Target.Value = 123 とすれば、注目セルに 123 が書き込まれます。

　　　　

　以下のサンプルでは、多少のゲーム性を持たせるため次のような仕様にしてみます。

  1. 　Sheet1において選択セルを移す度に、1〜30の乱数(整数)を発生させて書き込みます。
  2. 　乱数の値が25を超えたら、ゲーム終了で、Sheet2に移ります。
  3. 　Sheet2にはSheet1の内容のコピーが書かれています。
  4. 　再びSheet1をアクティブシートにすると、内容がすべてクリアされて最初からやり直せます。

　　　　

　VBAにおいてRnd関数は、０以上・１未満の乱数を返します。これに30を掛け算すると、０以上・30未満の数を得ることができます。

　Int() は、与えられた数値の小数部分を切り捨てて整数部分を返します。

　If文が入れ子になったりして少しごちゃごちゃしますが、イベントプロシージャの例を掲げます。

　　　　

△　event02.txt

 1' Sheet1で注目セルを変更したとき、乱数を書き込む
 2Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 3    Randomize  ' 乱数の初期化
 4    If Target.Value = "" Then  ' セルが空欄なら
 5        Target.value = Int(Rnd*30)+1  ' 30以下の乱数
 6        If Target.Value > 25 Then  ' 乱数が25を超えた
 7            MsgBox Target.Value & ">25  Over!"
 8            Worksheets("Sheet2").UsedRange.Clear  ' Sheet2の全クリア
 9            Range(Cells(1,1), Cells.SpecialCells(xlCellTypeLastCell)).Copy _
10                Worksheets("Sheet2").Range("A1")  ' Sheet1→Sheet2のコピー
11            Worksheets("Sheet2").Activate  ' Sheet2をアクティブに
12        End If
13    End If
14End Sub
15
16Private Sub Worksheet_Activate()
17    ActiveSheet.UsedRange.Clear  ' シートの全クリア
18    Application.EnableEvents = False  ' イベントプロシージャを無効化
19    Range("A1").Select
20    Application.EnableEvents = True  ' イベントプロシージャを有効化
21End Sub

　　　　

　上のイベントプロシージャをワークブックに組み込む VBScript, JScript は、先の event01.vbs, event01.js とほとんど同じです。

　シートモジュールを Sheet2 から Sheet1 に変更することと、
プロシージャが書かれているファイル名を event01.txt から event02.txt に変更するだけです。

　なので、組み込むための VBScript, JScript は省略します(zip圧縮ファイルには同梱されています)。

　以下、event02.txt について少し解説を加えます。

　　　　

◇　Randomize

　Randomizeは、乱数の初期化を行うものです。

　これを記述しないと、Rndがいつも同じ乱数を発生させます。同じ値を同じ順序で発生させます。

　　　　

◇　UsedRange.Clear

　UsedRangeは、該当のワークシートの中で使用済みの領域全体を指し示します。

　より正確には、使用済み領域を矩形化した領域全体です。

　UsedRange.Clear とすれば、シートの使用済みの領域が全部クリアされます。

　つまり、シート全体がまっさらな状態になります。

　　　　

◇　Cells.SpecialCells(xlCellTypeLastCell)

　Cells.SpecialCells(xlCellTypeLastCell) というのは、ワークシートの中の使用済み領域全体の一番右下のセルを指し示します。矩形化した領域の右下です。

　Range(Cells(1,1), Cells.SpecialCells(xlCellTypeLastCell)) は、A1セルから一番右下までの領域全体を示します。

　前述の UsedRange は、A1セルを始点とするとは限りません。B2セルが始点ということもあります。

　サンプルのイベントプロシージャでは、A1セルから一番右下までをSheet2にコピーしています。ワークシートそのもののコピーではなく、あくまでシートの中身のコピーです。

　　　　

◇　Application.EnableEvents

　Application.EnableEvents は、イベントプロシージャの呼び出しを有効にしたり無効にしたりするのに用います。

　サンプルでは、Worksheet_SelectionChange の他に Worksheet_Activate も一緒に組み込んでいます。

　Worksheet_Activate は、Sheet1がアクティブになったときに呼び出されますが、その中で次の処理を行っています。

  1. 　シートの中身を全クリア。
  2. 　A1セルを注目セルとして選択。

　２番目の処理のとき、何も工夫しないでいると、
イベントプロシージャの Worksheet_SelectionChange が呼び出されてしまいます。

　イベントプロシージャは、キー操作・マウス操作に反応するだけでなく、該当の操作と同等の処理がマクロ(プロシージャ)の中で実行されると、それにも反応します。

　そのため、マクロ内でSelectメソッドが用いられると Worksheet_SelectionChange が呼び出されます。

　今回、そうした呼び出しが発生すると困るので、Application.EnableEvents = False としてイベントプロシージャの呼び出しを一時的に無効化しています。

　Application.EnableEvents = True とすれば、イベントプロシージャの呼び出しが再び有効化されます。

目次に戻る

　　　　

4.　Worksheet_Change

　Worksheet_Changeは、セルに書き込まれている中身が変化すると呼び出されます。

　空欄だったA1に ABC と書き込むと Worksheet_Change が呼び出されます。

　逆に、ABCと書かれていたセルをクリアして空欄にしたときも Worksheet_Change が呼び出されます。

　前の項で取り上げた Worksheet_SelectionChange は、セルの中身に関係なく、選択セルを移すと呼び出されますが、Worksheet_Change の方は、選択セルが移ったかどうかに関係なく、中身が変化すると呼び出されます。

　より正確には、セルに何か書き込みがあると呼び出されるようです。結果的に中身が同じになったとしても(つまり実質的に変化しなかったとしても)、書き込みが行われると Worksheet_Change が呼び出されるようです。

　　　　

　以下では一種の数当ての例を掲げます。

  1. 　まず、Sheet1の最下行・１列のセルに、1〜20の乱数(整数)をセットします。これが正解の数になります。
  2. 　ユーザーは、A1とかB1など適当なセルに数字を書き込みます。
  3. 　その書き込んだ数値が正解と一致するとHitのメッセージが出ます。
  4. 　ワークシートを別のシートに切り替えて、再びSheet1にした場合、シートが全クリアされてゲームを再開できます。

　　　　

　プロシージャ内で、最下行・１列のセルに正解となる数値を書き込む訳ですが、セルに書き込みを行う時に Worksheet_Change が呼び出されるので、いわばプロシージャ内から同じプロシージャを呼び出すことになってしまいます。そうすると、その後の処理に不都合が生じます。

　そこで、Application.EnableEvents の値を調整して、最下行・１列への正解の書き込みの時は Worksheet_Change がよびだされないようにしています。

　　　　

△　event03.txt

 1' Sheet1において任意のセルに書き込んだ数が正解の数ならメッセージを出す
 2Private Sub Worksheet_Change(ByVal Target As Range)
 3    If Cells(ActiveSheet.Rows.Count, 1).Value = "" Then  ' 正解の数を設定
 4        Randomize
 5        Application.EnableEvents = False
 6        Cells(ActiveSheet.Rows.Count, 1).value = Int(Rnd*20)+1
 7        Application.EnableEvents = True
 8    End If
 9    If Str(Target.Value) = Str(Cells(ActiveSheet.Rows.Count, 1).Value) Then
10        MsgBox Target.Value & "  Hit!"
11    End If
12End Sub
13
14Private Sub Worksheet_Activate()
15    Application.EnableEvents = False
16    ActiveSheet.UsedRange.Clear
17    Application.EnableEvents = True
18End Sub

　　　　

　zip圧縮ファイルに同梱されている event03.vbs または event03.js を実行すると、上のイベントプロシージャが Sheet1 に組み込まれます。

〜　以上　〜