エクセル顧客管理 | 第21回.イベント処理について | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第21回.イベント処理について


エクセルで顧客管理を作ります、


前回で20回を達成しました、


気張らず、気負わず、気を抜かず、これからも続けて行きます。


今回は、特に前3回が駆け足で、説明不足であった事もあり、補足しつつ復習しましょう。


その中でも、おそらく初心者が特に分かりづらいと思われるイベントについて説明します。




ワークブックのイベント



VBEの画面です。

上図の、「プロジェクト」で、「ThisWorkbook」を選択し、


「表示」→「コード」、または、F7


ワークブックのイベントは、ここに記述します。


「(General)」と表示されているコンボボックスで、「Workbook」を選択すると、

自動で、

Private Sub Workbook_Open()

End Sub

が作成されます。


その他のイベントは、右のコンボボックスで、一覧から選択することが出来ます。


現在までは、この「Workbook_Open」のみ使用しています。


今後も使用するとしても、「BeforeClose」「BeforSave」くらいだろうと思います。


結局、ワークブックのイベントは、ブック全体に関するイベントですので、


あまり頻繁には使用しません。


どのようなイベントがあるかは、この一覧を見ればおおよそは理解して頂けると思います。


英語で書いてあるので、概ね直訳すれば、その通りの内容です。


今後使用することがあれば、個別に説明します。


Workbook_Open

ブックが開いた直後に実行されるイベントです。

ワークブックのイベントでは、最も使用頻度の高いイベントです。

このイベントでよくやる処理を多少追加説明します。


1.変数に初期化する

2.指定のシート、フォームを開く

3.キーやアクションにマクロを割り当てる

4.ブックを閉じる時に自動処理させる

等々になります。

その他、ピボットやハイパーリンク等の処理もあります。


今のところ、3.に該当する、

Application.OnKey "キー", "マクロ"

だけが出てきました。


1.2.の使い方は、本ソフトにおいても、終盤で出てくる事になると思います。

現在は、各シート毎の機能を作成していますので、

それが終了した後、メニュー用のシートを作成したりしますので、

その時に使う事になると思います。


また、4.については、まだ未定ですが、大切な顧客データの喪失防止に、

最後に対策を施そうかと考え得ています。



ワークシートのイベント


「プロジェクト」で、「Sheet1(Sheet1)」等のシートを選択し、


「表示」→「コード」、または、F7


ワークシートのイベントは、ここに記述します。


「(General)」と表示されているコンボボックスで、「Worksheet」を選択すると、

自動で、

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
が作成されます。
これは、セルの選択位置が変更された時に発生するイベントです。

なぜ、これが標準で作成されるのか、私は疑問です。

そんなに使わないと思うのですが・・・


今までに出てきたのは、

Private Sub Worksheet_Activate()

Private Sub Worksheet_Change(ByVal Target As Range)

の2つです。

Private Sub Worksheet_Activate()

これは、シートがアクティブになったときに発生します。
つまり、他のシートを選択している状態から、そのシートへ移ったときです。

ですから、シートの内容の初期化や、初期表示に使用する事になります。

今回は使用していますが、

本来は、マクロでシートを切り替える際に、同時に処理してしまうので、そんなに頻繁には使用しません。

シート名の表示を消していない場合は、手動でシート変更できますので、そのような場合に必要になります。

今回は、今のところはその状態ですので、使用しています。


Private Sub Worksheet_Change(ByVal Target As Range)

これが問題ですね。

少し凝ったプログラムを作成すると、どうしても使うことになります。

どこかのセルに変更が加えられた時に実行されるイベントです。

ここでの問題は、どのセルが変更されたかになります。

それが、Targetに入っているのです。

このTargetに、何が、どのように入ってくるかを理解すれば良いのです。


Target

引数の型を見て頂ければおわかりになると思いますが、

これは、Rangeオブジェクトです。

従って、通常のRangeと同様に扱えばよいのです。

だだ、問題は、普通のRangeなら、自分でアドレスを設定しています。

Range("A1:B10")または、Range(Cells(1, 1), Cells(10, 2))のようにです。

しかし、このTargetには、どこのセルが入っているかが分かりません。

従って、そのセル範囲を判定してから処理するしかなく、これが結構難しいのです。


単独セルしか操作しなければ、大した問題はありません。

Target.Addressには、$B$10のようになっています。


しかし、複数セルを選択した状態で操作した場合は、

$B$2:$C$3

$B$2,$C$10

のように入ってきます。

これが問題です。


今回作成したプログラムでは、

