エクセルVBAを始めてみましょう。第3回プログラミングの手法、ユーザーフォームからシートのセルを操作制御する。

エクセルVBA
透析室にてXPS13
この記事は約11分で読めます。

コロナの渦中

この地域でもコロナの脅威が蔓延しています。毎日、消毒やソーシャルディスタンスに神経をすり減らして滅入ってしまいます。透析のベットの上でカタカタキーボードを鳴らして気分を紛らわす日々ですが、万が一にも感染なきよう頑張っていますよ。

今回は3回目です、今回からちょっと難しくなりますが、できるだけわかりやすくを心掛けて書きました

新しいシートとユーザーフォームを作る

前回まで練習してきたのはワークブックのSheet1に対してコードを実行してきました。今回はシートを追加して更にユーザーフォームを作ってフォームからセルを制御する方法を練習します。

シートを追加します

シートを追加するのは簡単です。下図の赤矢印のところ、+をクリックします

 

下図のように、すぐにSheet1の右側にSheet2のタブができます。そして、同様にしてSheet3も追加しました。
Sheet3にはこの後データを格納します。

続けてユーザーフォームを作成します

左端にあるプロジェクトウインドウ赤矢印のところ右クリックして → 挿入ユーザーフォームを順にクリックします。

 

下図のようなフォームが挿入されました。

 

フォームにはツールボックスからラベルボタン、リストボックス等の様々なコントロールを配置することが出来ます。
ではまず、ラベルを貼り付けます
ツールボックスの「A」の形のアイコンをクリックしてから、マウスポインタをフォーム上の任意の場所を起点として青丸から緑丸のところまでドラッグして離すとラベルが張り付きます。
コントロールの場所や大きさはプロパティウインドウで、後から自由に変更できます。
同様にして②テキストボックスと③ボタンを付けます

プロジェクトウインドウの使い方

コードエディターの左側上部にあるプロジェクトウインドウでは、VBAのプロジェクトに属するBookを先頭にしたオブジェクト一覧がツリービューで見て、オブジェクトそれぞれのコードウインドウを表示できるようになっています。
下図の赤矢印の + のところをクリックするとブックに属するSheetオブジェクトが配置されているのがわかります。

 

各オブジェクトの配置を確認したら、今回は赤矢印の「UserForm1」をダブルクリックしてフォームを表示させます(すでにフォームが表示されている場合はそのまま操作します)

フォームのボタンコントロールから簡単な命令を出してみます。

フォーム上の「CommandButton1」と表示されているボタンをダブルクリックしてコードウインドウを表示させます。
ウインドウの上部右側のコンボボックスをクリックすると、このボタンがどのような状態になったら動作を始めるか、その動作状態の区分けの一覧が表示されます。標準では「Click」になっています。これはつまり、「ボタンがクリックされたとき」を意味します。エクセルではこの様な状態のことを「イベント」と言っています。つまり、「ボタンがクリックされたときのイベントを処理するコード」ということになります。
下図では、赤矢印クリックイベントの処理を記述する部分と、イベントのコンボボックス一覧で青丸ダブルクリックイベントを選んだ時の処理を記述する部分とが別々のブロックに分かれているので、それぞれイベントごとに独立してコードが処理されることになります。
今回は、ボタンがクリックされたときのクリックイベントを処理するので、上図の赤矢印のところに何か、動作するコードを書いてみましょう。
ダブルクリックイベントのブロックは紛らわしいので消してしまいましょう。
コードを書く前に、すでに作成してあるSheet3にテスト用のサンプルデータを準備します。
文部科学省から食品成分表をダウンロードします。エクセルで作成されているデータがあるので、それを開きます。
成分表データは全部だと2000行以上あるので、ここでは何でもいいですけどサンプルデータなので魚介類をクリックしました。すぐにダウンロードが終わるのでそれを開きます。お使いのブラウザによっては表示状態が違うと思いますが、ダウンロードフォルダーに保存されるのでそこからも開けると思います。
エクセル表が表示されるので、赤丸のところをクリックするとすべてのデータが選択状態になるので、その状態で「Ctrlキー + Cキー」でクリップボードにコピーします
次に今使っているプログラム練習用のエクセルを開いて、Sheet3を出してA1のセルを選択したら「Ctrlキー + Vキー」でクリップボードのデータを貼り付けします。
これでいったんブックを上書き保存します。これでサンプルデータの準備は終了です。
ここからやっと、ボタンのクリックイベントのコードを作ってみることにします。
下図のコードエディターに書き込みます。場所はUserForm1CommandButton1Clickイベントです。
コードの全文は以下のようになります。
動作の概要
Sheet3にコピー貼り付けしたサンプルデータの食品名を検索ワードで検索する。
部分一致検索で一致した行の食品番号食品名を取り出す。
一致データ行は30行に限定している。
その取り出した2個のデータを一行としてSheet2に書き出す。
最後にこのブックが開いたときに検索用のフォームを表示するようにする。
16行~21行入力チェック
If を使った条件分岐ですIf からEnd If がブロックになっています。

