2020/03/12

セルの罫線を矢印キーで引く




1.背景と概要

Excel でセルに罫線を引くには「セルの書式設定」で「罫線」を選択し、「線種を選び」「線を引く場所を選び」クリックする という多くの手順が必要です。
最近のバージョンでは鉛筆機能が追加され簡単に引けるようになりましたが、セルに文字を入力しながら罫線を引くにはやっぱり不便です。手を「キーボード」から「マウス」に持ち替える必要があるからです。

そこで、今回は「キーボードから手を離さずに罫線が引ける」マクロを考えました。「考えた」と言っても私のアイデアではなく、私が大昔に購入し使っていたソフトの機能を拝借したものです。もちろんマクロにはロックが掛かっていたのでロジックは新たに考えました。

使い方としては図1-1の通り、Ctrlキーを押しながら矢印キーを押すと罫線が引け、Shiftキーを押しながら矢印キーを押すと罫線が消える と言うものです。


 
Ctrl+「→」

図1-1

罫線を引く・消すの基準点はセルの左上角で、そこに鉛筆・消しゴムが付いているイメージです。また、複数セルを選択すると、その1つ1つのセルの左上角で罫線が一度に引けます。
線の種類・太さは、右上に表示されているダイアログボックスから選択でき、本来のCtrl+矢印キー、Shift+矢印キーの機能に戻す時は、ボタンを押して「OFF」にします。
また、ダイアログボックスを操作した後は、いちいちシートをクリックしてフォーカスをシート側に移さなくても勝手に戻ります。

2.プログラム

今回のマクロは「クラスモジュール(Class1)」「標準モジュール(Module1)」「フォームモジュール(UserForm1)」に跨ります。

2-1.クラスモジュール

図1-1の通り、今回のユーザフォームでは13個のオプションボタン(OptionButton)を使用していて「どのOptionButtonを押したか」で線種を変更するようにしています。
13個のClickイベントプロシージャに各々コードを書いても良いのですが、非効率でミスも発生し易く、修正も大変です。どのボタンを押しても共有のイベントとして受け取れるようにするため、クラスモジュールを使用します。

イベントを共有する為には、以下の手続きが必要となります。
1)Opt変数をイベントを持つオプションボタン型の変数として宣言(図2-1の2行目)
2)オプションボタンを変数に格納するプロシージャを作成(図2-1の4~6行目)
3)フォームでOptionButton配列をClass1型として作成(図2-17の188行目)
4)フォームのInitializeで、OptionButton配列にOptionButtonを登録(図2-18の192~194行目)
5)OptionButtonが押されたイベントを使って処理する(図2-1の8~16行目)

尚、同様の理由で共有イベントを使用した「セルへの日付入力をカレンダー日付クリックで選定する」も参考として下さい。

  1. '========== ⇩① イベントを持つオプションボタン型の変数を宣言 =============
  2. Private WithEvents Opt As MSForms.OptionButton
  3. '========== ⇩② OptionButtonをイベントを持つ変数に格納 =============
  4. Public Sub NewClass(ByVal c As MSForms.OptionButton)
  5.  Set Opt = c     ’←引数のオプションボタンを変数に格納
  6. End Sub
  7. '========== ⇩③ OptionButtonのイベントを取得 =============
  8. Private Sub Opt_Click()       ’←Optionボタンのクリックイベントで実行
  9.  Dim No As Integer
  10.  No = CInt(Mid(Opt.Name, 13))    ’←オプションボタンのオブジェクト名から数字を取り出し
  11.  LineS = Line_Array(1, No)     ’←リストからLineStyle変数へ代入
  12.  LineW = Line_Array(2, No)     ’←リストからWeight変数へ代入
  13.  SetFocus Application.hwnd     ’←シート側へフォーカス移動
  14. End Sub
図2-1

2行目は、イベントを持つオプションボタン型の変数として「Opt」を宣言しています。通常ですと「OptionButton1_Click()」というような形でイベントを受け取るところを、「Opt_Click()」という形で代わりに受け取ることができます。

4~6行目は、2行目で作成した変数に実際のOptionButtonを登録(図2-18の193行目)する為の式となります。フォームのコントロールはオブジェクトですので、Setステートメントを使用する必要があります。

イベントの共有化が出来ていれば、どのOptionButtonを押しても共有イベントプロシージャ(8~16行目)が反応します。どのOptionButtonが押されたのかは、Opt.Name(オブジェクト名)やOpt.Caption(ボタンの表示名)で判別していく事になります。(今回は数字で振り分けたいのでOpt.Nameを使用しています)
押されたOptionButtonのオブジェクト名の一番最後についている数字(1~13の番号)を使いますので、11行目ではオブジェクト名から「OptionButton」の12文字を取り除き、13文字目から後ろを読むために「Mid(文字列 , 開始位置 [, 終了位置は設定せず] )」を使用ます。
尚、Mid では文字列が返りますので、明示的にCIntで数字に直しています。

12行目では罫線のLineStyleを、13行目では罫線のWeightをリストから取り込んでいます。 リストである「Line_Array」については、図2-22で説明します。
また、15行目の「SetFocus Application.hwnd」は「ボタンを押したら、すぐにシート上で作業できる」ようにフォーカスを移動するコードです。これについては、図2-2の18行目で説明します。

2-2.標準モジュール

標準モジュールでは、フォームの表示、キーに対するマクロの割り当て、罫線の作成と削除などを行っています。

まず、図2-2の標準モジュールの先頭部分(宣言部)で、変数宣言などを行います。
  1. '========== ⇩④ 宣言部(変数などの宣言)  ===========
  2.     '↓指定されたWindowをキーボードフォーカスを持つWindowにする
  3. Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
  4. Public On_Off As Boolean           ’←ボタン(罫線マクロのOn-Off)のフラグ変数
  5. Public Line_Array(1 To 2, 1 To 13) As Long    ’←罫線のLineStyle、Weightのリスト配列
  6. Public LineS As Long             ’←実際に罫線を引く時に使用するLineStyle値
  7. Public LineW As Long             ’←実際に罫線を引く時に使用するWeight値
図2-2

