【Excel】可変で名前の定義を設定してVBAで利用する

こんにちは、みすくです。

VBAなら起点から末尾を求めてセルの範囲を取得するというのもできるのですが、
わたしはあらかじめ名前を定義しておいて、
スクリプトで利用するというのをよくやります。


=OFFSET(conf!$A$2,0,0,COUNTA(conf!$A:$A)-1,2)
OFFSETはExcel関数のOFFSET。
OFFSET(基準, 行数, 列数, [高さ], [幅])
[高さ]のパラメーターに、A列に入力されている件数 – タイトルぶんを
指定することで、可変で設定できるというわけ。
横方向も可変にしたいなら、[幅]にもカウントした件数を入れれば良いです。
=OFFSET(conf!$A$2,0,0,COUNTA(conf!$A:$A)-1,COUNTA(conf!$1:$1))

VBAで使うときは、Range(定義した名前)で使えます。

Option Explicit

Dim confSh As Worksheet

Private Sub init()
    Set confSh = ThisWorkbook.Worksheets("conf")
End Sub

Private Sub term()
    Set confSh = Nothing
End Sub

Function vLookUp(SearchTarget As String)
    On Error GoTo ERR_LBL
    vLookUp = Trim(WorksheetFunction.vLookUp(SearchTarget, confSh.Range("TITLE"), 2, False))
    Exit Function
    
ERR_LBL:
    vLookUp = "？"
End Function

Sub main()
    Call init
    
    Debug.Print vLookUp("A")    '1
    Debug.Print vLookUp("E")    '5
    Debug.Print vLookUp("Z")    '?

    Call term
End Sub

では、ごきげんよう