この春にやりたいこと 住所録をつくる⑭ ～検索機能～
ExcelVBAを使用して、住所録を作っていきたい。
ユーザーフォーム上に入力フォームを作り、エクセルシート上に入力データを保存する、という流れを実現していく。
今回は、検索機能を追加させたい。
名前の検索
検索方法 前準備
検索ワードと名前、読み仮名の部分一致で該当するものだけをリストボックスに反映させる、という動作を行う。
そのためにフォームにTxt_検索(TextBox)とBtn_検索(CommandButton)を追加する。
検索動作を確認するために適当なダミーデータを作成しておく。
検索のコード
Dim Data1, Data2(), Data3()
Dim R As Long, C As Long, N As Long
Dim Str As String, Name1 As String, Name2 As String
Data1 = Sheets("住所録").Range("A1").CurrentRegion
Str = Txt_検索.Text
Str = StrConv(Str, vbKatakana + vbUpperCase + vbWide)
'Data2の大きさを決める。いまはData1の行と列と同じ大きさにする
ReDim Data2(UBound(Data1, 2), UBound(Data1))
'ヘッダーの文字列をData2の1列目に入れる
For C = LBound(Data1, 2) To UBound(Data1, 2)
Data2(C, 1) = Data1(1, C)
Next C
N = 1
'該当するData1の内容をData2に入れる
For R = 2 To UBound(Data1)
Name1 = StrConv(Data1(R, 2), vbKatakana + vbUpperCase + vbWide)
Name2 = StrConv(Data1(R, 3), vbKatakana + vbUpperCase + vbWide)
If InStr(Name1, Str) > 0 Or InStr(Name2, Str) > 0 Then
N = N + 1 'Data2の列を１ずつ増やす
For C = LBound(Data1, 2) To UBound(Data1, 2)
Data2(C, N) = Data1(R, C)
Next C
End If
Next R
'Data2の大きさをNに戻す
ReDim Preserve Data2(UBound(Data1, 2), N)
'Data2をData3に入れ替える
ReDim Data3(UBound(Data2, 2), UBound(Data2))
For R = LBound(Data2, 2) To UBound(Data2, 2)
For C = LBound(Data2) To UBound(Data2)
Data3(R, C) = Data2(C, R)
Next C
Next R
'Data3のデータをLst_一覧に入れる
With Lst_一覧
.List = Data3
.ColumnCount = 2
.ColumnWidths = "50;80"
End With
コードの動きの流れ
住所録シートのデータをData1に入れる。
Data2の大きさを決める。１次元目はData1の２次元、２次元目はData1の１次元の大きさにする。（列と行、逆転して設定）
Data2にData1のヘッダー部分を入れる。Data2には１列目にData1のヘッダー部分が入る。
検索結果に該当するData1のデータをData2に入れていく。
Data２の大きさ（列）を再調整する
Data2のデータをData3に入れ直す（列行再逆転)
Data3のデータをLst_一覧に入れる
このような流れで削除が実行される。
なぜ、列行逆転するのか？
RedimとRedim Preserve
なぜData１からData2へデータを入れるときに列行を逆転させる必要があるのか？逆転させずに入れることはできないのか？
最初にData2の宣言でData2()と宣言している。この時点ではData2の大きさが決まっていない状態。どれくらいのデータが入るかこの時点ではまだ不確定なので、大きさを決定できない。
そこでコードの途中で、大きさを決定、変更できるのがRedimになる。ReDim Data2(UBound(Data1, 2), UBound(Data1))でData2の大きさが決定した。初期の状態ではData1の列行逆転の大きさで設定している。
しかし、名前の検索でどれくらいの大きさが必要になるかわからない。そこで、Redim Preserveが必要になる。Redimだと、大きさを変えたときにこれまで入れていたデータが消去されてしまう。それではいけないので、今まで入れたデータをキープしつつ、大きさを変えたいときはRedim Preserveを宣言しておかないといけない。
Redim Preserve
ただRedim Preserveも決まりがあって、多次元配列では「最後の次元しか Preserve で変更できない」という決まりがある。つまり最後の次元以外は変更できない。この場合Data2は二次元配列で使うので、”最後の次元”は２次元目（列数）にあたる。１次元目の（行数）は変更できない。
しかし、大きさが変わる可能性があるのは行数になる。逆に列数が変わることはない。なので、無理やり行数を変えたいので、あえて列数と行数を逆転させてData1からData2にデータを入れている。そうすることでData2の大きさを変更することができる。
変数Nの効果
変数でNを使っているが、これは、検索条件と合致したときにNを１ずつ増やしている。最初のヘッダー部分でN＝１として、そこから検索条件が一致した場合はN=2,N=3…とNが１ずつ増えていく。
これはあとでData2の大きさを検索結果と同じ大きさにするために検索と一致した数をカウントするために使っている。このNを利用してReDim Preserve Data2(UBound(Data1, 2), N)でData2の大きさを最終的に決めている。
しかしなぜ、最終次元しか取得できないのだろう？プログラミングの知識はなく、ＶＢＡを最近学んだ私からすると不便でしかない。が、何か理由があるのだろう。いつかなぜそうなのか勉強したい。
Transpose関数
ちなみにワークシート関数のTranspose関数という縦横を反転させる関数がある。これを使っているものもある。一度これを使ってみたが、検索結果が１件でもあればうまく動いてくれるが、検索結果がないときにはどうもうまく動かない。１行、１列の場合だと二次元配列としてではなく１次元配列として動くらしい。確かにうまく動かないので、これは採用しないことにした。
次回は郵便番号検索を追加させたい。
