読者です 読者をやめる 読者になる 読者になる

VBAで名前の定義をするといろいろ楽

セル範囲の名前の定義をVBAでやったら便利

転記するごとにセル範囲を定義し直す

このときみたいなデータ転記系の処理をした場合、ワークシート関数のCOUNTIFなんかを使って種別ごとの数を勘定したい、ということがよくある。

しかしながら、転記件数が変化する場合、COUNTIFの第1引数(範囲)が変化することになる。

だからといって、毎回転記処理が終わってから手動でCOUNTIFをセットするというのもマヌケな話。

そこで、

抽出するごとにCOUNTIFの第1引数になるセル範囲に名前を定義すりゃいいじゃん!

と考えた。

方針

手順は次の通り。

  1. まず、対象となるセル範囲に「StyleRangeForCount」と名前を付けておく
  2. VBAで、一旦「StyleRangeForCount」と名付けられたセル範囲のNameプロパティをDeleteする
  3. 新たにCOUNTIFの第1引数にしたいセル範囲を取得する
  4. 3.で取得したセル範囲のNameプロパティに「StyleRangeForCount」という名前をセットする

このようにしておいて、COUNTIFの第1引数を「StyleRangeForCount」にしておけば、転記結果が変化してもCOUNTIFの第1引数が変化に追随してくれるはずだ。

リスト1
Public Sub renameRange()
  Set styleSh = ThisWorkbook.Worksheets("戦法別")
  Dim objRange As Range
  Dim lastRow As Integer
  Range("StyleRangeForCount").name.Delete    '……(1)'
  With styleSh
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set objRange = Range(.Range("D3"), _
                         .Range("D" & lastRow))    '……(2)'
  End With
  objRange.name = "StyleRangeForCount"    '……(3)'
End Sub

実際には、もう少し複雑なコードを書いたんだが、説明のために単純化したコードを載っけている。

まず、(1)の

Range("StyleRangeForCount").Name.Delete

で、もともとの「StyleRangeForCount」という名前をDelete。

次に、(2)の

Set objRange = Range(.Range("D3"), .Range("D" & lastRow))

で、新たにできたCOUNTIFの第1引数にすべき範囲を変数objRangeにセット。

最後に、(3)の

objRange.name = "StyleRangeForCount"

で新たにできたCOUNTIFの第1引数にすべき範囲に「StyleRangeForCount」と名前を付けている。

実行

f:id:akashi_keirin:20170422215310j:plain

ちょっと分かりにくいかも知れないが、最初は、D3~D37のセル範囲に「StyleRangeForCount」という名前が定義されている。

f:id:akashi_keirin:20170422215316j:plain

転記元データをちょっといじくって、失格選手(w)を増やし、再度転記処理を行ってみる。

f:id:akashi_keirin:20170422215327j:plain

当然、転記される件数が減るので、こんな状態になる。依然、「StyleRangeForCount」と名付けられたセル範囲はD3~D37のまま。

ここで、リスト1を実行。

f:id:akashi_keirin:20170422215344j:plain

セル範囲D3~D37から名前がぬぐい去られていることがお分かりだろうか。

んで、セルの選択範囲をD3~D27に変えると、

f:id:akashi_keirin:20170422215406j:plain

ほれ、「StyleRangeForCount」という名前が定義されている。

f:id:akashi_keirin:20170422215417j:plain

こんなふうに、COUNTIFの第1引数を「StyleRangeForCount」にしておくと、

失格者を増やす前

f:id:akashi_keirin:20170422215424j:plain

失格者を増やした後

f:id:akashi_keirin:20170422215433j:plain

と、COUNTIFの結果が転記結果に追随していることが分かる。

感想

手作業で名前の定義や編集・削除を行うのはかなりメンドクサイんだけど、VBAでやると簡単・便利だと思いました。

@akashi_keirin on Twitter