300銘柄ずつのダウンロードを13秒間隔で15回繰り返すには?

Question 5308 拙著「そのまま使える実用マクロ 500連発」 Previous Next
詳細は こちら サンプルNo.029 ◆ 環境・・ Excel 2007 & Windows XP

私はこのマクロを、(楽天証券のマーケットスピードを使って)東証・大証等、約全4500銘柄の毎日の株価のダウンロードに使っています。
例えば、東証1部(東1:T) の極洋(コード番号1301) の現在値と出来高をダウンロードするためには、Excelのセルに数式「 =RSS|'1301.T'!現在値 」と「 =RSS|'1301.T'!出来高 」と記入して、マーケットスピードを立ち上げれば、インターネット回線を通じて、しばらく時間がたてば(13秒ぐらい)、それぞれのセルにその現在値と出来高がダウンロードされます。
具体的には、Excelのセルにマトリックスで

以下、約4500銘柄を記入したワークシートを作っておき、マーケットスピードを立ち上げると、13秒後に全銘柄の現在値と出来高がダウンロードされることになる。
ところが、マーケットスピードとインターネット回線の制約上、「1度に300銘柄しかダウンロードできない」 ことになっている。 それでとりあえず、全4500銘柄の数式の頭に「#」を付けて (「#=RSS|'1301.T'!現在値」 「#=RSS|'1301.T'!出来高」)、数式を無効にしておきます。

【マクロの作成】
 (1)初めの300銘柄の「#」を削除して数式を有効にする。
 (2)ダウンロードするのに13秒経つのを待つ。
 (3)ダウンロードした数値を、別の場所に値複写する。
 (4)それらの300銘柄の数式の前に「#」を記入して数式を無効にする。
 (5)次の300銘柄を同様にしていく……
 (6)これを15回繰り返す(4500銘柄)。
【課題】  (1)~(5)までのマクロは問題ないのですが、(6)15回繰り返すマクロを記入(作成)することができません。
 (繰り返しのマクロの作り方は分かるのですが、それをどこに記入するか?が分かりません。うまくいきません。)。
現状は以下のようなマクロを作成し、
の部分の「0」を「1」「2」…「15」と順次変更してマクロを実行し、問題なくダウンロードができています。
Option Explicit
Const 始 As Integer = 1             '行の開始セルである「始」を「現在値」に設定
Const 終 As Integer = 2             '行の終了セルである「終」を「出来高」に設定
Const 段始 As Integer = 1 * 300     'マクロを有効にする最初の銘柄である「段始」を
                                    '「0」から「1」「2」…「15」と変更して、300銘柄
                                    'づつマクロ実行する
Const 段終 As Integer = 段始 + 300  'マクロを有効にする最後の銘柄である「段終」を
                                    '「段始」から300銘柄目に設定
Sub 自動記録()
    Dim 指定時刻 As Date
    Dim 待ち時間 As Date
    有効化
    指定時刻 = Now + TimeValue("0時00分13秒")   '13秒       '13秒待つ
    待ち時間 = TimeValue("0時00分01秒")
    Application.OnTime TimeValue(指定時刻), _
    "転写と値複写", _
    TimeValue(待ち時間)
End Sub
Sub 有効化()                        '「#」を削除して数式を有効にする
    Cells(2, 3).Select
    ActiveCell.Range(Cells(段始 + 1, 始), Cells(段終, 終)).Select
    Selection.Replace What:="#=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveCell.Offset(299, 0).Range("A1").Select
End Sub
Sub 転写と値複写()                  'ダウンロードしたデータを移動して、値複写する
    Cells(2, 3).Select
    ActiveCell.Range(Cells(段始 + 1, 始), Cells(段終, 終)).Select
    Selection.Copy
    ActiveCell.Offset(0, 50).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(299, -50).Select
    Cells(2, 3).Select
    ActiveCell.Range(Cells(段始 + 1, 始), Cells(段終, 終)).Select
    Selection.Replace What:="=", Replacement:="#=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveCell.Offset(299, 0).Range("A1").Select
                 Application.Speech.Speak "end", SpeakAsync:=True
End Sub
○質問1  For ~ Next 等のマクロを使って 『※の部分の「0」を「1」「2」……「15」』の自動化したいのですが、うまくいきません。  どうすれば、いちいち「0、1、2、……、15」と入力し直して、実行する必要がないようにできるでしょうか?

○質問2  指定時刻(例、毎日18:00)にマクロ(ダウンロード)を開始するマクロはできないか?
「実用マ 029(指定時刻の一定時間後にマクロを実行する)」と、「実用マ 030(マクロの実行を一時中断し指定時刻に再開する)」の2つのマクロを使って、いろいろやっていますがうまくいきません。 何卒宜しくお願いします
Answer   Copyright (C) 2007.10.29 永井善王
拙著「Excel VBA そのまま使える実用マクロ 500連発」をご購読、ありがとうございます。
No.029の 「指定時刻の一定時間後にマクロを実行する」 と題するマクロは下記のとおりです。
Sub 指定時刻にマクロを実行する()
    指定時刻 = Now + TimeValue("00:00:04")  '(注)
    待ち時間 = TimeValue("00:03:00")
                                '指定時刻に他のプロシージャが実行中の場合の待ち時間
    Application.OnTime TimeValue(指定時刻), _
        "指定時刻に実行するマクロ", _
        TimeValue(待ち時間)
