|
親愛なる貴方のために・・・え？誰？
ユーザーが必要性を感じたときに、目的のマクロが実行できるよう、[ Excelメニューバー
]へオリジナルメニューを追加します。マクロの作者が「オリジナルメニューを追加する」と言っても、そのオリジナルメニューを追加するか否か、最終の判断は使用するユーザーが決定します。
図：ブックがマクロを含んでいると、警告メッセージが表示される
- ★ マクロの警告メッセージを表示させない方法
マクロを含んだブックは、開かれる前に警告メッセージダイアログが表示されます。開こうとしているブックに含まれるマクロが、有益無害なマクロなのか、悪意を持って作られたマクロウイルスなのか、[
Excel ]自らでは、判断ができないために表示されるメッセージです。
[ Excel ]の起動フォルダ[
XLStart
]を使うと、マクロウイルスを含んだブックでも、この警告メッセージは表示されず、マクロが有効な状態で開かれます。この特殊なフォルダに
[ Personal.xls ]という[ 個人用VBAマクロブック
]を保存し、定型的な繰り返し作業を登録しておくと便利です。マクロブックではなくても、[ Excel
]の起動時に必ず開くブックなどを保存しても良いでしょう。
マクロの警告メッセージで[ マクロを無効にする(D) ]を選択して開いたり、[ Excel ]のセキュリティレベルを[ 高
]に設定するなどして、マクロを無効にして開かれたブックでは、有益なマクロであっても動きません。[ Excel
]のセキュリティレベルは、設定を頻繁に変える部分ではないため、ユーザーが気付かないまま[ 高
]になっているときがあります。「マクロが、全く動かない」という場合、セキュリティレベルの設定を確認しましょう。セキュリティレベルを[ 低
]にする必要はありません。レベルを[ 中
]にして、マクロを含んだブックの場合に、確認のメッセージを表示するようにします。
自分で組んだVBAマクロブックが、他のユーザーが使用するときに実行可能な状態で読み込まれるためには、「・マクロを有効にして開いてください・」のくだりが、ユーザーへの操作説明に必要です。ユーザーに「僕のブックを使うのなら、マクロを有効にして！」と言うのですから、マクロブックは、十分なウイルスチェックを行ってから、配布しなければなりませんね。
VBAマクロプログラムは、「きっかけ」があって初めて実行される、イベントドリブンプログラムです。マクロを実行させるには、ユーザーの操作（マウスやキーボード入力）や、マクロの実行過程で発生する[
イベント ]が必要です。
ブックを開いたとき、マクロを自動的に実行するには、[ Subプロシージャ ]には [
Auto_Open ]という名前を付けます。[ Auto_Open ]と、ブックを閉じるとき自動的に実行される[ Auto_Close
]は、１つのブックに１つだけ作る事が出来ます。
ブックを開いた直後からマクロを実行し、作業を終了するまでマクロを実行し続けるのであれば、先の[ Sub Auto_Open()
]という名前を持ったプロシージャを使います。でも、マクロは必要に応じて呼び出せたほうが使い勝手が良くなりますので、他の手段も加えておきましょう。
マクロを実行するために、その都度[ メニューバー ]の[ マクロ
]にあるリストから、実行するプロシージャ名を選択しなければならない作り方だと、目的のマクロにたどり着くまでの操作が多くて不便さを感じますね。使いたいマクロをすぐに呼び出せるように、ユーザーがマクロを実行する「スイッチ」となる手段が必要です。
[ Excelメニューバー
]へオリジナルのメニューを追加しユーザーへ明示的に知らせる方法や、ユーザーの操作に合わせ、何かしらのイベントが発生したときにマクロが実行されるようにする方法、また、VBAマクロがその時の状況を判断し、自動で動作させる方法などがあります。
[
ActiveXコントロール ]などをワークシート上に直接配置する方法もありますが、簡単に扱える [ Excelメニューバー
]へ、オリジナルのメニューを作ってみましょう。
図：[ メニューバー ]のオリジナルメニュー
- ★
Excelメニューバーに、オリジナルのメニューを追加する
- マクロを有効にしてブックを開くと、オリジナルメニューを追加するだけのプロシージャを実行し、マクロは終了します。追加した各コントロールには、[
OnActionプロパティ ]を使って、ユーザーの選択に応じたプロシージャの名前を指定しています。
[
Captionプロパティ ]はメニューバーに表示されるメニュー項目、[ OnActionプロパティ
]は選択時に実行されるプロシージャ名、[ BeginGroupプロパティ ]はメニューグループの括り（直前に水平線が入る）、[
FaceIdプロパティ ]はメニューアイコンの番号です。
- ★
Excelメニューバーから、オリジナルのメニューを削除する
一見すると、４行目の[ Deleteメソッド ][
.Item("BakuJAN(&X)").Delete ]の行が不要に思えます。これは[ BakuJAN(X)メニュー
]が複数作られることがないようにするための一行です。
自動実行後に、手動で再び[ Auto_Open
]が実行されたり、[ BakuJAN(X)メニュー ]を削除しないうちに[ Auto_Open
]が実行されると、同じメニューが作成されて２以上になってしまうので、メニューの存在有無に関係なく、エラーを承知で[
Deleteメソッド ]をコードの最初で実行しています。
- ★
VBAマクロのコードの実行速度を速くする
メニューの選択項目は６個ですが、[ Withステートメント
]の階層は４段になっています。[ Withステートメント ]と同じ行、または[ :(コロン)
]までにあるのがオブジェクトになっていて、「.（カンマ）」以降のオブジェクト名の再指定を省略できるため、プロシージャの実行速度が向上します。ん〜、これは、一つの箱の中にある複数の物を表現する言葉で例えると分かりやすいかもしれないですね。
街中の信号機に例えてみましょう。「信号機には、赤色のランプが含まれています。信号機には、黄色のランプが含まれています。信号機には、青色のランプが含まれています。」という言い方より、「信号機には、赤色、黄色、青色のランプが含まれています。」と言った方が、同じ意味でも相手に早く伝わるのと同じで、「信号機」という箱物オブジェクトの再定義を省略して、コードの実行速度を速くするわけです。
- ★
エラーが発生しても、マクロを中断させない
プロシージャの行頭に、[ On Error Resume
Nextステートメント]を記述しています。これは、プロシージャの実行途中でエラーが発生しても、マクロを中断させずに以後のコードを実行するためのものです。
ブックを開いた直後の
[ Auto_Open ]実行時には、[ BakuJAN(X)メニュー ]がありません。2行目に[ Deleteメソッド
]を記述していますが、無いものを削除しようとするのでエラーが発生します。それを[ On Error Resume
Nextステートメント]でトラップします。
エラーのトラップ方法には色々ありますが、この[ On Error Resume
Nextステートメント]は、プロシージャ内にエラーが含まれていても、どんどん先へ進んでしまうので、デバッグ時などはコメントアウトしておきましょう。コメントアウトは、「’」に続く１行が、説明文として扱われます。
作ったものは消す作業も必要になります。ブックを閉じても残されたオリジナルメニューを再度使うと、閉じたブックを再び読み戻してしまいます。これは
[ OnActionプロパティ
]で指定しているプロシージャが、閉じたブックの中にあるからです。閉じても閉じても勝手にブックが開かれるときは、ここを疑ってみてください。
- ★
ブックを閉じるとき、マクロを自動的に実行する
- [ Auto_Close ]にも、[ On Error Resume Nextステートメント
]を記述します。何らかの原因で、[ BakuJAN(X)メニュー
]が既に無い場合は、エラーが発生して処理が中断されてしまいます。それを防ぐために[ On Error Resume
Nextステートメント]でトラップしています。
[ Sub Auto_Close() ]は、ブックが閉じられる直前に実行されるプロシージャ名です。実際に閉じられたかどうかや、[
Excel ]を終了したかどうかなどは、このプロシージャが実行された後のことになります。
番外１-１.
「使い勝手の分っているユーザー」へは、オリジナルメニューから操作するマクロよりも、次のプロパティ・メソッドを使って、直接VBAマクロを実行させる方が、簡単なときもあります。これらのプロパティ名は、「On*****」から始まりますが、Excelのバージョンが初期の頃のもので、そのほとんどは現在のExcelのヘルプファイルに載っていません。古いものですが、今でも使い勝手のあるものを挙げてみました。
- [ OnCalculateプロパティ ]
ActiveSheet.OnCalculate =
"実行されるマクロ名"
アクティブシートの再計算時にマクロが実行されます。
- [ OnDoubleClickプロパティ ]
ActiveSheet.OnDoubleClick =
"実行されるマクロ名"
ユーザーが、セルをダブルクリックするとマクロが実行されます。
- [ OnEntryプロパティ ]
ActiveSheet.OnEntry =
"実行されるマクロ名"
ユーザーがセルにデータを入力（編集）するとマクロが実行されます。
※【3.使用者の履歴を残す】編で、実例ソフトを紹介しています。
- [ OnKeyメソッド ]
Application.OnKey "^{g}",
"実行されるマクロ名"
キーボードの[ Ctrl ] + [ G ]キーが押されるとマクロを実行します。
- [ OnSheetActivateプロパティ ]
Application.OnSheetActivate =
"実行されるマクロ名"
アクティブシートが変わるたびにマクロが実行されます。
- [ OnTimeメソッド ]
Application.OnTime TimeValue("15:00:00"),
"実行されるマクロ名"
午後3時にマクロが実行されます。
- [ OnWindowプロパティ ]
Application.Windows("Book1.xls").OnWindow
= "実行されるマクロ名"
他のブックから、ブック名[ Book1.xls ]へウィンドウが切り替わるとマクロが実行されます。
直接、ワークシートへ値や数式を入力する使い方以外に（これがノーマルの使い方ですが）、[ Enterキー
]押下後のセルの移動方向を制御することで、入力作業が楽になることもあります。
- ★ [ Enterキー
]押下後の、アクティブセルの移動方向を制御する
- H列は、A列から数え始めて8列目なので、編集されたセルの持つ[ Columnプロパティ ]が、[ 8
]以上を返した時に、アクティブセルをB列まで戻します。同様に[ Rowプロパティ
]と組み合わせれば、指定列、指定行での動作を設定することが出来ます。タイプライターのように、アクティブセルが定めた値に達すると、相対的な指定先のセルにアクティブセルを移動します。
これら[ On***** = "実行されるマクロ名"
]を設定したら、不要になった後は解除することを忘れないようにしてください。これらのコードを実行した後にブックを閉じても、イベントが発生すると再びブックを開いてしまいます。
番外１-２.
オリジナルのメニューバーは、先の例のようにマクロを使って項目を追加します。項目は、一度記述したコード通りにしか追加されません・・・、と思っていませんか？メニューは、その時々の状況に合わせて機能が変化すると、もっと便利かもしれません。
日付は、基準とする「ある日」以降、毎日１日ずつ加算されていく絶対単位ですが、「ある日」が「今日」を基準としている場合、一昨日は「今日」を基準とする相対単位ですね。売上の集計表や日々の進捗表、はたまた前年度という名目で、「今日」を基準として過去何日、何ヶ月、何年というデータを扱う場合、メニューとして必要なのは、絶対単位ではなく、相対的な日付日時、時間の単位になります。
- ★ [
For〜Nextステートメント ]で、今日を基準とした複数のメニュー項目を作る
- このVBAマクロは、マクロブックと同じフォルダの中にある[ httpd.(年月日).log
]という名前を持った、過去７日間のテキストログファイル(スペース区切りの)を探し、テキストログファイルがあれば、それぞれのファイルを開くためのメニューを追加します。
当日を含め、過去の日付を求めるために、ここでは[
DateValue(Now)関数 ]で求めた今日の日付から、[ For〜Nextステートメント
]内で、過去日のファイル名とメニューを結び付けています。[ Dir関数
]で、ファイルが存在すればメニュー項目の選択を可能にします。また、ファイル名に「/（スラッシュ）」は使えないので、[
Substitute関数
]を使って取り除いています。
メニュー化する日付を当日から７日分としましたが、もっと多くのメニューを持つ可能性もあります。日付だけが異なる繰り返しのコードですから、それぞれのメニュー項目に対応するプロシージャは作らず、
[ Parameterプロパティ ]を[ OnActionプロパティ ]の引数代わりに使っています。
この[ Parameterプロパティ ]をメニュー構成するコントロールの引数として使うことで、[ OnActionプロパティ
]によって実行される７種類のプロシージャが、[ Function Dat_sheet() ]プロシージャ１つだけで済みます。
- ★
１つのプロシージャで、複数の異なるテキストファイルをワークシートに読み込む
- 動作が異なるプロシージャを実行するためには、それぞれ異なった名前を付けるのが一般的です。しかし、日付や数値など、一定量で変化する値を扱うときは、このように引数を使うことによって、プロシージャの実行結果を変化させることも可能です。
- ★
新規に作成するブックに含まれるシート数を定める
[ Function Dat_sheet()プロシージャ
]の目的は、テキストログファイルを読込み、新規に追加したブックに展開することなのですが、展開に必要となるシート数は１枚だけです。
新規にブックを追加したとき、含有されるシートの数は、ユーザーの設定環境によって異なりますので[
SheetsInNewWorkbookプロパティ ]を使い、先にシート数の設定値を取得して変数[ S_Add
]に保存し、コードの最後で、ユーザーが元々設定していた[ SheetsInNewWorkbookプロパティ
]を元に戻します。
- ★
新規にブックを作成する
予め[ Parameterプロパティ ]によって、[ OnActionプロパティ
]の引数として受け取った値は、テキストログファイルの名前を指しています。ですが、年月の区切りに「/（スラッシュ）」を使っているので、[
Sunstitute関数 ]で「"/"」を「""（無）」と置き換えた正しいファイル名にします。ファイルの存在を [ Dir関数
]で調べ、実際にファイルがない場合は[ Exitステートメント
]でプロシージャを抜け、マクロを終了します。ファイルがある場合はコードが継続されますので、[ Addメソッド
]が新規にブックを追加します。
- ★
任意の文字で区切られたテキストファイルをシートに展開する
ブック以外のファイル、例えばテキスト形式やCSV形式のファイルを開くには、ActiveXオブジェクトへの参照を作成する[
CreateObject関数 ]の[ OpenTextFileメソッド ]を使ってファイルを開きます。
[
OpenTextFileメソッド
]は、CSV形式などの一定のフォーマットで保存されたテキストファイルなど、Excelブック以外の外部ファイルを扱う際にとても便利な関数です。必要なときだけ読み込むことが出来るので、使い方によっては、大量のデータを詰め込みがちで肥大化傾向にあるブックを軽量化することができます。
[
Do While 〜 Loopステートメント ]で、ファイルの最後を示す[ AtEndOfLineプロパティ
]が、真（True）を示すまで、行の読み込みが繰り返されます。
ここで指定しているファイルは、半角スペース区切りで構成されるWEBサーバーが吐き出すログファイルで、個々のデータは半角スペースで区切られています。[
ReadLineメソッド ]で１行ずつ読み取りながら[ Split関数
]でデータを区切り、新規に追加したブックに１行ずつ展開しています。[ Split関数 ] の引数[ " "（半角スペース）
]を指定していますが、CGIなどで使用される[ <>
]を使えば、ローカル環境でExcelを使ったデータの操作が楽になります。
次の章では、外部テキストファイルを作ったり、内容を書き込み更新する方法を説明しています。
|