Excelセルの書式設定が「時刻」になっている事をVBAで確認する

「Excel VBA Tips」ではExcel VBAを使用していて気付いたことを取り上げて行きます。

今回はセルに「時刻」を設定する際にレアケースがあるため、セルの書式設定が「時刻」になっている事を判定する時にこのレアケースにも対応した方法について改めて調べてみました。

Excel 2016バージョン2010(ビルド 13328.20356)を使用しています。

セルの書式設定における「時刻」のバリエーション

まずはセルの書式設定に組み込まれている「時刻」の表示形式にどのようなものがあるのか?確認します。

表示形式タブの分類で「時刻」に設定されている種類

セルを選択した状態でマウスの右ボタンからショートカットメニューを表示させて、「セルの書式設定」をクリックすると下図が表示されます。

表示形式タブの分類で「ユーザ―定義」に設定されている時刻形式の種類

「ユーザー定義」にデフォルトで組み込み設定されている時刻を持つ形式は下記表の19種類になります。

まずはこれらすべてをセルに設定してどのように表示されるのか?確認する事にします。

• 適用例のセルの値はすべて同じ「12:20:30」にしています。
• 「時刻」に設定されていた9種類は分類の列に「時刻」と表記してあります。

No	時刻形式の適用例	表示形式	分類
1	12:20 PM	h:mm AM/PM
2	12:20:30 PM	h:mm:ss AM/PM
3	12:20	h:mm
4	12:20:30	h:mm:ss
5	12時20分	h”時”mm”分”
6	12時20分30秒	h”時”mm”分”ss”秒”
7	1900/1/0 12:20	yyyy/m/d h:mm
8	20:30	mm:ss
9	20:30.0	mm:ss.0
10	12:20:30	[h]:mm:ss
11	12:20:30	[$-x-systime]h:mm:ss AM/PM	時刻
12	12:20:30	h:mm:ss;@	時刻
13	12時20分30秒	h”時”mm”分”ss”秒”;@	時刻
14	12:20	h:mm;@	時刻
15	12:20 PM	[$-en-US]h:mm AM/PM;@	時刻
16	12:20:30 PM	[$-en-US]h:mm:ss AM/PM;@	時刻
17	1900/1/0 12:20 PM	[$-en-US]yyyy/m/d h:mm AM/PM;@	時刻
18	1900/1/0 12:20	yyyy/m/d h:mm;@	時刻
19	12時20分	h”時”mm”分”;@	時刻

Cell関数format値とNumberFormatLocalプロパティを調べる

前章の表に記載した19種類の適用例を「Cell関数のformat値」と「VBA NumberFormatLOcalプロパティ」で調べた結果が下図になります。

なお赤枠で囲った3種類は「日付」と判断されいる事も分かりますが、その内の2種類は分類が時刻に出てくるものになります。

ただし、この3種類をVBA IsDate関数で調べるとTrueが返りますのでシステム的には「日付」という判断は正しく、どちらかというと時刻に載っているのは人間の使い勝手を意識したものなのかもしれません。

以上のことから、『Cell関数のformat値はすべての「時刻」形式を判定してくれる訳ではない』という事が分かります。

なお今回調べたNumberFormatLocalプロパティにつきましては後述いたしますが、「表示形式」と見比べるとほぼほぼ同じ内容が取得できていることが分かります。

ただし次の2点で違いが生じています。

1. [$-x-systime]⇒[$-F400]
2. [$-en-US]⇒[$-409]

どちらもユーザーが指定するものというよりも、システムによって使われる地域の情報がコード変換されているように思われます。

従いまして、ユーザー目線では違いを意識しなくて良さそうです。

ただし、最初の「x-systime」の文字が「F400」に変換されている事は、後述する話の中でとても重要な要因になってきますのでご記憶に留めて置いていただければ幸いです。

「ユーザ―定義」に設定されている日付形式の種類も合わせて確認する

前章で「時刻」形式の中に「日付」と判断される種類がある事を述べましたので、ここで「ユーザ―定義」に設定されている日付形式の種類について確認しておきたく存じます。

表示形式タブの分類で「日付」をクリックすると2種類の時刻形式が設定されている事が解りますが、先ほどの「時刻」と同様にこの2種類の日付形式は分類の「ユーザ―定義」にも同じものがすべて設定されていますので、「ユーザー定義」に設定されている日付形式を調べる事にします。

