VBA研究会で論じられた内容を記録する議事録の17本目（2023/06/03開催分）です。前回の議事録はコチラになります。
前回はXLOOKUP関数をVBAのFunctionプロシージャを使って再現してみました。
今回も引き続きXLOOKUP関数に取り組もうと思います。
水平方向に対応する
前回のコードは垂直方向のみ対応していました。でも実際のXLOOKUP関数は検索箇所、結果取得箇所とも範囲で指定します、なので水平方向（HLOOKUPの代用）でも使用できます。
それでは実際のコードです。
Function Exlookup(ByVal 検索値 As Variant, _
ByVal 検索範囲 As Range, _
ByVal 結果の範囲 As Range, _
Optional ByVal 見つからなかった場合の値 As Variant = "該当するものがありませぬ") as Variant
'検索範囲を捜査して、検索値が見つかったらそのセルが属する行全体を取得して、結果の範囲との交差セルの値を返す
Dim foundCell As Range
Set foundCell = 検索範囲.Find(what:=検索値, lookat:=xlWhole)
'検索方向判定
Dim areaDirection As Range
Select Case Direction(検索範囲)
Case xlByRows
Set areaDirection = foundCell.EntireRow
Case xlByColumns
Set areaDirection = foundCell.EntireColumn
End Select
'あるときないとき処理
Dim ret As Variant
If foundCell Is Nothing Then
ret = 見つからなかった場合の値
Else
ret = Application.Intersect(areaDirection, 結果の範囲).Value
End If
Exlookup = ret
End Function
'検索方向指定
Function Direction(ByVal targetArea As Range) As Long
Dim ret As Long
With targetArea
Select Case True
Case .Rows.Count > 1 And .Columns.Count = 1
ret = xlByRows
Case .Columns.Count > 1 And .Rows.Count = 1
ret = xlByRows
End Select
End With
Direction = ret
End Function
検索範囲の指定状況から、FunctionプロシージャDirectionで検索方向を決定したのちに結果の範囲から値を割り出しています。
エラー処理
ここまでXLOOKUP関数をFunctionプロシージャで再現してきました。しかし、指定された引数が適切でなかった場合の処理がありません。実際のXLOOKUPでは柔軟に対応できるのですが、ここで作成したFunctionプロシージャはそこまで対応は難しいでしょう。
予想外の設定に対するエラー処理を考えてみましょう。
検索値の複数選択
複数の検索値に対して処理をしようとすると、複雑になってしまいます。複数のセルを選択しときにはエラーを返すようにします。検索範囲と結果の範囲
検索範囲と結果の範囲の形状を同じにすると、処理が簡単になります。検索範囲と結果の範囲の形状が異なる場合はエラーを返すようにします。
以上の機能を持ったFunctionプロシージャを作ってみましょう。エラーの場合はFalse、問題がなければTrueを返します。
Function ChckError(ByVal 検索値 As Variant, _
ByVal 検索範囲 As Range, _
ByVal 結果の範囲 As Range) As Boolean
ChckError = False
'引数確認
Select Case True
'検索値が複数されている場合はError
Case IsArray(検索値)
Exit Function
'検索範囲が1行N列または、N行１列でない場合はError
Case 検索範囲.Rows.Count * 検索範囲.Columns.Count <> 検索範囲.Count
Exit Function
'検索範囲と結果の範囲の行列が異なる場合はError
Case 検索範囲.Rows.Count <> 結果の範囲.Rows.Count
Exit Function
Case 検索範囲.Columns.Count <> 結果の範囲.Columns.Count
Exit Function
End Select
'エラーなし
ChckError = True
End Function
検索値が複数指定されているの場合はIsArray関数でTrueが返ってきます。 1行N列または、N行1列の確認は、行数と列数をかけた結果が総セル数と同じかどうかを見ればいいでしょう。 最後に検索範囲と結果の範囲の行列数が合致していることを確認すればよいです。
作成した関数の開始直後に以下のように挿入すればいいでしょう。
If Not ChckError(検索値, 検索範囲, 結果の範囲) Then
'エラー時処理
Exit Function
End If
次回はTextBefore/TextAfter関数
いかがでしょうか。エラー処理の方法も考えてみると奥が深いですね。次回はTextBefore/TextAfter関数です。これも最近のExcelで追加された関数です。VBAでどこまで再現できるでしょうか。お楽しみに！！