19行目の「Declareステートメント」ですが、Windows API の「user32」というDLL(ダイナミックリンクライブラリ)内に存在する外部プロシージャ(ここでは「SetFocus」)を使えるように宣言するものです。
この「SetFocus」は「指定したウィンドウをフォーカスする」役目のもので、「指定したウィンドウ」とは今回Excel本体ですから「Application.hwnd」となります。
今回のマクロの中でフォーカスを移動している箇所は「図2-21の206行目」「図2-24の257行目」「図2-25の261行目」で、「SetFocus Application.hwnd」というコードになります。
尚、フォーカスを移すには、フォームをモードレス( .Show の引数として「0」または「vbModeless」をつける)で開く事が必要です。

20行目の「On_Off」変数は、罫線を引く状態(=True)か引かない状態(=False)かを保持するフラグで、主にダイアログボックス上のボタンによってOn-Offするものです。

22行目のLine_Array配列は、罫線のLineStyle(線種)、及びWeight(太さ)の組み合わせを保持しておくものです。尚、罫線のLineStyleは7種(「無し」を入れると8種)、Weightは4種あるので28種類の線種が出来そうですが、実際には13種に限定されます。(「セルの書式設定」でも13種しか選択できません)
実際に配列に値を代入するのは、フォームモジュールの図2-22になります。
尚、罫線の線種・太さは「xlContinuous」や「xlHairline」等の定数ですが、実体は数値ですのでLong型にします。(間違ってString型にしてしまうと、ゼロとみなされ罫線が引かれません)

23~24行目の「LineS」「LineW」変数は、実際に罫線を設定するLineStyleプロパティ、Weightプロパティへの定数相当です。
別な方法として、現在選択している線種番号を変数として保持し、LineStyleプロパティへ与える定数をLine_Array配列の要素指定として渡す事も考えられます。


図2-3はメインプロシージャで、実際に今回マクロを起動させるのは、このプロシージャからになります。
  1. '========== ⇩⑤ メインプロシージャ  =============
  2. Public Sub Line_Draw()               ’←罫線引きマクロ開始のプロシージャ
  3.  On_Off = Not On_Off           ’←罫線引きのOnとOffを反転(トグル化)
  4.  If On_Off = True Then            ’←罫線引きのOnの時
  5.   UserForm1.CommandButton1.Caption = "ON"  ’←ボタン表示をONにする
  6.   Call SetOnkey                ’←キーにマクロを割り当てる
  7.  Else                      ’←罫線引きのOffの時
  8.   UserForm1.CommandButton1.Caption = "OFF"  ’←ボタン表示をOFFにする
  9.   Call ReSetOnkey               ’←キーを標準に戻す
  10.  End If
  11.  If UserForm1.Visible = False Then      ’←既にUserformが出ている時は再表示させない
  12.   UserForm1.Show 0              ’←Userformをモードレスで表示
  13.  End If
  14. End Sub
図2-3

27行目は「罫線引きOn-Offフラグ」を切り替えるコードです。このマクロを立ち上げた状態では、変数On_Offは図2-2の20行目で宣言した状態ですので、False(既定値)です。
「On_Off = Not On_Off」は、Not で False ⇔ True を 反転させますので、メインプロシージャ(Line_Draw)を起動させるとFalse(既定値)→True になります。

29~35行目は、その変数On_Offの値で「矢印キーで罫線を引く」又は「矢印キーで罫線を引かない」の設定を行っています。
設定の1つ目は、ダイアログボックス上のボタンの表示です。「矢印キーで罫線を引く」時は「ON」、引かない時は「OFF」表示をします。
設定の2つ目は、キーにマクロを登録するか、解除するかの設定です。ONでマクロ登録する際には「SetOnkey」プロシージャ(図2-4)を動かし、OFFでマクロ解除する際には「ReSetOnkey」プロシージャ(図2-6)を動かします。

37~39行目は「UserForm1が表示されていなければ表示する」というものです。UserForm1上のボタンを押すと、この「Line_Draw」プロシージャを起動するようにしていますが、その際にはUserForm1は表示済なので、無駄な再表示を防止させています。


図2-4は、図2-3の31行目から呼び出されるプロシージャで、「キーにマクロを登録」しています。
  1. '========== ⇩⑥ キーにマクロを登録するプロシージャ  =============
  2. Private Sub SetOnkey()
  3.  Application.OnKey "^{UP}", "'key_Line ""UP""'"      ’←Ctrl+UPは、key_line("UP")を実行
  4.  Application.OnKey "^{RIGHT}", "'key_Line ""RIGHT""'"   ’←Ctrl+RIGHTは、key_line("RIGHT")を実行
  5.  Application.OnKey "^{DOWN}", "'key_Line ""DOWN""'"    ’←Ctrl+DOWNは、key_line("DOWN")を実行
  6.  Application.OnKey "^{LEFT}", "'key_Line ""LEFT""'"    ’←Ctrl+LEFTは、key_line("LEFT")を実行
  7.  Application.OnKey "+{UP}", "'key_Line_D ""UP""'"     ’←Shift+UPは、key_line_D("UP")を実行
  8.  Application.OnKey "+{RIGHT}", "'key_Line_D ""RIGHT""'"  ’←Shift+RIGHTは、key_line_D("RIGHT")を実行
  9.  Application.OnKey "+{DOWN}", "'key_Line_D ""DOWN""'"   ’←Shift+DOWNは、key_line_D("DOWN")を実行
  10.  Application.OnKey "+{LEFT}", "'key_Line_D ""LEFT""'"   ’←Shift+LEFTは、key_line_D("LEFT")を実行
  11. End Sub
図2-4

Application.OnKeyメソッドには、第一引数と第二引数があります。(第二引数は省略可能)
第一引数は、キーを示す文字列を指定します。例えば43行目では「^{UP}」とありますが、{UP}は「方向キーの↑」です。その前に「^」印が付いていますが、これは「Ctrlキーを押しながら」という意味になります。また48行目の「+」は「Shiftキーを押しながら」という意味になります。
その他については「{RIGHT}」は「方向キーの→」、「{DOWN}」は「方向キーの↓」、「{LEFT}」は「方向キーの←」です。

