シートオブジェクトにPropertyを生やそう！

VBAに関する考え

シートモジュールにPropertyを生やそう！

シートモジュールにPropertyを設置すると便利、というだけの話。

準備

たとえば、ワークシート（オブジェクト名は「Sheet1」）上に、次のようなリストがあるとする。

f:id:akashi_keirin:20190628171100j:plain

とりあえず超シンプルな表にした。

Propertyを設定する

このリスト部分を、Sheet1オブジェクトのPropertyにしてしまおう、という試み。

後々の拡張性も考えて、次のような仕様にした。

仕様
  • 列数は定数で指定
  • 行数は伸び縮みすることを見越して指定列の最終セルをその都度取得

以上。

コーディング

Sheet1モジュールに次のようにコードを書く。

リスト１　Sheet1モジュール
Option Explicit

'Constants'
'リスト領域の列数'
Private Const LIST_AREA_WIDTH As Long = 4
'リスト左上端のセルアドレス'
Private Const LEFTTOP_ADDRESS As String = "A1"

Public Property Get ListAreaRange( _
             Optional ByVal targetColumn As Long) As Range
  '引数省略時は「1」にする。'
  If targetColumn = 0 Then targetColumn = 1
  '一旦基準セルを変数に格納。'
  Dim rng As Range
  Set rng = Me.Range(LEFTTOP_ADDRESS)
  'リストのタテ方向のサイズを取得。'
  Dim verticalSize As Long
  verticalSize = Me.Cells(Me.Rows.Count, targetColumn).End(xlUp).Row
  verticalSize = verticalSize - Me.Range(LEFTTOP_ADDRESS).Row + 1
  '変数rngをリサイズする。'
  Set rng = rng.Resize(verticalSize, LIST_AREA_WIDTH)
  Set ListAreaRange = rng
End Property

リストの列数と、左上端セルのアドレスは、定数で指定している。変更があった場合は書き換えればよい。

Property Getプロシージャ内での処理はコメントのとおり。なるべく変更に強い書き方を試みた。

リスト最終行の取得は、おなじみのEndプロパティを用いるやり方にしたが、気に入らなければ他のやり方にしたら良いと思う。

今回のようなリストなら、

Set ListAreaRange = Me.Range(LEFTTOP_ADDRESS).CurrentRegion

でも良いのだが、たとえば

f:id:akashi_keirin:20190628171103j:plain

このようなリストで、「会員番号」の欄にあらかじめ値が入っていて、２列目以降にデータを抽出してきて表を完成させるような場合、CurrentRegionプロパティを使うやり方では不要な部分まで全部取得してしまうので、今回のようなやり方にした。

実験

イミディエイト・ウインドウに

?Sheet1.ListAreaRange.Address

と打ち込んで[Enter]！

f:id:akashi_keirin:20190628171105j:plain

バッチリ！

おわりに

シートのよく使う部分については、こんなふうにPropertyを生やしておくと便利だし、命名次第では非常にreadableになるのでオススメです。

処理の手順をコーディングするのではなく、シートオブジェクトの機能としてオブジェクトに封印してしまい、それを利用するだけ、というのはオブジェクト指向的なアプローチでもあると思う。