カテゴリー: ExcelVBA

コードコピー

'Excelのアプリを代入する変数、イベント作成が可能
Dim WithEvents エクセル As Excel.Application

コードコピー

'変数とExcelの紐付け（イベントが発生する）
Set エクセル = Excel.Application

コードコピー

'紐付けの解除（イベントは発生しない）
Set エクセル = Nothing

Excelオブジェクトの作成

Excelイベントを作成するには、Excel自体をまずオブジェクトとして作成を行う必要があります
これには、1つ目のコードをオブジェクトモジュール内の最上部に挿入するだけで可能です

コードコピー

'Excelのアプリを代入する変数、イベント作成が可能
Dim WithEvents エクセル As Excel.Application

このコードを入れたモジュール内では、オブジェクトボックスに変数名が表示されます
それがExcelのオブジェクトになります
これは処理を実行しなくても、この変数名のオブジェクトは使用可能です
コードを入力した時点で使用可能になっています

ここで使用しているWithEventsキーワードは、変数でイベントの作成を行うことが出来るようにするステートメントです
このステートメントは、オブジェクトモジュール内でしか使用できません
オブジェクトモジュールとは、要は標準モジュール以外の全てになります

コーディング時点でエラーが発生するので、エラーが発生しないモジュールに移動してもらえばいいです

基本的には、ThisWorkbookかUserForm内に作成すればいいです
元々はクラスモジュールで、コントロールなどを動的作成する際に使用するもので、多くはクラスモジュールで使用されますが、このステートメント自体がクラスモジュール内限定のものでは無いので、使用するモジュール内で良いと思います

イベントの作成と変数の紐付け

イベントの作成に関しては、通常のイベント作成と同様に行えます
イベント名も引数も全て自動的に作成されます

画像の様にオブジェクトボックスから選んだ時点で既定のイベントが作成されますので、違うイベントを作成したい場合は通常通りです

さらに、もう一つ作業が必要になります
それが紐付け作業です

コードコピー

'変数とExcelの紐付け（イベントが発生する）
Set エクセル = Excel.Application

このコードを実行する事で、変数のExcelとコードを実行したExcelが紐付けられます
これ以降、この紐付けたExcelで作成したイベントが実行されるようになります

基本的には、WorkbookイベントのOpenイベントであったり、UserFormのInitializeイベントの初期イベントで実行しておきます
動きは後述します

コードコピー

'紐付けの解除（イベントは発生しない）
Set エクセル = Nothing

そして、この紐付けを解除するには変数を初期化します
このコードを実行する事で、以降はExcelのイベントは発生しなくなります

オブジェクト変数なので、この解除（解放）までをセットで使用するようにしてください

基本的には、WorkbookのCloseイベントやUserFormのTerminateイベントの終了イベントで実行するようにします

イベント発生時の動き

上記のコードで紐付けが完了したら、イベントが発生するようになります
実際に動きを確認してみましょう

Private Sub エクセル_NewWorkbook(ByVal Wb As Workbook)
MsgBox Wb.Name
End Sub
-------------------------------------------------------------------------
Private Sub Workbook_Activate()
'変数とExcelの紐付け（イベントが発生する）
Set エクセル = Excel.Application
End Sub
-------------------------------------------------------------------------
Private Sub Workbook_Deactivate()
'紐付けの解除（イベントは発生しない）
Set エクセル = Nothing
End Sub

今回の例では上記のようなコードを作成しています
Workbookイベントで、アクティブになったら紐付けして、非アクティブになったら紐付けを解除します
このブックでしか、イベントが発生しないような作りです

イベントは、新規ブック作成時に発生するイベントです
内容はそのブック名をメッセージ表示します

まずは、新規作成によりイベントが発生している動きです
コード実行ブックで、新規作成を行う事で新規作成された「Book1」の名前がメッセージに表示されます

次に、そのBook1でさらに新規作成を行った時の動きです

ここではイベントが発生していません
元々コードの実行されていたブックが、Book1作成時点で非アクティブになったためExcelの紐付けが解除されたためです

最後に、また元のブックに戻って再度新規作成を実行します
今回はメッセージが表示されました

元のブックがアクティブ状態になったことで、また紐付けが行われたため
イベントが発生するようになりました
今回はBook3のメッセージが表示されました

Excelイベントの使用について

上記の通りに、Excelのイベントを作成して実行できます

例で挙げたような、新規作成ブックの名前を表示する処理は非常に単純な処理ですが、これをイベントを使用せずにする場合はいろいろな事を考慮して作成する必要があり面倒です

イベントを使用すれば、作成したブックが引数としてオブジェクトに取得されるため、その後の新規作成ブックに対する処理が非常に簡単に行えるようになります

また、このExcelイベントの強力な利点は、Excel自体のイベントプロシージャの作成をマクロブックに行うことで、そのマクロブックを開いて紐付けをしておけば、マクロ無しの標準ブックであってもイベント処理を実行することが出来る点です
もちろんイベントではなく、通常の処理であればブックを指定すれば可能です