keyward = “” Then →文字列変数keyward が空白(文字が無い状態)なら以下の文を実行すると読みます。以下の文はメッセージを表示させる。メッセージが閉じたらテキストボックスにフォーカスを当てて、その後の22行目以降の処理をスキップしてここで動作を終了させる。(フォームは開いたままです)keyward に何らかの文字が入っていら22行目以降の処理に移ります。

23行~39行、繰り返し処理
For からNextまで24行目~39行目の間の処理を指定回数繰り返します。

For に続く r = 9 To 500 が繰り返しの条件です。整数型変数rに9から始まって500までを繰り返すそのつど代入するという意味です。つまり処理が最初にForに入った時はrに9が入って繰り返してrに500が入ったら繰り返しをやめて40行目以降に処理を移します。

Cells(引数1, 引数2)の使い方
Cells(1 , 1)は実際のシート(表)ではA列の一行目と同じです。
又はRange(”A1”)と同じです。
引数1には行の位置、引数2には列(桁)の位置を整数で指定できるのでプログラム中でセルを操作するときに便利に使えます。
26行目  Foodname = Sheet3.Cells(r, 4).Value     ‘シート3の4列目の指定行食品名を格納
最初の繰り返しでrには9が入りますから、シート3の9行目の4列目のセルと読みます。このセルには「<魚類>あいなめ 生」と書かれています。つまり変数Foodnameに<魚類>あいなめ 生が代入されたことになります。
27行目    Foodcode = Sheet3.Cells(r, 2).Value     ‘シート3の2列目の食品コードを格納
上と同様、変数Foodcodeにシート3の9行目の2列目のセルの値「10001」が代入されます。
つまり、繰り返しの度に変数rには行の位置が10、11、12・・・・と500までその行の値が順次代入されていきます。そして

32行目~38行目、繰り返しの中でIf を使ってその都度その行の値を判定して検索を掛けます。
32行目、If Foodname Like “*” & keyward & “*”Thenの意味は
Foodnameには繰り返しのその時々の食品名
keywardにはテキストボックスに入力された検索ワードが入っています。
Like は演算子であいまい検索するとき使われます。
*はワイルドカードと言ってこれを付けることで曖昧さを表現できます。

例えば、一回目の繰り返しでFoodnameに<魚類>あいなめ 生 が入ります。
検索ワードに「さんま」とテキストボックスに入力されたとするとkeywardにはさんまが入っています。このとき32行目では、もし<魚類>あいなめ 生の中にさんまが入っていれば33行目以降の処理を実行しなさい。と読みます。つまり、一回目の繰り返しの時この条件式はfalseとなるので32行目以降の処理は実行しないで二回目の繰り返しに移ります。

この検索ワードの場合最初にFoodnameにさんまという文字列が含まれるのは190回目の繰り返しの時です。199行目の食品名が<魚類>さんま 皮つき、生 ですから”*さんま*”つまり前後が何であろうとさんまが含まれているから条件を満たします。ですからTrueとなり32行目以降のコードを処理します。

