pulogu.net

ExcelVBA。Format 関数。数字の 3 桁区切り（1,000）、ハイフン区切り（010-0100）、桁数固定（0001）、小数点以下桁数固定（1.000）。

執筆者:

Editor

カテゴリ:

,

目的

  • 数字を三桁区切りで出力する。
  • 桁数を揃える。
  • ハイフンで区切る。
  • Format 関数を使用する。

その一例

Sub Format関数で数字を三桁区切りにする()
 Debug.Print Format(1000, "0,0")
End Sub

上のマクロを実行してみます。

3 桁区切りになっている。

実行結果です。VBE のイミディエイトウィンドウに 3 桁区切りで出力されました。

Format(1000, “0,0”)

の部分は、

Format( 元の値 , ユーザーが定義する書式 ) になっています。

“0,0”

の部分の「0」の文字はプレースホルダー（place holder）というものらしいです。

プログラミング用語では、後で値を入力できる箇所をプレースホルダーというらしいです。ワイルドカード *,* の逆向きのようなものでしょうか。

プレースホルダーはそのまま出力されるのではなく、元の値を整形するための条件として解釈されるようです。

「0」は数字用のプレースホルダーのようで、元の値が数字の場合に機能を発揮するようです。

プレースホルダーとして使用可能な文字はプログラミング言語、関数の仕様で独自に決まっているようです。

参考資料

Format 関数 (Visual Basic for Applications) | Microsoft Learn

https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications#user-defined-numeric-formats

「,」カンマも数字用のプレースホルダーらしいです。

カンマが出力されるかどうかは数字の桁数によって決まるようです。カンマが入る事を仮に決めています。

"AAAA" 数字以外の値を入れてみます。

“AAAA” 数字以外の値を入れてみます。

三桁区切りになっていない。

三桁区切りになっていない。

「0」「,」は数字用のプレースホルダーなので数字以外の値が入ってきた場合は三桁区切りにならないようです。

Sub 数字変更_Format関数で数字を三桁区切りにする()
  Debug.Print Format(1, "0,0")
  Debug.Print Format(10, "0,0")
  Debug.Print Format(1000000, "0,0")
End Sub

元の数字を変更して更にテストしてみます。

01 になってしまっている。

1 が「01」と出力されてしまっています。

プレースホルダーの「0」はそこに数字がない場合、「0」で埋めて出力する機能を持っているようです。

Sub シャープ使用_Format関数で数字を三桁区切りにする()
  Debug.Print Format(1, "#,#")
  Debug.Print Format(10, "#,#")
  Debug.Print Format(1000000, "#,#")
End Sub

同じく数値系のプレースホルダーの「#」シャープを試してみます。

1 になっている。0 が付いていません。

1 になっている。0 が付いていません。

プレースホルダーの「#」は「0」で埋める機能がないようです。

Sub 小数点_シャープ使用_Format関数で数字を三桁区切りにする()
  Debug.Print Format(1234.567, "#,#")
End Sub

小数点が入った数字でテストしてみます。

1235 に丸められている。

1235 に丸められている。

Sub 小数点以下二桁まで出力()
  Debug.Print Format(1234.567, "#,#.##")
End Sub

小数点以下二桁まで出力してみます。 「.##」を追加しました。

「.」ピリオドの後の「#」の数で桁数を調整できます。「#」二つで二桁を指定しています。

小数点以下二桁まで出力されている。 .57 に丸められている。

小数点以下二桁まで出力されている。 .57 に丸められている。

Sub プレースホルダー0の機能を使用して小数点以下の桁数を揃える()  
  Debug.Print Format(1, "#,#.00")
  Debug.Print Format(1234.5, "#,#.00")
  Debug.Print Format(1234.567, "#,#.00")
End Sub

プレースホルダー「0」の「0」で埋める機能を利用して、小数点以下の桁数を揃えてみます。

桁数が揃っている。

桁数が揃っている。

Sub プレースホルダー0の機能を使用して整数部分の桁数を揃える()
  Debug.Print Format(1, "0000")
  Debug.Print Format(2, "0000")
  Debug.Print Format(999, "0000")
End Sub

「0000」として整数部分の桁数を揃えてみます。

数字がない桁は 0 が出力されている。

数字がない桁は 0 が出力されている。

Sub ハイフン区切りで出力()
  Debug.Print Format(123, "000-0000")
  Debug.Print Format(1230123, "000-0000")
  Debug.Print Format(1234567, "000-0000")
End Sub

「000-0000」としてハイフン区切りで出力してみます。

ハイフン区切りになっている。

ハイフン区切りになっている。

Sub 円記号を付けて出力する()
  Debug.Print Format(1234, "¥¥#,#")
End Sub

