翌日の指定時刻にマクロを自動実行するには?

Question 48.7   Previous Next
待望の「500連発第2弾」がSAS便で届きました。ロンドン駐在の者で Excel 97を使っています。
詳細は こちら サンプルマクロ012の「現在より一定時間後にマクロを実行する」というのを Auto_Openに取り込み、下記のように作りました:
Sub Auto_Open()
  Set_Macro1 = TimeValue("07:30:00")
  Set_Macro1WAIT = TimeValue("00:00:10")
  Application.OnTime TimeValue(Set_Macro1), "Macro1", TimeValue(Set_Macro1WAIT)

  Set_Macro3 = TimeValue("16:30:00")
  Set_Macro3WAIT = TimeValue("00:00:10")
  Application.OnTime TimeValue(Set_Macro3), "Macro3", TimeValue(Set_Macro3WAIT)

  Set_Timer_Clock = Now + TimeValue("00:30:00")
  Set_Timer_ClockWAIT = TimeValue("00:00:15")
  Application.OnTime TimeValue(Set_Timer_Clock), "Auto_Open", _
      TimeValue(Set_Timer_ClockWAIT)

  Range("AC4").Value = "Timer OK"
  Range("AD4").Value = Now
End Sub
Auto_Openがうまくいくと、セルにタイムスタンプを打つように設定し、初めはマクロ3後にその15時間後の翌日7時半のマクロ1を走らせたいのですがうまくいきませんでした。昨日は待ち時間を30分ごとに設定して実験してみましたが、23時59分でマクロがとまってしまうのです。
日付をこす特別な書き方があるのでしょうか? どうしてもマクロが日付をこして実行できずにいます。ワークブックを閉じずにこのマクロを実行させるのに悩んでいます。
どうか、ご教授お願いいたします。
Answer   Copyright (C) 2001.8.31 永井善王
500連発第2弾をご活用いただき、うれしいです。

012番のマクロが使っている OnTimeメソッドは、Visual Basicのヘルプによると「指定された時刻 (特定の
時、または特定の期間の経過後) にプロシージャを実行します」となっています。

OnTimeメソッドの構文
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

これで明らかなように引数 EarliestTime に、'
' と '時' を指定すれば日付越えが可能になります。
ところが、012番のサンプルでは、引数 EarliestTime に 「TimeValue(指定時刻)」と '時' だけ指定しているので、日付の判断がされません。24時をまたいで仕事することは少ないようなので、一般的なサンプルにしてあります。

では、'
'を指定するには、どうすればよいかですが、例えば、
    指定日時 = DateValue("2001/08/31") + TimeValue("07:30:00")
のように、DateValue関数(下記参照)を組み合わせます。

あなたのマクロはホボ出来上がっていると思いますので、上記の点を修正してみてください。このマクロを稼動させるには通常、一晩中、パソコンの電源を入れっぱなしにしておく必要があります。

OnTimeメソッドの引数
EarliestTime プロシージャを実行する時刻。バリアント型の値を使用する。必須。
Procedure  実行するプロシージャ名。文字列型 の値を使用する。必須。
LatestTime  プロシージャを実行できる最終時刻。省略するとプロシージャが実行できるまで待つ。バリアント型の値。
Schedule   新しいOnTimeプロシージャを設定するならTrue、直前のプロシージャの設定解除ならFalse。省略可能。

DateValue関数
構文 DateValue(date)
引数 date は必須で、日付を表す文字列式(100年1月1日から 9999年12月31日まで)を指定。日付と時刻の両方、あるいは和暦で指定することも可能。

お知らせ
・500連発第2弾は完売になりましたが、その後に発売された
 「Excel VBA そのまま使える実用マクロ500連発」の029番に「指定時刻の一定時間後にマクロを実
 行する」として掲載されています。
できました!
もう感動ものです。
テストで成功したので、これからメインマシンに設定していって明日の朝をまちます。どうかうまく行きますように。
テストMACROは:
Sub Auto_Open()                                    
   |
   |(前略)
   |
Dim myDate As Date
    myDate = DateValue(Now() + 1)
    Application.OnTime TimeValue("07:00:00") + (myDate), "Auto_Open"

    Range("AC4").Value = "Timer OK"
    Range("AD4").Value = Now
End Sub
明日の朝になると結果がでますので楽しみです。 テストで何度もうまくいっているので(ローカルマシン上で時間の設定を変えて日付越えは確認済み)。その後もちゃんと続けてマクロは順調に走っています。
すごく嬉しいです。ありがとうございました。

ご連絡 2011.6.16 SA
いつもありがとうございます。質問ではなくご報告です。
上記のマクロを実行したのですが、何回やってもうまくいかなくて試行錯誤していたのですが
(myDate)( ) を外したら実行しました。
Answer   Copyright (C) 2011.6.18 永井善王
ご連絡いただき、ありがとうございます。 何回も試行錯誤されたとのこと、うまくいくようになって良かったですね。
上記のマクロは拙著の読者さんが、ロンドン駐在当時に懸命に作られたものです。
それを、あなたも役立てていただいていると知られれば、きっと、喜ばれるでしょうね。

あなたからご連絡いただいた内容を、一応、検証させていただきました。
うまくいくようになって喜んでみえるところへ水を差すようですが、後日のために、参考になさってください。

上記のマクロでは、myDate という名前の日付型の変数を使って処理しています。
メッセージボックス1 下記のマクロを任意のブックの Module1 にコピペして実行してみると、右図のメッセージボックスが表示されるはずです。
Sub test1()
Dim myDate As Date
    myDate = DateValue(Now() + 1)
    MsgBox myDate
End Sub
今日つまり、この回答を作成した日は 2011/06/18 ですから、プラス 1 した 2011/06/19 となっていて正解です。

続いて、もう一つ、同様にして実行してみましょう。メッセージボックスが2回表示されますが、2回とも、まったく同じで右図のとおりです。
メッセージボックス2
Sub test2()
Dim myDate As Date
    myDate = DateValue(Now() + 1)
    MsgBox TimeValue("07:00:00") + (myDate)
    MsgBox TimeValue("07:00:00") + myDate
End Sub
以上のとおり、変数 myDate にカッコが有るか無いかはエラーの直接原因にならないのではないでしょうか。
では、なぜ、ですが。
・うまくいかないときのエラーメッセージに表示された原因を取り除くこと
・『日時を扱うデータの型つまり日付型はデリケートである』とでも肝に銘じてコードを検証すること
が必要と思います。 チャレンジしてみても上手くいかない場合は、ご相談ください。

Excel VBA Macro