[Excel VBA]表示形式と書式設定の取得や設定方法をわかりやすく解説
本記事のテーマ
  • Excel VBAでセルに表示形式を設定する方法
  • Excel VBAでセルの書式を設定・取得する方法
  • Excel VBAでセルの書式設定の細かい設定方法

今回は上記のテーマで解説をしていきます。

表示形式も書式設定もRange.NumberFormatLocalプロパティもしくはCells.NumberFormatLocalプロパティを使うことで一通りの処理を行うことができます。具体的にいうと下記のようになります。

セルの表示形式や書式の設定

Range.NumberFormatLocal = “設定内容”
Cells.NumberFormatLocal = “設定内容”

セルの表示形式や書式の取得

格納する変数 = Range.NumberFormatLocal
格納する変数 = Cells.NumberFormatLocal

細かいところについて詳しく解説していきますので、是非参考にして頂ければと思います。

[Excel VBA]セルに表示形式を設定する方法

まず最初にセルに表示形式を設定する方法について解説をしていきます。

基本的な設定の仕方

表示形式を設定する基本的な設定の仕方は下記の通りです。

Range(“A1”).NumberFormatLocal = “G/標準”
Range(“A2”).NumberFormatLocal = “yyyy/m/d”
Range(“A3”).NumberFormatLocal = “@”

Cells.NumberFormatLocalプロパティを使う場合は下記となります。

Cells(1, 1).NumberFormatLocal = “G/標準”
Cells(2, 1).NumberFormatLocal = “yyyy/m/d”
Cells(3, 1).NumberFormatLocal = “@”

表示形式で設定できる値としては下記いずれかとなります。（詳細は後述）

分類名値
標準G/標準
数値0_
通貨\#,##0;\-#,##0
会計_ * #,##0_ ;_ * -#,##0_ ;_ * “-“_ ;_ @_
日付yyyy/m/d
時刻[$-F400]h:mm:ss AM/PM
パーセンテージ0%
分数# ?/?
指数0.E+00
文字列@
その他[<=999]000;[<=9999]000-00;000-0000

セルの範囲に表示形式を設定する方法

セルの範囲に表示形式を設定する場合はRange.NumberFormatLocalプロパティを使って設定します。

下記はいずれもA1:A3セルに日付で表示するための設定となります。

Range(“A1:A3”).NumberFormatLocal = “yyyy/m/d”
Range(Cells(1, 1),Cells(3, 1)).NumberFormatLocal = “yyyy/m/d”

範囲指定で表示形式を設定したい場合は上記のように使いましょう。

指定したシートのセルに表示形式を設定する方法

シートを指定して表示形式を設定する場合はWorksheetsプロパティを使用します。Sheet1を指定する場合は下記のようにWorksheets(“Sheet1”)もしくはWorksheets(1)を指定して設定しましょう。

Worksheets(“Sheet1”).Range(“A1”).NumberFormatLocal = “@”
Worksheets(1).Range(“A1”).NumberFormatLocal = “@”

ワークシートを指定せずに表示形式を設定するとアクティブシートのセルが指定されることになるため、下記はいずれも同じ設定となります。

Range(“A1”).NumberFormatLocal = “@”
ActiveworkSheet.Range(“A1”).NumberFormatLocal = “@”

[Excel VBA]セルの書式を取得・設定する方法

