関数を作る_5（VBA研究会議事録_16）

VBA研究会で論じられた内容を記録する議事録の16本目（2023/05/27開催分）です。前回の議事録はコチラになります。

hirocom777.hatenadiary.org

前回まででUNIQUE関数をVBAのFunctionプロシージャを使って再現してみました。

今回からXLOOKUP関数として実装してみようと思います。

XLOOKUP関数とは

XLOOKUP関数とは、Excel2021、Office365で新しく実装されたワークシート関数です。VLOOKUP関数（HLOOKUP関数）のパワーアップ版の様なものですね。いったい何が違うのでしょうか？ざっくりと違いを見ていきましょう。

VLOOKUP

VLOOKUP関数の書式は以下のようになります。[]内は省略可能です。

  =VLOOKUP（検索値, 範囲, 列番号, [検索の型]）

検索値を範囲の左端の列から検索して、範囲内の列番号で指定した列から対応する値を返します。

XLOOKUP XLOOKUP関数の書式は以下のようになります。

  =XLOOKUP（検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード]）

検索値を検索範囲から検索して、戻り範囲内対応する値を返します。

XLOOKUPは返す値を範囲で指定できるんですね。そのためVLOOKUPの弱点である「検索範囲より左の列の値は取得できない」を克服しています。また、範囲指定を行方向にすればHLOOKUPの機能もカバーできます。

今回のお題

それでは今回のお題です。以下の引数構成で、検索値を返す（関数名はお任せ）関数を作りましょう。

関数名（検索値, 検索範囲, 結果の範囲, 見つからない場合の値）

検索値:単一の値、または単一のセルを示す参照式検索範囲: 検索値を捜査するセル範囲（n行1列で指定）結果の範囲: 検索値に対応して返したい値を含むセル範囲（n行1列で指定）, 検索範囲の右側、左側どちらでも可能見つからない場合の値:　値、文字列、Bool値などの任意の値

実際のコード

それでは実際のコードです。標準モジュールに記述します。

Function Wlookup(ByVal 検索値 As Variant, _
                 ByVal 検索範囲 As Range, _
                 ByVal 結果の範囲 As Range, _
                 Optional ByVal 見つからなかった場合の値 As Variant = "該当するものがありませぬ")
            
    '検索範囲を捜査して、検索値が見つかったらそのセルが属する行全体を取得して、結果の範囲との交差セルの値を返す
    Dim foundCell As Range
    Set foundCell = 検索範囲.Find(what:=検索値, lookat:=xlWhole)

    Dim ret As Variant
    If foundCell Is Nothing Then
        ret = 見つからなかった場合の値
    Else
        ret = Application.Intersect(foundCell.EntireRow, 結果の範囲).Value
    End If
    
    Wlookup = ret
    
End Function

RangeオブジェクトのFindメソッドで、検索範囲を検索。検索結果と結果の範囲からIntersectメソッドを使って交わる場所を探します。今回は多数のコード提案がありました。もう1つコードをご紹介します。

Function IndexMatch(ByVal 検索値 As Variant, _
                    ByVal 検索範囲 As Range, _
                    ByVal 結果の範囲 As Range, _
                    Optional ByVal 見つからない場合の値 As Variant) As Variant

    On Error GoTo エラー
    
    Dim mat As Long: mat = WorksheetFunction.Match(検索値, 検索範囲, 0)
    Dim ind As Variant: ind = WorksheetFunction.Index(結果の範囲, mat)
    IndexMatch = ind
    
    Exit Function

エラー:
    IndexMatch = 見つからない場合の値
End Function

先述したとおり、VLOOKUP関数は検索範囲より左の列の値は取得できません。この問題を解決する手段として、ワークシート関数の「Match」と「Index」を組み合わせる方法があります。この方法を関数に組み込もうというわけです。