配列の検索にFilterが使えないのでループとInstrを使う：Access VBA

配列から検索する場合Excel VBAではFilterが使えますが、Access VBAで試してみるとエラーが出て使えません、なのでループとInstrを使い総当たりで検索するしかないようです。

ここではExcelのVBAで正常に検索可能かテストし、ほぼ同じコードでAccessで動かしてみました。

色々試しましたが、Filterは使えないようなので、ループとInstrを使った配列の検索を掲載しておきます。 どちらが速いかテストしてみないと分かりませんが、大きな配列でなければ大きな違いはないかと思います。

ReDimを使った配列の使い方は、「配列かIsArrayで調べ、LBoundとUBoundの要素数でループする」と「配列からFor Each Nextのループを使いデータを取り出す」を参照してください。

■ Excel VBAでFilterを使い配列から検索する

下のようなシートを作成しました。
［配列にセット］のコマンドボタンで、配列を作成しデータをセットします。
［配列から検索］ボタンで、Filterを使い検索します。


Filterを使い配列から検索するExcel VBA

VBAの解説

・配列に使用する変数を、フォームモジュール部分に宣言します。
・ReDimで要素が1から150の配列を作成します。　
・Forループで「AAA連続した数字BBB」のデータを配列にセットします。

・Filter(tAry, "A9")で「A9」が含まれる配列を検索します。 見つかればvに配列が作成され、セットできます。
・LBoundで要素の下限、UBoundで上限を取得しループします。
・テキストボックスに見つかったデータを表示します。

Private tAry As Variant
Private Sub CommandButton1_Click()
    Dim i As Long
    
    ReDim tAry(1 To 150)
    
    For i = 1 To 150
        tAry(i) = "AAA" & i & "BBB"
    Next
End Sub

Private Sub CommandButton2_Click()
    Dim v As Variant
    Dim i As Long
    
    If IsArray(tAry) = False Then
        MsgBox "配列が宣言されていません"
        Exit Sub
    End If
    
    テキスト2 = Null
    
    v = Filter(tAry, "A9")
    
    For i = LBound(v) To UBound(v)
        テキスト2 = テキスト2 & v(i) & vbCrLf
    Next
End Sub

実行結果です。
「A9」が含まれるデータが表示されています。
このようにExcel VBAでは、Filterが問題なく使用できました。

■ Access VBAでFilterを使い配列から検索する

フォームに上のExcelと同じコントロールを配置しました。


Excel VBAとほぼ同じコードを入力し、Accessで実行すると、下のようにFilterの部分で「コンパイルエラー：引数の数が一致していません。または不正なプロパティを指定しています。」のエラーが発生します。
原因を色々調べましたが分かりませんでした。

VBAの解説

・上のExcel VBAとほぼ同じです。

Option Compare Database

Private tAry As Variant
Private Sub コマンド0_Click()
    Dim i As Long
    
    ReDim tAry(1 To 150)
    
    For i = 1 To 150
        tAry(i) = "AAA" & i & "BBB"
    Next
End Sub

Private Sub コマンド1_Click()
    Dim v As Variant
    Dim i As Long
    
    If IsArray(tAry) = False Then
        MsgBox "配列が宣言されていません"
        Exit Sub
    End If
    
    Me!テキスト2 = Null
    
    v = Filter(tAry, "A9")
    
     For i = LBound(v) To UBound(v)
        Me!テキスト2 = Me!テキスト2 & v(i) & vbCrLf
    Next
End Sub

■ Access VBAでループとInstrを使い配列から検索する

配列から検索するVBAです。

VBAの解説

・完全一致で検索する場合は、Instrを使わずに「＝」にします。

Private Sub コマンド1_Click()
    Dim i As Long
    
    If IsArray(tAry) = False Then
        MsgBox "配列が宣言されていません"
        Exit Sub
    End If
    
    Me!テキスト2 = Null
    
    For i = LBound(tAry) To UBound(tAry)
        If InStr(1, tAry(i), "A9") > 0 Then
            Me!テキスト2 = Me!テキスト2 & tAry(i) & vbCrLf
        End If
    Next
End Sub

実行結果です。
「A9」が含まれるデータが配列から検索され表示できました。