【Excel VBA】1秒毎に再計算して時間を表示するツール

このページで書くこと

終了予定時刻を入力するだけで現在時刻からの時間を計算するツールの仕組みを書きます。
「○時間△分後」と「○分後」のどちらにも対応しています。
タイマーのように残り時間をカウントダウンすることもできます。

経緯

会議などで「○時まで意見出し」のような時間の決め方をすることってありませんか？
パソコンやスマートフォンの機能で測るだけならアラームを使用すればいいだけなのですが、会議ツールや物理的なタイマーだと
まず終了予定時間から今の時間を引き算して、そのあと分に変換して……
みたいな計算が必要になって面倒です。というか私はほぼ必ず計算ミスします。

最近、今さらになってZoomのブレイクアウトルームを使用する機会が増えました。
「○時にこのルームを終了させる」といった感じで設定したいのですが、ブレイクアウトルームではそのような設定ができません。できるのは「○分後に終了」という設定です。速く正確な時間の計算が必要です。そんなの無理です。
なので代わりに計算してくれるツールを作成しました。

作成したツール

B2セル「終了時間」に時間を入力すると自動で計算されます。
D2セル[自動更新]ボタンをクリックするとA2セル「今の時間」や計算結果の変化が1秒毎にわかります。ツールに時間を入力する際の目安にしています。
F2セル[停止]ボタンは自動更新を止めるボタンです。Excelごと終了しても問題ありませんが、いちいち終了するのも面倒なのでつけました。
E3セル[手動更新]ボタンは一度だけ計算を更新させます。

f:id:arekoretips:20211014220016j:image:w400 — 使用画面

また、「終了時間」が「今の時間」より遅い場合は計算しません。

f:id:arekoretips:20211014221201j:image:w400 — 「終了時間」が「今の時間」より遅い場合

ツールは以下のGoogleドライブに保存してあります。ダウンロードしてご使用ください。
drive.google.com

使用するコードと関数

1．関数

1-1．今の時間

以下の関数を入力します。
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

f:id:arekoretips:20211014222911j:image:w400 — 今の時間

現在時刻はTIME関数を使用して時分秒を分けて入れます。NOW関数だけで入れられたらよかったのですが、NOW関数は「YYYY/MM/DD HH:MM:SS」の形式のため「終了時間」のセルと計算させる場合は「終了時間」セルも同じ形式で入力しなくてはいけなくなります。
「明日の○時まで」など日付を跨いだ時間設定がしたい場合はその形式がいいと思いますが、私の仕事では日付を跨いで会議をすることがないので省略しました。
反対に、日付を跨いだ時間設定がしたい方はA2セルに「=NOW()」と入力し、B2セルに「YYYY/MM/DD HH:MM:SS」形式で時間を入力するのがいいと思います。
　

1-2．計算結果（時分秒形式）

計算結果は、ただ「終了時間」の値から「今の時間」の値を引いているだけです。
エラー防止のため、「終了時間」の値が「今の時間」の値より大きい場合のみ計算するようにしています。
　
1-2-1．時間
=IF(B2>A2,HOUR(B2-A2),"")

f:id:arekoretips:20211014224508j:image:w400 — 時間の計算結果

　
1-2-2．分
=IF(B2>A2,MINUTE(B2-A2),"")

f:id:arekoretips:20211014224704j:image:w400 — 分の計算結果

　
1-2-3．秒
=IF(B2>A2,SECOND(B2-A2),"")

f:id:arekoretips:20211014224803j:image:w400 — 秒の計算結果

1-3．計算結果（分秒形式）

時間を分に直す場合は1時間＝60分として計算しているだけです。
　
1-3-1．分
=IF(B2>A2,(HOUR(B2-A2)*60)+MINUTE(B2-A2),"")

f:id:arekoretips:20211014225143j:image:w400 — 分の計算結果

　
1-3-2．秒
=IF(B2>A2,SECOND(B2-A2),"")
※前述の秒計算と同じです。

f:id:arekoretips:20211014225342j:image:w400 — 秒の計算

2．使用するコード

このツールでは3つのプロージャー（Subのかたまり）を作成しました。
　

2-1．全体

「再計算」「自動更新」「自動更新停止」の3つのプロージャーがあります。
「自動更新」は「再計算」を1秒毎に呼び出すだけの存在です。
複数のプロージャーで使用するため、Date形式の変数「oTime」はパブリックにしてプロージャーの外に出しました。

Public oTime As Date

Sub 再計算()
'
    ActiveSheet.Calculate
'
End Sub

Sub 自動更新()
'
    oTime = Now + TimeValue("00:00:01")
    Call 再計算
    Call Application.OnTime(oTime, "自動更新", , True)
'
End Sub
 
Sub 自動更新停止()
'
    On Error Resume Next
    Call Application.OnTime(oTime, "自動更新", , False)
    '
End Sub

2-2．詳細

1-2-1．計算更新
現在表示しているシートの関数の再計算をします。
「ActiveSheet」を別のシート名にすれば特定のシートのみを再計算させることも可能です。

Sub 再計算()
'
    ActiveSheet.Calculate
'
End Sub

　
1-2-2．自動更新
パブリック関数「oTime」に今の時間＋1秒を足して、「再計算」プロージャーを実行します。
その後OnTimeメソッドで自身を呼び出し、再度1秒足して「再計算」を実行します。
OnTimeメソッドは以下の形式です。
OnTime (EarliestTime, Procedure, LatestTime, Schedule)

EarliestTime	開始時間（ここでは「oTime」）
Procedure	実行するプロージャー（ここでは「再計算」）
LatestTime	終了時間（ここでは指定していません）
Schedule	OnTimeメソッドを実行する場合は「True」、実行中のOnTimeメソッドを停止させる場合は「False」です。「True」の場合は省略可能

Sub 自動更新()
'
    oTime = Now + TimeValue("00:00:01")
    Call 再計算
    Call Application.OnTime(oTime, "自動更新", , True)
'
End Sub

※参考
Application.OnTime メソッド (Excel) | Microsoft Docs
　
1-2-3．自動更新停止
OnTimeメソッドで繰り返している「自動更新」プロージャーを停止させます。
「自動更新」プロージャーでは「True」だった「Schedule」パラメーターを「False」に変更します。
当然ですが「EarliestTime」と「Procedure」は停止させたいプロージャーに書かれているOnTimeメソッドと同じものを書かなければ停止しません。
「On Error Resume Next」はエラーが発生しても次のステートメント（ここでは「Call」以降）から実行させるために書いています。これでなにかエラーが発生していてもとりあえず停止してくれます。

Sub 自動更新停止()
'
    On Error Resume Next
    Call Application.OnTime(oTime, "自動更新", , False)
    '
End Sub

まとめ

本当はこんなことをしなくてもツール側でいい感じにしてほしいです。

技術tips保管庫

Excel VBAを中心に、仕事で役立つあれこれを記録