OnTimeでの設定を解除する

繰り返し実行するのには便利なOnTimeメソッドですが、
いろいろと不明な部分もあります。

OnTimeで繰り返しを始めたかを忘れてしまって
2回目の繰り返しを始めてしまうことがあります。
結果パラレルワールドです。

なぜそのようなことになってしまうかと考えてみると
OnTimeで設定したものが調べられないからです。
調べられないから解除もできない。

実はOnTimeメソッドは解除もできます。
前にVBAヘルプのOnTimeメソッドを読んだときには
気が付きませんでした。

しかしこの解除の方法…
なかなか使えないシロモノです。

OnTimeで10秒後に設定するときはこうです。

Application.OnTime _
    Now() + TimeValue("00:00:10"), _
    "マクロ名"

上のコードを実行する時点で
Now() + TimeValue("00:00:10") = "2011/05/21 9:32:20"とする。

このコードで設定したものを解除するには
同じ日時、同じマクロ名で
最後に「, , False」を付加します。
詳細はVBAヘルプにあります。

Application.OnTime _
    CDate("2011/05/21 9:32:20"), _
    "マクロ名", , False

これで解除できます。

次のコードでは解除できずにエラーになります。

Application.OnTime _
    Now() + TimeValue("00:00:10"), _
    "マクロ名", , False

理由はNow関数はその時々によって値が変化していくので
同じ「Now() + TimeValue("00:00:10")」でも
値が違ってきます。

以上の理由でOnTimeの設定をキャンセルするには
どこかに設定した日時を残しておかなければいけません。

なにかいい方法はないかと調べてみました。
マイクロソフトサポートに下のページがありました。
XL2000: Canceling OnTime Macro When Time Argument Is Volatile

このページに書かれている方法は要するに、
開始した時刻から59秒間だけ
15秒間隔でTestMacroプロシージャを実行するということです。

ん～使えない、
やはりシートに日時を残すしかないのか？
まあこの簡便さがVBAがいいなと思うところでもあります。

ではまず
それ用の"OnTime"シートを作成します。
OnTimeメソッドを使用したときには、
ここに引数の値を残します。

もし解除したいときは、
"OnTime"シートの値を使います。
以上

コードはこちら

Sub OnTimeFunc(d As Date, mName As String)
'解除可能なOnTime使い方
   
    'OnTimeシートがなければ挿入
    Dim sh As Object
    For Each sh In Worksheets
        If sh.Name = "OnTime" Then
            GoTo Step1
        End If
    Next sh
   
    Sheets.Add.Name = "OnTime"
    Cells(1, 1) = "OnTime設定"
    Cells(2, 1) = "日時"
    Cells(2, 2) = "マクロ名"
   
Step1:
    'OnTimeの設定を記録
    With Sheets("OnTime")
        .Rows(12).Delete xlShiftUp
        .Rows(3).Insert
        .Cells(3, 1) = d
        .Cells(3, 1).NumberFormat = _
            "yyyy/mm/dd hh:mm:ss"
        .Cells(3, 2) = mName
    End With
   
    'OnTime実行
    Application.OnTime d, mName
   
End Sub

Sub macro110521a()
'OnTime2使用例
    Debug.Print "macro110521a実行"
   
    '繰り返し実行したい処理を記述
    '…

    '次回の実行を設定
    Call OnTimeFunc( _
        Now() + TimeValue("00:00:10"), _
        "macro110521a" _
        )
   
End Sub

Sub macro110521b()
'OnTime2で記録しておいたものを使って
'直前のOnTimeを解除する

    Debug.Print "macro110521b実行"
   
    Dim d As Date
    Dim mName As String
    d = Sheets("OnTime").Cells(3, 1)
    mName = Sheets("OnTime").Cells(3, 2)
   
    '解除する
    On Error Resume Next
        Application.OnTime d, mName, , False
    On Error GoTo 0
   
    '解除した行を削除
    Sheets("OnTime").Rows(3).Delete xlShiftUp
   
End Sub

繰り返し実行したい処理は
macro110521aのコメントで指定した箇所に入れてください。
macro110521bは直前のOnTimeをキャンセルします。

"OnTime"シートの様子：

OnTimeメソッドで何を設定したか忘れて
それを実行したくないときは、
Excelを一旦終了しましょう。
または繰り返しているマクロの途中に
Stopかブレークポイントを作ると中断して停止できます。