ExcelVBA で時間が正しく変換されない
あらすじ
ExcelVBA で時間が入力されたセルの値を受け取るとセルの表示形式が「文字列」でない場合浮動小数点数になりますよね。
たとえば A1 セルに 「9:45」 という時間の値が入力されている場合
Dim data as String
data = Cells(1,1).Value
この変数 data には 「9:45」 ではなく 「0.40625」 という値が格納されます。
これを ExcelVBA の処理で別のセルに入力させると 「9:45」 ではなく 「0.40625」 という値が入力されてしまいます。
Cells(1,2).Value = data
これは Excel では時間の値をシリアルタイムで管理しているからなのです。
セルの表示形式が 「時刻」 や 「ユーザ定義」 の場合、時刻は入力した通りに表示されるはずです。
「標準」 のセルに時間を入力すると表示形式は 「ユーザ定義」 になります。
で、時間を入力したセルの表示形式を 「標準」 に戻すと入力した時刻とは別の数値になりますよね。
たとえば 「9:45」 と入力したセルの表示形式を 「標準」 にするとセルの値は 「0.40625」 となります。
これがシリアルタイムです。
ExcelVBA でも同様のことが起きていて、時間を入力したセルの値はシリアルタイムで受け取ることになります。
このシリアルタイムを 「時刻」 形式の文字列に変換する関数に Format 関数があります。
Format 関数 (Visual Basic for Applications) | Microsoft Docs
Cells(1,2).Value = Format(data, "hh:nn")
前置きが長くなりましたが、ここからが本題です。
「9:36」 と入力されたセルの値を ExcelVBA で受け取り、Format 関数で 「時刻」 形式に変換してセルに入力すると、想定とは違う時刻になっていたのです。
Dim data as String
data = Cells(1,1).Value
Cells(1,2).Value = Format(data, "hh:nn")
確認したところ 「時:分」 の形式だ下記のパターンの場合に正しく変換できませんでした。
「時:分:秒」 の形式だともっとたくさんパターンがありますが割愛します。
さて困りました。
対処方法
結論からいうと Format 関数に String 型の値を渡してしまったことが問題でした。
たとえば 「9:36」 のシリアルタイムは 「0.4」 です。
Format 関数は受け取った値が String 型かつ小数点以下が 2 桁以下の場合、変換するとその値をそのまま 「分」 と認識して変換してしまう仕様のようです。
つまり 「0.4」 を 「0時4分」 と認識して 「0:04」 と変換したのです。
対処方法としては Format 関数に渡すシリアルタイムの値を String 型でなく Double 型で渡せば正しく変換されます。
Dim data as Double
data = Cells(1,1).Value
Cells(1,2).Value = Format(data, "hh:nn")
時間の値を受け取る変数を Double 型にしてしまうのがいちばん手っ取り早いかとは思います。
これだけで事足りるプログラムであればこれで OK でしょう。
そもそもなんで String 型で受け取っていたかというとセルの入力チェックをしたかったからです。
時間以外のデータ (Double 型で受け取れないデータ) も入力されうる想定だったので、一旦 String 型で受け取って Double 型に変換できない場合は警告を出したりする処理にしたかったのです。
では、時間の値を String 型で受け取る場合はどうすればいいかというと、Format 関数に変数を渡す際に Double 型に変換してあげればよいのです。
Double 型に変換するには CDbl 関数を使用します。
Sub example()
Dim data As String
data = Cells(1, 1).Value
' 変数 data をチェックする処理
Dim errFlg As Boolean
On Error GoTo Err:
errFlg = True
data = CDbl(data) ' data の値が Double に変換できない場合エラーとなり Err: の処理に飛ぶ
errFlg = False
Err:
If errFlg Then
MsgBox "時刻の形式で入力してね"
Exit Sub
End If
Cells(1, 2).Value = Format(CDbl(data), "hh:nn")
End Sub
(おまけ) シリアルタイムについて
Excel でいうところのシリアルタイムは 「0時0分0秒」 を 0 とし、「24時0分0秒」 を 1 として時刻の秒数を 24 時間の秒数 (86400) で割った値です。
たとえば 「9:36」 は秒に直すと
9 * 60 * 60 + 36 * 60 = 34560
シリアルタイムは
34560 / 86400 = 0.4
という感じで算出できます。
