セル範囲の伸び縮みに追随して名前を定義する[Excel] - 素人がExcelVBAによる「一人働き方改革」に挑むブログ

セル範囲の伸び縮みに追随して名前を定義する

セル範囲に名前を定義しておくと、ワークシート関数の引数にするのにも、VBAでRangeオブジェクトを参照する際にも便利なことがある。

ただ、名前を付けて管理したいセル範囲に伸び縮みがあるような場合に、その都度[数式]タブから修正するのはメンドクサイ。

そこで、セル範囲の変化に追随して名前を定義し直すようなプロシージャを作ってみた。

ただし、今回は、「データの入力規則」の「リスト」機能の参照先を想定しているので、１列だけのセル範囲なのでご承知おきくだされ。まあ、複数列の範囲に対応するのもカンタンだと思いますが。

セル範囲の伸び縮みに追随して名前を定義するプロシージャ

まず、下記のコードを書く。

リスト１　標準モジュール

Public Sub updateRangeName(ByVal Target As Range, _
                           ByVal targetColumn As Long, _
                           ByVal startRow As Long, _
                           ByVal targetName As String)    '……(1)'
  If Target.Column <> targetColumn Then Exit Sub    '……(2)'
  Dim Sh As Worksheet
  Set Sh = Target.Parent
  Dim maxRow As Long    '……(3)'
  maxRow = Sh.Cells(Rows.Count, targetColumn).End(xlUp).Row
  If Target.Row < startRow Then Exit Sub    '……(4)'
  If startRow > maxRow Then Exit Sub    '……(5)'
  With Sh    '……(6)'
    .Range(.Cells(startRow, targetColumn), _
           .Cells(maxRow, targetColumn)).Name = targetName
  End With
End Sub

まず(1)の

Public Sub updateRangeName(ByVal Target As Range, _
                           ByVal targetColumn As Long, _
                           ByVal startRow As Long, _
                           ByVal targetName As String)

でこのプロシージャに渡す引数を設定。

第１引数Targetは、このプロシージャが実行されるときのセル。WorksheetのChangeイベントで呼び出すことを想定しているので、変更が加えられたセル、ということになる。

第２引数targetColumnは、名前を付けて管理したいセル範囲の列位置。

第３引数startRowは、名前を付けたいセル範囲の上端の行番号。

第４引数は付けたい名前。

(2)、(4)、(5)はガード節。変更されたセルの位置によっては、名前を付けるセル範囲を更新する必要がないのでこうする。

まず(2)の

If Target.Column <> targetColumn Then Exit Sub

では、変更が加えられたセルが名付け対象列でなければ、何もせずに処理を抜けるようにしている。

(3)からの２行

Dim maxRow As Long
maxRow = Sh.Cells(Rows.Count, targetColumn).End(xlUp).Row

では、お馴染みの方法で名前を付けるセル範囲の最終行を取得。

今のところ、単純なやり方で最終行を取得しているが、フィルター等で非表示にされる恐れがある場合は、それなりの対応を追加する必要があるだろう。

(4)の

If Target.Row < startRow Then Exit Sub

では、変更されたセルが名前を付けるセル範囲上端の行よりも上にある場合に、何もせずに処理を抜けるようにしている。

(5)の

If startRow > maxRow Then Exit Sub

では、引数として渡された名前を付けるセル範囲上端の行よりも最終行が上にあるような場合に、何もせずに処理を抜けるようにしている。

ここまで来たら、後は伸び縮みしたセル範囲に再度名前を付け直すだけ。

(6)の

With Sh
  .Range(.Cells(startRow, targetColumn), _
         .Cells(maxRow, targetColumn)).Name = targetName
End With

で新しいセル範囲に引数targetNameで受け取った名前を付けておしまい。

WorksheetのChangeイベントから呼び出す

あとは、リスト１をWorksheetのChangeイベントから呼び出すようにしたらよい。

f:id:akashi_keirin:20180318084426j:plain

こんなふうにシートに表を作成しておき、次のコードをシートモジュールに書く。この段階では、ご覧のように選択範囲には名前が定義されていない。

リスト２　シートモジュール

Private Sub Worksheet_Change(ByVal Target As Range)
  Call updateRangeName(Target:=Target, _
                       targetColumn:=1, _
                       startRow:=2, _
                       targetName:="梁山泊")
End Sub

Ａ列２行目～最終行の範囲に「梁山泊」と名前を付けることになる。