Target.Cells(1.1)のように、Tagetの先頭セルのみ判定しています。


では、問題です。

「作成したプログラムのバグとも言える欠陥は何でしょうか?」


ビールカクテルグラス順番に飲んで待ってます(笑)


それは、例えば、住所1と住所2を同時に選択し、「Delete」した場合には、

後で選択されたセルは、計算式が設定されないことです。

当然ですね、Target.Cells(1.1)で、Tagetの先頭セルしか処理していませんから。


これは作成中に分かっていた事ですが、対応はしませんでした。

これに対応する意味があまりないからです。

個別に、Deleteすれば計算式が設定されますし、顧客番号が変更されれば全て再設定されますから。


元々は、Addressを比較して、対象セルかを判断していましたので、

複数セルの判定は、かなり面倒なので、対応を止めました。

無理にやろうとすれば、プログラムを複雑にしてしまうだけです。

このような場合は、無理に対応せずに、使用上の注意としておけば良いと考えています。

いずれ問題があっさり解決する事もあります。


その後、Intersectに変更したため、多少ちぐはぐなプログラムになってしまっているようです。

しかし、この記事を書きながら、再度プログラムを見てみると、

なんと簡単に対応可能ではありませんか。チョキ



以下にその修正版を掲載します。

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim strSht As String
  Dim lngRow As Long, lngCol As Long
  Dim adrCode As String
  Dim strAdr1 As String, strAdr2 As String
  Dim strCalc As String
  
  If Intersect(Target, Range("納品書_顧客番号")) Is Nothing And _
    Intersect(Target, Range("納品書_郵便番号")) Is Nothing And _
    Intersect(Target, Range("納品書_住所1")) Is Nothing And _
    Intersect(Target, Range("納品書_顧客名")) Is Nothing And _
    Intersect(Target, Range("納品書_担当者名")) Is Nothing Then
    Exit Sub
  End If
    
  adrCode = Range("納品書_顧客番号").Address(1, 1, xlR1C1)
  With シート取得("顧客一覧")
    strSht = .Name & "!"
    lngRow = .Range("顧客番号").Rows.Count
    lngCol = .Cells.SpecialCells(xlLastCell).Column - .Range("顧客一覧開始").Column
    strAdr1 = .Range(.Range("顧客番号").Cells(1, 1), _
            .Range("顧客番号").Cells(1, 1).Offset(lngRow - 1, lngCol)).Address(1, 1, xlR1C1)
    strAdr2 = .Range(.Range("顧客一覧開始").Cells(1, 1), _
            .Range("顧客一覧開始").Cells(1, 1).Offset(0, lngCol)).Address(1, 1, xlR1C1)
  End With
  
  strCalc = "VLOOKUP(" & adrCode & "," & strSht & strAdr1 & ",MATCH(RC1," & strSht & strAdr2 & ",0),FALSE)"
  
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  If Not Intersect(Target, Range("納品書_顧客番号")) Is Nothing Then
      Range("納品書_郵便番号").FormulaR1C1 = "=""〒""&" & strCalc
      Range("納品書_住所1").FormulaR1C1 = "=" & strCalc
      Range("納品書_住所2").FormulaR1C1 = "=" & strCalc
      Range("納品書_顧客名").FormulaR1C1 = "=" & strCalc & "&"" 御中"""
      Range("納品書_担当者名").FormulaR1C1 = "=" & strCalc & "&"" 様"""
  End If
  If Not Intersect(Target, Range("納品書_郵便番号")) Is Nothing Then
    If IsEmpty(Range("納品書_郵便番号")) Then
      Range("納品書_郵便番号").FormulaR1C1 = "=""〒""&" & strCalc
    End If
  End If
  If Not Intersect(Target, Range("納品書_住所1")) Is Nothing Then
    If IsEmpty(Range("納品書_住所1")) Then
      Range("納品書_住所1").FormulaR1C1 = "=" & strCalc
    End If
  End If
  If Not Intersect(Target, Range("納品書_住所2")) Is Nothing Then
    If IsEmpty(Range("納品書_住所2")) Then
      Range("納品書_住所2").FormulaR1C1 = "=" & strCalc
    End If
  End If
  If Not Intersect(Target, Range("納品書_顧客名")) Is Nothing Then
    If IsEmpty(Range("納品書_顧客名")) Then
      Range("納品書_顧客名").FormulaR1C1 = "=" & strCalc & "&"" 御中"""
    End If
  End If
  If Not Intersect(Target, Range("納品書_担当者名")) Is Nothing Then
    If IsEmpty(Range("納品書_担当者名")) Then
      Range("納品書_担当者名").FormulaR1C1 = "=" & strCalc & "&"" 様"""
    End If
  End If
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
End Sub


