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

2016/05/19

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

《目次》

• 1.　概要
• 2.　Workbook_Open
• 3.　Workbook_BeforeClose
• 4.　Workbook_BeforeSave

1.　概要

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

　ワークシートのイベントプロシージャについては既に触れましたが、今回はワークブックのイベントプロシージャです。

　イベントは、たとえば「該当のワークブックがオープンされるとき」とか、「ワークブックが保存されるとき」あるいは「ワークブックがクローズされるとき」などです。

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

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

　ワークブックのイベントプロシージャは、ブックモジュールというところに登録します。標準モジュールやシートモジュールとは違うところに登録します。

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

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

　イベントプロシージャとしては最も簡単と思われるもの ＝ 該当のワークブックがオープンされた時に、「Book1.xlsを開きます.」といったメッセージを出すスクリプトを取り上げます。

　イベントプロシージャを設定したワークブックをオープンする度に、そのメッセージが出ます。

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

Private Sub Workbook_Open()
    MsgBox ThisWorkbook.Name & "を開きます."
End Sub

　ThisWorkbook というのは、ここではイベントプロシージャを組み込んだワークブックオブジェクトを指し示します。

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

目次に戻る

2.　Workbook_Open

　「概要」で述べた、オープン時にメッセージを出すスクリプトを掲げます。

　Book1.xlsをオープンする度に、「Book1.xlsを開きます.」というメッセージを出します。

　マクロにショートカットキーを割り当てることはしません。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Workbook_Open()
     MsgBox ThisWorkbook.Name & "を開きます."
 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("ThisWorkbook").CodeModule
    cmod.AddFromString(macro_str)
    wb.save
end

　上のrubyスクリプトに出てくる Item("ThisWorkbook") というのが、ブックモジュールを処理の対象としていることを示します。

目次に戻る

3.　Workbook_BeforeClose

　Workbook_BeforeClose は、ワークブックがクローズされる直前に呼び出されるプロシージャです。

　ほんとうにクローズするか、それともクローズを取りやめるのかを、プロシージャの中で選ぶことができます。

　具体的には下のようなコードになります。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.Saved = False Then  ' 変更されているのに未保存
        MsgBox ThisWorkbook.Name & "：未保存なのでクローズ取りやめ."
        Cancel = True
    End If
End Sub

　Cancelという変数は、クローズをキャンセルするかどうかを制御するための変数です。

　元々は False が代入されています。つまり、キャンセルせずにクローズする、という動作につながります。

　これを True にすると、クローズがキャンセルされます。ワークブックが閉じられずに元の状態に戻ります。

　このコードの場合、ワークブックが未保存だとクローズできません。ちょっと不便です。

　そこで、下のスクリプトでは、「未保存だけど、本当にクローズしてもいいかどうか」を確認し、それへの Yes, No に応じてクローズするかどうか決めるようにしています。

　少しごちゃごちゃした感じになりますが、枠組みは簡素です。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Dim Res As VbMsgBoxResult
     If ThisWorkbook.Saved = False Then  ' 変更されているのに未保存
         Res = MsgBox(ThisWorkbook.Name & "が未保存です。" & _
             "本当にクローズしますか？", vbYesNo + vbQuestion)
        If Res = vbNo Then
            Cancel = True
        Else
            ThisWorkbook.Saved = True  ' 保存したことにする
        End If
    End If
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("ThisWorkbook").CodeModule
    cmod.AddFromString(macro_str)
    wb.save
end

　上のスクリプトを実行すると Book1.xls ができます。

　それをオープンしてから何か書き込みをし、Excelを終了させようと試みると、
「Book1.xlsが未保存です。本当にクローズしますか？」というメッセージが出て、「はい」　「いいえ」のボタンも出ます。

　「はい」を選ぶとクローズ、「いいえ」だと元に戻ります。

　Book1.xlsをオープンしてから何も書き込みをせずにExcelを終了させようとしたときは、メッセージが出ずにすんなり終了します。

目次に戻る

4.　Workbook_BeforeSave

　Workbook_BeforeSave は、ExcelをGUI操作している場合でいうと「上書き保存」あるいは「名前をつけて保存」を選択したときに、保存処理の前に呼び出されます。

　VBAのマクロ記述でいうと、Save, SaveAs が実行される前に呼び出されます。

　ワークブックをクローズするとき、または、Excelを終了させるときも、ブックに変更が加えられているとブックを保存するかどうかのダイアログが出ます。

　しかし、この場合は Workbook_BeforeSave が呼び出されないようです。

　Workbook_BeforeSaveには二つのパラメータがあり、たとえば次のように記述します。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    Cancel = True
End Sub

　SaveAsUI, Cancel の両法とも、その値は True, False のどちらかです。

　SaveAsUI は、「上書き保存」(Save)の前に呼び出されたときは False、「名前をつけて保存」(SaveAs)のときは True が代入されています。

　Cancel には False が代入されています。

　これを True に変更すると、保存が行われないまま元の状態に戻ります。

　第１ワークシートのA1セルが空欄の場合にメッセージを出して保存はしない、というイベントプロシージャは下のようになります。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    If Worksheets(1).Range("A1").Value = "" Then
        MsgBox "第１ワークシートのA1せるが空欄なので保存しません。"
        Cancel = True
    End If
End Sub

　前置きが長くなりました。以下、イベントプロシージャを登録するスクリプトです。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
         Cancel As Boolean)
     If Worksheets(1).Range("A1").Value = "" Then
         MsgBox "第１ワークシートのA1せるが空欄なので保存しません。"
         Cancel = True
    End If
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("ThisWorkbook").CodeModule
    cmod.AddFromString(macro_str)
    wb.Application.EnableEvents = false
    wb.save
    wb.Application.EnableEvents = true
end

　スクリプトの最後の方で、Application.EnableEvents の値を false にしたり true に戻したりしています。

　これは、Book1.xlsを保存するときに Workbook_BeforeSave が呼び出されるのを抑制するための処置です。

　スクリプトやマクロの中でワークブックの保存を行う場合、Workbook_BeforeSave が呼び出されると困るときは、Application.EnableEvents を False にして下さい。イベントプロシージャの呼び出しが抑制されます。

当シリーズ「ExcelVBAの散策とruby」は、これで終了です。