第二引数は、実行するプロシージャ名を示す文字列を指定します。例えば43行目は「"'key_Line ""UP""'"」となっていますが、少し複雑です。図2-5で説明します。

図2-5

通常でしたら、key_Lineプロシージャに引数として「UP」を文字列で渡す場合は、図2-5の左側となります。これはメッセージを出す時の「 MsgBox "こんにちは" 」と同じです。

「プロシージャ名+引数」全体を文字列にする場合には、以下の手順で変換します。
1)文字列の中のダブルクォーテーションは記号と判断されてしまうので、2つ連続させて「文字としてのダブルクォーテーション」にします。
2)プロシージャ名と引数の間は、スペースで空ける必要がありますので、1つスペースを入れて結合します。
3)「引数を持つプロシージャ名」は、全体をシングルクォーテーションで囲みます。
4)1つの文字列として渡すために、全体をダブルクォーテーションで囲みます。

尚、文字列として引数のあるプロシージャを文字列として渡す場面は、Application.OnTimeメソッドなどでもあります。
別項目ですが、「再計算されたか否かのチェックをイベントで取得」も参照下さい。



図2-6は、図2-3の34行目から呼び出されるプロシージャで、「キーのマクロを解除」しています。
  1. '========== ⇩⑦ キー割り当てを元に戻すプロシージャ  =============
  2. Private Sub ReSetOnkey()
  3.  Application.OnKey "^{UP}"     ’←Ctrl+UPを通常操作に戻す
  4.  Application.OnKey "^{RIGHT}"    ’←Ctrl+RIGHTを通常操作に戻す
  5.  Application.OnKey "^{DOWN}"    ’←Ctrl+DOWNを通常操作に戻す
  6.  Application.OnKey "^{LEFT}"    ’←Ctrl+LEFTを通常操作に戻す
  7.  Application.OnKey "+{UP}"     ’←Shift+UPを通常操作に戻す
  8.  Application.OnKey "+{RIGHT}"    ’←Shift+RIGHTを通常操作に戻す
  9.  Application.OnKey "+{DOWN}"    ’←Shift+DOWNを通常操作に戻す
  10.  Application.OnKey "+{LEFT}"    ’←Shift+LEFTを通常操作に戻す
  11. End Sub
図2-6

Application.OnKeyメソッドは、第二引数を省略すると「前のOnKeyメソッドで行われた特殊なキーの割り当てはクリアされ、Excel での通常の動作に戻り」ます。
つまり、今回のマクロの機能をOFFにすると通常のExcel機能に戻り、Ctrlキーを押しながら矢印キーを押すと記入済みセルの端まで移動し、Shiftキーを押しながらだとセルを連続して選択することが出来ます。


図2-7は、罫線を引くプロシージャです。
図2-4のOnKeyメソッドで登録した通り、UP等の方向キーを示す「文字列」を引数(Direction)として渡されます。
  1. '========== ⇩⑧ 罫線を引く+移動するプロシージャ  ====================
  2. Public Sub key_Line(Direction As String)   ’←Direction=動かす方向(半角で)
  3.  Dim RC As Range               ’←単一セル変数の宣言
  4.  Application.ScreenUpdating = False     ’←線引きの間は、画面更新中止
  5.  On Error Resume Next            ’←エラー時(セル位置が動かせない)は引ける罫線だけ引く
  6.  For Each RC In Selection         ’←選択範囲を1セルずつ調査
  7.   Select Case UCase(Direction)      ’←引数を大文字に統一させて分岐
  8.    Case "UP"               ’←「UP」の時
  9.     With RC.Offset(-1, 0).Borders(xlEdgeLeft)  ’←上のセルの左側ライン
  10.      .LineStyle = LineS              ’←線の種類を指定
  11.      '.ColorIndex = xlAutomatic          ’←(線の色を指定)
  12.      '.TintAndShade = 0              ’←(線の濃さを指定)
  13.      .Weight = LineW                ’←線の太さを指定
  14.     End With
  15.     With RC.Offset(-1, -1).Borders(xlEdgeRight)  ’←左上のセルの右側ライン
  16.      .LineStyle = LineS
  17.      .Weight = LineW
  18.     End With
  19.    Case "RIGHT"                ’←「RIGHT」の時
  20.     With RC.Offset(0, 0).Borders(xlEdgeTop)    ’←このセルの上側ライン
  21.      .LineStyle = LineS
  22.      .Weight = LineW
  23.     End With
  24.     With RC.Offset(-1, 0).Borders(xlEdgeBottom)  ’←上セルの下側ライン
  25.      .LineStyle = LineS
  26.      .Weight = LineW
  27.     End With
  28.    Case "DOWN"                ’←「DOWN」の時
  29.     With RC.Offset(0, 0).Borders(xlEdgeLeft)   ’←このセルの左側ライン
  30.      .LineStyle = LineS
  31.      .Weight = LineW
  32.     End With
  33.     With RC.Offset(0, -1).Borders(xlEdgeRight)  ’←左セルの右側ライン
  34.      .LineStyle = LineS
  35.      .Weight = LineW
  36.     End With
  37.    Case "LEFT"               ’←「LEFT」の時
  38.     With RC.Offset(0, -1).Borders(xlEdgeTop)  ’←左セルの上側ライン
  39.      .LineStyle = LineS
  40.      .Weight = LineW
  41.     End With
  42.     With RC.Offset(-1, -1).Borders(xlEdgeBottom)  ’←左上セルの下側ライン
  43.      .LineStyle = LineS
  44.      .Weight = LineW
  45.     End With
  46.   End Select
  47.  Next RC
  48.  Select Case UCase(Direction)
  49.   Case "UP"
  50.    Selection.Offset(-1, 0).Select   ’←上に動かす
  51.   Case "RIGHT"
  52.    Selection.Offset(0, 1).Select   ’←右に動かす
  53.   Case "DOWN"
  54.    Selection.Offset(1, 0).Select   ’←下に動かす
  55.   Case "LEFT"
  56.    Selection.Offset(0, -1).Select   ’←左に動かす
  57.  End Select
  58.  On Error GoTo 0
  59.  Application.ScreenUpdating = True    ’←線引き完了した為、画面更新再開
  60. End Sub
図2-7

