はじめに

ListRow オブジェクト (Excel) はExcelのテーブルの行を表すオブジェクトです。

自分のテーブル（ListObject）の使い方

ワークシート関数のVLOOKUPのようなことをマクロから行うことが多いです。

単純な検索であればVLOOKUPやMATCH+INDEX関数で大丈夫ですが、複数の列から同時に情報を取得したい、検索条件を柔軟にしたいと言った理由からマクロを使っています。

以上のような操作を行う場合

まずは以下のように対象の列の値の範囲を取得し、検索値がどの行にあるのかを探します。

ListObject.ListColumns.Item(検索する値の列見出し).DataBodyRange
'検索処理...

行番号が見つかったら、以下のように値を取得していきます。

ListObject.ListColumns.Item(取得したい見出し1).DataBodyRange.Item(行番号).Value()
ListObject.ListColumns.Item(取得したい見出し2).DataBodyRange.Item(行番号).Value()
'…

…はい、やけに長ったらしくなりますね。

Withを使えば多少は短くなりますが、ListColumns.Item().DataBodyRange.Item()と結構深く掘る必要があるのが面倒です。

またListColumnと対をなすListRowの方では列見出しから値を取得するすることができません。

ならばListRowから列見出しで値を取得できるようにすればいいじゃないか？という事でそのためのクラスを作成しました。

クラス本体

以下のコードをListRow2という名前のクラスモジュールに貼り付けてください。

Option Explicit

Implements Excel.ListRow
Public Base As Excel.ListRow

'列の番号or見出しから該当するセルを取得する
Function Item(Index As Variant) As Excel.Range
'Attribute Item.VB_UserMemId = 0
    Set Item = getItem(Me, Index)
End Function

Private Function getItem(this As ListRow2, Index As Variant) As Excel.Range
    Set getItem = this.Parent.ListColumns.Item(Index).DataBodyRange.Item(this.Index)
End Function


'以下は既存のメンバーのラップ用
Private Sub ListRow_Delete():   Call Base.Delete: End Sub
        Sub Delete():           Call Base.Delete: End Sub

Private Property Get ListRow_Application() As Application:  Set ListRow_Application = Base.Application: End Property
        Property Get Application() As Excel.Application:    Set Application = Base.Application:         End Property

Private Property Get ListRow_Creator() As XlCreator:        Let ListRow_Creator = Base.Creator:         End Property
        Property Get Creator() As Excel.XlCreator:          Let Creator = Base.Creator:                 End Property

Private Property Get ListRow_Index() As Long:               Let ListRow_Index = Base.Index:             End Property
        Property Get Index() As Long:                       Let Index = Base.Index:                     End Property

Private Property Get ListRow_InvalidData() As Boolean:      Let ListRow_InvalidData = Base.InvalidData: End Property
        Property Get InvalidData() As Boolean:              Let InvalidData = Base.InvalidData:         End Property

Private Property Get ListRow_Parent() As Object:            Set ListRow_Parent = Base.Parent:           End Property
        Property Get Parent() As Excel.ListObject:          Set Parent = Base.Parent:                   End Property

Private Property Get ListRow_Range() As Range:              Set ListRow_Range = Base.Range:             End Property
        Property Get Range() As Excel.Range:                Set Range = Base.Range:                     End Property

使用例

新規ブックに以下のコードと上記のクラスを貼り付けて実行してみてください（既存のブックだと一枚目のワークシートが強制的に書き換えられます）。

なお、コード中で作成しているテーブルの中身は以下の記事を参考に作成しています。

VBA クラスモジュールでExcelのテーブル(ListObject)を操作する - t-hom’s diary

Sub SampleListRow2()
    
    Dim ws As Excel.Worksheet
    Set ws = Excel.ThisWorkbook.Worksheets.Item(1)
    
'テーブルの準備
    'テーブルの中身。配列定数をEvaluate
    tableData = [{"果物","いちご","150";"果物","ばなな","100";"果物","りんご","200";"野菜","キャベツ","150";"野菜","なすび","100";"野菜","レタス","120";"肉","牛肉","300";"肉","豚肉","200";"肉","鶏肉","100"}]
    
    ws.UsedRange.Clear
    With ws.Range("A1").Resize(UBound(tableData, 1), UBound(tableData, 2))
        .Value() = tableData
        
        Dim table As Excel.ListObject
        Set table = ws.ListObjects.Add(xlSrcRange, .Cells)
        
    End With 'ws.Range("A1").Resize(UBound(tableData, 1), UBound(tableData, 2))
    
    With table
        .ShowHeaders = True
        .HeaderRowRange.Value() = VBA.Split("種目,品名,価格", ",")
    End With 'table
    
    
'検索して値の取得
    'りんごについて、種目と価格を知りたい場合
    Dim nameCol As Excel.ListColumn
    Set nameCol = table.ListColumns.Item("品名")
    
    'Matchで手抜き検索
    Dim appleIndex As Long
    appleIndex = Excel.WorksheetFunction.Match("りんご", nameCol.DataBodyRange, 0)
    
    
    '通常の場合
    With table.ListColumns
        Debug.Print "種目", .Item("種目").DataBodyRange.Item(appleIndex).Text '←最後でインテリセンスが切れる
        Debug.Print "価格", .Item("価格").DataBodyRange.Item(appleIndex).Value()
    End With
    
    '自作クラスを使った場合
    Dim appleRow As ListRow2
    Set appleRow = New ListRow2
    Set appleRow.Base = table.ListRows.Item(appleIndex)
    
    Debug.Print "種目", appleRow.Item("種目").Text '←最後までインテリセンスが効く
    Debug.Print "価格", appleRow.Item("価格").Value()
    
    Stop
    
End Sub

その他

Excel.ListRowをImplementsできたのでクラス化しましたが、getItemだけを関数にしてもいい気がします。

こういった「既存のクラスを拡張したい」時に .NET Frameworkの拡張メソッドがうらやましくなります。

imihitoのブログ

VBA ListRowを使いやすくするクラス

はじめに

自分のテーブル（ListObject）の使い方

クラス本体

使用例

その他