ExcelVBA(マクロ)で指定の時間に処理を行う・アラームをかける方法/OnTimeメソッドの使い方と実行時エラー1004の対処法

the-eleventh-hour IT

Excel上で所定の時間になるとなんらかの処理を行う、OnTimeメソッドのご紹介です。

Advertisements

所定の時間になったら処理を行う

時間になったらマクロを実行
Application.OnTime 時間,マクロ名
ontime1
ひとつマクロを作って、マクロ名は「alarm」とし、メッセージボックスを表示する命令を作ります。
ontime2
別のマクロを作ります。別のウィンドウにしてますが、sub~で分けて下にそのまま書いても大丈夫です。
ontime3
Application.OnTime TimeValue(“7:00”), “alarm”7時になったら「alarm」メソッドを実行と記述します。
Applicationというのはメソッドを命令する相手で、アプリ=ソフト=Excelのことです。
ontime4
では、時間までお茶でも飲んで待ちましょう。
ontime5
時間になりました。
ontime6
するとalarmメソッドが実行され、メッセージボックスが自動的に表示されました。
ontime7
timevalue~は引数名で言うと「EarliestTime」、指定するマクロは「Procedure」なので、
Application.OnTime EarliestTime:=TimeValue(“7:15″), Procedure:=”alarm”
と書いても同じ意味になります。

指定した時間が過ぎたら処理を実行

ontime8
さっきのコードをちょっといじって、Application.OnTime Now + TimeSerial(0, 0, 10), “alarm”とします。Nowは今、TimeSerial(0, 0, 10)はプラス10秒という意味です。
Now + TimeValue(“00:00:10”)と書いても同じ意味になります。
ontime9
マクロをセットして、しばし天井を見つめて過ごします。
ontime10
10秒経過するとアラームが表示されます。

指定の時間ごとに同じ処理を繰り返す

ontime14
今までは時間になるとマクロ「alarm」を実行していましたが、これを「setalarm」に変更します。
時間指定をしたマクロ「setalarm」内で「setalarm」を実行するということは、指定した時間ごとに自分自身を繰り返し実行するマクロということになります。
ontime15
秒数をちょっと縮めて、その下にalarmと記述し、マクロ「alarm」も呼び出すようにしておきます。
ontime16
これで、5秒ごとにメッセージボックスが表示される鬱陶しいマクロとなります。
ontime17
ほっとけば無限に繰り返し処理を行いますが、マクロ編集画面の「中断」を押せば止められますし、なんならExcelを一回閉じればマクロは中断されます。

アラームを中断する

ontime18
アラームを停止するマクロも作ることができますが、意外と手ごわいです。
また別のマクロを作り、「stopalarm」とします。
基本的には「Schedule」という引数をFalseにする(OnTimeが有効になっている状態はScheduleがTrue)ことでタイマーをストップできるので、Application.OnTime stoptime, “setalarm”, , Falseと書くとか……(途中の,と,の間はまた別の引数を入れられる場所ですが、省略可能)。
ontime19
またはApplication.OnTime stoptime, “setalarm”,schedule:=Falseと書けばいけそうなもんですが、
ontime20
これだとガッツリエラーになります。
ontime21
理由①としては、OnTimeを実行前は当然ScheduleがFalseなわけですが、その状態でさらにScheduleにFalseを設定しようとするとエラーになります。
なので先にOnTimeを実行してからFalseにするとエラーになりません(Scheduleの記述を省略した場合もTrueになります)。ただこれだと繰り返しになりません。
ontime22
理由②としては、Trueにする時とFalseにする時ではEarliestTimeに同じパラメーターを指定する必要があります。
ScheduleをTrueにする時Now + TimeValue(“00:00:05”)、Falseにする時Nowを指定すると、これもエラーになります。
ontime23
そこで対処法としては、まずpublicで時間を代入する変数を作成します。publicで作った変数は他のマクロと使い回すことができます。as Dateと付けることで日付(時間)を扱う値であることを指定し、今回はPublic alarmtime As Dateと入力します。
ontime24
マクロ「setalarm」内では、このalarmtimeにNow + TimeValue(“00:00:05”)を指定します。
詐欺みたいですが、同じ変数名であればふたつのマクロで実質違う時間であってもOKです。
ontime25
EarliestTimeにalarmtimeを指定します。
ontime26
マクロ「stopalarm」のほうでも、EarliestTimeにalarmtimeを指定します。
as Dateで作った変数に何も代入しないと、自動的に現在時間が適用されます。
ontime27
この状態で「setalarm」を実行すると、5秒ごとにメッセージボックスが表示され……、
ontime28
stopalarmを実行すると、繰り返し処理が中断されます。
ontime29
ついでに「stopalarm」単体で実行した時のエラーも出なくしておきましょう。
OnTimeを実行する前にOn Error Resume Nextと記述します。エラーが出ても無視して次へ進む、という意味です。
ontime30
これでscheduleをFalseにするだけの命令を実行しても、エラーが出なくなりました。
やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント