ワークシートのイベントプロシージャ – ExcelVBAの散策とruby

2016/04/09

参考サイト：　 Excel VBA 入門講座 ワークシートのイベントプロシージャ

《目次》

• 1.　概要
• 2.　Worksheet_Activate
• 3.　Worksheet_SelectionChange
• 4.　Worksheet_Change

1.　概要

　イベントプロシージャは、ExcelをＧＵＩ操作している最中に何らかの出来事(イベント)が発生した時に呼び出される手続き(プロシージャ)のことです。

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

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

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

　これまで示したマクロは、いずれも標準モジュールというところに登録してきました。多くのマクロは、標準モジュールに登録します。

　それに対して、イベントプロシージャは、シートモジュールというところに登録します。それがどこにあるかはともかく、要は標準モジュールと違うところだということです。

　VBEを起動してシートモジュールを選択する方法については、冒頭の参考サイト「Excel VBA 入門講座 ワークシートのイベントプロシージャ」に詳しく書かれています。

　ここでは、これまでやってきたのと同様に、rubyによってシートモジュールを登録する方法を採ります。VBEを立ち上げてコードを入力する必要はありません。

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

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

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

Private Sub Worksheet_Activate()
    Range("A1").Value = "=NOW()"
    Range("A1").NumberFormatLocal = "HH:MM:SS"
End Sub

　Private Sub Worksheet_Activate() のWorksheet_Activateというのは、一定の規則に従ってつけられている名前です。

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

　その辺の事情をきちんと説明すると長くなります。また、ExcelのＧＵＩ操作を苦手とする私には荷が重い分野です。詳しく知りたい方は、たとえば下のサイトを参照して下さい。

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

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

目次に戻る

2.　Worksheet_Activate

　「概要」で述べた現在時刻表示のスクリプトを掲げます。

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

　これまでのようにマクロにショートカットキーを割り当てても意味がないので割り当てません。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Worksheet_Activate()
     Range("A1").Value = "=NOW()"
     Range("A1").NumberFormatLocal = "HH:MM:SS"
 End Sub
 EOS

File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
    cmod = wb.VBProject.VBComponents.Item("Sheet2").CodeModule
    cmod.AddFromString(macro_str)
    wb.save
end

　上のrubyスクリプトに出てくる Item("Sheet2") のSheet2をSheet3に変更すれば、プロシージャがSheet3に登録されます。

　そうすると、Sheet3がアクティブシートになった時にそのA1セルに現在時刻が表示されるようになります。

目次に戻る

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が書き込まれます。

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

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

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

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

　If文が入れ子になったりして少しごちゃごちゃしますが、サンプルスクリプトを掲げます。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Randomize
     If Target.Value = "" Then
         Target.value = Int(Rnd*30)+1
         If Target.Value > 25 Then
            MsgBox Target.Value & ">25  Over!"
            Worksheets("Sheet2").UsedRange.Clear
            ActiveSheet.UsedRange.Copy Worksheets("Sheet2").Range("A1")
            Worksheets("Sheet2").Activate
        End If
    End If
End Sub

Private Sub Worksheet_Activate()
    ActiveSheet.UsedRange.Clear
End Sub
EOS

File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
    cmod = wb.VBProject.VBComponents.Item("Sheet1").CodeModule
    cmod.AddFromString(macro_str)
    wb.save
end

　前の項で取り上げた Worksheet_Activate も一緒にシートモジュールに登録しています。

　Sheet2から移って再びSheet1がアクティブシートになった時に、Sheet1の内容をクリアして、ゲームを最初からやれるようにするためです。

　ruby利用者には If Target.Value = "" Then というのに少し違和感を覚えるかもしれません。

　rubyではif文における比較で、’==’ を用いるからです。イコール記号を二つ書きます。

　それに対して、VBAではイコール記号を一つしか書きません。知識としては知っていても、rubyになじんでいる私の場合は、ついイコール記号を二つ書いてしまいます。

目次に戻る

4.　Worksheet_Change

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

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

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

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

• まず、Sheet1の最下行・１列のセルに、1〜20の乱数(整数)をセットします。これが正解の数になります。
• ユーザーは、A1とかB1など適当なセルに数字を書き込みます。
• その書き込んだ数値が正解と一致するとHitのメッセージが出ます。

　ここで少々ややこしい話を一つ。

　スクリプト内に Application.EnableEvents = False というのが出てきます。これはイベントプロシージャの呼び出しを抑制するための指示・命令です。Trueにすれば再び呼び出しが行われるようになります。

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

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

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Worksheet_Change(ByVal Target As Range)
     If Cells(ActiveSheet.Rows.Count, 1).Value = "" Then
         Randomize
         Application.EnableEvents = False
         Cells(ActiveSheet.Rows.Count, 1).value = Int(Rnd*20)+1
        Application.EnableEvents = True
    End If
    If Str(Target.Value) = Str(Cells(ActiveSheet.Rows.Count, 1).Value) Then
        MsgBox Target.Value & "  Hit!"
    End If
End Sub

Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    ActiveSheet.UsedRange.Clear
    Application.EnableEvents = True
End Sub
EOS

File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
    cmod = wb.VBProject.VBComponents.Item("Sheet1").CodeModule
    cmod.AddFromString(macro_str)
    wb.save
end

　Str() は、与えられたパラメータが数値であっても文字型に変換する関数です。

　If文で二つのセルの中身を比較する際、一方が文字で他方が数値だと比較がうまくいかないので、両法とも文字に変換した上で比較するようにしています。