VBAからセルの書式設定 NumberFormat
Excelシートのセルの書式設定をVBAから行う方法です。
セルを選択して右クリックから「セルの書式設定」を行うのと同じ操作になります。
Excelのセルは、表示されている値と内部の値が同じであるとは限りません。
例えば日付型では、シリアル値（数値）に対して、様々な表示方法で表現されています。
シリアル値 44970
日付型（標準表示） 2023/2/13
日付型（書式設定） 2月13日
例えば、「44970」という数値を日付型として見ると「2023/2/13」という日付となります。
さらに書式設定を変更する事で、表示を「2月13日」のように変更できます。
上のように「2月13日」と表示されたセルの数式バーには、「2023/2/13」となっています。
このセルの書式設定を「標準」に戻すと日付型ではなくなり「44970」と表示されます。
「44970」は日付型が持っていたシリアル値になります。
尚、日付型と認識されるような文字列は、入力後に日付型として自動変換される場合もあるので注意しましょう。
VBAで書式設定を変更
セルの書式設定をVBAから変更するには、セルのNumberFormatプロパティを設定します。
Range("A1").NumberFormat = "@" 'セルの値を文字列として設定
Rangeの部分は、CellsやRows、Columnsなどのセルを対象とするオブジェクトになります。
NumberFormatプロパティに適切な文字列を指定する事によってセルの書式設定を指定できます。
書式設定の指定文字
書式設定は、以下の要素を組み合わせて指定します。
| 指定文字 | 説明 |
|---|---|
| G/標準 | 標準の表示形式 |
| _ | _(アンダーバー)に続く文字分、文字間隔をあける |
| “ ” | ” “で囲まれた文字列を表示 |
| ＠ | 値を文字列として表示 |
| ＃ | # の数より少ない時、 0 は表示しない |
| ０ | 数値の桁数が 0 の桁数より少ない場合、その桁まで 0 が付く |
| ？ | 有効桁以外の 0 にスペースが挿入され、列内で小数点の位置が整列される |
| . | 数値の小数点 |
| , | 数値の桁区切り記号 |
| yy | 年を2桁の数値で表示 |
| yyyy | 年を4桁の数値で表示 |
| g | 元号のアルファベット1文字（M,T,S,H,R）で表示 |
| gg | 元号の1文字（明,大,昭,平,令）で表示 |
| ggg | 元号(明治,大正,昭和,平成,令和)で表示 |
| m | 月数を表示 |
| d | 日数を表示 |
| aaa | 日～土 |
| aaaa | 日曜日～土曜日 |
| h | 時を 0～23 範囲で表示 |
| m | 分を 0～59 範囲で表示 |
| s | 秒を 0～59 範囲で表示 |
| [h] | 24時を超える時間表示 |
| [m] | 60分を超える分を表示 |
| [s] | 60秒を超える秒を表示 |
既に書式設定した値を調べる
Excelのメニューから書式設定を行った場合で、その書式設定の指定文字を得たい場合は、以下のようにNumberFormatプロパティを文字列で受けてやればＯＫです。
Sub myNumberFormat()
Dim str As String
str = Range("A2").NumberFormat
MsgBox str
End Sub上記のプログラムの str を他のセルのNumberFormatプロパティに割り当てれば、セルの書式をコピーしたのと同じになります。
エスケープシーケンスに注意
自分で文字列を作って書式設定する場合は文字列中にある ” をエスケープシーケンスしなければいけない事に注意してください。
上記のプログラムで得られた「m”月”d”日”」は、文字列内の ” を “” としなければいけませんので、
Range(“A5”).NumberFormat = “m““月““d““日““”
となります。
赤が文字列を囲むための “
青が文字列内の ” をエスケープシーケンスするための “ になります。
複雑なのですがExcelVBAの仕様なので仕方ありません、、、
