Excelで自動化するものを作って、動作確認をしているときに閉じてもいつの間にか復活する、という怪現象が出る場合があります。
原因は、VBAで一定時間経過後に他のマクロを呼び出すOnTimeを使っていることです。
この場合の原因を解説し、対処法を考えていきます。
OnTimeとは
OnTimeはある時間になったら指定したマクロを自動的に実行するように設定するものです。
今月の最終日の23時になったら請求書処理のマクロを自動的に行うということが可能で、その日のその時間に人が操作しなくてもその場にいなくても、その時間になったら自動的に動作が開始する便利な設定です。
OnTimeの設定
OnTimeの設定は次のように行います。
Application.Ontime マクロの開始日時,マクロ名
もしも2021年3月31日の23:00に請求書作成マクロを実行する場合は次のようにVBAに記述します。
Application.Ontime DateValue ("2021/3/31") + TimeValue("23:00:00"), "請求書作成"
このように簡単に設定ができます。
もしも、解除したい場合は、次のように,,Falseを後ろに付けます。
Application.Ontime DateValue ("2021/3/31") + TimeValue("23:00:00"), "請求書作成",,False
経過時間
OnTimeは何分後にマクロを動かすという設定もできます。今の時間をNow関数で求め、その時間にマクロを動かしたい時間を足せばその時間に起動します。
Application.Ontime Now() + TimeValue("00:01:00"), "請求書作成"
例えば1秒ごとにセルに現在時間を表示したい場合は、次のようなVBAになります。
Sub 経過時間()
Range("A1").Value=Now()
Application.Ontime Now() + TimeValue("00:00:01"), "経過時間"
End Sub
経過時間マクロは、セルA1に現在時刻を書き込んで、1秒後にまた経過時間マクロが実行されるように設定します。これにより、ずっと1秒後にセルA1に書き込む動作が実行されます。
さて、問題はここからなのですが、この経過時間を解除したい場合はVBAでどうしたらいいでしょうか。前の説明通りだと次のようになります。
Application.Ontime Now() + TimeValue("00:00:01"), "経過時間",,False
しかし、設定した時点のNow()と解除する時点のNow()は違います。この違いによってこの方法では解除することができません。セルA1に記録した時のNow()と設定した時のNow()も違うかもしれません。
解除すべきNow()をどこかに記録しておいて、その値を使って解除する必要があります。
しかしこのマクロの場合は1秒ごとにNow()が変わっていきますので、その都度記録して、その記録したものを追っていくというのも大変かもしれません。
また解除をどんなシチュエーションで行うのかにもよります。意図的に止めたいとしたら、いつでしょうか。
起動でExcelが起動する原因
どうやらOnTimeで予約した時間にマクロが動き出すのは、ブックが開いていなくても動き始めるようなのです。
なので、OnTimeをVBAで仕掛けておくと、その時間になったらExcelが起動されている限り、Excelのそのブックが開いてマクロが実行される、ということがおきます。
1秒ごとに実行するマクロは頻繁にこれが起きるのでブックが閉じられないという現象になります。
どう対処したらいいか
対処の方法はいくつか考えられます。Excelを閉じてしまえば、予約はリセットされるようなので、ブックを閉じるだけではなくExcelの閉じるようにするというのも手です。VBAで言えばQuitを使います。
しかし、閉じるときに保存するかどうかの選択をしているうちに1秒経過してしまうとまたブックが開きます。
次のようにバブリック変数xを使って、始めの30秒だけ動くという設定をするなど、絶対に続かない仕掛けづくりも重要かと思います。
Sub 経過時間()
Range("A1").Value=Now()
x = x + 1
If x < 30 then
Application.Ontime Now() + TimeValue("00:00:01"), "経過時間"
end If
End Sub
コメント