そうなんです、基本は、Select Caseを使わず、IF文を繰り返せば良かったのです。

それで、複数セルに対応できるので、Target.Cells(1.1)もTargetだけにしました。


ただし、特別注意として、

イベントの連鎖に気を付ける必要があります。


プログラムでセルの内容を書き換えても、Worksheet_Changeが、再度呼ばれますので、

イベントの発生を抑止する必要があります、前回は入れ忘れていましたね。

それが、

Application.EnableEvents = False
です。
モジュールの最後で、Trueに戻します。

また、件数が増えてくると、VLOOKUPの計算時間がかかってきますので、

一旦計算を停止します。

Application.Calculation = xlCalculationManual

ですね。

これを指定しないと、1つのセルに計算式を設定するたびに再計算が実行され、

処理がおそくなってしまいます。

これらの処理は、「マクロ開始処理」「マクロ終了処理」で同じですが、

これらのモジュールには、他の処理も入っており、ここでは、この2つだけで良いでしょう。


また、先頭で対象セルでは無い場合には、直ぐにモジュールを抜けるようにも追加しました。

また、「顧客番号」で、全計算式を設定した後は、モジュールを抜けた方が良いのだが、

まあ、同時に全てDeleteなんて、そんな操作する人はいないだろうから、どうでも良いか。



標準モジュールとの使い分け


特段の決まりがある訳ではないが、覚えておく必要があることがあります。


他のモジュールからCallする場合は、

モジュール.Subモジュール

となります、このモジュールとは、

「プロジェクト」の「Sheet1(Sheet1)」の最初の「Sheet1」です。

()内は、シート名です。

このシートモジュールの名称は、「プロパティ」で変更ができます。

ただし、プロパティを変更しても、Callしている箇所は自動では直りませんのでご注意を。


また、当然のことではありますが、

このシートモジュールは、シートと対なので、

シートを他のブックに移動・複写した時も、一緒にくっついていきます。

そして、これが最も考慮すべきことです。


つまり、作成したシートだけを誰かに渡す可能性があるなら、

全て、シートモジュールに記述しないと、動作しなくなります。

もちろん、標準モジュールも一緒に渡せばよいのですが、

そこには、膨大なコードが入っている場合は、ちょっと困りますよね。

まあ、ある程度のソフトの場合は、このような事は無いと思います。

作成中のソフトは、間違いなく無理ですよ!


では、通常の使い分けはどうするべきか。

基本的には、イベントのみ記述するべきでしょう。

ただ、イベントのプロシージャーからCallする、SubやFunctionで、

他からCallする可能性が無いものは、入れても構わないと思います。

ただし、他のシートを操作するようなコードは、標準モジュールに入れた方が良いでしょう。


また、標準モジュールでは、シートの指定は必ずすべきです。

たとえ、そのシートがActiveになっていることが確実であったとしても、

シート指定は省略すべきではないと思います。

つまり、コードの「流用性」を考慮すべきでしょう。

もちろん、そのSubモジュールの先頭でActiveにしているのなら問題はないでしょうが、

ただ、なるべく、Activeにせずに、With等を使用するようにして下さい。


しかし、シートモジュールではどうでしょうか。

そのシートでしか使用しないのですから、指定する必要がありません。

他のシートモジュールにコピペしたとしても、結果は同じですから。




なかなか、話がまとまらなかったです。

正直なところ、イベント処理は結構難しいです。


イベント個々に注意点が違いますし、VBAと言っても、アクセスでは全く違ってきます。

当然、他のVB系の言語では、さらに全く違うものです。

ベテランのプログラマーでも結構目に見えないバグを残している事があります。

あっ、今回は私もだ


どうでしょう、少しはイベントに対する理解が進みましたでしょうか。


次回は、コントロールについて、追加説明する事にしましょうか。


イベントと、コントロールの扱いが分かれば、ぐっと作成できるプログラムの幅が広がります。


ではでは、今回はここまで。


また次回に。






同じテーマ「エクセル顧客管理」の記事

第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)
第25回.納品書を作成、商品情報を取得(3)
第26回.WorksheetFunctionについて
第27回.RangeとCellsの深遠
第28回.納品書データをデータベース化(1)

新着記事 ・・・新着記事一覧を見る

データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • イベント処理について

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP