[Excel VBA]表示形式と書式設定の取得や設定方法をわかりやすく解説

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. 塗りつぶし

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

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

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

文字の配置を設定

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 = “MS P明朝” ‘ 名前
Range(“A1”).Font.Size = 14 ‘ サイズ
Range(“A1”).Font.Bold = True ‘ 太字

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

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

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

罫線を取得する方法

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.456 123.46
## 0 表示なし
0 1桁を表示 0000.0 123.456 123.5
00 0 00
,
(カンマ)
3桁ごとの区切り
または1000単位の省略
#,##0 55555555 55,555,555
#,##0, 55,556
.
(ピリオド)
小数点 0.0 12.34 12.3
% パーセント 0.0% 0.2345 23.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/PM 7 PM
時:分AM/PM(12時間) h:mmAM/PM 7:05 PM
時:分:秒A/P(12時間) h:mm:ssA/P 7:05:25 P

 

文字の書式設定

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

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

 

スキルを身につける プログラミングスクールおすすめ7選!徹底比較した失敗しない選び方

フリーランスで収入上げる フリーランスエンジニア向けエージェントおすすめ3社!