【エクセルVBA】エクセル表のヘッダーの列番号を動的に取得する（項目名、列番号をCollectionに詰めて返却するFunctionプロシージャ）

'-----------------------------------------------------------------------
'表のヘッダー部の項目名、列番号を詰めたCollectionを取得する
'引数「tableHeaderRng」には、表のヘッダー部のセル範囲のみ（1行のみ）を設定する
'-----------------------------------------------------------------------
Public Function GetTableHeaders(ByVal tableHeaderRng As Range) As Collection

    '項目名、列番号を詰めるためのCollectionを準備
    Dim headers As Collection
    Set headers = New Collection
    
    '引数に指定されたヘッダー部のセル範囲分、キーを「項目名」、値を「列番号」としてCollectionに詰める
    Dim headerCell As Variant
    For Each headerCell In tableHeaderRng
        headers.Add Key:=headerCell.Value, Item:=headerCell.Column
    Next headerCell

    'Collectionを返却する
    Set GetTableHeaders = headers
End Function

Sub Test()
    'メンバー表を開く
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\00_myenv\10_macro\01_test\member.xlsx")
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    'メンバー表のヘッダー部のセル範囲のみ（1行のみ）を取得
    With ws.Range("A1").CurrentRegion
        Dim headerRng As Range
        Set headerRng = .Resize(1, .Columns.Count)
    End With
    
    'Functionプロシージャを呼び出し、ヘッダー部の項目名と列番号が詰まったCollectionを取得
    Dim headers As Collection
    Set headers = GetTableHeaders(headerRng)
    
    'インプットボックスを表示し、入力された氏名を取得
    '（未入力やキャンセルに対する処理は、ここでは割愛する）
    Dim inputName As String
    inputName = InputBox("取得対象の氏名を入力してください")
    
    '氏名が合致する行番号を取得
    '（合致する値がない場合、Match関数はエラーとなるが、ここではエラー処理は割愛する）
    Dim targetRow As Long
    targetRow = WorksheetFunction.Match(inputName, ws.Columns(1), 0)
    
    'メンバー表から値を取得し、イミディエイトウィンドウに表示する
    '列番号は固定値とせず、項目名をキーにしてCollectionから取り出した値を使用する
    Debug.Print ws.Cells(targetRow, headers.Item("氏名"))
    Debug.Print ws.Cells(targetRow, headers.Item("生年月日"))
    Debug.Print ws.Cells(targetRow, headers.Item("電話番号"))
    Debug.Print ws.Cells(targetRow, headers.Item("住所"))
    Debug.Print ws.Cells(targetRow, headers.Item("マイナンバー"))
    
    wb.Close
    
End Sub

    Debug.Print ws.Cells(targetRow, 1)     '氏名
    Debug.Print ws.Cells(targetRow, 6)     '生年月日
    Debug.Print ws.Cells(targetRow, 10)    '電話番号
    Debug.Print ws.Cells(targetRow, 13)    '住所
    Debug.Print ws.Cells(targetRow, 17)    'マイナンバー

    Debug.Print ws.Cells(targetRow, 1)     '氏名
    Debug.Print ws.Cells(targetRow, 7)     '生年月日　　　　「6 → 7」に変更
    Debug.Print ws.Cells(targetRow, 10)    '電話番号
    Debug.Print ws.Cells(targetRow, 17)    '住所　　　　　　「13 → 17」に変更
    Debug.Print ws.Cells(targetRow, 16)    'マイナンバー　　「17 → 16」に変更

    Debug.Print ws.Cells(targetRow, headers.Item("氏名"))
    Debug.Print ws.Cells(targetRow, headers.Item("生年月日"))
    Debug.Print ws.Cells(targetRow, headers.Item("電話番号"))
    Debug.Print ws.Cells(targetRow, headers.Item("住所"))
    Debug.Print ws.Cells(targetRow, headers.Item("マイナンバー"))