ここで2次元配列の説明です
10行目、Dim Foods(30, 2) As Variant が宣言してます。
第一引数は行の最大値、第二引数は列の最大値です。
30行で2列のセルを持った表をイメージしてください。セル番地は0(ゼロ)から始まります。1行目の1列目のセル番地は(0,0)です。
34行目、Foods(ct, 0) = Foodcode
配列変数Foodsのct行の1列目に食品番号を格納します。ctは23行目で繰り返しが始まる前に0が代入されているので一回目の繰り返しではctは0です。つまり1行目の1列目に食品番号が入ります。
35行目、Foods(ct, 1) =Foodname
上と同様、1行目の2列目に食品名格納します。
37行目、ct = ct + 1
現在(その時点)のctに1を加えてctの値を更新します。つまり次の繰り返しの時はctは1です。順次繰り返しの度2、3、4・・・・と30まで続きます。
このコードはIfの
あいまい検索でTrueになった時だけ実行されるので配列の一番上から順にデータが埋まっていくとイメージします。
後は、上であいまい検索した結果を格納した配列変数の値を、今度はSheet2のセルに埋め込んでいきます。配列にはイメージとして1行には2列で2個のデータがあるはずです。これをForを入れ子にしたForを使って表現します。
47行目、For t = 0 To siz は配列のの動きを繰り返します。
変数sizには45行目の UBound(Foods, 1)というエクセルの関数で配列Foodsの行の最大値を格納しています。siz=30です。変数 t には順次0から30までが代入されます。31回繰り返します。
49行目、For i = 0 To 1 は配列のの動きを繰り返しで表現します。ここは単純に0行目と1行目まで2回繰り返します。変数 i には0と1が順次代入されます
47行目の一回目の繰り返しの中で49行目の繰り返しに入ります。そして49行目を2回繰り返した後47行目の2回目の繰り返しに入ります。
ちょっとややこしいですが49行目の繰り返しのなかでシート2のセルにデータを埋め込みます。
51行目、 Sheet2.Cells( t + 1 i  + 1) = Foods( t ,  i ) この一行が肝です。
配列Foodsの番地指定は0から始まります。
Cellsの番地指定は1から始まります。配列の番地をCellsに合わせるため t と i にそれぞれ1を加算してこのコードを抜けて2回目の繰り返しには入ります。2回目は変数 i は1なので1加算だから2が入る。シートの2列目に配列の2列目の値が入る。
これで、内側のForを抜けて、外側のForの2回目の繰り返しに入ります。この様に入れ子のForを繰り返してシートに配列のデータを書き込んで、プログラムが終了します。

繰り返し処理のイメージを図解

Cellsの番地に配列の番地をそのままを指定して0が入ると実行時エラーがおきて処理がストップしてしまいます。
最後にこのワークブックが開いたとき検索用のフォームを表示するように設定します
プロジェクトウインドウからThisWorkbookをダブルクリックするとコードウインドウが開く。
デフォルトでOpenイベントがセットされている。つまり、ワークブックが開いたときどういう風にするのかをここに記述します。
記述内容は下図のようにしました。
ワークブックが開いたときの動作
・シート3を表示させる。
・検索ワードをすぐに入力できるようにテキストボックスにフォーカスを当てる。
・ユーザーフォームを表示させる。
以上でコードの記述は終了です。
一旦エクセルブックを保存して終了します。そして、再度ブックを開くとフォームが表示されるはずです。
検索ワードを打ち込んでコマンドボタンをクリックしてみます。
どうでしょう、シート2が表示されて食品名がリスト表示されたと思います。
今回のプログラムでSheet3には食品成分表の魚介類の部分だけ(約400行ちょっと)をコピーしているので検索ワードには魚介類の名前を指定してください。すべての食品を検索するには、食品成分表の全部のカテゴリーをコピーする必要があります。(全部なら約2000行ちょっとありますよ。)練習用なら一つのカテゴリーで十分だと思います。
コードをどこに、どのように書くのか大体わかっていただけたと思います。今回はIF(条件分岐)とFor(繰り返し処理)、それから変数の使い方をやってみました。いきなり二次元変数を出しましたが、プログラムの作成では、頻繁に使用するので頑張って覚えてください。
今回のプログラムはあくまで練習用です。コードも簡潔ではありません。もっとスマートに効率よい書き方もあると思います。とりあえず、セルの扱い方やフォームにコントロールを付けたり、条件分岐と繰り返しの処理の方法を示したもので使い方の感覚を覚えていただき、面白いと思っていただければ嬉しく思います。

コメント

タイトルとURLをコピーしました