Format関数と表示形式の違い

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

Format関数は、数値や日付などに書式を設定する関数です。たとえば、次のように使います。

Sub Sample1()
    MsgBox Format(Now, "ggge年")
End Sub

Format関数で指定できる書式記号は、[セルの書式設定]ダイアログボックス[表示形式]タブで指定できる書式記号とは、微妙に異なっています。ここでは、Format関数だけで使える書式記号をいくつか紹介しましょう。

まず、Format関数には「定義済み書式」というのがあります。たとえば、こんなやつ。

Sub Sample2()
    MsgBox Format(Now, "Long Date")
End Sub

主な定義済み書式は次の通りです。

・日付/時刻に関する定義済み書式

定義済み書式 解説 例
Long Date 日付の長い形式(コントロールパネルの設定)で表した日付を返します 2008年11月27日
Medium Date 簡略形式で表した日付を返します。ホスト アプリケーションで使用されます 08-11-27
Short Date 日付の短い形式(コントロールパネルの設定)で表した日付を返します 2008/11/27
Long Time 時刻、分、秒を含む形式で表した時刻を返します 14:11:09
Medium Time 時間と分を12時間制で表した時刻を返します。同時に午前、午後も追加します 02:11 午後
Short Time 時間と分を24時間制で表した時刻を返します 14:11

・数値に関する定義済み書式
Currency 通貨記号、小数点以下の桁数、1000単位の区切り記号など、オペレーティングシステムの国別情報の設定による形式で表した文字列を返します (略)
Fixed 整数部を最低1桁、小数部を最低2桁表示する形式で返します。1000単位の区切り記号は付きません (略)
Standard 整数部を最低1桁、小数部を最低2桁表示する形式で返します。1000単位の区切り記号が付きます (略)
Percent 指定した数値を100倍して、小数部を常に2桁表示する形式で返します。パーセント記号(%)が右側に付きます (略)
Yes/No 数値が0の場合にはNo、それ以外の場合にはYesを返します (略)
True/False 数値が0の場合には偽(False)、それ以外の場合には真(True)を返します (略)
On/Off 数値が0の場合にはOff、それ以外の場合にはOnを返します (略)

次に書式記号です。数値を表す「#」「0」や日付の「yyyy」「m」などですね。セルの表示形式で使えるのは、ほぼ同じように使えます。ここでは、表示形式では使えないFormat関数独自の書式記号をいくつかご紹介します。

・日付に関する書式記号

書式記号 解説 例
w 曜日を表す数値を返します (日曜日が1、土曜日が7) (略)
ww その日が1年のうちで何週目に当たるかを表す数値を返します(1～54) (略)
y 1年のうちで何日目に当たるかを数値で返します(1～366) (略)
oooo 月の名前を日本語で返します(1月～12月) (略)
q 1年のうちで何番目の四半期に当たるかを表す数値を返します(1～4) (略)

「ww」は分析ツールアドインを組み込んだときワークシート上で使えるWEEKNUM関数と同じですね。この書式記号を知らなかったころ、第何週かを計算によって求めるFunctionを自作して使っていました＞私。

「q」は例によって1月始まりです。期首の月を自由に設定できれば使いやすいんですけどね。

・文字列に関する書式記号

書式記号 解説 例
@ 任意の1文字。存在しないとスペースを返す (後述)
& 任意の1文字。存在しないときスペースを返さない (後述)
! 文字を左から右に埋める (後述)
< すべての文字を小文字にする (後述)
> すべての文字を大文字にする (後述)

「@」「&」「!」は、ちょっと変わってます。たとえば「@」は、こんな風に使えます。

Sub Sample3()
    MsgBox Format("ABC", "@は@の@です")
End Sub


Sub Sample4()
    Dim buf As String
    buf = buf & Format("AB", "@") & vbCrLf
    buf = buf & Format("AB", "@@") & vbCrLf
    buf = buf & Format("AB", "@@@") & vbCrLf
    buf = buf & Format("AB", "@@@@") & vbCrLf
    buf = buf & Format("AB", "@@@@@") & vbCrLf
    MsgBox buf
End Sub

これを「&」にすると、存在しないところにスペースが入らなくなります。

Sub Sample4()
    Dim buf As String
    buf = buf & Format("AB", "&") & vbCrLf
    buf = buf & Format("AB", "&&") & vbCrLf
    buf = buf & Format("AB", "&&&") & vbCrLf
    buf = buf & Format("AB", "&&&&") & vbCrLf
    buf = buf & Format("AB", "&&&&&") & vbCrLf
    MsgBox buf
End Sub

「!」を付けると左から表示します。

Sub Sample5()
    Dim buf As String
    buf = buf & Format("AB", "@@@@@@") & "です" & vbCrLf
    buf = buf & Format("AB", "!@@@@@@") & "です" & vbCrLf
    MsgBox buf
End Sub

「>」「<」で大文字/小文字に変換できます。

Sub Sample6()
    Dim buf As String
    buf = buf & Format("AB", "<@@@@@@") & "です" & vbCrLf
    buf = buf & Format("AB", "!@@@@@@") & "です" & vbCrLf
    MsgBox buf
End Sub

まあ、どんなとき便利に使えるかはともかく、Format関数で使える書式記号と、セルの表示形式で使える書式記号は、微妙に異なっているということです。