大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAでセルの表示形式を日付や数値に変換する方法をご紹介します。日付や数値以外にも、よく使いそうな表示形式をまとめましたので、ド忘れしちゃったというような場合にご活用ください。

はじめに

この記事では、Excel VBAを使って表示形式を日付や数値に変換する方法についてご紹介します。

よく使いそうな表示形式を記載していますので、使いたい表示形式だけをチェックするといいかと思います。

この記事で紹介すること

  • 表示形式を設定する方法
  • 表示形式をコピーする方法

目次から見たい項目へ移動すると便利ですよ。

目次

ポイントとなるVBAコード

初めに、ポイントとなるVBAコードを記載しておきます。

'表示形式の変更

'標準
.Cells(1, 1).NumberFormatLocal = "G/標準"
'日付
.Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd" '西暦
.Cells(1, 1).NumberFormatLocal = "yyyy年mm月dd日" '長い西暦
.Cells(1, 1).NumberFormatLocal = "ggge年mm月dd日" '和暦
.Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd(aaa)" '曜日を追加
.Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss" '時刻を追加
'時刻
.Cells(1, 1).NumberFormatLocal = "hh時mm分dd秒" '長い時刻
.Cells(1, 1).NumberFormatLocal = "hh:mm:dd" '短い時刻
'数値
.Cells(1, 1).NumberFormatLocal = "#0.000" '小数点
.Cells(1, 1).NumberFormatLocal = "#,##0" '桁区切り
'通貨
.Cells(1, 1).NumberFormatLocal = "\#,##0" '\で表示
.Cells(1, 1).NumberFormatLocal = "#,##0円" '円で表示
'文字列
.Cells(1, 1).NumberFormatLocal = "@" '文字列

'表示形式のコピー
a = .Cells(1, 1).NumberFormatLocal
.Cells(2, 1).NumberFormatLocal = a

だいたい使い方はわかるけどコードを忘れた、という場合にご活用ください。

セルの表示形式は、『NumberFormatLocal』で設定できます。

では、表示形式について、具体例をあげて説明していきます。

VBAのNumberFormatLocalで表示形式を標準にする

表示形式を『標準』にする例を紹介します。

標準にする『G/標準』

セルの表示形式を標準にするには、『G/標準』とするとできます。

Sub TEST1()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "G/標準" '標準
    End With
    
End Sub

結果をみてみます。

標準にする

表示形式をNumberFormatLocalで標準にした結果

標準になっています。

VBAのNumberFormatLocalで表示形式を日付にする

つづいて、表示形式を日付に変更する方法です。

短い日付の西暦

セルの表示形式を西暦にするには、『"yyyy/mm/dd"』とするとできます。

Sub TEST2()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd" '西暦
    End With
    
End Sub

結果をみてみます。

短い日付の西暦

表示形式をNumberFormatLocalで西暦の日付にした結果

西暦の日付になっています。

長い日付の西暦

セルの表示形式を長い西暦の日付にするには、『"yyyy年mm月dd日"』とするとできます。

Sub TEST3()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "yyyy年mm月dd日" '長い西暦
    End With
    
End Sub

結果をみてみます。

長い日付の西暦

表示形式をNumberFormatLocalで長い西暦の日付にした結果

長い西暦の日付になっています。

和暦

セルの表示形式を和暦にするには、『"ggge年mm月dd日"』とするとできます。

Sub TEST4()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "ggge年mm月dd日" '和暦
    End With
    
End Sub

結果をみてみます。

和暦

表示形式をNumberFormatLocalで和暦にした結果

和暦になっています。

曜日も付ける

セルの表示形式を曜日を付けた日付にするには、『"yyyy/mm/dd(aaa)"』とするとできます。

Sub TEST5()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd(aaa)" '曜日をつける
    End With
    
End Sub

結果をみてみます。

曜日もつける

表示形式をNumberFormatLocalで曜日を付けた日付にした結果

日付に曜日がついています。

時刻も付ける

セルの表示形式を時刻を付けた日付にするには、『"yyyy/mm/dd hh:mm:ss"』とするとできます。

Sub TEST6()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss" '時刻をつける
    End With
    
End Sub

結果をみてみます。

日付に時刻をつける

表示形式をNumberFormatLocalで日付に時刻をつけた結果

日付に時刻がついています。

VBAのNumberFormatLocalで表示形式を時刻にする

Excel VBAのNumberFormatLocalで表示形式を時刻にする方法です。

長い時刻

セルの表示形式を長い時刻にするには、『"hh時mm分ss秒"』とするとできます。

Sub TEST7()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "hh時mm分dd秒" '長い時刻
    End With
    
End Sub

結果をみてみます。

長い時刻

表示形式をNumberFormatLocalで長い時刻にした結果

