VBAで時間を操作【 時間に関する代表的な関数をサンプルを使って紹介 】

エクセル業務で時間を操作する処理が必要なことってありますよね

・ある時刻になったらVBAを実行させたい

・一定の時間が経ったらVBAを実行させたい

・時間を計測したい

 

時間を操作する処理はVBAを使わなければ出来ないことです。時間を操作するVBAによく使われる関数やメソッドを紹介します。


 

広告

VBA時間関数① Timer関数

午前0時から経過した秒数を取得するもの

戻り値は数値型(Single,Double)の宣言になる

 

サンプル

1 Sub tr()
2 t = Timer
3 m = t / 60
4 h = m /60
5 MsgBox m
6 MsgBox h
7 End Sub

変数tには0時から経過した秒数が代入される。

秒数が代入された t を60で割ると分数が求められ、それをさらに60で割ると時間が求められる。

サンプルの実行結果(13時に実行した場合)

 

1つ目のメッセージボックス

 

780
⇒0時から780分経過

 

 

2つ目のメッセージボックス

 

13

⇒0時から13時間経過

 

Timer関数はVBAのプログラム動作時間の計測にも使える関数なので、特性をしっかり覚えておきましょう。

 

VBA時間関数② TimeSerial関数

引数で指定した(時 , 分 , 秒 )の値を返すもの

基本構造

TimeSerial( 時 , 分 , 秒 )

0~23の数値を指定
0~59の数値を指定
0~59の数値を指定

戻り値は日付型(Date)の宣言になる

 

サンプル

使用例 結果
Cells(1,1) = TimeSerial(0,0,1) 12:00:01 AM
Cells(1,1) = TimeSerial(23,59,59) 11:59:59 PM
Cells(1,1) = TimeSerial(24,60,60) 1900/1/1 1:01:00

入力されたせるの書式設定を確認してみると、

1つ目と2つ目の使用例は

A1セルのユーザー定義⇒h:mm:ss AM/PM

 

3つ目の使用例は

A1セルのユーザー定義⇒yyyy/m/d h:mm

 

になっています。

 

入力する値によって書式設定が変わるので注意が必要です。

 

VBA時間関数③ TimeValue

文字列で入力された時間をバリアント(Variant)型、日付(Date)型で返すもの

 

基本構造