「ユーザー定義」にデフォルトで組み込み設定されている日付を持つ形式で「時刻」に出てきた3種類を除くと下記表の15種類になります。

• 適用例のセルの値はすべて同じ「2020/11/26 12:20:30」にしています。
• 「日付」に設定されていた2種類は分類の列に「日付」と表記してあります。

No	日付形式の適用例	表示形式	分類
1	R2.11.26	[$-ja-JP]ge.m.d	日付
2	令和2年11月26日	[$-ja-JP]ggge”年”m”月”d”日”
3	2020/11/26	yyyy/m/d
4	2020年11月26日	yyyy”年”m”月”d”日”
5	2020年11月	yyyy”年”m”月”
6	11月26日	m”月”d”日”
7	11/26/20	m/d/yy
8	26-Nov-20	d-mmm-yy
9	26-Nov	d-mmm
10	Nov-20	mmm-yy
11	2020年11月26日	[$-x-sysdate]dddd, mmmm dd, yyyy	日付
12	令和2年11月26日	[$-ja-JP-x-gannen]ggge”年”m”月”d”日”;@
13	令2年11月26日	[$-ja-JP]gge”年”m”月”d”日”;@
14	令2年11月26日	[$-ja-JP-x-gannen]gge”年”m”月”d”日”;@
15	2020年11月26日	yyyy”年”m”月”d”日”

時刻と同様にCell関数format値とVBA NumberFormatLOcalプロパティで調べた結果が下図になります。

なおNumberFormatLocalプロパティと表示形式の違いは下記の3点になります。

1. [$-ja-JP]⇒[$-411]
2. [$-x-sysdate]⇒[$-F800]
3. [$-ja-JP-x-gannen]⇒[$]

こちらも時刻と同様に違いを意識する必要はなさそうです。

Excelは何をもって書式設定が「時刻」や「日付」であると判断しているのか?

ご存じの方が多いとは思いますが、入力された「時刻」や「日付」は実際の値としては、「時刻」は小数点以下の数値で「日付」は整数で「日付と時刻」の場合は整数と小数点以下の数値の両方で保管されています。

つまりデータだけでは「時刻」なのか「日付」なのか「日付と時刻」なのか、はたまた単なる数値なのか?Excelには区別がつきません。

では「何をもって判断している」のでしょうか?

普通「日付」や「時刻」を入力する時は書式設定が「標準」のセルに、「/」や「-」区切で日付を入力したり、「:」区切で時刻を入力したりするのであまり意識する事はありませんが、Excelは入力されたデータをそれぞれのタイプの数値(整数や小数点以下の数値)に自動変換し、そして書式設定は「標準」からそれぞれの表示形式に自動変換して保存しています。

従いまして『入力した時の入力形式をExcelがどこかに保存していて、それに基づいて「時刻」や「日付」を判断している』分けではなく、Excelはセルの書式設定の表示形式を見て判断するしか情報を持っていません。

そのためVBAで「時刻」を判断するにはNumberFormatLocalプロパティ(≒表示形式)を見る事が必須になるのですが、これまたご存じにようにセルの書式設定の表示形式は自分でカスタマイズができるようになっています。

そして更に問題を複雑化させているのは、「日付」の「月」を表す書式記号と「時刻」の「分」を表す書式記号がどちらも同じ「m」が使われていて、NumberFormatLocalプロパティ(≒表示形式)だけでは判断できません。

でも大丈夫です。慌てる事はありません。ひとつづつ課題をクリア―していく事にいたします。

表示形式の書式記号を調べて課題を洗い出し解決策を考える

まず最初に表示形式に設定する書式記号の中身を正しく理解する必要があります。

そのため下記のMicrosoftドキュメントを一読いたします。

このドキュメント参照しながら、「時刻」と判断する上で考慮が必要なポイントを洗い出して行きます。

日付の「月」と時刻の「分」は同じ書式記号が使われている

前章でもお話しましたが、まずはこの点から考えてみます。

先ほどのドキュメントから該当する説明箇所を下記に引用いたします。

Excelで「日付の月」と「時刻の分」の違いをどのように判断しているか?はドキュメントに次のように書かれています。

重要:“h” または “hh” コード (時間) の直後、あるいは “ss” コード (秒) の直前に “m” または “mm” コードを使用すると、Excel では月ではなく分が表示されます。

という事はこの引用に当てはまらない場所で”m” または “mm”を指定したら、それは「日付」と解釈されるという理解になります。