長い時刻になっています。

短い時刻

セルの表示形式を短いにするには、『"hh:mm:ss"』とするとできます。

Sub TEST8()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "hh:mm:dd" '短い時刻
    End With
    
End Sub

結果をみてみます。

短い時刻

表示形式をNumberFormatLocalで短い時刻にした結果

短い時刻になっています。

VBAのNumberFormatLocalで表示形式を数値にする

Excel VBAのNumberFormatLocalで表示形式を数値にする方法です。

小数点第3位まで

セルの表示形式を小数点第3位までの数値にするには、『"#0.000"』とするとできます。

Sub TEST9()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "#0.000" '小数点第3位
    End With
    
End Sub

結果をみてみます。

小数点第3位

表示形式をNumberFormatLocalで小数点第3位までの数値にした結果

小数点第3位までの数値になっています。

桁区切りを使用

セルの表示形式を、桁区切りをした数値にするには、『"#,##0"』とするとできます。

Sub TEST10()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "#,##0" '桁区切り
    End With
    
End Sub

結果をみてみます。

桁区切り

表示形式をNumberFormatLocalで桁区切りをした数値にした結果

数値が桁区切りされています。

VBAのNumberFormatLocalで表示形式を通貨にする

Excel VBAのNumberFormatLocalで表示形式を通貨にする方法です。

『\』にする

セルの表示形式を『\』の通貨にするには、『"\#,##0"』とするとできます。

Sub TEST11()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "\#,##0" '\の通貨
    End With
    
End Sub

結果をみてみます。

\の通貨

表示形式をNumberFormatLocalで\の通貨にした結果

通貨に\がついています。

『円』にする

セルの表示形式を円の通貨にするには、『"#,##0円"』とするとできます。

Sub TEST12()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "#,##0円" '円の通貨
    End With
    
End Sub

結果をみてみます。

円の通貨

表示形式をNumberFormatLocalで円の通貨にした結果

通貨に円がついています。

VBAのNumberFormatLocalで表示形式を文字列にする

Excel VBAのNumberFormatLocalで表示形式を文字列にする方法です。

数字を文字列にする

数字を文字列の表示形式にするには、表示形式を『"@"』にしたあとに、セルに数字を入力します。

Sub TEST13()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "@" '文字列にする
        .Cells(1, 1) = "01"
    End With
    
End Sub

結果をみてみます。

数字を文字列にする

NumberFormatLocalで数字を文字列の表示形式にした結果

数字が文字列になっています。

日付を文字列にする

先ほどと同じで、日付を文字列の表示形式にするには、表示形式を『"@"』にしたあとに、セルに日付を入力します。

Sub TEST14()
    
    With ActiveSheet
        .Cells(1, 1).NumberFormatLocal = "@" '文字列にする
        .Cells(1, 1) = "2020/1/1"
    End With
    
End Sub

結果をみてみます。

日付を文字列にする

NumberFormatLocalで日付を文字列の表示形式にした結果

日付が文字列になっています。

NumberFormatLocalで表示形式をコピーする

Excel VBAのNumberFormatLocalで表示形式をコピーする方法です。

表示形式はコピーすることもできます。

すでに入力されているセルの表示形式を別セルにも使いたい、という場合に使えます。

日付の表示形式をコピーする

日付の表示形式をコピーするには、

  • ①日付の表示形式をコピーする
  • ②セルに表示形式を貼り付ける

という手順でできます。

日付の表示形式をコピーするVBAコードです。

Sub TEST15()
    
    With ActiveSheet
        a = .Cells(1, 1).NumberFormatLocal '表示形式をコピー
        .Cells(2, 1).NumberFormatLocal = a '表示形式を貼り付け
    End With
    
End Sub

結果をみてみます。

日付の表示形式をコピー

NumberFormatLocalを使って日付の表示形式をコピーした結果

日付の表示形式がコピーされました。

数値の表示形式をコピーする

数値の表示形式をコピーするには、先ほどと同じように、

  • ①数値の表示形式をコピーする
  • ②セルに表示形式を貼り付ける

という手順でできます。

数値の表示形式をコピーするVBAコードです。

Sub TEST16()
    
    With ActiveSheet
        a = .Cells(1, 1).NumberFormatLocal '表示形式をコピー
        .Cells(2, 1).NumberFormatLocal = a '表示形式を貼り付け
    End With
    
End Sub

結果をみてみます。

数値の表示形式をコピー

NumberFormatLocalを使って数値の表示形式をコピーした結果

数値の表示形式がコピーされました。

おわりに

この記事では、Excel VBAを使って表示形式を日付や数値に変換する方法についてご紹介しました。

よく使いそうな表示形式を記載しましたので、度忘れしちゃったときにこちらの記事ご活用ください。

参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す