コードの説明の前に、まず罫線の引き方について説明します。
例えば図2-8のように、B2セルを選択した状態から「Ctrlキー + 右矢印キー(→)」を押した時を考えます。

図2-8

今回のシステムでは、選択しているセルの左上角にペン先があるとしています(これ以外のところにペン先があると、A1セルの上・左に罫線が引けなくなる為)ので、セルの移動と共にペン先で「B1セルとB2セルの間の罫線」が引けることになります。
しかし、罫線は「セルのプロパティ」です。「B1セルとB2セルの間」などというプロパティはありません。「B1セルのプロパティ」又は「B2セルのプロパティ」を変更していく必要があります。

まず、図2-9でセルを拡大して見てみます。

図2-9

「B1セルとB2セルの間の罫線」を引くためには、①B2セルの上側に罫線を引く、②一つ上のB1セルの下側に罫線を引く、③B2セルの上側とB1セルの下側の両方に罫線を引く の3種類存在します。

それぞれデメリットを考えてみると、
①印刷時に記入済みセルの1つ下の行まで印刷範囲を広げないと、罫線が印刷されない。
 全行罫線を引いて印刷した場合、複数頁に跨った一番下の罫線が印刷されない。
②端の行・列(例えば、1行目の上側)には罫線が引けない
③印刷時に罫線だけの頁が出来る可能性有り。マクロ処理が多くなる

となるので、①または③が良さそうですが、今回のシステムでは③としました。理由は、複数セル選択時にセル書式変更で罫線を設定する時、セル間は両側のセルから引いていることが分かったからです。
しかし①の方法も、罫線の引き方を理解した上で使うのであれば問題無いので、その際は以下の部分を削除し使用して下さい。
●図2-7の80~83行目、90~93行目、100~103行目、110~113行目
●図2-14の144~146行目、152~154行目、160~162行目、168~170行目

次に、Ctrlキーを押しながら方向キーを押すと、どのセルの罫線が引かれるかを考えます。

図2-10

B2セルを起点として上下左右に動かした結果が図2-10です。セルの左上角にペン先があり、ペン先が通過した軌跡の両側のセルの罫線を変更していますので、起点・終点のセル以外のセルにも罫線設定が必要である事が分かると思います。
この図2-10を見ながら「どのセルのどの端面(Border)に罫線を引くか」を考えていきます。例えばCtrl+↑キーであれば、図2-7の74行目・80行目のような感じです。

さて、以上のことを踏まえて、コードを上の方から見ていきましょう。
68行目の「Application.ScreenUpdating = False」で画面更新を一旦中止させ、処理速度の向上を図っています。
罫線引き処理が終わった130行目で、画面更新を再開しています。

69行目の「On Error Resume Next」は、129行目の「On Error GoTo 0」でエラー処理解除するまでの間、「エラーが発生しても次に進め」という意味です。
ここで、どのような場合に「エラー発生」が起きるかを図2-11で確かめてみます。

図2-11

図2-11の①や②の場合は、セルの移動は可能な位置にいますが「罫線を引くセルが存在しない(黄色の罫線部分)」ことが分かります。しかし片方の罫線は引けますので、見た目にはOKとなります。
また、③や④の場合はセルの移動もできない位置にいますし、罫線も全く引けません。これは「セルの移動が出来ないのですから、ペン先の移動が無く線が書けない」と考えれば、実際に鉛筆で線を引いている感覚にも合っていると思います。

このような「罫線が引けない」「セル移動が出来ない」異常が発生した場合、その後の処理を全て中止してしまったら隅のセルには罫線が引けない事になります。そればかりか、NGが出る前に完了した処理に対して取り消し処理の様なものも必要になるかもしれません。
そこで、「出来ない処理は無視して次の処理に進む」のが「On Error Resume Next」命令となります。
但しこの命令は未知のエラーも無視します。どんなエラーが発生するかを充分把握した上で使わないと、望んでいる結果が得られない事になります。またエラーにはエラー番号がありますので、それに従って処理を振り分ける方法も良いと思います。

71行目では、選択したセル範囲を1つ1つのセルに分解し、それぞれのセルの動きに応じて罫線を引いていきます。
この For Each Next を使用せず選択範囲単位(オブジェクトをSelectionにする)で罫線を引こうとすると、連続した複数範囲を選択した場合は、図2-12の様に「選択範囲の左上角は1箇所」と判断されて、1本しか罫線を引いてくれません。

図2-12

それを避けるため、 For Each Next で選択したセル範囲を1つ1つのセルに分解することで、図2-13の様に選択したセル分だけの罫線を一回で引いてくれることになります。

図2-13

72行目では、Select Case を使って、引数であるDirectionに従って分岐させています。
ここで「UCase(Direction)」と引数の文字列を大文字変換させています。今回は1箇所のプロシージャ(ReSetOnkey)でしか「マクロ+引数」を使用していませんので引数文字列の大文字小文字は管理できそうですが、複数個所から呼び出したりする場合にはミスが起きる(=引数を小文字にしてしまう)場合も考えられます。
文字列の内容で分岐をする場合には、分岐する文字として「"UP","up","Up","uP"」と考えられる大文字小文字の組み合わせを書く方法もありますが、分岐前にUCase・LCaseなどで揃える方が効率的です。

73行目以降は、引数文字列で分岐させて対応するセルに罫線を書く部分になります。「UP」「RIGHT」「DOWN」「LEFT」のどれも同様の内容ですので、ここでは「UP」を例に説明します。

引数が「UP」だった場合、73行目の「Case "UP"」の中に入ります。
「UP」では、どのセルに罫線を引くかを図2-10で考えてみましょう。「UP」は図2-10の左上の図になりますので、動く前にいる位置から見て「1つ上のセルの左側」と「左ななめ上のセルの右側」に線を引けば良いことが分かります。

それを式で表すと、以下のようになります。
・「1つ上のセルの左側」   → 「 RC.Offset(-1, 0).Borders(xlEdgeLeft)」
・「左ななめ上のセルの右側」 → 「 RC.Offset(-1, -1).Borders(xlEdgeRight)」

尚この式の中での「RC」は、71行目で「選択範囲を1つ1つに分解したセル」ですので、まだ動く前の位置になります。