TimeValue(“00:00:00")

または

TimeValue(“00:00:00 PM")

 

サンプル

使用例 結果
Cells(1,1) = TimeValue(“4:00:00") 4:00:00 AM
Cells(1,1) = TimeValue(“16:00:00") 4:00:00 PM
Cells(1,1) = TimeValue(“4:00:00 PM") 4:00:00 PM
Cells(1,1) = TimeValue(“6/1 16:00:00") 4:00:00 PM
Cells(1,1) = TimeValue(“6/1 16:00:00 AM") 4:00:00 PM

最後の16:00:00 AM は間違った時間を設定するとどうなるか試してみたのですが、AMが無視された結果となりました。

 

入力されたせるの書式設定を確認してみると、

A1セルのユーザー定義⇒h:mm:ss AM/PM

 

になっています。

 

VBA時間関数④ DateAdd関数

設定した基準時間に設定した時間を加算してする関数のこと

 

基本構造

DateAdd( interval , number , date )

interval 時間を加算する単位(設定値)
number 加算する時間の数や数式
date 時間を加算する基準となる時間

 

 

interval(設定値)の種類

yyyy
q 四半期
m
y 年間通算日
d
w 週日
ww
h
n
s

 

サンプル

現在2020年6月1日13時00分 結果
DateAdd( “yyyy" , 2 , Now) 2022/06/1 13:00:00
DateAdd( “yyyy" , 2 , Date) 2022/6/1
DateAdd( “yyyy" , 2 , Time) 1901/12/30 13:00:00
DateAdd( “h" , 2 , Now) 2020/6/1 15:00:00
DateAdd( “h" , 2 , Date) 2020/6/1 2:00:00
DateAdd( “h" , 2 , Time) 15:00:00
DateAdd( “d" , 2 , Now) 2020/6/3 13:00:00
DateAdd( “d" , 2 , Date) 2020/6/3
DateAdd( “d" , 2 , Time) 1900/01/01 13:00:00

いろんな比較してみました。

interval(設定値)に対応するdate(基準となる時間)を記載しなければ変な結果が出てしまうことがわかります。

取得したい時間が、日付なのか、時間なのかしっかりと決めておく必要があります。

 

VBA時間関数⑤ DateDiff関数

設定した時間と時間の間隔を計算する関数のこと

 

基本構造

DateDiff( interval , date1 , date2 , [firstdayofweek] , [firstweekofyear] )

interval(設定値)は上記DateAddで紹介しているものと全く同じ値なので省略します。

 

firstdayofweekの種類(省略可能)

vbSunday 1 日曜日
vbMonday 2 月曜日
vbTuesday 3 火曜日
vbWednesday 4 水曜日
vbThursday 5 木曜日
vbFriday 6 金曜日
vbSaturday 7 土曜日

 

 

firstweekofyearの種類(省略可能)

vbFirstJan1 1 1 月 1 日が含まれる週から開始
vbFirstFourDays 2 新年の少なくとも 4 日が含まれる最初の週から開始
vbFirstFullWeek 3 年の最初の完全な週から開始

 

サンプル

現在2020年6月1日13時00分 結果
DateDiff( “d" , “2020/6/1" , “2020/6/3") 2
DateDiff( “yyyy" , “2020/6/1" , “2020/6/3") 0
DateDiff( “h" , “2020/6/1" , “2020/6/3") 48
DateDiff( “m" , “2020/6/1" , “2020/9/3") 3
DateDiff( “n" , “2020/6/1 13:00:00" , “2020/6/2 20:00:00") 1860
DateDiff( “n" , “2020/6/2 20:00:00" , “2020/6/1 13:00:00") -1860

基本構造のdate2 – date1を計算し、interval(設定値)の単位で表示してくれる。

 

VBA時間メソッド① OnTimeメソッド

時間を設定してVBAを実行することができる

 

基本構造

Application.OnTime EarliestTime , Procedure , LatestTime , Schedule

Applicationのメソッドなので上記のようになります

 

EarliestTime プロシージャの実行する時刻
Procedure 実行するプロシージャ名
LatestTime プロシージャを実行できる最終時刻
Schedule 新しいOnTimeプロシージャを設定・・True
直前のプロシージャの設定を解除・・False

 

サンプル

Application.OnTime Now + TimeValue(“12:00:00"),"プロシージャ名1 12時にプロシージャ名1を実行
Application.OnTime Now + TimeSerial( 1 , 2 , 3 ),"プロシージャ名2  今から1時間2分3秒後にプロシージャ名2を実行

OnTimeメソッドはエクセル業務でよく使われるメソッドなので使い方を覚えておきましょう。

 

実務でよく使われる時間を操作するVBA

設定した時間が経過するとエクセルが閉じる

プログラムの内容

1.エクセルを開くと同時に制限時間をカウントするVBAを実行

2.時間が経過すると自動で上書保存される

3.VBAが設定されているえくせるのみ閉じる

 

メリット

エクセルの閉じ忘れ対策になる

エクセルを閉じ忘れて、他の人がそのエクセルを開こうとすると「読み取り専用」になって編集できない!ということがありますよね。この問題を解決するのにうってつけのVBAです。

 

デメリット

VBAの内容を使う人全員に周知する必要がある

VBAの内容を知らされていないで自動的にエクセルが閉じるとビックリしますよね。大人数の職場だと周知することが難しい。

 

Left Caption

豆父ちゃん

私は制限時間〇〇分と表示して対応している

 

設定した時刻でプログラムを実行するVBA

プログラムの内容

 

1.VBAを記載したエクセルを開いておく

2.設定した時刻になるとVBAが自動動作

3.処理のの最後に上書き保存のされる

 

 

 

メリット

毎日行う作業に導入すると作業効率アップ

大勢の人が扱うエクセルで自分のが編集する時間が決まっていることってありますよね。そんなときにうってつけのVBAです。

 

Left Caption

豆父ちゃん

私はみんなが休憩している12時にプログラムが動作するよう設定してます。

 

デメリット

設定した時刻にVBAを設定したエクセルを開いている必要がある。

VBAでは「エクセルを一つも開いていない状態からエクセルを開いてVBAを実行する」という処理はできません。「既に開いているエクセルのVBAで他のエクセルを開く」という処理はできるます。

 

カウントダウンタイマーが表示されるVBA

プログラムの内容

 

1.エクセルを起動すると設定した時間のタイマーが動作

2.カウントダウンが0になると上書保存される

3.大勢の人が扱うエクセルならば自動で閉じる処理を追加

 

使い方

エクセルを起動するとカウントダウンタイマーが表示され、編集時間の制限を設けている。

大勢の人が扱うエクセルをずっと開いておくと、次に編集したい人に迷惑をかけてしまいますよね。

カウントダウンタイマーを表示することで編集時間を明確にすることができ、メリハリのある作業ができるようになりました。

 

Left Caption

豆父ちゃん

みんなから意外と好評で、いろんなエクセルにデータに表示してほしいと依頼が!!