End Sub
'------------------------------------------------------------------------------
Sub 指定時刻に実行するマクロ()
    MsgBox "指定時刻(待ち時間あり)になりました。" & vbCr & vbCr & _
        "処理を再開します。", , Worksheets("Title").Range("書名").Value
End Sub
これに関してのご質問ということですが、拝見しますと、このテクニックを応用した新しいマクロを作成する方法を知りたいようですね。
私は、このように読者質問の域を超えたお問い合わせにも、出来る限り相談に乗るように努めていますが、ただいま次の本の執筆中で、あまり時間がありません。 ですが、丁度、土日にあたりましたので、ヒントだけの回答になりますがまとめておきます。 ご了承ください。

・毎日一定時刻にマクロを自動実行する方法 (質問2への回答)
これにつきましては、拙著
ExcelVBAマクロ組み方講座―Excel97/98/2000/2001/2002/v.X対応 」 の P.316~322に詳しい解説があります。
各地の図書館にもあるようですから、参照してください。

・For...Next文などでマクロを自動化する方法 (質問1への回答)
1回のダウンロードで300銘柄、4500銘柄だから15回繰り返し。なら、For...Next ・・ と考えるのは自然の流れと思います。しかし、No.029では OnTimeメソッドを利用しているので、その方法は使えないと思います。
高等テクニックとして、OnTimeメソッドの 引数Procedure (実行するプロシージャ名) に自分自身 (上例の場合は「指定時刻にマクロを実行する」) を指定する方法があります。これなら 1つのサブプロシージャで済みますが、永久ループにならないように終わらせる工夫が必要になります。
よって、このホームページの「一定の時間間隔でマクロを実行する」 で紹介されているテクニックを使った分かりやすいマクロにされることを、お勧めします。 下記に、あなたのマクロに取り入れるためのポイントを示します。
Option Explicit
Const 始 As Integer = 1             '行の開始セルである「始」を「現在値」に設定
Const 終 As Integer = 2             '行の終了セルである「終」を「出来高」に設定
    Dim 段始 As Integer
    Dim 段終 As Integer
    Dim 指定時刻 As Date
    Dim 待ち時間 As Date
Sub 自動記録()
    段始 = 0 * 300: 段終 = 段始 + 300
    有効化
    指定時刻 = Now + TimeValue("0時00分13秒")   '13秒待つ
    待ち時間 = TimeValue("0時00分01秒")
    Application.OnTime TimeValue(指定時刻), "自動記録2", TimeValue(待ち時間)
End Sub
Sub 自動記録2()
    転写と値複写
    段始 = 1 * 300: 段終 = 段始 + 300
    有効化
    指定時刻 = Now + TimeValue("0時00分13秒")   '13秒待つ
    待ち時間 = TimeValue("0時00分01秒")
    Application.OnTime TimeValue(指定時刻), "自動記録3", TimeValue(待ち時間)
End Sub
Sub 自動記録3()
    転写と値複写
    段始 = 2 * 300: 段終 = 段始 + 300
    有効化
    指定時刻 = Now + TimeValue("0時00分13秒")   '13秒待つ
    待ち時間 = TimeValue("0時00分01秒")
    Application.OnTime TimeValue(指定時刻), "自動記録e", TimeValue(待ち時間)
End Sub
Sub 自動記録e()
    転写と値複写
    MsgBox "終わりました。"
End Sub
Sub 有効化()                        '「#」を削除して数式を有効にする
    | (変更なし)
End Sub
Sub 転写と値複写()                  'ダウンロードしたデータを移動して、値複写する
    | (変更なし)
End Sub
説明の必要はないかもしれませんが、Sub 自動記録3() の後に Sub 自動記録4()Sub 自動記録5() ・・と書き並べることになりますが、一度限りの簡単な力仕事ですね。 なお、時間がないので Sub 転写と値複写() とか突っ込んで見ていませんが、Sub 有効化() は無くせるような気がします。

参考ページ
 ・
タイマーを使ってマクロの動きを制御するには?
 ・
30分おきにマクロを自動的に走らせるには?
 ・
開始時刻から終了時刻まで一定間隔でマクロを実行するには?
 ・
タイマー設定をキャンセルするには?
 ・
予定時刻に音を鳴らすことはできないか?
 ・
翌日の指定時刻にマクロを自動実行するには?
 ・
毎日指定の時刻にマクロを実行するには?
終わりになりましたが、大がかりなマクロをよくここまで組まれましたね。 あと少しですから頑張ってください。

P.S. このように読者質問の域を大きく超えるご質問は、次回から
受付期間限定のご質問のページ、または、Excel VBA Board をご利用ください。 なお、ズバリのマクロを望まれる場合は、 オーダーをお勧めします。 私も本の執筆をしていない時は、お受けできます。
後日情報
「指定時刻の一定時間後にマクロを実行する」と題するマクロは、2012年3月に出版された下記の本にも掲載されています。
 ・
Excel VBA 実用マクロ 800連発 [中・上級編] の698番

Excel VBA Macro