「1つ上のセルの左側」については74~79行目で罫線を設定していますが、オブジェクトである「RC.Offset(-1, 0).Borders(xlEdgeLeft)」までは共通ですので全体をWithで囲っています。そのWithの中で、75行目で線種「LineStyle 」を、78行目で線の太さ「Weight 」を設定しています。

75行目の「LineStyle 」には「LineS」変数を、78行目の「Weight 」には「LineW」変数を設定しています。この2つの変数は、初期値はフォームモジュールの図2-23の「Line_Ini」プロシージャで設定をし、OptionButtonをクリックすることで線種変更した際はクラスモジュールの図2-1の11~13行目で設定変更を行っています。

なお、罫線には色や色の濃さを設定するプロパティもありますが、今回のシステムでは設定していません(全て黒にしてあります)。もし、そのような設定をする場合には、コメントアウトしてある「ColorIndex(76行目)」、「TintAndShade(77行目)」に係数を入れるような改造を検討されるのも良いかと思います。

引数が「"RIGHT"」の場合は、85~93行目、「"DOWN"」の場合は、95~103行目、「"LEFT"」の場合は、105~113行目で罫線を引く処理を行っています。

選択範囲の1つ1つのセルについて罫線引き処理が完了すると、次に118~127行目の処理に入ります。
まず118行目で、引数の文字列に従って分岐するのは72行目の処理方法と同じです。

例えば、引数が「UP」だった場合には119行目で引っ掛かり、120行目の「Selection.Offset(-1, 0).Select」を実行します。「Offset(-1, 0)」ですから「一つ上のセル」を選択することになります。
ここで注目してほしいのは、動かす対象は「Selection」としている事です。セルを1つ1つ動かすのではなく、選択している範囲を1つの「まとまりとして動かしている」のです。
「まとまりとして動かす」事で、Ctrl+↑ であれば一番上の選択セルが頭打ちで動くことが出来なくなることで「選択範囲全体が動かない」(=選択範囲の形が壊れない)ことになるのです。
もしこのセル移動コード部分を、Selectionを1つ1つのセルに分解して処理する工程(71~116行目)の中で実施してしまうと、「動かせるセル」と「動かせないセル」が混在し、選択範囲の形が壊れることになってしまいます。(分解したセル位置を、再び結合するコードも必要になります)

最後に129行目でエラー処理を終了し、130行目で画面更新を再開させます。


図2-14は、図2-6の60~63行目のApplication.OnKeyで設定した、罫線を削除(Shift+矢印キー)するプロシージャです。
  1. '========== ⇩⑨ 罫線の削除+移動プロシージャ  =============
  2. Public Sub key_Line_D(Direction As String)   ’←Direction=動かす方向(半角で)
  3.  Dim RC As Range                ’←選択範囲を1セルずつに分ける変数
  4.  Application.ScreenUpdating = False      ’←線消しの間は、画面更新を停止
  5.  On Error Resume Next           ’←エラー時(セル位置が動かせない)は引ける罫線だけ引く
  6.  For Each RC In Selection         ’←選択範囲を1セルずつ調査
  7.   Select Case UCase(Direction)
  8.    Case "UP"                ’←「UP」の時
  9.     With RC.Offset(-1, 0).Borders(xlEdgeLeft)  ’←上のセルの左側ライン
  10.      .LineStyle = xlNone             ’←線を消す(xlLineStyleNoneでもOK)
  11.     End With
  12.     With RC.Offset(-1, -1).Borders(xlEdgeRight)  ’←左上のセルの右側ライン
  13.      .LineStyle = xlNone
  14.     End With
  15.    Case "RIGHT"               ’←「RIGHT」の時
  16.     With RC.Offset(0, 0).Borders(xlEdgeTop)  ’←このセルの上側ライン
  17.      .LineStyle = xlNone
  18.     End With
  19.     With RC.Offset(-1, 0).Borders(xlEdgeBottom)  ’←上セルの下側ライン
  20.      .LineStyle = xlNone
  21.     End With
  22.    Case "DOWN"                  ’←「DOWN」の時
  23.     With RC.Offset(0, 0).Borders(xlEdgeLeft)  ’←このセルの左側ライン
  24.      .LineStyle = xlNone
  25.     End With
  26.     With RC.Offset(0, -1).Borders(xlEdgeRight)  ’←左セルの右側ライン
  27.      .LineStyle = xlNone
  28.     End With
  29.    Case "LEFT"                 ’←「LEFT」の時
  30.     With RC.Offset(0, -1).Borders(xlEdgeTop)  ’←左セルの上側ライン
  31.      .LineStyle = xlNone
  32.     End With
  33.     With RC.Offset(-1, -1).Borders(xlEdgeBottom)  ’←左上セルの下側ライン
  34.      .LineStyle = xlNone
  35.     End With
  36.   End Select
  37.  Next RC
  38.  Select Case UCase(Direction)
  39.   Case "UP"
  40.    Selection.Offset(-1, 0).Select   ’←上に動かす
  41.   Case "RIGHT"
  42.    Selection.Offset(0, 1).Select   ’←右に動かす
  43.   Case "DOWN"
  44.    Selection.Offset(1, 0).Select   ’←下に動かす
  45.   Case "LEFT"
  46.    Selection.Offset(0, -1).Select   ’←左に動かす
  47.  End Select
  48.  On Error GoTo 0
  49.  Application.ScreenUpdating = True    ’←線消し完了した為、画面更新再開
  50. End Sub
図2-14

構造は罫線を引くプロシージャ(図2-7)と全く同じで、引数として受け取る文字列の「UP、RIGHT、DOWN、LEFT」で分岐し、罫線を消していきます。
罫線を消すには、引いた時と同様に図2-10に従って2つのセルのBorderに罫線の設定をします。罫線を消すためには「LineStyle」プロパティに「xlNone」または「xlLineStyleNone」を設定します。どちらも数値としては「-4142」で同じ値です。
消す場合も、図2-11のように消せない場所・移動できない位置が出る可能性がありますので、136行目の「On Error Resume Next」でエラー発生しても次のコードに進むようにしています。
なお「消すのはLineStyleだけで良いのか」と思われるかもしれませんが、xlNone または xlLineStyleNone を LineStyleプロパティに設定すると、Weightプロパティは「xlThin(細線)」の既定値に自動的に変わるようです。ですので「 Weight 固定でLineStyle だけの設定で罫線を付けたり消したり」という操作は出来ないようです。

