[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.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

スキルを身につける プログラミングスクールおすすめ7選!67名の体験から徹底比較

人気No.1プログラミングスクール テックアカデミーのおすすめコース!人気から選ぶ3選

IT業界での転職 SE歴9年が解説!ITに強い転職エージェントおすすめ9選と選び方

フリーランスで収入上げる フリーランスエージェントおすすめ16選と選び方の注意ポイント

\SNSでシェアする/