エクセルのマクロで、自動的に値を埋めて表を完成させたいという要望をよく聞きます。
そんなとき表に罫線がなければわかりにくくなってしまいますね。
それでは罫線はどうやって引いたらよいでしょうか?
ワークシート上で罫線を引いておいて値を入れていくというのもひとつの手ですが、今回は「VBAで罫線を引いて表を作成する方法」について解説します。
1.ステップとゴール
こんにちは。エクセルマン・ブリーダーのしもむぎ(@re_znd13)です。
まずは表作成のステップと、こういう表を作るよというゴールを確認しておきます。
今回はステップを以下の3つに分けて考えてみましょう。
- 罫線削除:既にある罫線を削除します。
- 範囲選択:表のあるセルを選択します。
- 罫線付加:選択範囲に罫線を引きます。
一見、2と3だけでよいのでは?と思われるかもしれませんが、1も大事なことなんです。
例えば、表の大きさが変わっちゃったよとか、表の場所が変わっちゃったよといった場合もあるでしょう。
そういうケースも考慮して、まずは罫線を削除してからというステップで解説していきますね。
次にゴールの確認です。
画像のような表を作成します。
全体に罫線を引くだけでなく、項目と内容を分ける箇所を二重線にしてみましょう。
ちなみに、前提条件として、対象のシートにはこの表だけがあることとします。
そうでないケースも多々あるとは思いますが、この記事で解説する3ステップの組替で対応できますので、応用してみてくださいね。
また、表の内容(今回で言えば名前や科目、点数など)を取得したり入力したりという部分については取り扱わないことにします。
表の内容ができたあとに罫線を引く、という状況と見てくださいね。
2.各ステップの解説
2.1 罫線削除
シート内の罫線を削除するステップです。
VBAのコードは以下になります。
アクティブシートの、
全てのセルに対して、
フォーマットをクリアする、
という内容になります。
Sub 罫線削除()
ActiveSheet.Cells.ClearFormats
End Sub
実行すると画像のように罫線がない状態となります。
2.2 範囲選択
今回のような表を作成する場合、ふたつの範囲を選択する場面が出てきます。
ひとつは表全体を選択する場面。
もうひとつは表の1行目を選択する場面。
ワークシート上でよくショートカットキーを使う方はご存知かと思いますが、表のどこかのセルが選択されている状態で、ctrl+Aでシートのセルを全て選択しようとすると、一度目は表全体が選択されます。同じようなことをVBAで書いていってもよいのですが、ふたつめの場面が少し厄介なのです。
そこで、今回は表の開始セル(左上)と最後のセル(右下)の行番号・列番号を取得することにします。今回の表でいうと開始セルはB2で、最後のセルがH6であることを知る必要があるのです。
これをするためのVBAコードは以下になります。
Sub 範囲()
Dim fcl, lcl As Range
Dim fr, fc, lr, lc As Integer
'最後のセルを取得
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
'最後のセルをlclと設定
Set lcl = ActiveCell
'lclと同じ範囲におけるひとつめのアイテムを開始セルfclと設定
Set fcl = ActiveCell.CurrentRegion.Item(1)
'最後のセルの行列番号を取得
lr = lcl.Row
lc = lcl.Column
'開始セルの行列番号を取得
fr = fcl.Row
fc = fcl.Column
'表全体を選択してメッセージ表示
Range(Cells(fr, fc), Cells(lr, lc)).Select
MsgBox "表は、セル(" & fr & "," & fc & ")からセル(" & lr & "," & lc & ")"
'項目行を選択してメッセージ表示
Range(Cells(fr, fc), Cells(fr, lc)).Select
MsgBox "項目は、セル(" & fr & "," & fc & ")からセル(" & fr & "," & lc & ")"
End Sub
2.3 罫線付加
範囲の選択ができたらいよいよ罫線を引きます。
選択範囲に罫線を引くのは以下のようなVBAコードになります。
Sub 罫線()
Selection.Borders.LineStyle = xlContinuous
End Sub
意外とシンプルかと思います。
ここでは選択範囲にcontinuous(連続線)なLinestyle(スタイル)で罫線を引いています。
選択範囲の右側だけ二重線にするのは以下のコードです。
Sub 罫線二重()
Selection.Borders(xlEdgeRight).LineStyle = xlDouble
End Sub
私はあまり使用しないですが、線を太くする場合は以下のコードで可能です。これは最大まで太くしています。
今回は使用しませんが、太線や二重線を最終行に対して使用すると、xlCellTypeLastCellで最後のセルを取得するときに失敗することがありますので、ご注意ください。
Sub 罫線太さ()
Selection.Borders.Weight = xlThick
End Sub
3.表作成するVBAコード
第2章の内容を踏まえて、表を作成するVBAコードを書いてみます。
Sub 表作成()
Dim fcl, lcl As Range
Dim fr, fc, lr, lc As Integer
'罫線削除→2.1節参照
ActiveSheet.Cells.ClearFormats
'範囲選択→2.2節参照
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
Set lcl = ActiveCell
Set fcl = ActiveCell.CurrentRegion.Item(1)
lr = lcl.Row
lc = lcl.Column
fr = fcl.Row
fc = fcl.Column
’罫線付加→2.3節参照
Range(Cells(fr, fc), Cells(lr, lc)).Borders.LineStyle = xlContinuous
Range(Cells(fr, fc), Cells(fr, lc)).Borders(xlEdgeBottom).LineStyle = xlDouble
Range(Cells(fr, fc), Cells(lr, fc)).Borders(xlEdgeRight).LineStyle = xlDouble
End Sub
4.まとめ
「VBAで罫線を引いて表を作成する方法」 について解説しましたが、いかがでしたか?
せっかく値や項目の取得・入力をVBAで自動化しても、表が見にくければ残念ですよね。
今回の内容を適用して、ぜひ見やすい表を作ってみてくださいね。