開始時刻から終了時刻まで一定間隔でマクロを実行するには?

Question 5312   Previous Next
詳細は こちら サンプルNo.029 ◆ 環境・・ Excel 2003 & Windows XP

マクロを組んでいまして、指定時間になるとマクロが動き出します。 このマクロに、1秒ごとにコピー貼り付けを繰り返しするマクロを、下記のとおり組んでいます。
朝~夜まで12時間ほどデータ収集するようにしたいのです。
現状ではある時間でマクロが起動し、データ収集までは動いていますが 、ある時間(例えば夜9時、もしくは不定期)にマクロを止めたい場合はどうしたらよいでしょうか? 宜しくお願いします。
Sub timer1()
    指定時刻 = TimeValue("08:58:00")
    待ち時間 = TimeValue("00:00:05")
    Application.OnTime TimeValue(指定時刻), _
        "時刻書き込み", _
        TimeValue(待ち時間)
End Sub

Sub 時刻書き込み()                  '1秒ごとに価格と時刻データを書き込んでいく
    Sheets("RSS").Select
    Range("E19:f19").Select
    Selection.Copy
    Sheets("テスト").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Offset(1, 0).Select '1行ずらす
    指定時刻 = Now + TimeValue("0時00分01秒")   '1秒後
    待ち時間 = TimeValue("0時00分00秒")
End Sub
Answer   Copyright (C) 2009.5.23 永井善王
「1秒ごとにコピー貼り付けを繰り返す」とは、精緻な作業に取り組んでみえるのでしょうね。
最初にお断りしておきますが流れをわかりやすくするために、変数「待ち時間」に関するコーディングをカットして進めます。 理由はVBEのヘルプの 「OnTimeメソッド」 を読んで理解してください。

あなたは、きっと、質問を出される前にアレコレ試されているでしょうから、理屈っぽい説明を後回しにして、ズバリ、回答からお示しします。
下記は、あなたのマクロをできるだけ活かし、主な修正カ所を赤字で示したものです。
Option Explicit
Dim 指定時刻, 終了時刻

Sub timer1()
    Sheets("テスト").Select
    指定時刻 = TimeValue("09:00:00")
    終了時刻 = TimeValue("21:00:00")
    Application.OnTime 指定時刻, "時刻書き込み"
End Sub

Sub 時刻書き込み()
    If TimeValue(Now) >= 終了時刻 Then          '終了時刻になっら終わる
        MsgBox "終了時刻になりました。"
        Application.CutCopyMode = False
        Exit Sub
    End If
    Sheets("RSS").Range("E19:f19").Copy       '価格と時刻データをコピー
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.Offset(1, 0).Select '1行ずらす
    指定時刻 = Now + TimeValue("0時00分01秒")   '1秒後
    Application.OnTime 指定時刻, "時刻書き込み"
End Sub
このマクロの最大の特徴は、再帰処理を行っていることです。下から2行目にあるコードがそれにあたり、OnTimeメソッドの2番目の引数 (指定時刻に実行するプロシージャ名) に自分自身を指定しています。
再帰処理は「再帰呼び出し」とも呼ばれ、このように再帰的に呼び出すことによって繰り返し処理を行うので、コンパクトなマクロにできます。
しかし、終了条件をきちんと行わないと無限ループに陥ってしまったりするので、慎重に行いましょう。
では、結果をお知らせください。 お待ちしています。

参考ページ
 ・
タイマーを使ってマクロの動きを制御するには?
 ・
30分おきにマクロを自動的に走らせるには?
 ・
タイマー設定をキャンセルするには?
 ・
予定時刻に音を鳴らすことはできないか?
 ・
翌日の指定時刻にマクロを自動実行するには?
 ・
毎日指定の時刻にマクロを実行するには?
後日情報
「指定時刻の一定時間後にマクロを実行する」と題するマクロは、2012年3月に出版された下記の本にも掲載されています。
 ・
Excel VBA 実用マクロ 800連発 [中・上級編] の698番
ありがとうございました
マクロを修正して頂きまして感謝いたします。
本日実際に12時間動かしてみまして、問題なくマクロの停止もOKです。
条件分岐で組むのですね、勉強になりました。 これからまた色々と挑戦してみます。 ありがとうございました。

Excel VBA Macro