会社の規定などにより、マクロ有効ブックが作成できない場合があります
とはいえ、入力処理などが面倒でイベント処理で楽にできればええなぁと感じることはあります

そんな時に、このExcelイベントの出番になります
これは同じExcelアプリで開くブック全てに対して有効なイベントなので、シートのセルをダブルクリックするイベントなどを標準形式保存のマクロ無しブックでも実行する事が出来るようになります

SheetBeforeDoubleClickイベントは、シートのセルをダブルクリックすると発生するイベントです
このイベントに処理を作成すれば、Excelで開いているブック全てに同じイベント処理を実行する事ができます

WorkbookOpenイベントを利用すれば、ブックのOpenイベントも必要ありません

考えれば考えるほど、応用力があるイベントになります
あんなことや、こんなこと・・・えぇっ！？そんなことまでっ！！？
とは、まさにこのことです

ただ、イベント特有の注意点が便利なほど重要になってきます
全てのブックに適用されて、なおかつ紐付けが解除されるまで無条件で発動し続けるイベントになるので非常に要注意です

基本的には、このイベントでしか実現できない処理にだけ使用します
Workbook以下のイベントで対応できるものを作成する事だけは止めておきましょう

イベントとはワークシートやセル、ブックやユーザーフォームのコントロールなどのオブジェクトに存在する自動的に発生するプロシージャのことです

イベントプロシージャの作成方法

Workbookオブジェクトに対するイベントの作成方法です
まずはコードウィンドウ上部にあるオブジェクトボックスから選択を行います
この時に、既定のイベントが自動的に作成されます
このイベントが不要な場合は、後のイベント追加後に削除してかまいません

イベント作成には、作成したいオブジェクトのコード内に入力カーソルを置いた状態にします
すると、オブジェクトの選択ボックスにそのオブジェクトが指定されます
その状態で右にあるプロシージャボックスから指定のイベントを選択することで作成が可能です
画像にもあるようにこの時に、このオブジェクト名とイベント名、その引数までを自動的に作成してくれます
ちなみに、引数名は変更可能ですが絶対に変更しないでください
非常にコードが分かりづらくなります

当サイトでのイベントの解説に関して

基本的にイベントの作成は上記の様にVBEの画面で、全て自動的に作成されます

サイト内コードをコピペすると、オブジェクト名を修正しないといけなくなり面倒だと思います
なのでサイトの趣旨とは異なってしまいますが、コピー用コードの記載はせずにイベントの説明を行なっていきます
なので複数のイベントをまとめて記事にしていますので探しにくい場合ももしかしたらあるかもしれませんがご容赦ください

イベント使用時の注意点

イベント全般で言える注意点は2つあります

・イベントは無意識下に実行されるプロシージャのため、ユーザーの行動次第で想定外のイベントが実行されてしまう可能性がある

まず、自動的に発生するという特性から非常に利点の多いイベントです
意識的にボタンをクリックしたりする必要がなく、ユーザービリティ向上には欠かせない要素です
ただ、意識して行うようなイベントであれば構いませんが、無意識に実行されるイベントもある点には注意してください

例えば、ダブルクリックすると発生するイベントはほぼユーザーが意識して行うイベントと思って良いと思います
対して、再計算やシート・ブックなどの選択変更ではユーザーの意思とは関係なく実行される可能性のあるイベントです

作成者側で想定していたタイミングでユーザーがそのイベントを発生させるとは限らない、ということは念頭に置いておいたほうが良いです

・プロシージャの実行中であってもイベントは発生する

イベントはイベントプロシージャでも通常のプロシージャでも、実行中にそのイベントが発生するとイベントプロシージャが割込みで発生します

セルのChangeイベントが作られたシートで、プロシージャの処理でセルの値を変更した場合、その変更した瞬間にセルのChangeイベントが実行されます

その後、元のプロシージャに戻ってきて処理が継続します

そして継続後にもセルの変更処理を行なっていた場合、その全てにおいてChangeイベントは実行されます
それが仕様としていれば構いませんが、違う場合は要注意です
その場合には、Excelでのイベントの発生を抑止する必要があります
以下の記事にその解説があります

Excelのイベント発生を抑止する

イベントは非常に便利なのですが多用は禁物です

過去にイベントを多用していた頃、一つのChangeイベントが複数のイベントを連鎖的に発生させる状態になってしまっていました
そのせいで、一つのChangeイベントの処理変更を行う際に関係するイベント全てに対して修正が必要になり非常に大変な思いをしました

もしボタンなどで実行が問題ない場合は、イベントに頼らずボタンを作成し処理を実行するにしてください
後々機能追加などの処理変更の際にイベントが悪さをせずに済みます

Excelの本体イベントに関して

Excelのイベントというのは、初期状態では上記の作成方法では作成できません
これはオブジェクトボックスにExcelが表示されないためです

記事中で解説しているのは、Workbookに対するイベント作成です
これはブックのことになるので、Excelで開いているファイルの1つでしかないということです

それに対してExcelとはアプリそのものになります、これがVBEで初期状態では表示できないため少し特殊なものになります

表示させる作業が必要になります、以下の記事で解説しています

Excelイベントの作成方法