Excel VBA セルの書式設定を簡単にセットするマクロ個人用マクロブックに組み込もう

書式設定マクロ

Excelのセルの書式に、

「数値をカンマ区切りにして、マイナス値なら△マークを付ける」
「8桁日付」
「曜日付き日付」

の設定をワンタッチで施す簡単なマクロを下記に示す。

Sub formatSetting(buf As String) 'bufに指定した書式をセルにセットする
    Selection.NumberFormatLocal = buf
End Sub
 
Sub setNumberFormat() '数値書式
    Call formatSetting("#,0;△#,0")
End Sub
 
Sub setYMDFormat() '日付書式
    Call formatSetting("yyyy/mm/dd")
End Sub
 
Sub setYMDAAAFormat() '曜日付き日付書式
    Call formatSetting("yyyy/mm/dd(aaa)")
End Sub

Sub formatSetting(buf As String) 'bufに指定した書式をセルにセットする

Selection.NumberFormatLocal = buf

End Sub

Sub setNumberFormat() '数値書式

Call formatSetting("#,0;△#,0")

End Sub

Sub setYMDFormat() '日付書式

Call formatSetting("yyyy/mm/dd")

End Sub

Sub setYMDAAAFormat() '曜日付き日付書式

Call formatSetting("yyyy/mm/dd(aaa)")

End Sub

1番目の「Sub formatSetting」というのは、各マクロの共通受け入れ口といったもの。

2番目以降の「Sub set◯◯Format」というやつらがそれぞれ、書式設定を行う個別のマクロだ。

これらのマクロを、あるセルに実行して書式設定したら、直後に別のセルでF4キーを押すと繰り返し実行ができる。

マクロって普通、元に戻すことはできないけど、実はF4キーによる繰り返しならできるのだ。

また、マクロ実行直後に単純に「元に戻す」ことはできないけど、F4キーで繰り返した行為についてはCtrl+Zで「元に戻す」ことができる。

このマクロを個人用マクロブックに追加し、リボンやクイックアクセスツールバーに設置すれば、頻繁に行う書式設定が楽にできると思う。

個人用マクロブックとかについては、ここを展開。

オリジナルのマクロをリボンに追加する方法はこちら。

個人用マクロブックについては、こちらやこちらで述べています。

それでは、これらマクロを当記事で紹介しようと思った背景などを語る。

書式設定作業の煩雑さ

数値の書式について

私は以前に、会計士のセミナーで、数値の書式については3桁ごとにカンマ「,」で区切って、マイナスの場合は三角のマークを使うよう教わった。

三角マークというのは「△」「▲」どちらでも良いと思うが、Excelの標準機能でこの書式設定をするのは意外と手間なのだ。

ショートカットキーを使ってやるとしても、

セルを選んで「Ctrl+1」
→左側の分類で「数値」を選択
→「桁区切り(,)を使用する」にチェックを入れる
→「負の数の表示形式」のところで、「△」マークが入っているものにチェックを入れる

と、何度かの操作が必要。

この三角マーク付き書式設定をワンタッチでやれる機能は、Excelに標準ではついていない。
単なるカンマ区切りというのはワンタッチで出来るけどね。

まあこれくらい、大した手間じゃないと思う人が大半だとは思うが、私としてはこれも、頻繁に行う設定なのでもっと簡略化したい。

日付の書式について

日付の書式設定についても面倒なことはある。

上図の「売上日」列で、慣れていない人はパッと気付かないかもしれないが、「2015/10/10」のような日付なら8桁になるけど、「2015/4/1」などのように月と日が10未満の数値だと6桁になる。
また「2015/4/11」などは7桁だ。

それくらいなんだ、と思われるかもしれないが、こういう桁数の不統一な日付データを縦に並べていくと、見苦しくなるのだ。

また、この縦に並んだ日付データをコピーしてテキストエディタに貼り付けて加工するとかなると、桁数が不統一だと凄く作業がしにくいのだ。

他には、「2015/04/01(水)」のように、曜日を併記したいというケースもあるだろう。

これらの日付設定をExcelでやろうとすると、意外と面倒。
「Ctrl+1」の書式設定メニューから「日付」を選んでも、実は8桁日付の選択肢は出てこない・・・はずだ。
曜日付き設定もないはず。

正攻法で設定しようとするなら、「Ctrl+1」の書式設定メニューから「ユーザー定義」を選び、「種類」枠のところに

8桁日付→「yyyy/mm/dd」と入れる。
曜日付き8桁日付→「yyyy/mm/dd(aaa)」と入れる。

というようになる。

「aaa」というのは、曜日を入れるためのワードだ。

マクロによる書式設定の簡略化

上記で設定が面倒、面倒と繰り返したけど、それが冒頭のマクロを作ろうと思った理由だ。

マクロを再掲する。

Sub formatSetting(buf As String) 'bufに指定した書式をセルにセットする
    Selection.NumberFormatLocal = buf
End Sub
 
Sub setNumberFormat() '数値書式
    Call formatSetting("#,0;△#,0")
End Sub
 
Sub setYMDFormat() '日付書式
    Call formatSetting("yyyy/mm/dd")
End Sub
 
Sub setYMDAAAFormat() '曜日付き日付書式
    Call formatSetting("yyyy/mm/dd(aaa)")
End Sub

Sub formatSetting(buf As String) 'bufに指定した書式をセルにセットする

Selection.NumberFormatLocal = buf

End Sub

Sub setNumberFormat() '数値書式

Call formatSetting("#,0;△#,0")

End Sub

Sub setYMDFormat() '日付書式

Call formatSetting("yyyy/mm/dd")

End Sub

Sub setYMDAAAFormat() '曜日付き日付書式

Call formatSetting("yyyy/mm/dd(aaa)")

End Sub

これらマクロをリボンやクイックアクセスツールバーに追加して、頻繁に行うセルの書式設定を簡略化しては如何だろうか。

他にも自分でオリジナルの書式設定マクロを追加したければ、

Sub 好きな名前
Call formatSetting(“セットしたい書式項目“)
End Sub

というものを、マクロを書くVBE(Alt+F11で呼び出せるエディタ)に追加し、上記の赤文字のところに適切な言葉を入れると良い。

しかし「セットしたい書式項目」に適切な言葉を入れるといっても、それってどう調べたら良いのか？

おまけ：ユーザー定義書式

たとえば「小数点以下2桁のパーセンテージ表示」の書式を設定したいとすると、「Ctrl+1」のセル書式設定メニューから普通に「パーセンテージ」のところを選んで小数点以下の桁数を２に設定する。

そして「分類」のところを「ユーザー定義」に移動すると、書式設定に使う文字が「種類」のところに表示されるから、それを使えば良い。

この「ユーザー定義」に設定できる書式の種類は、本当に数多くあるけど、まずは私が上で紹介したものくらいで良いと思うし、後は次のものくらいか。

入力した値	書式設定	表示結果
2018/4/1	yyyy/mm/dd	2018/04/01
2018/4/1	yyyy年mm月dd日	2018年04月01日
2018/4/1	yyyy年mm月dd日(aaa)	2018年04月01日(日)
-12345	#,0	-12,345
-12345	#,0;△#,0	△12,345
-12345	#,0;[赤]△#,0	△12,345
-12345	¥#,0;¥-#,0	¥-12,345
12345	+#,0;-#,##0	+12,345
0	#,0;△#,0;-	-