罫線を消したあとは、174~183行目で選択範囲を全体的に移動させます。


2-3.フォームモジュール

最後にフォームモジュールです。まずはダイアログボックスを図2-15の様に配置します。


図2-15

線種は(線無しを除いて)全部で13種類ありますので、選択式に出来るように「OptionButton1 ~ OptionButton13 」の13個を並べます。並び順は自由ですが、OptionButtonの番号(名称)と種別はちゃんと結びつける必要があります。

線種(LineStyle)は7種、太さ(Weight)は4種ありますが、設定可能なのは図2-16の組み合わせの13種です。
xlHairline  極細xlThin  細xlMedium  中xlThick  太
xlContinuous   実線(細)   〇   〇   〇   〇
xlDash      破線   〇   〇
xlDashDot    一点鎖線   〇   〇
xlDashDotDot   二点鎖線   〇   〇
xlDot       点線   〇
xlDouble     二重線   〇
xlSlantDashDot  斜め斜線    〇
図2-16

また、ダイアログボックスには「機能ON-OFFのボタン」をどこか(サンプルファイルでは下側)に配置します。
このボタンには、罫線操作とExcelの通常操作を切り替える役目を持たせます。本マクロでは罫線を引いたり消したりするために「Ctrl+矢印キー」「Shift+矢印キー」を使ってしまっているため、「Ctrl+矢印キー」の本来の機能である「記入済みセルの端まで移動」、「Shift+矢印キー」の本来の機能である「セルを連続して選択」が使えなくなります。
「本来の機能も使いたい」という方のために、このボタンを押して切り替えてもらう事を考えました。


フォームモジュールに記述するコードが、図2-17以下です。
  1. '========== ⇩⑩ OptionButton配列をClass1型として作成 ============
  2. Private NumOpt(1 To 13) As New Class1
図2-17

まず、フォームモジュールの先頭(188行目)に、UserForm1に配置したOptionButton13個をイベントを発生出来るClass1の型として宣言します。13個が入る配列形式にします。


次に、UserForm起動時の初期設定が、図2-18となります。
  1. '========== ⇩⑪ UserForm起動時の設定 =============
  2. Private Sub UserForm_Initialize()
  3.  Dim i As Integer
  4.  For i = 1 To 13
  5.   NumOpt(i).NewClass Controls("OptionButton" & i)  ’←13個のOptionButtonをNumOpt変数に格納
  6.  Next i
  7.  StartUpPosition = 0      ’←フォームは手動移動可能とする
  8.  Top = Application.Top     ’←フォームの初期位置(上下方向)はExcelウィンドウの上に揃える
  9.  Left = Application.Left + Application.Width - UserForm1.Width
  10.               '↑ フォームの初期位置(左右方向)はExcelウィンドウの右に揃える
  11.  Call Line_Set         ’←線の種類と太さのリスト作成
  12.  Call Line_Ini         ’←線の初期値代入
  13. End Sub
図2-18

まず192~194行目は、13個のOptionButtonをイベントが発生するNumOpt変数に格納しています。
この中の「NewClass」は図2-1の4~6行目のプロシージャ名ですが、このプロシージャ内の「Set Opt = c(引数)」を使って、図2-1の2行目でWithEvents宣言したOpt変数(イベント発生する変数)に1つ1つのOptionButtonを登録しています。

196~198行目は、表示するフォーム(=ダイアログボックス)の初期の位置を設定しています。
196行目は、フォームの表示位置を指定する「 StartUpPosition 」の設定で、設定値は図2-19のように4種類選択できます。
 Value 動作
0自分で画面上の位置を決定
1Excel内で中央の位置
2PCの画面全体の中央の位置
3PCの画面の左上隅の位置
図2-19

今回は「0」を選び、その位置を197行目でPC画面の上からの位置(Topプロパティ)、198行目でPC画面の左からの位置(Leftプロパティ)を指示します。
使い勝手から考えて、Excel画面の右上にダイアログボックスを表示させたかったので、図2-20の様に、197行目・198行目を設定しました。


図2-20

その下の200行目は、罫線の種類と太さのリストを作り(図2-22)、201行目は罫線の種類と太さの初期値を設定(図2-23)しています。

ダイアログボックス上に配置した「機能ON-OFFボタン」を押した時のコードが図2-21です。
  1. '========== ⇩⑫ フォーム上のボタンが押された時に実行されるイベントプロシージャ ========
  2. Private Sub CommandButton1_Click()
  3.  Call Line_Draw          ’←ボタン表示、キー割り当てを変更
  4.  SetFocus Application.hwnd    ’←シート側へフォーカス移動
  5. End Sub
図2-21

205行目で、標準モジュールの「Line_Draw」プロシージャ(図2-3)を呼び出します。
これにより、機能ON と機能OFF が切り替わり、ボタンの表示とキーに対する登録マクロの切り替えが行われます。

その後、206行目の「SetFocus Application.hwnd」が実行されますが、図2-2で説明の通り「Excel本体のウィンドウをフォーカスする」ことでワークシート側にフォーカスが移ります。
見た目的には「ボタンを押した後、シート側をクリックしなくてもシート入力が可能」となります。


