質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

4273閲覧

TextBox Changeイベントを利用した検索フォームの速度について

zorac

総合スコア42

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

1グッド

3クリップ

投稿2018/01/04 09:59

編集2018/01/05 16:19

###前提・実現したいこと
TextBoxとListViewを組み合わせて、1文字入力される度に検索結果を更新する検索フォームを作りたいと思っています。
Googleインスタント検索と同じような挙動を期待して、ひとまず動くものを作りました。
ソースを動かすためには下記要件を満たして頂く必要があります。

  1. Sheet1のA列からG列の2行目以降に検索対象となるデータを入力
  2. TextBoxをオブジェクト名TextBox1として準備
  3. ListViewをオブジェクト名ListView1として準備

###発生している問題・エラーメッセージ
TextBoxに文字を入力していくと期待通りの動作をし、速度も実用上問題のないレベルです。
しかし、BackSpaceを押してTextBoxに入力済みの検索ワードを削除していくと、途端に検索に掛かる時間が長くなります。
なぜ検索ワードを消した際の検索が遅くなってしまうのか、どうすれば速く出来るのかアドバイスをお願いします。
同じような事をもっとスマートに実現する方法があればそれについても教えて頂きたく思います。
宜しくお願いします。

###速度計測結果
実際に15,000件弱のデータを対象に検索を実行し、実行時間を計測しました。(単位:秒)
TextBoxに文字を入力していく場合(検索ワードを4文字入力した場合の計測結果)
データの検索: 0.1
ListViewへの反映: 0.32

データの検索: 0.09
ListViewへの反映: 0.16

データの検索: 0.08
ListViewへの反映: 0.04

データの検索: 0.08
ListViewへの反映: 0

TextBoxの文字を1文字ずつ削除していく場合(入力済みの4文字を削除していく場合の計測結果)
データの検索: 1.52
ListViewへの反映: 0

データの検索: 1.52
ListViewへの反映: 0.05

データの検索: 1.55
ListViewへの反映: 0.16

データの検索: 1.61
ListViewへの反映: 0.33

###該当のソースコード

VBA

1'郵便番号データダウンロードサービスで入手可能な郵便番号データを利用する 2Private Sub UserForm_Initialize() 3 With ListView1 4 .View = lvwReport 5 .HideSelection = False 6 .FullRowSelect = True 7 .ColumnHeaders.Add , "_X0401-X0402", "全国地方公共団体コード", 120 8 .ColumnHeaders.Add , "_OldPostalCode", "旧郵便番号", 120 9 .ColumnHeaders.Add , "_PostalCode", "郵便番号", 80 10 .ColumnHeaders.Add , "_Prefectures", "都道府県名", 80 11 End With 12End Sub 13 14 15Private Sub TextBox1_Change() 16 '-------------------- ListViewのクリア ココカラ -------------------- 17 ListView1.ListItems.Clear 18 '-------------------- ListViewのクリア ココマデ -------------------- 19 20 21 '-------------------- データの検索 ココカラ -------------------- 22 Dim t As Single 23 Dim ws As Worksheet 24 t = Timer() 25 Set ws = Worksheets("Sheet1") 26 27 With ws 28 Dim lastRow As Long 29 Dim dataSet As Variant 30 lastRow = .Cells(.Rows.count, 1).End(xlUp).Row 31 dataSet = .Range(.Cells(1, 1), .Cells(lastRow, 4)) 32 33 Dim i As Long 34 For i = 2 To lastRow 35 If dataSet(i, 3) Like "*" & TextBox1 & "*" Then 36 With ListView1.ListItems.Add 37 .Text = dataSet(i, 1) '全国地方公共団体コード 38 .SubItems(1) = dataSet(i, 2) '旧郵便番号 39 .SubItems(2) = dataSet(i, 3) '郵便番号 40 .SubItems(3) = dataSet(i, 4) '都道府県名 41 End With 42 End If 43 Next i 44 End With 45 Debug.Print "データの検索: " & Round(Timer() - t, 2) 46 '-------------------- データの検索 ココマデ -------------------- 47End Sub

###補足情報(言語/FW/ツール等のバージョンなど)
Excel 2016 (office 365)

退会済みユーザー👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

imihito

2018/01/04 10:26

データ検索時の`If dataSet(i, 9) Like "*" & TextBox1 & "*" Then`ですが間違っていないでしょうか?
zorac

2018/01/04 10:40

ご指摘ありがとうございます。質問するに当たって簡略化した際、変更が漏れました。質問文も修正しておきました。
guest

回答3

0

勘違いしていたらすみませんが削除をした方が遅いというのは当たり前ではないですか?
提示されている部分とテスト方法をみると「4文字入力して、4文字消す」という方法に見えます。
最初はchengeイベントが発生しないため、0文字目の入力が存在していません。
"1234"を入力すると仮定すると

入力時は
"1"の部分一致
"12"の部分一致
"123"の部分一致
"1234"の部分一致

削除時は
"123"の部分一致
"12"の部分一致
"1"の部分一致
""の部分一致

になります。当然削除時のほうが一致する数量が多く最後には全件一致が発生しているので
時間がかかるのでは?

vba

1 Debug.Print "データの検索: " & Round(Timer() - t, 2),TextBox1

