夏到来。今日も暑くて，海水浴日和だ！「先生，どうして水着を着ているんですか！？」「えーと，その…」「ちょっと目を離すとすぐ遊びに行こうとするんだから。次のかた，ど～ぞ～」。ひー，うちの看護師さんが鬼に見えるよー！
|
「リスト1[拡大表示]のような曜日を返すユーザー定義関数を作成しました。(1)(2)(3)のコードで，土曜日と日曜日の文字の色を変えようとしているのですが，文字の色が変わりません。何が問題なのでしょうか？」 |
ふむふむ，図1[拡大表示]のシートで，C列の行番号3～33のセルに，リスト1[拡大表示]のユーザー定義関数を埋め込んであるわけですね。なるほど，処理的には問題ないように思えますね。
しかし，残念ながら，(1)(2)(3)のステートメントは実行されません。結論から言うと，これはExcelの仕様です。関数（Functionプロシジャ）ではオブジェクト（この例ではフォント）を操作することはできないのです。
ユーザー定義関数と
自動実行マクロは一長一短
このような場合には，イベント・プロシジャを利用した自動実行マクロを作るのがいいでしょう。Worksheet_Changeイベントを使えば，お望みの機能を実現できると思います。Worksheet_Changeイベントは，セルの値が変更されると発生します。これを使って，曜日を返すマクロを作りましよう（リスト2[拡大表示]）。このマクロで，セルB3に「2005/10/01」と入力すると，セルC3に「土曜日」と自動的に入力できますし，色を変更することも可能です。
ただし，ユーザー定義関数と自動実行マクロはそれぞれ仕様が異なりますので，完全な代替にはなりません。例えば，図1[拡大表示]でセルB3の日付を変更した場合，それを参照している（＝B3＋1，＝B4＋1…と書いてある）B4～B33の日付も変わります。ユーザー定義関数の場合は，それと同時にC3～C33の曜日が更新されます。しかし，Worksheet_Changeイベントを使った自動実行マクロの場合は，B3の日付を変更すると，C3の曜日が更新されて，B3を参照しているB4～B33の日付も変わりますが，C4～C33の値（曜日）は変わりません。Worksheet_Changeイベントは，イベント検知の対象となるセルに計算式を入力している場合，式が参照するセルの値を変更したことによる計算結果の変化は検知しない（イベントが発生しない）からです。C4～C33の曜日を更新したい場合は，B4～B33で明示的にイベントを発生させます。例えば，B4～B33を範囲選択して，その場でコピー→貼り付けを実行すると，C4～C33の曜日が変わります。
したがって，図1[拡大表示]のように複数のセルを同時にかつ自動的に変更するには，ユーザー定義関数のほうが便利だと言えるでしょう。一方，どうしてもオブジェクトを操作したいという場合は，多少不便でもイベント・プロシジャを使ったマクロを組むしかありません。用途と目的を考えて，使い分ける必要があります。
Worksheet_Changeイベントを使う際には，もう一つ注意点があります。このプロシジャをどのモジュールに記述するかで対象となる範囲が変わることです。Worksheet_ChangeプロシジャをWorksheetモジュールに記述した場合はそのシートのセルだけが対象となり，ThisWorkbookモジュールに記述した場合はすべてのシートのセルを対象とします。
イベントの停止・再開を制御する
では，リスト2[拡大表示]を解説しましょう。今回のサンプルでは，曜日を求めるのは，B3～B33のセルの値が変更されたときだけです。そこで(3)と(4)の条件分岐でセルを限定します。
また，(5)(6)はセルが空っぽの場合の処理です。その場合，空の文字列を返し，文字の色を黒に戻します。これは，このシートを繰り返し利用することを想定した処置です。
これらの条件をクリアしたら，(7)で日付の右隣（C列）のセルに曜日を代入します。そして，(8)の条件分岐で，曜日に応じて文字の色を変更します(9)。
さて，(1)と(10)のステートメントは何でしょう。(1)はイベントの発生を停止するステートメントで，(10)はそれを再開するステートメントです。このマクロでは，(7)でセルに曜日を代入します。するとその時点でWorksheet_Changeイベントが発生し，またWorksheet_Changeプロシジャが呼び出されます（これを再帰呼び出しと言います）。プロシジャの再帰呼び出しは，無限ループになる可能性もあるので注意が必要です。こうした再帰呼び出しを防ぐために，(1)のステートメントでイベントの発生を停止しているわけです。その場合，プロシジャを抜ける直前には，(10)のようにイベントの発生を再開しなければなりません。
もし，マクロの作成途中でエラーが発生したり，デバッグの都合でマクロの実行を中断したりして，(1)のステートメントを実行した後に(10)のステートメントが実行されない事態になると，Excelを再起動するまでイベントは発生しません。こうした場合に備えて，イベントを再開するステートメントだけを記述したプロシジャ（リスト3[拡大表示]）を用意しておくと便利です。マクロ実行でこのプロシジャだけ実行します。また，条件分岐などであちこちにプロシジャを抜けるコードがある場合は要注意です。それぞれの前に必ずイベントを再開するプロシジャを記述しましょう。
さて，(2)のステートメントをまだ説明していませんでした。Excelから引き渡されるオブジェクトTargetをそのまま利用せず，Eachキーワードでその中からセルを取り出すようにしているのは，一括して複数のセルに値が代入される，または消去される場合があるからです。
Worksheet_Changeプロシジャの引数Targetは，値が変更されたセルをRangeオブジェクトとして返します。その中身は，単独のセルの値が変更された場合は単独のセル，複数のセルの値が同時に変更された場合は値が変更された複数のセルの集合体になります。(2)のようにしておけば，Targetが単独のセル，複数のセルのどちらにも対応できます。
リスト1●曜日を返すユーザー定義関数。残念ながら(1)(2)(3)のステートメントは実行されない
|
Function Youbi(myDate As Date) As String If myDate = 0 Then Youbi = "" Else Youbi = WeekdayName(Weekday(myDate)) Select Case Weekday(myDate) Case 1 Application.Caller.Font.ColorIndex = 3 Case 7 Application.Caller.Font.ColorIndex = 5 Case Else Application.Caller.Font.ColorIndex = 0 End Select End If End Function |
リスト2●セルの値が更新されると自動的に実行されるイベント・プロシジャ（Worksheet_Changeプロシジャ）。値（日付）が更新されたセルの隣のセルに曜日を入力する
|
Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range Application.EnableEvents = False For Each R In Target If R.Column = 2 Then Select Case R.Row Case 3 To 33 If R.Value = Empty Then R.Offset(0, 1).Value = "" R.Offset(0, 1).Font.ColorIndex = 0 Else R.Offset(0, 1).Value = _ WeekdayName(Weekday(R.Value)) Select Case Weekday(R.Value) Case 1 R.Offset(0, 1).Font.ColorIndex = 3 Case 7 R.Offset(0, 1).Font.ColorIndex = 5 Case Else R.Offset(0, 1).Font.ColorIndex = 0 End Select End If End Select End If Next R Application.EnableEvents = True End Sub |
リスト3●イベントを再開するステートメントだけを記述したプロシジャ。何らかの理由でイベント発生が停止されたままになった場合，このプロシジャを実行すればよい
|
Sub ENEvevts() Application.EnableEvents = True End Sub |
古庄 潤 |