罫線の種類は全13種という事は図2-16で説明しました。また、UserForm1上に配置したOptionButtonの数も13個です。その13個のOptionButtonに対応した罫線の「線種(LineStyle)」と「太さ(Weight)」を図2-22でリスト化しています。
  1. '========== ⇩⑬ 罫線の種類と太さのリスト作成  =============
  2. Private Sub Line_Set()
  3.  Line_Array(1, 1) = xlContinuous   '実線(極細)
  4.  Line_Array(2, 1) = xlHairline
  5.  Line_Array(1, 2) = xlContinuous   '実線(細)
  6.  Line_Array(2, 2) = xlThin
  7.  Line_Array(1, 3) = xlContinuous   '実線(中)
  8.  Line_Array(2, 3) = xlMedium
  9.  Line_Array(1, 4) = xlContinuous   '実線(太)
  10.  Line_Array(2, 4) = xlThick
  11.  Line_Array(1, 5) = xlDot       '点線(細)
  12.  Line_Array(2, 5) = xlThin
  13.  Line_Array(1, 6) = xlDash      '破線(細)
  14.  Line_Array(2, 6) = xlThin
  15.  Line_Array(1, 7) = xlDash      '破線(中)
  16.  Line_Array(2, 7) = xlMedium
  17.  Line_Array(1, 8) = xlDashDot     '一点鎖線(細)
  18.  Line_Array(2, 8) = xlThin
  19.  Line_Array(1, 9) = xlDashDot     '一点鎖線(中)
  20.  Line_Array(2, 9) = xlMedium
  21.  Line_Array(1, 10) = xlDashDotDot   '二点鎖線(細)
  22.  Line_Array(2, 10) = xlThin
  23.  Line_Array(1, 11) = xlDashDotDot   '二点鎖線(中)
  24.  Line_Array(2, 11) = xlMedium
  25.  Line_Array(1, 12) = xlSlantDashDot  '斜め斜線(中)
  26.  Line_Array(2, 12) = xlMedium
  27.  Line_Array(1, 13) = xlDouble     '二重線(太)
  28.  Line_Array(2, 13) = xlThick
  29. End Sub
図2-22

リスト(Line_Array配列)の一次元目で「LineStyle」か「Weight」かを分け、二次元目で13種類の線種に分け、図2-16に従って値を代入しています。
この代入している値(例えば「xlContinuous」)は、文字列では無く実体は数値です。ですので図2-2の22行目では、Line_ArrayをLong型で宣言しています。


図2-22(Line_Setプロシージャ)で作成したリストを使用し、罫線種類の初期値を代入するのが図2-23です。
  1. '========== ⇩⑭ Form1のOptionボタンに合わせて線の初期値代入  =================
  2. Private Sub Line_Ini()
  3.  UserForm1.OptionButton2.Value = True   ’←実線(細)を初期値にする
  4.  LineS = Line_Array(1, 2)   ’←線の種類設定
  5.  LineW = Line_Array(2, 2)   ’←線の太さ設定
  6. End Sub
図2-23

251行目では、2番目のOptionButton(実線(細))を選択した状態にしています。もし、OptionButtonのValue値をフォーム上で変更しても、251行目が実行されると2番目が初期値になります。

252行目・253行目では、罫線リストを使用し2番目の値(実線(細)の値)をLineStyleとWeightプロパティに代入しています。

なお「初期値設定」の方法として、今回は2番目の「実線(細)」のボタンのValue値をTrue(選択済み)にしてから「リストの2番目を代入」していますが、「フォームのプロパティ設定をFor~Nextで拾ってValue値がTrueになっているものを初期値として設定」する方法もあると思います。
しかし、全てのOptionButtonのValue値をFalseにすることも出来ます(=どれも選択されていない状態)ので、その場合には「LineStyle=0 Weight=0」となり罫線が引かれないという不具合が発生します。


図2-24は、マクロを起動し最初にUserForm1を表示された時に、フォーカスをExcelシート側に移すプロシージャです。
  1. '========== ⇩⑮ フォームを表示した時に実行するイベントプロシージャ ==================
  2. Private Sub UserForm_Activate()
  3.  SetFocus Application.hwnd   ’←シート側へフォーカス移動
  4. End Sub
図2-24

図2-25は、UserForm1の上でマウスが動いた時に発生するイベントプロシージャです。
  1. '======= ⇩⑯ フォーム上でマウスが動いた時に実行するイベントプロシージャ =======
  2. Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  3.  SetFocus Application.hwnd    ’←シート側へフォーカス移動
  4. End Sub
図2-25

これは、表示されたUserForm1のタイトル部分(「UserForm1」と書かれている部分)をクリックしてフォームの位置を移動した後に、フォーカスをExcelシート側に移す方策として作成しました。
しかしこのイベントは、フォーム上部のタイトル部分以外のフォーム本体部分でマウスが動かなければ反応しません。ですので、タイトル部分をクリックして移動後、マウスをタイトル部分に置いたままとか、本体を通過しないように動かされると、フォーカスがシート側に移らないのが弱点です。(実用上は大丈夫かと思っています)


図2-26は、UserForm1を右上の×印で閉じた時の処理です。
  1. '========== ⇩⑰ Form1を×で閉じる時に発生するイベントプロシージャ ===============
  2. Private Sub UserForm_Terminate()
  3.  On_Off = True          ’←閉じた後は線引き中止にさせる
  4.  Call Line_Draw          ’←(ボタン表示をOFFにし、)キーを通常に戻す
  5. End Sub
図2-26

×印で閉じるということは「罫線引きを使わない意思」と判断できます。
ですので265行目で「On_Off = True」とした後、266行目でメインプロシージャの「Line_Draw」を呼び出していますので、On_Off が反転し 機能OFF 状態にしています。



3.Excelへのマクロの登録

さて、マクロは完成したのですが、このままでは使えません。というのは、罫線を引きたいファイルはマクロが記述してあるファイルでは無く普通のファイルのはずです。しかも1つのファイルだけではなく、どんなファイルにでも罫線が引けないと、あまり意味がありません。
そうです。この手のマクロは「ウラで動いて」くれないと困るのです。

簡単にウラで動かすためには、次の2つの方法があります。
1)Excelのアドインファイルにする
2)Excelの個人用マクロブックにする



3-1.Excelのアドインファイルにする

まず図3-1の左図ように、マクロ作成したファイル(拡張子 .xlsm)をアドインファイル(拡張子 .xlam)として保存します。
保存先は、保存形式をアドインファイルを選択すると「自動的に保存すべきフォルダに移動」するようですが、もし移動しない場合は「C:¥Users¥(ユーザ名)¥AppData¥Roaming¥Microsoft¥AddIns¥」を選んで下さい。

続いて図3-1の右側のように、Excelの開発タブの中の「Excelアドイン」を開き、先ほど保存したファイル名の「有効なアドイン」にレ点をつけ、OKボタンを押して下さい。