¥ 円記号を付けて出力してみます。

「¥」の後に「¥」を記述しています。「¥」の後の文字はそのまま出力されるようです。その後に「#,#」と記述して数字を整形しています。

¥ が出力されている。

¥ が出力されている。

「¥」は出力されず、「¥」の後の 「#」がそのまま出力されました。「,」カンマも出力されています。

上記マクロの「”¥¥#,#”」の部分を「”¥#,#”」とした結果です。

「¥」は出力されず、「¥」の後の 「#」がそのまま出力されました。「,」カンマも出力されています。

「¥」を一つだけにしたので「¥」は出力されませんでした。

名前付き数値書式

Format 関数で事前に定義されている「名前付き数値書式」というものを使用してみます。

「Yes/No」「True/False」「On/Off」のキーワードを Fromat 関数の書式として指定すると、事前に定義されている書式で結果が返ってくるようです。

下記ヘルプページに一覧が載っていると思います。

参考資料

Format 関数 (Visual Basic for Applications) | Microsoft Learn https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications#named-numeric-formats

Sub 名前付き数値書式_YesNo()
  Debug.Print Format(0, "Yes/No") '「0」は「No」を表示
  Debug.Print Format(1, "Yes/No") '「1」は「Yes」を表示
End Sub

名前付き数値書式の「Yes/No」を使用してみます。

No Yes

No/ Yes が出力されました。

Sub 名前付き数値書式_TrueFalse()
  Debug.Print Format(0, "True/False") '「0」は「False」を表示
  Debug.Print Format(1, "True/False") '「1」は「True」を表示
End Sub

「True/False」を使用してみます。

False True

False/True が出力されました。

Sub 名前付き数値書式_OnOff()
  Debug.Print Format(0, "On/Off") '「0」は「Off」を表示
  Debug.Print Format(1, "On/Off") '「1」は「On」を表示
End Sub

「On/Off」を使用してみます。

Off On

Off/On が出力されました。

Sub 元の値が正の値_負の値_0の場合で出力を分岐する()
 Debug.Print Format(1, "¥正;¥負;¥ゼ¥ロ")
 Debug.Print Format(-1, "¥正;¥負;¥ゼ¥ロ")
 Debug.Print Format(0, "¥正;¥負;¥ゼ¥ロ")
End Sub

名前付き数値書式ではなくユーザーが定義する書式でも同じような事ができます。

正 負 ゼロ

正/負/ゼロが出力されました。

"¥正;¥負;¥ゼ¥ロ"

の部分の

セミコロン「;」はセクションの区切りになっています。

「第 1 セクション;第 2 セクション;第 3 セクション」の 3 つのセクションを使用しています。

セクションを 3 つ使用した場合、

元の値が「正」の値だと、第 1 セクションの書式で出力されます。

元の値が「負」の値だと、第 2 セクションの書式で出力されます。

元の値が「0」だと、第 3 セクションの書式で出力されます。

¥

の部分の円記号の後の文字はそのまま出力されます。

「ゼロ」は一文字ずつ「¥ゼ」「¥ロ」と記述しています。

Sub 第4セクションまで使用()
  Debug.Print Format(Null, "¥正;¥負;¥ゼ¥ロ;¥ヌ¥ル")
End Sub

元の値が数字の場合、最大 4 つのセクションを使用できるようです。

第 4 セクションは Null 値に適用されるようです。

Null はヌルと出力されている。

Null はヌルと出力されている。

コードに入力した Null 値。

コードに入力した Null 値。

マクロストック

Sub Str関数でマイナスとプラスの数字を文字列に変換()
  Debug.Print """" & Str(-1) & """"
  Debug.Print """" & Str(0) & """"
  Debug.Print """" & Str(1) & """"
End Sub

Str 関数を使用してみます。

マイナスの符号「-」が入るスペースが確保されている。

マイナスの符号「-」が入るスペースが確保されている。

数字がマイナスの場合、「-」が入るスペースが必要なので、事前にスペースが確保されているようです。

数字がプラスの場合、「+」の符号を出力しないのでスペースが空くようです。

Str 関数の先頭のスペースについて下記ページに記載がありました。

Str 関数 (Visual Basic for Applications) | Microsoft Learn

https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/str-function

Sub Format関数で出力すると先頭にスペースが付かない()
  Debug.Print """" & Str(1) & """"
  Debug.Print """" & CStr(1) & """"
  Debug.Print """" & Format(1) & """"
End Sub

三種類の関数を比較してみます。

スペースが付いていない。

CStr 関数と Format 関数にはスペースが付いていない。

テスト環境

  • Windows 10（64 ビット）
  • Microsoft Office Excel 2003

以上、閲覧ありがとうございました。

広告

投稿をさらに読み込む