VBAで日ごろの業務を自動化させるという場面は多いのではないでしょうか。プログラムを作成するに当たりよくあるの一定間隔で処理を実行させたいという場面です。そのような動作をVBAでもする方法を解説します。

VBAで指定時間に処理を実行する方法

VBAで一定時間ごとに処理を実行させたいという時につかえそうなのがApplication.OnTimeです。Application.OnTimeを使用すると、指定した時間に指定したプロシージャを実行することができます。

これをうまく利用する事で、一定間隔ごとに処理を実行させることができます。

OnTimeメソッドは以下の様に最大4つの引数を取ります。
OnTime(EarliestTimeProcedureLatestTimeSchedule)

  • EarliestTime(必須):プロシージャを実行する時刻を指定します。
  • Procedure(必須):実行するプロシージャ名を実行します。
  • LatestTime(省略可能):EarliestTimeで指定した時刻になってもExcelがProcedureを実行できる状態に無かった場合、最大LatestTimeまで待機します。
  • Schedule(省略可能):Trueの場合、設定したスケジュールをキャンセルします。

Microsoftの公式リファレンスの例には以下の様に記載されています。この例では、現在時刻より15秒後に、"my_Procedure"を実行するということになります。

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
ポイント

VBAでは、Application.OnTimeを使用することで、指定した時間に指定した処理を実行することができます。

一定間隔で処理を繰り返す方法

Application.OnTimeメソッドだけでは、指定時間に指定した処理を実行することしかできません。そこで少し工夫をして、一定間隔で処理を実行してみましょう。

考え方としては、一定間隔で実行したい処理をプロシージャにまとめる。そのプロシージャ内でApplication.OnTimeを実行し、自身のプロシージャを呼び出します。そうすることで、一定間隔で繰り返し処理を実施できます。

メインのプロシージャだけを以下に示します。

Sub Timer_Event()
   '何かやりたい処理を書く
   
   '次にタイマーを実行するのを現在より10秒後にセット
   next_time = Now() + TimeValue("00:00:10")
   
   'タイマーでは自分自身を呼び出し、繰り返し処理させる
   Application.OnTime EarliestTime:=next_time, Procedure:="Timer_Event"
   
End Sub

1行目~一定間隔で処理したい内容を記述します。
3行目で、次に同じ処理を実行したい時刻を指定します。
Now()で現在時刻を取得し、TimeValue()で経過分を表します。例では、10秒後に実行することにしています。
6行目でApplication.OnTimeメソッドを呼び出しています。ここで重要なのは、Procedureに自身のプロシージャを指定することです。これによって10秒後に再び処理が実行され、その10秒後にまたスケジュールが登録され繰り返されます。

ここで注意点があります。一定間隔で処理と書いてきましたが厳密はそうではありません。実行したい処理が完了して10秒後に処理を実施します。例えば処理に3秒かかった場合は、その10秒後、つまり最初の処理が開始された時点から13秒後ということになります。

一定間隔で処理を実施し、キャンセルするコード

最後に、全体のサンプルコードを掲載します。このコードでは、シート上のボタンでタイマーをスタートさせ、10秒ごとにセルに現在時刻を記載します。また、もう1つのボタンでタイマー処理を止めることができます。

エクセルのシートは以下のようになります。スタートとストップの2つのボタンを用意しています。

実際のコードは以下のようになります。

'時刻を入力する行番号
'※onTimeメソッドの引数(Procedure)は引数を取れないためグローバル変数で管理
Dim i As Integer

'次にタイマーがセットされている時刻
'設定済みのタイマー処理をキャンセルするにはこの時刻がないとできない
Dim next_time As Variant

'タイマー処理をスタート
Sub ボタン1_Click()
    i = 1
    Timer_Event
End Sub

'タイマー処理をストップ
Sub ボタン2_Click()
    Application.OnTime EarliestTime:=next_time, Procedure:="Timer_Event", Schedule:=False
End Sub


Sub Timer_Event()
   'A列に現在時刻を入力する
   ThisWorkbook.Sheets("Sheet1").Cells(i, 1) = Now()
   i = i + 1
   
   '次にタイマーを実行するのを現在より10秒後にセット
   next_time = Now() + TimeValue("00:00:10")
   
   'タイマーでは自分自身を呼び出し、繰り返し処理させる
   Application.OnTime EarliestTime:=next_time, Procedure:="Timer_Event"
   
End Sub

PVアクセスランキング にほんブログ村

Twitterでフォローしよう

おすすめの記事