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

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

 

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

 

VBA時間関数① Timer関数

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

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

 

サンプル

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

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

サンプルの実行結果(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

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

１つ目と２つ目の使用例は

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

 

３つ目の使用例は

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

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

プログラムの内容

 

メリット

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

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

 

デメリット

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

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

 

 

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

プログラムの内容

 

 

 

 

メリット

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

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

 

 

デメリット

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

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

 

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

プログラムの内容

 

 

使い方

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

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

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