本当にそうなるのかちょっとだけ確認してみる事にします。

確かに書かれている通りの動きになる事が分かります。

そうなりますと両者を区別するための仕様としてこのまま実装する事も考えられますが、VBAコーディングを長くしたくないというのが正直なところです。

従いまして解決策としては下記を採用する事にいたします。

「時刻」と判定するための書式記号を洗い出す

ドキュメントから該当箇所を下記に引用していますが、下右表は組み込み設定されている時刻の表示形式には出ていない形になっています。

特に経過時間の表示は組み込み設定されている「時刻」の中には含まれていないので注意が必要です。

カスタマイズのバリエーションは無限にあると思いますが、日付が除外された条件において「時刻」と認識されるためには下左表の書式記号「h」「m」「s」のいづれかが表示形式に設定されている必要がある認識です。

従いまして時刻の書式記号をから「時刻」と判定するための解決策としては下記といたします。

表示形式に設定されているテキスト文字は取り除く必要がある

レアケースであると思いますが例えば、明治・昭和・平成を示すテキスト文字として「m」・「s」・「h」を表示形式に設定としていた場合、前章の解決策その②で判定すると表示形式に「h」「m」「s」が１つ以上設定されているために、「時刻」ではないセルを「時刻」と判定してしまいます。

このようにテキスト文字には何が含まれているか?わからないために表示形式から取り除いた上で処理をする必要があります。

ただしMicrosoftドキュメントの中に気になる文言があるので、その部分を下記に引用いたします。

1 つのセルにテキストと数値の両方を表示するには、テキスト文字を二重引用符 (” “) で囲むか、単一の文字の前に円記号 (¥) を付けます。

テキスト文字を二重引用符 (” “) で囲むのは良いのですが、その後に続くの「…か、単一の文字の前に円記号 (¥) を付けます」の箇所が問題です。

日本の場合の通貨記号は「¥」であり、組み込み設定されている「通貨」の表示形式の中にも二重引用符 (” “) 無しで設定されているものです。

例えば「12:20:30」と入力されているセルの表示形式を「¥h」に設定すると「¥12」と表示されます。

そのため「単一の文字の前に円記号 (¥) を付けます」という機能は日本では有効ではないと判断いたします。

従いましてテキスト文字を除外するための解決策としては下記のようにいたします。

【補足】「単一の文字の前に円記号 (¥) を付ける」への対応

MicrosoftドキュメントでFormat関数を見ている時に「ユーザー定義数値書式の作成に使用できる文字–(\)」に関して記述がある事を発見しましたので補足いたします。