アドインファイルとして保存
  
保存したアドインを有効化
図3-1


次に、Excelに新しい自分用のタブを作ります。図3-2の左側のように、どこのリボンでも良いので、リボンの上でマウスの右ボタンをクリックし「リボンのユーザー設定」を選択して下さい。
すると、Excelのオプションとして「リボンのユーザー設定」ダイアログが出てきますので、図3-2の真ん中の図のように、左欄の上の「コマンドの選択」欄で「ユーザー設定のタブとグループ」を選択します。すると「新しいタブ(ユーザー設定)」と「ユーザー設定のグループ」の2種類が現れます。
初めて自分用のタブを作る人は上側の「新しいタブ(ユーザー設定)」を選択し、追加ボタンを押します。
既に自分用のタブがある人は、下側の「ユーザー設定のグループ」で「新しいグループ(ユーザー設定)」を追加しても良いですし、既にあるグループを使用するならば、タブの設定は不要です。
尚、図3-2の右側図のように、タブの名前変更も可能です。

リボン上でマウス右クリックし
「リボンのユーザー設定」を選択
  
左欄で「ユーザー設定のタブとグループ」
を選択し、追加ボタンを押す
  
名前の変更も可能(いつでも可)
図3-2


ここで一度Excelを閉じ(マクロ入りの.xlsm を閉じる事になる)、新たにExcelの新規ブックを開きます。
開いたら、先ほどと同じようにリボンの上でマウス右ボタンを押して「リボンのユーザー設定」を選択し、「リボンのユーザー設定」ダイアログボックスを表示させます。
左欄の「コマンドの選択」で、今度は「マクロ」を選択します(図3-3の左図)。するとアドインしたファイル(拡張子 .xlam )内の実行できるプロシージャ名が出てきます。
実行できるプロシージャとは、「Public 」且つ「引数を持たない」プロシージャですので、今回のマクロの中ではメインプロシージャである「Line_Draw」プロシージャのみということになります。

まず、その「Line_Draw」を選択(図3-3の中央図)し、次に追加先の「新しいグループ」を選択(図3-3の右図)します。
追加元・追加先ともグレー色で選択状態になったら「追加」ボタンを押します。

左欄で「マクロ」を選択
  
メインプロシージャの
「Line_Draw」を選択
  
追加先「新しいグループ」を選択
し、追加ボタンを押す
図3-3


最後に右下の「OKボタン」を押して「リボンのユーザー設定」ダイアログを閉じます。
Excelの「新しいタブ」を開いてみると、Line_Drawマクロが登録されているはずです。そのアイコンをクリックすれば、罫線ダイアログが表示され、方向キーで罫線が引けるようになります。(図3-4)


図3-4


尚「プロシージャ名やアイコンの絵を変えたい」という方は、「リボンのユーザー設定」で「新しいグループ」の下にぶらさがっている「Line_Draw」をクリックした後「名前の変更」をクリックして、図3-5の様に表示名とアイコンを変更する事が出来ます。


図3-5




3-2.Excelの個人用マクロブックにする

もう一つの登録方法である「個人用マクロブック」にするには、作ったマクロ入りファイル(拡張子 .xlsm)を図3-6のようにファイルの種類を「Excelバイナリブック(*.xlsb)」にし、ファイル名も「personal.xlsb」と変更して保存します。(ファイル名は大文字小文字どちらでもOKです。
保存先は、「C:¥Users¥(ユーザ名)¥AppData¥Roaming¥Microsoft¥Excel¥XLSTART」です。
(アドインの様に自動的にフォルダが移動する事はありませんので注意して下さい)


図3-6


保存したら、Excel上部のファイル名が「Personal.xlsb」に変わります。
次に、「表示タブ」の「ウィンドウ」の中の「ウィンドウを表示しない」ボタン(図3-7の矢印の位置)をクリックします。


図3-7


するとワークシートが消えますので、Excelの右上の×印でExcelを閉じる操作をすると、図3-8の様に「保存しますか?」ダイアログが出ますので、「保存」をクリックしてExcelを閉じます。

図3-8




もう一度、新たにExcelの新規ブックを立ち上げます。
「新しいタブ」と「新しいグループ」を作るのは、アドインの時と同じですので、図3-2を参照下さい。
マクロの登録もアドインの時とほぼ一緒ですが一応説明します。
リボン上でマウス右ボタンを押し「リボンのユーザー設定」ダイアログボックスを表示させた後、左欄の「コマンドの選択」で「マクロ」を選択します。
左欄には、図3-9のようにメインプロシージャの名前が表示されます。(アドインの時と少し違うのは「personal.xlsb ! Line_Draw」と、ファイル名+プロシージャ名で表示されます)
そのプロシージャ名と追加する側のグループの両方をクリックし選択した後、追加ボタンをクリックします。

図3-9


すると、図3-10のように「新しいタブ」の中にアイコンが出来、クリックすることでLine_Drawプロシージャが動いて罫線ダイアログが出てきます。

図3-10


4.最後に

実は「大昔に購入し使っていた罫線引きソフト」でのペン先は、セルの左上角のみではなく四隅どこでも選択できるようになっていました(罫線の引き方も図2-9の①の一重でした)。
四隅のどこでも罫線が書けるので混在させて使ってしまうと、(罫線を消すのも一重でしたので)罫線を消しているつもりなのに消えない、と何度もイライラした覚えがあります。
そのような記憶もあったため、今回のマクロでは「ペン先は左上角固定で二重線」にこだわりました。

また最近のバージョンでは斜め罫線も可能です。今回は盛り込みませんでしたが、適当なキーを割り当てて斜め罫線を引く機能を追加するのも便利かと思います。

3項目でアドイン登録と個人用マクロブック登録の方法を紹介しましたが、今回のマクロだけでなく自分で作ったマクロをどんどん登録して活用されるのが良いと思います。
尚、アドインファイルは何個でも登録できますが、「Personal.xlsb」は1つのみです。個人用マクロブックの方法を採用するのであれば、元のxlsmファイルは残しておき、マクロを追加し完成したら再登録する、という方法が良いかと思います。


セルの罫線を矢印キーで引く(it-021.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。