として本当に同じ検索(上の例では"1234"からの"4"削除時と"12"からの"3"入力時)で
タイムが違うのか提示された方がいいです。自分の理解力不足でしたらすみません。

投稿2018/01/06 04:07

sousuke

総合スコア3828

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

zorac

2018/01/06 06:13 編集

回答ありがとうございます。 ご指摘の通り、私の質問の仕方では問題点が分かり難いですね。 検索ワード毎の計測結果が分かりやすいようまとめました。 []内は検索ワードです。 入力していく場合の計測結果 検索ワード[9]: 0.42s 検索ワード[90]: 0.25s 検索ワード[903]: 0.12s 削除していく場合の計測結果 検索ワード[903]: 1.52s 検索ワード[90]: 1.57s 検索ワード[9]: 1.71s このように何故か削除していく場合だけ、同じ検索のはずなのに動作が異常に遅く、 原因が分からずに悩んでいます。 今回はChangeイベントを使用せず、検索ボタン設置の方向で解決したいと思います。 ありがとうございました。
guest

0

ベストアンサー

Changeイベントは1文字変更されても発生するので、AfterUpdateか明示的に検索用のコマンドボタンで行う方が良いかと思います。

投稿2018/01/06 03:54

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

zorac

2018/01/06 06:03

回答ありがとうございます。 AfterUpdateを使う場合は入力後にフォーカスの移動が必要になるので、検索ボタンを用意する方が実用的かもしれませんね。 リアルタイムに検索出来るようなものがあったら直感的で良いなと思って取り組んでみましたが、 解決が難しそうなので、検索ボタンを付ける方向で検討を進めたいと思います。
guest

0

今回の遅くなっている原因は不明ですが、
早くなるかもしれない点と、コードの書き方について幾つか。

・処理の前に、画面更新の停止、数式の自動計算を手動計算にし、
処理の後に元に戻す(高速化の定石)

・Like を InStr にする(単なる文字の検索だけなら、Likeより早くなるはず)

・searchResultを使わず、見つかったらすぐにListViewへデータ追加する。
※データが数万件なのでメモリ不足等はないかもだけど、
配列に格納しておく理由が見つからない。
もしデバッグ用なら、1行分の配列があれば十分かな。
(どちらにしても、データ検索開始前にListViewをクリアしておくべきでしょう)

・「Worksheets("Sheet1")」はWorkSheet型変数に格納して使う
※インテリセンスが使えて便利

・.Cells(1048576, 1) の「1048576」は「.Rows.Count」が妥当
※Excelのバージョンアップで行数が増えた場合に正しくなくなる

・dataSet(i, 1)の「1」等はEnumで定義すると、
dataSet(i, E_Col.ID)等と書けるので可読性があがる

以上、参考まで。

投稿2018/01/05 01:06

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

zorac

2018/01/05 14:24

回答ありがとうございます。いまから1つ1つ確認させて頂きます。
zorac

2018/01/05 15:25

・処理の前に、画面更新の停止、数式の自動計算を手動計算にし、処理の後に元に戻す(高速化の定石) ⇒この設定を入れると誤差の範囲かもしれませんが、0.01sほど遅くなるようです。速くはなりませんでした。 シート操作を行う際はこの設定が有効であることを理解していますが、 今回のようにフォームコントロールのみの操作でもこの設定は効果があるのでしょうか。 ・LikeをInstrにする ⇒実際に変更して計測してみたところ、Likeの方が早いようです。 【計測結果】 4文字入力して比較 Like 0.54s , 0.26s , 0.05s , 0.04s Instr 0.60s , 0.32s , 0.12s , 0.09s ・searchResultを使わず、見つかったらすぐにListViewへデータ追加する。 ※データが数万件なのでメモリ不足等はないかもだけど、配列に格納しておく理由が見つからない。 ⇒遅いのは検索であってListViewの操作ではなさそうだということを明示する為、質問に当たって処理を分けました。 実際のコードではsearchResultを経由せずに実装することを考えています。 今回良い機会だったので速度を調べてみましたが、searchResultを使った方が若干速くなるようです。 【計測結果】 4文字入力して比較 searchResultを使う 0.50s , 0.25s , 0.05s , 0.04s searchResultを使わない 0.54s , 0.26s , 0.05s , 0.04s ・「Worksheets("Sheet1")」はWorkSheet型変数に格納して使う ※インテリセンスが使えて便利 ⇒サンプルコードなどでこの用法をよく見かけますが、メリットが分からず使っていませんでした。 確かにインテリセンスが使えるのは便利ですね。今後活用させて頂きます。 ・.Cells(1048576, 1) の「1048576」は「.Rows.Count」が妥当 ※Excelのバージョンアップで行数が増えた場合に正しくなくなる ⇒いつも1048576を定数化して使っていましたが、確かに先々の事を考えるとrows.countでやっておいた方が良さそうですね。 今後活用させて頂きます。 ・dataSet(i, 1)の「1」等はEnumで定義すると、 dataSet(i, E_Col.ID)等と書けるので可読性があがる ⇒今回データまで提示できなかった為、こういった配慮は出来ませんでしたが、 次回以降、可読性についてもう少し考えさせて頂きます。 色々とアドバイスを頂きましてありがとうございました。 質問文のソースも後ほど差し替えておきます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問