VBA 配列内検索する方法

配列内検索を行うには？

結論から言うと、Match関数とIsError関数を使って、検索します。

Match関数：
　指定した文字列が配列内にで見つかった要素数の位置を返す。見つからなかった場合はエラー 2042を返します。
　※小文字と大文字は区別しない

IsError関数：
　エラー値を検出する関数です。

配列内に見つからなかった場合はエラーが出るので、IsError関数で検知し、エラー出たら不一致、そうでなければ一致という処理を作れば、OKです。

サンプルコード１

下記は大文字と小文字を区別する必要ない場合です。

Sub ArrayMatchTest1()
    Dim searchString As String
    Dim arr() As Variant
    Dim result As Variant
    
    searchString = "Apple"
    
    arr = Array("Apple", "Grape", "Orange", "Watermelon")
    
    ' 配列をVariant型の変数に代入してからMatch関数を実行
    result = Application.Match(searchString, arr, 0)
    
    'エラーが発生していないか判定
    If Not IsError(result) Then
        ' エラーが発生していなければ一致
        MsgBox "一致しました。" & vbLf _
                & "マッチした配列の位置数: " & arr(result - 1)
    Else
        ' エラーが発生した場合の処理
        MsgBox "一致しません｡"
    End If
End Sub

上記のコードをコピペして実行すると、searchStringに格納した文字列が配列内(arr)にあるかどうかをメッセージで知らせてくれます。
Match関数はあくまで見つかった配列の要素数を返すのではなく、要素が始まりを１として、見つかった位置を返すので、「result - 1」をして、要素数を求めています。第三引数の0は完全一致を指定しています。

サンプルコード２

大文字小文字を区別したい場合は単純に一致した後、＝で比較して、完全に同じ文字列が入っているかを比べれば、大文字小文字を区別することができます。

Sub ArrayMatchTest1()
    Dim searchString As String
    Dim arr() As Variant
    Dim result As Variant
    
    searchString = "Apple"
    
    arr = Array("Apple", "Grape", "Orange", "Watermelon")
    
    ' 配列をVariant型の変数に代入してからMatch関数を実行
    result = Application.Match(searchString, arr, 0)
    If Not IsError(result) Then
        If arr(result - 1) = searchString Then
            ' マッチした位置(result)を利用する
            MsgBox "一致しました。"
        Else
            MsgBox "一致しませんでした。"
        End If
    Else
        ' エラーが発生した場合の処理
        MsgBox "一致しませんでした。"
    End If
End Sub

サンプルコード３

このコードはDBから取得した値を配列に変換し、その中を検索する方法です。

' DB接続を確立するクラス
Function createConnect() As Object
    Dim conn As Object
    Dim connectionString As String
    ' SQLiteデータベースへの接続文字列を指定します
    connectionString = "DRIVER=SQLite3 ODBC Driver;Database=Your_Database_Path"
    
    ' ADOのConnectionオブジェクトを作成して接続します
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connectionString
    
    Set createConnect = conn
End Function


' DBから値を取得、配列に変換し、検索
Sub CheckValueInFirstColumnWithMatch()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim dbPath As String
    Dim data As Variant
    Dim valueToCheck As String
    Dim result As Variant

    ' 確認する値を設定
    valueToCheck = "Test"

    ' SQLクエリ
    sql = "SELECT name FROM username;"

    ' ADOの初期化
    Set conn = createConnect
    Set rs = CreateObject("ADODB.Recordset")

    ' SQLクエリを実行
    rs.Open sql, conn

    ' レコードセットを配列に変換
    If Not rs.EOF Then
        data = rs.GetRows()
    End If

    ' Application.Match関数を使用して確認
    On Error Resume Next
    data = Application.Transpose(data)
    result = Application.Match(valueToCheck, data, 0)
    On Error GoTo 0

'     結果を表示
    If Not IsError(result) Then
        MsgBox "値が見つかりました: " & valueToCheck
    Else
        MsgBox "値が見つかりませんでした: " & valueToCheck
    End If

    ' リソースの解放
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

解説

２次元配列変換

data = rs.GetRows()

１次元配列変換

data = Application.Transpose(data)

上記の部分でレコードセットから２次元配列へ変換。２次元配列から１次元配列に変換し、「Application.Match」を使用して検索できるようにしています。

最後に

　自分がMatch関数は配列の見つかった要素数を返すものだと勘違いしてしまい、大文字小文字を区別するコードを苦労したのでメモとして残しました。