| 1 |
親愛なる貴方のために・・・え？誰？
広大なワークシートの中から、検索ワードを指定して、そのデータがどこにあるか調べるには、[ Excelメニュー ]にある[
検索コマンド
]を使います。では、VBAマクロの実行中、ユーザーフォームが表示されている状態で、検索/抽出/置換えを行うには、どんな方法があるのでしょうか。
セルの中に、ワークシート関数として記述する定番といえば、[ VLOOKUP関数 ] [HLOOKUP関数 ] [ MATCH関数
]などがあります。検索の対象となる範囲から、特定の値を検索して、その値やデータの位置を調べます。VBAマクロで、ワークシート上のデータを検索するには、[
Findメソッド ]を使います。
- ★
検索値を基準として、相対位置にあるセルデータを検索抽出する
- テキストボックス[ Edt商品コード ]の[ Changeイベント ]で動き出すプロシージャです。3行目の[
If〜Then〜Elseステートメント ]で入力値の桁数を調べ、規定の13桁以外ではプロシージャから抜けるようにしています。[
Exitイベント ]と違い、[ Edt商品コード
]の値が変更されるたびに実行されるので、とても忙しない総当りのコードになります。
コンボボックス[ Cmb倉庫
]には、特定の語句で始まるワークシート名が、すでにリスト化されています。ユーザーがテキストボックス[ Edt商品コード
]へ商品コードを入力すると、[ セルE列
]で検索を行い、商品のデータをユーザーフォームの各コントロールに表示します。
検索値が見つかれば、検索値を基準とした相対位置のセルにあるデータを[
Offsetプロパティ ]を使って抽出します。[ Edt***
]コントロールは、ユーザーフォーム上に配置されたテキストボックスコントロールで、抽出結果を反映する前に、[ Format関数
]で日付や3桁カンマ区切りの表示書式を設定しています。
- ★
検索値が見つからなくとも、マクロを中断させない
入力値が13桁になると、5行目以降のデータ検索を行う[
Findメソッド ]のコードへと進みます。[ Findメソッド
]は検索値が見つからないと、エラーが発生してVBAマクロの実行を中断してしまうので、2行目の[ On Error GoTo
ステートメント ]でエラー発生時は[ TRAP
]行までジャンプし、未登録商品（コード）であることを示して「該当する商品コードはありません。」のメッセージボックスを表示します。
図：カテゴリを変更すると、選択値を含むデータをリストアップする
（ワークシートのA列で「ｽｷﾔｷ」を含むセルデータがリストアップされた）
- ★ ワイルドカード「 *
」を使って、「〜を含む」セルデータを検索抽出する
- ★ ワイルドカード「 *
」を使って、「〜を含む」セルを数える
8行目でワークシート関数の[ CountIf関数
]を使い特定語句を含むデータの数を数えています。その数を元に、[ ReDimステートメント ]で動的な多次元配列[ V_Dat()
]の次元数を設定します。
- ★
多次元配列のデータをリストボックスの値に追加する
動的な多次元配列[ V_Dat()
]には、絶対参照アドレスと商品コード、また商品名を格納します。検索ワードを絞り込む条件式を書き加え、絞り込んだデータのアドレスをリストに追加します。[
Do While〜Nextステートメント ]を抜けた後、一気にリストボックス[ Lst商品群 ]のリストへ設定します。
- ★
検索抽出を連続して行う
検索抽出を連続して複数行うときには、[ Findメソッド
]に加え、２回目以降の検索に[ FindNextメソッド ]を使っています。このプロシージャでは、[ Selectメソッド
]でセル範囲を選択しているので、フルスクリーンを解除していれば、セル範囲がフォーカスを持つ様子を見ることができます。なにやらセルを１行ずつ検証していますね。ワークシートのデータをそのまま使うのは、忙しく見えます。[
Selectメソッド ]や[ Activateメソッド ]の多用は、実に非効率ですね。
データの数が少ないうちは、この「非効率さ」というのが見えてきません。１枚のワークシートには、16,777,216個のセルがありますが、この、たった１万６千個チョビチョビのセルに数字の「1」を入れただけで、ブックのサイズは約１００メガバイトの巨大なファイル容量となります。
これでは、ブックを保存するのにも開くのにも時間がかかりますし、データの検索はおろか、編集作業もメモリ不足でおぼつかない状態でしょう。このブックをCSV形式（カンマ区切り）で保存すると、約３２メガバイトになります。これでも結構な容量ですが、データを分散して使えば、数キロバイト程度のデータを使って、１００メガバイトを超えるデータベースでも、自由に扱うことができるようになります。
例えば、それぞれに分野も名称も異なった１０００個の商品の中から、「A」という名前を持った商品を選ぶとき、何を基準に抽出するかが重要です。商品「A」は、その商品コードを「KUDAMONO8931」、価格を「200円」、生産者「宮城県の○田さん」、「仕入日」と「販売日」という情報を持っています。他の９９９個の商品も、同じような情報を持っているとして、これらのデータが、１つのExcelブックに収まっていると、かなりのファイルボリュームになることは、想像にかたくないでしょう。
「宮城県の○田さんが作った商品名」を探すときや指定するとき、「価格」や「仕入日」、「販売日」の情報は不要ですね。データとしては、商品名の「A」だけが返されれば良いわけです。
データ同士の結びつきは、【生産者「宮城県の○田さん」
：「A」】や【商品コード「KUDAMONO8931」】を使い、この情報を持ったデータを外部（Excelブック以外）に置けば、その分、ブックは軽くなり、メモリの消費も抑えられます。
実際には、【生産者コード：生産者名】【商品コード：生産者コード】【商品コード：商品名】・・・というような、ID番号やナンバリングを付与したデータを分散して作り、必要に応じてテーブルに呼び出し、データの連結を行います。これが、リレーショナルデータベースといわれるもので、本項の「商品コードを元に、商品データの詳細データを検索する」方法は、このテーブルにあたります。
さて、もう一度前章の【7.リストの項目を設定する】編を読み直して、組み合わせたVBAマクロを作ってみてください。
番外８.
申請書や契約書など、ワークシートを使った帳票は珍しくありません。使いようなのですが、本来なら Microsoft(R) Word
で作りそうなものも、表計算ソフトのExcelで作ってあります。こと契約書ともなれば、入力事項よりも、条項条文の方が遥かに文書の容量を食っていて、表計算文書ですね。
ここまでは、値の変化しない大量のデータは、定数として外部ファイルから取り込んできましたが、ユーザーが入力した値だけを外部ファイルに保存する方法も、便利な時があります。
取引先、おもに納入元に対して、独自のフォーマット（様式）をもった書類を指定する場合があります。フォーマット自体は電子化された書類ですが、商品情報（商品名や価格、型番、個数、納品日・・・）をパソコンで入力後、電子スタンプまでは導入が追いつかず、印刷したのち検印を受けた書類を商品と一緒に納入、はたまたFAXするという流れになります。フォーマットの配布側では、受け取った書類を元に、再び自社のパソコンに入力する手間を必要とします。
最近では電子認証のスタンパーなどもありますが、紙にあらわされたデータとパソコンのデータ連携をはかる手立ては、OCR（光学読取装置）と呼ばれ、随分と昔からあります。バーコードも似たようなものです。ただし、発行する側も受領する側も、それなりの設備が必要です。
身近にあるもので、もっと手軽に、印刷物をもっと簡単に且つ正確な情報をパソコンへ取り込む方法は無いのでしょうか。え？
はい、コレをどうぞ。
- 番外Download -->
QRコードスタンプVBAマクロ「QRスタンパー
ver1.0」Excel用
あらかじめ指定したセルの内容を編集すると、そのセルの内容を持つQRコードを自動で右隣のセルに貼り付けるVBAマクロです。名前や金額、数量などを入力するセルを指定しておけば、自動でQRコードを作成・貼付します。保存されるQRコードの画像（BMP形式）は、元になったセルの名前で、ブック名からのフルアドレスです。
|