続いて、セルの書式を取得・設定する方法について解説をしていきます。セルの書式設定に関しては下記5つを見ていくので、必要な部分をチェックしていただければと思います。

  1. 配置
  2. フォント
  3. 罫線
  4. 塗りつぶし

    それぞれについて見ていきましょう。

    配置を取得・設定する方法

    まず配置に関しては、「文字の配置」「文字の制御」「文字の方向や角度」の３つを設定することができます。

    文字の配置を設定

    Range(“A1”).HorizontalAlignment = xlGeneral ‘ 横位置
    Range(“A1”).VerticalAlignment = xlCenter ‘ 縦位置
    Range(“A1”).AddIndent = False ‘ 前後にスペースを入れるか否か
    Range(“A1”).IndentLevel = 0 ‘ インデント

    横位置（HorizontalAlignment）に関しては、下記いずれかの値を入れることができます。

    定数名設定内容
    xlGeneral標準
    xlLeft左詰め
    xlCenter中央揃え
    xlRight右詰め
    xlFill繰り返し
    xlJustify両端揃え
    xlCenterAcrossSelection選択範囲内で中央
    xlDistributed均等割り付け

    横位置（VerticalAlignment）に関しては、下記いずれかの値を入れることができます。

    定数名設定内容
    xlTop上詰め
    xlCenter中央揃え
    xlBottom下詰め
    xlFill繰り返し
    xlJustify両端揃え
    xlDistributed均等割り付け
    文字の制御を設定

    Range(“A1”).WrapText = True ‘ 折り返して全体を表示する
    Range(“A1”).ShrinkToFit = True ‘ 縮小して全体を表示する
    Range(“A1”).MergeCells = True ‘ セルを結合する

    文字の方向や角度を設定

    Range(“A1”).ReadingOrder = xlContext ‘ 文字の方向
    Range(“A1”).Orientation = 0 ‘ 方向の角度

    文字の方向（ReadingOrder）に関しては、下記いずれかの値を入れることができます。

    定数名設定内容
    xlContext対象
    xlLTR左から右
    xlRTL右から左

    フォントを取得・設定する方法

    フォントを取得・設定する場合はFontプロパティを使用することで行えます。

    フォントを取得する方法

    Dim afont As Font
    Set afont = Range(“A1”).Font

    フォントを設定する方法

    Range(“A1”).Font.Color = RGB(128, 128, 128) ‘ 文字色
    Range(“A1”).Font.Name = “ＭＳ Ｐ明朝” ‘ 名前
    Range(“A1”).Font.Size = 14 ‘ サイズ
    Range(“A1”).Font.Bold = True ‘ 太字

    Fontプロパティに関しては他にも色々と使いどころがありますが、色に関しては下記にて詳しく解説をしています。

    [Excel VBA]セルの色の設定を自由にできるようになろう ｜ プロテク
    https://propagandes.info/vba-cells-color/
    Excel VBAでセルの色を設定する方法について解説。font color、colorIndex、interior colorを活用して背景色の塗りつぶしや文字色を変える方法について網羅的に解説してるので、セルの色を変更したい場合は参考にしてみてください。

    罫線を取得・設定する方法

    セルの罫線を取得や設定する方法については下記の通りとなります。

    罫線を取得する方法

    Dim aborders As Borders
    Set aborders = Range(“A1”).Borders

    罫線を設定する方法

    Range(“A1”).Borders.Color = RGB(0, 255, 0) ‘ 色
    Range(“A1”).Borders.ColorIndex = 1 ‘ 色番号
    Range(“A1”).Borders.LineStyle = xlContinuous ‘ 罫線の種類
    Range(“A1”).Borders.Weight = xlThin ‘ 罫線の太さ

    罫線の種類については下記のものがあります。

    定数名設定内容
    xlContinuous実戦
    xlDash破線
    xlDashDot一点鎖線
    xlDashDotDot二点鎖線
    xlDot点線
    xlDouble二重線
    xlSlantDashDot斜線
    xlLineStyleNoneなし

    塗りつぶしを取得・設定する方法

    Interiorプロパティを使用することで塗りつぶし（背景色）を取得・設定することができます。

    塗りつぶしを取得する方法

    Dim l As Long
    Dim i As Integer
    l = Range(“A1”).Interior.Color ‘ 背景色
    i = Range(“A1”).Interior.ColorIndex ‘ 背景色番号
    i = Range(“A1”).Interior.Pattern ‘ パターン
    l = Range(“A1”).Interior.Pattern.Color ‘ パターンの色
    i = Range(“A1”).Interior.Pattern.ColorIndex ‘ パターンの色番号

    塗りつぶしを設定する方法

    Range(“A1”).Interior.Color = RGB(0, 255, 0) ‘ 背景色
    Range(“A1”).Interior.ColorIndex = 1 ‘ 背景色番号
    Range(“A1”).Interior.Pattern = xlPatternGray50 ‘ パターン
    Range(“A1”).Interior.Pattern.Color = RGB(0, 255, 0) ‘ パターンの色
    Range(“A1”).Interior.Pattern.ColorIndex = 4 ‘ パターンの色番号

    保護を取得・設定する方法

    シートやセルが保護されているかどうか取得する、セルやシートの保護を行う場合は下記のように行います。

    保護状況を取得する方法

    Dim b As Boolean
    b = ActiveSheet.ProtectContents ‘ シートが保護されているか
    b = Range(“A1”).Locked

    保護を設定する方法

    Call ActiveSheet.Protect(UserInterfaceOnly:=True) ‘ シートを保護する
    ActiveSheet.Unprotect ‘ シートの保護を解除する
    Range(“A1”).Locked = True ‘ セルをロックする

    最後にセルをロックするコードを紹介していますが、セルのロックをするためには先にシートを保護する必要があります。シート保護→セルロックの順番で行っていくようにしましょう。

    [Excel VBA]NumberFormatLocalの書式設定

    最初に表示形式の取得・設定方法について解説をしましたが、表示形式で使用できる記号について詳しく解説してなかったので最後に紹介していきます。

    • 数値
    • 日付
    • 時刻
    • 文字
    • 通貨

      それぞれの設定記号を紹介していくので、是非参考の上で設定して頂ければと思います。

      数値の書式設定

      数値の場合は下記のように設定することが可能です。

      書式記号内容表示形式数値例表示結果
      #1桁を表示##.##123.456123.46
      ##0表示なし
      01桁を表示0000.0123.456123.5
      00000
      ,
      (カンマ)      		3桁ごとの区切り
      または1000単位の省略      		#,##05555555555,555,555
      #,##0,55,556
      .
      (ピリオド)      		小数点0.012.3412.3
      %パーセント0.0%0.234523.5%
      ?小数点位置を揃える???.???123.45
      12.345      		123.45
      _12.345

      日付の書式設定

      日付の場合の書式設定は下記となります。

      内容書式記号表示結果（2020/4/1）
      西暦yy
      yyyy      		20
      2020
      和号g
      gg
      ggg      		R
      令
      令和
      和暦e
      ee      		2
      02
      月m
      mm
      mmm
      mmmm      		4
      04
      Apr
      April
      日d
      dd      		1
      01
      曜日ddd
      dddd
      aaa
      aaaa      		Wed
      Wednesday
      水
      水曜日

       

      時刻の書式設定

      続いて時刻の場合は下記のように「h」「m」「s」を使って設定します。

      内容書式記号表示結果（19時05分25秒）
      時（24時間）h
      hh      		19
      19
      分（hやsとともに使用）m
      mm      		19:5（h:mの場合）
      19:05（hh:mmの場合）
      秒s
      ss      		19:5:25（h:m:sの場合）
      19:05:25（hh:mm:ssの場合）
      時AM/PM（12時間）hAM/PM7 PM
      時:分AM/PM（12時間）h:mmAM/PM7:05 PM
      時:分:秒A/P（12時間）h:mm:ssA/P7:05:25 P

       

      文字の書式設定

      文字の書式設定は下記のように汎用的に使用することができます。

      内容書式記号使用例表示形式
      @入力した文字列@””様””鈴木太郎様
      [黒][赤][青][緑][黄][紫][水][白]文字の色¥#,##0;[赤]¥-#,##0¥-5,100
      ¥#,##0;[赤]¥-#,##0通貨1500¥5,100