書式指定文字列内の次の文字を表示します。 特殊な意味を持つ文字をリテラル文字として表示するには、その文字の前に円記号 (\) を付けます。 円記号自体は表示されません。 円記号を使用するのは、その文字を二重引用符記号で囲むことと同じです。 円記号自体を表示するには、円記号を 2 つ (\) 使用します。 リテラル文字として表示できない文字には次のような文字があります。日付書式文字および時刻書式文字 (a、c、d、h、m、n、p、q、s、t、w、y、/、および :)、数値書式文字 (#、0、%、E、e、コンマ、およびピリオド)、および文字列書式文字 (@、&、<、>、および !)。

要約すると「書式記号を書式指定内に設定する時は一文字ごと前に円記号 (\) を付ける」という事になります。

なお正確にはformat関数で使用する書式記号と「表示形式」で使用する書式記号には微妙な違いがあるのですが上記引用は「表示形式」にも当てはまると推察いたします。

そうであれば、解決策その②を実行する前に「特殊な意味を持つ文字をリテラル文字」を取り除いておく必要がありますが、日本の場合「書式記号の前に付ける文字は円記号ではない」事は確認済みであるために対処したくても対処のしようがありません。

もしもVBAコーディングでこの処理を記述するとしたら、例えば次のような仕様になる認識です。

なお日本の場合は、そもそも円記号 (\)が使用できないので、「この処理は実装しなくても大丈夫」という判断です。

角かっこ([])に囲まれた文字列には何がセットされているのか?

表示形式のなかで角かっこ([])は、色・条件式・経過時間設定とシステム環境を指定するために使用されている認識です。

ただし先の章で確認したようにVBA NumberFormatLocalプロパティ(≒表示形式)で見たときに何がセットされているのか?については正確には把握できていません。

そのため少し気にし過ぎであるとは思いますが、角かっこ([])に囲まれた中の文字列は経過時間設定以外は除外した上で解決策その②の処理をした方が安心です。

そこで角かっこの文字列に対する解決策は下記といたします。

解決策をすべて実装するためのVBAコーディング例

コーディングはできるだけ短くなるようにしています。そのためにステートメントをコロンで区切っている箇所があります。

また分かり難いと思われるコーディング箇所がありますが、その内容は後述いたします。

しかし、そもそも「説明が必要なコーディングとは如何なものか?」というご意見はある事と存じますがあらかじめご容赦いただければ幸いです。

なお仕様としては、ワークシートのセル上に一列に入力されているデータを読み込んで、読み込んだ結果をイミディエイトウィンドウに出力しています。

このコーディングをもとに「対象のセルをオブジェクト変数にセットしてコールする」形のFunctionに変更するためには「【※】」の注釈からのFor文の中身を取り出して加工してください。

Option Explicit
Sub TimeCheck()
 Dim i As Integer, j As Integer, rw As Long, cl As Long, cnt As Integer: cnt = 0
 Dim cladrs As String, strwk As String, strkp1 As String, strkp2 As String, NowCell As Range
 Const sprtr As String = "＿", tmcd As String = "h,m,s"
    ActiveSheet.UsedRange.Select
    cl = Selection(1).Column
    For rw = Selection(1).Row To Selection(Selection.Count).Row
        Set NowCell = Cells(rw, cl)        '【※】処理対象セルをオブジェクト変数にセット
        cladrs = NowCell.Address(False, False)
        If IsDate(NowCell.Value) = True Then
            cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-1 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal
        ElseIf NowCell.Value <> "" Then
            cnt = cnt + 1: strwk = ""
            For i = 0 To UBound(Split(NowCell.NumberFormatLocal, """"))
                If i Mod 2 = 0 Then strwk = strwk & Split(NowCell.NumberFormatLocal, """")(i)
            Next i
            strkp1 = Replace(strwk, "]", "["): strkp2 = ""
            For i = 0 To UBound(Split(strkp1, "["))
                If i Mod 2 = 1 Then
                    strwk = Split(strkp1, "[")(i)
                    For j = 0 To UBound(Split(tmcd, ",")): strwk = Replace(strwk, Split(tmcd, ",")(j), ""): Next j
                    If strwk = "" Then strkp2 = strkp2 & Split(strkp1, "[")(i)
                Else: strkp2 = strkp2 & Split(strkp1, "[")(i)
                End If
            Next i
            strwk = strkp2
            For i = 0 To UBound(Split(tmcd, ",")): strwk = Replace(strwk, Split(tmcd, ",")(i), ""): Next i
            If strkp2 <> strwk Then
                Debug.Print Format(cnt, "000") & "-2 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal & sprtr & strkp1 & sprtr & strkp2
            Else: Debug.Print Format(cnt, "000") & "-3 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal & sprtr & strkp1 & sprtr & strkp2
            End If
        End If
    Next rw
    ActiveSheet.Range("A1").Select
End Sub

解決策その①：11行目

処理対象セルの表示形式が「日付」の場合、IsDate関数はTrueとなり、この条件文に入ります。

「時刻」はこの条件文には入らないので後続処理に進みます。

なおセルに入っているデータが文字列であったとしてもセルの表示形式は「日付」に設定できるのでIsDate関数がTrueを返したとしても数値ではない場合がありますので注意が必要です。

もしセルに入っているデータが数値であることも併せて確認したいのであれば、IsNumeric関数を使用することになりますが、IsNumeric関数は「日付」はFalseを返します。

IsNumeric関数ではTrueになった後に「混じりっけのある数値」を除外するための条件文が必要になりますので、詳細は「セルに入力された値をExcel VBA IsNumeric関数と×1で数値判定する」をご参照いただければ幸いです。

13行目の条件文で範囲指定されている中での空セルを除外

IsDate関数は空文字のセルはFasleを返します。従いまして「時刻」判定をする前に除外が必要になります。

IsNumeric関数の場合は空文字のセルはTrueを返します。「時刻」はIsNumeric関数ではTrueになるので同じように除外が必要です。

解決策その③：15行目から17行目のFor文

ここで対象セルのNumberFormatLocalプロパティからダブルコーテーション(“)でくくられた中身を削除しています。

ところで「Split関数で分割した結果を配列にセットする」というコーディングを文字通りに書き起こすと大変長くなってしまいます。

そこでこれをSlpit関数のままで使用して配列要素を取り出しています。この場合は「Split(対象セル,区切文字)(添え字)」の形で配列要素を取り出すことができます。

標準化されたコーディングでは見た事がない書き方であると存じますが、「Split(対象セル,区切文字)」全体を配列の変数名と見なしていただければ違和感は小さくなる事と存じます。

なおダブルコーテーション(“)が含まれていない場合は、添え字は０のままになりますが、0を2で割った余りは０なので、strwk変数には必ずデータがセットされる事になります。

解決策その④：18行目から26行目まで一連の処理

角かっこは「かっこ開く([)」と「かっこ閉じる(])」がありますが、前章のダブルコーテーション(“)のやり方を使うためにはどちらかにかっこにReplace関数で置き換えて揃える必要があります。

今回は「かっこ開く([)」を使用してかっこの中身を取り出しています。

具体的には「配列の添え字を２で割った余りが１」の位置の文字列を「時刻」の書式記号をカンマ区切りにしたコンスタント変数(tmcd)をSplit関数で配列にセットして、一文字づつ文字列からReplace関数で消去して行きます。

最終的に文字列が空文字になった場合は経過時間の表示形式になるので、消去処理する前の文字列をstrkp2変数にセットします。

「配列の添え字を２で割った余りが0」の条件文は24行目のElse句ですが、Elseの後に「コロン(:)」を付けて行を1つにまとめてありますのでご注意ください。

解決策その②：28行目から32行目

strkp2変数に格納されている値は、ダブルコーテーション(“)の中身を削除し、角かっこでくくられた中身は経過時間の書式記号以外は削除された状態になっています。

「時刻」の書式記号をカンマ区切りにしたコンスタント変数(tmcd)をSplit関数で配列にセットして、一文字づつ文字列からReplace関数で消去した後で、消去する前と後を比較して違いがあれば「時刻」の書式記号が表示形式に含まれていることが確定します。

違いが無い場合は「時刻」ではないのですが、今回は確認のためにイミディエイトウィンドウに出力しています。

イミディエイトウィンドウに出力するフォーマット

このVBAコーディングを実行してイミディエイトウィンドウに出力されるデータのフォーマットは次のようになります。

もっとも設定している内容はコーディング確認が主目的になりますので、必要に応じて修正をしていただければ幸いです。

ただし、NumberFormatLocalプロパティの書式記号をそのままFormat関数にセットした場合に、正しい値にならないケースがある事を確認していますので注意が必要です。

最後に

大変長文になってしまいましたが、最後に「いろいろある事は解ったけれど、表示形式は組み込み設定されているものしか使用していないので、もう少し簡単になりませんか?」と思われる方もいらっしゃる事と存じます。

組み込み設定された表示形式につきましては、ダブルコーテーション(“)でくくられた文字にしても、角かっこでくくられた文字にしても確認する事ができているために、「内容を確認するための処理」は必要ありません。

また「日付」を除いた「時刻」の表示形式には書式記号「mm」がすべてに設定されている事が分かりますので、Instr関数を使って「mm」が存在するか?を確認すれば良い事になります。

従いまして、組み込み設定された表示形式だけであれば次のようなコーディングで対応する事ができます。

Option Explicit
Sub NumericCheck()
 Dim rw As Long, cl As Long, cnt As Integer: cnt = 0
 Dim cladrs As String, NowCell As Range
 Const sprtr As String = "＿"
    ActiveSheet.UsedRange.Select
    cl = Selection(1).Column
    For rw = Selection(1).Row To Selection(Selection.Count).Row
        Set NowCell = Cells(rw, cl)        '【※】処理対象セルをオブジェクト変数にセット
        cladrs = NowCell.Address(False, False)
        If IsDate(NowCell.Value) = True Then
            cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-1 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal
        ElseIf NowCell.Value <> "" And InStr(NowCell.NumberFormatLocal, "mm") <> 0 Then
            cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-2 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal
        ElseIf NowCell.Value <> "" Then
            cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-3 " & sprtr & cladrs & sprtr & NowCell.NumberFormatLocal
        End If
    Next rw
    ActiveSheet.Range("A1").Select
End Sub

イミディエイトウィンドウに出力する内容は前章と同じですが3項目目・4項目目の出力はありません。

以上最後までご一読いただき誠にありがとうございました。