この動画では、Excel VBAを極限まで活用して作成されたカスタム在庫管理ソフトウェアの開発手法を詳細に解説している。一般的なユーザーフォームとワークシートの組み合わせを超越し、モダンなUIの特徴を持つ完全にインタラクティブなシステムの構築方法を紹介している。検索可能なドロップダウン、シンプルな日付ピッカー、プロフェッショナルなアプリケーションのようなカスタムリストボックスなど、VBAでは通常見られないモダンスタイルのUI機能の実装について、チームツール構築や Excel の機能拡張に興味がある開発者向けに実践的なアイデアを提供している。
イントロダクション
この動画では、Excel VBAを絶対的な限界まで押し上げたカスタム在庫管理ソフトウェアの作り方をお見せします。これは一般的なユーザーフォームとワークシートの設定ではありません。VBAでは通常見ることができない、本格的にモダンなUI機能を含む、完全にインタラクティブなシステムを作成しました。
モダンスタイルのメニュー、検索可能なドロップダウン、クリーンでシンプルな日付ピッカー、そして私のお気に入りの部分の一つであるプロフェッショナルなアプリケーションのようなルック・アンド・フィールを持つカスタムリストボックスをご覧いただけます。
チーム向けのツールを構築している方も、Excelでできることの限界に挑戦することが好きな方も、この動画はあなた自身のプロジェクトで使えるアイデアが満載です。必ず下の説明欄のリンクからこの動画のコードをダウンロードしてください。それでは早速始めましょう。
在庫管理ソフトウェアの概要
まず、私たちが構築する在庫管理ソフトウェアを見てみましょう。ご覧の通り、左側にメニューがあり、アイテムを選択すると表示データが適切に更新されます。データが表示されている場合、検索ボックスに入力するとリストがリアルタイムでフィルタリングされます。また、データを昇順または降順でソートすることもできます。
製品をダブルクリックすると、編集用の詳細画面が開きます。下には、その製品に関連する売上を表示するセクションがあります。Hammermill コピー用紙のような売上のある製品を選んで、「製品売上を表示」をクリックしてみましょう。その商品のすべての売上の内訳が表示されます。日付、顧客、数量などが確認できます。
次に、新しい製品を追加してみましょう。「新規」をクリックして詳細を入力します。カテゴリを技術、価格を1500、数量を10、サプライヤーを TechPro wholesale に設定します。「保存」をクリックすると製品が追加されます。ダイアログを閉じると、製品がリストに追加されているのがわかります。
売上セクションに移動すると、さらに多くの機能があります。「新規」をクリックして売上を追加し、日付ピッカーを使用して日付を選択し、検索可能なドロップダウンを使用して顧客を選択します。「新しい製品を追加」をクリックすると、別の検索可能なドロップダウンが表示されます。アイテムを選択し、数量を調整すると、価格が自動的に更新されることに注目してください。完了したら「保存」をクリックして売上に追加します。ダイアログを閉じると、新しいアイテムがリストの下部に追加されているのがわかります。
モダンな動的メニューの追加
私たちが構築する在庫管理ソフトウェアがわかったところで、始めましょう。まず、モダンメニューアドインを使用して、Excel VBA ユーザーフォームにモダンな動的メニューを追加する方法を学びます。
最初に必要なのは、ダウンロードファイルに含まれているモダンメニューアドインです。Excelが閉じていることを確認してから、アドインファイルをExcelのアドインフォルダに配置します。なぜ閉じる必要があるかというと、Excelは起動時にのみアドインを読み込むからです。
ステップ2でアドインをアクティブ化します。Excelを開き、開発者タブに移動し、Excelアドインをクリックすると、モダンメニューがリストに表示されます。チェックボックスにチェックを入れて「OK」をクリックします。これで、すべてのプロジェクトでグローバルに利用できるようになりました。
ステップ3で参照を追加します。VBAプロジェクトに移動し、ツール参照を開いてスクロールダウンし、モダンメニューにチェックを入れます。これにより、プロジェクトがこの外部ライブラリを使用していることをVBAに伝えます。これにより、それを必要としないプロジェクトの読み込みを避けることができます。
ステップ4でメニューフレームを構築します。ユーザーフォームを挿入し、フレームコントロールを追加します。これがモダンメニューを保持します。「frame_menu」という名前を付けましょう。
コードの実装
次にコードを書く時間です。心配しないでください、驚くほど短いです。まず、メニューイベントを処理するためのWithEvents変数を宣言します。それができたら、ユーザーフォーム初期化イベントを追加します。フォームが開いたときに自動的にメニューを構築し、整理するために「create_menu」というサブを呼び出します。
create_menu内では、いくつかのことを行う必要があります。最初にメニューをインスタンス化します。これによりメニューが作成されます。次に、コードを少しきれいにするためにwithステートメントを作成します。そしてフレームを割り当て、これにより作成したフレームをメニューフレームとして割り当てます。これが完了したら、メニューアイテムを追加します。この配列内のすべてのアイテムは文字列で、これらがメニューに表示されます。すべてが完了したら、build_menuを呼び出してメニューを構築します。
コードを実行すると、メニューが表示されます。組み込みスタイリングでホバーと選択を既に処理しています。
アイコンとカスタマイズ
ステップ8で、アイコンを追加してこれをよりきれいにしましょう。まず、アイコンフォルダへのパスを設定します。この場合、現在のワークブックフォルダ内のiconsという名前のサブフォルダです。次に、アイコンファイル名を提供します。ここでは、よりクリーンな外観のために透明GIF画像を使用しています。そして、ホームボタンアイコンプロパティを使用してホームボタンのアイコンを追加します。フォームを再実行すると、メニューにアイコンが表示されているのがわかります。
このメニューが既に持っているもう一つのクールな機能を確認してみましょう。メニューにランダムなコントロールを追加しています。これの目的は、メニューを非表示にして開くときに、フォーム上のすべてのコントロールが自動的に移動することです。これは組み込み動作で、スムーズなユーザーエクスペリエンスを実現します。
モダンメニューは高度に設定可能です。ホバーとハイライトの色をカスタマイズしてみましょう。ホバーカバーを水色に設定し、ハイライト色を黄色に設定します。build_menuを呼び出す前にこれらを設定することを確認してください。そうしないと、デフォルトのスタイルが最初に適用されます。ホバー色として青、メニューアイテムが選択されたときのハイライトカバーとして黄色が設定されているのがわかります。
メニュークリックイベントの処理
メニューが設定できたので、ユーザーがアイテムをクリックしたときに何かを実行させましょう。ここでitem_clickedイベントが登場します。このイベントハンドラーを作成するには、ユーザーフォームのコードウィンドウに移動し、左側のドロップダウンをクリックして、右のドロップダウンからメニュー変数（この場合はm_menu）を選択します。item_clickedが選択されているのがわかります。これは唯一のイベントだからで、VBAが自動的にイベントスタブを挿入します。
このサブ内では、ユーザーがメニューアイテムを選択するたびに好きなコードを実行できます。例えば、クリックされたアイテムの行番号とメニューテキストを表示してみましょう。以上です。ユーザーが顧客のようなアイテムをクリックすると、選択したアイテムの行番号と名前を示すメッセージが表示されます。
これで、わずか数行のコードを使用して、ExcelVBAプロジェクトに洗練されたプロフェッショナルなメニューができました。
モダンリストボックスの追加
次に、製品データを表示するモダンリストボックスを追加します。前に使ったメニューアドインと同様に、Excelが閉じている間にこれをアドインフォルダにドロップします。次にExcelを開き、開発者タブに移動してアドインをクリックし、モダンリストボックスの横のチェックボックスにチェックを入れます。
Visual Basic エディターに移動し、これを使用したいプロジェクトを開きます。ここで使用しているプロジェクトは test_inventory_2です。次に、ツール参照に移動し、モダンリストボックスを見つけてチェックを入れます。これでアドインがこのプロジェクトで使用できるようになりました。
ユーザーフォームをクリーンアップします。以前使用していたこれらのデモコントロールをすべて削除します。フレームを追加し、これがリストボックスのコンテナとして機能します。これを「frame_product」という名前にして、「product」というラベルを付けて、後で他のフレームと簡単に区別できるようにします。
次にフレームの位置を設定します。left、top、widthプロパティを設定します。ここでは、ユーザーフォームをExcelウィンドウの幅に設定しており、Excelでフルスクリーンを使用していない場合は、Excelのサイズに合わせて調整されます。
リストボックスの作成とデータ処理
実際にリストボックスを作成するために、create_list_boxという再利用可能な関数を使用します。これは非常に有用です。なぜなら、複数のリストボックス（製品、サプライヤー、顧客など）を持つことになり、それぞれに対してコードを繰り返したくないからです。
この関数内では、CLS_ModernListBoxクラスをインスタンス化します。これはnewキーワードの使用に似ていますが、アドインを扱っているため少し異なります。オブジェクトを作成したら、ユーザーフォームで作成したフレームにアタッチし、データを渡します。これは通常配列で、通常は範囲から取得した配列です。
create_list_boxesというサブを作成し、これがすべてのリストボックスを作成する中央の場所として機能します。そうすることで、それぞれに適切なパラメータを使用してcreate_list_boxを呼び出すだけで済みます。位置的には、メニューの右側に15ピクセルのオフセットでリストボックスを設定します。次に、フレームとデータを渡して、戻り値のリストボックスを listbox_product のような変数に割り当てます。
mod_dataという新しいモジュールを追加し、このモジュールがすべてのデータ取得を処理するようにして、ユーザーフォームのコードがクリーンでユーザーインターフェースに集中できるようにします。この場合、製品シートのTB_Productという名前のテーブルから製品データを取得します。Sheet_Product、次にListObjects、次にTB_Product（テーブル名）を使用してテーブルにアクセスし、ヘッダーなしのデータだけであるDataBodyRangeを取得します。
ヘッダーを取得する関数も作成しますが、これは非常に似ており、唯一の違いは今回はDataBodyRangeの代わりにHeaderRowRangeを使用することです。これがテーブルが非常に有用な理由で、テーブルからデータを取得するのが非常に簡単だからです。
まだこの変数を宣言していないので、上部で宣言しましょう。モダンメニューと同様に、private with eventsで宣言され、変数名はas CLS_ModernListBoxです。これが完了したら、create_list_boxesを呼び出します。これをユーザーフォーム初期化から行います。
コードを実行して何があるかを見てみましょう。リストボックスがそこにありますが、思うように設定されていません。幅を自動サイズにして、列サイズも設定したいと思います。そうすれば、きれいに見えるはずです。
列幅の設定とヘッダーの追加
列幅を設定します。パラメータに設定しますが、そのパラメータが何になるかを後で正確にお見せします。これは単純に列で区切られた文字列です。リストボックスの上部にパラメータを追加します。ここにいる間に、ヘッダーデータも追加します。なぜなら、ヘッダー列はすべてのリストボックスで異なるからです。
次に自動幅をtrueに設定します。これは、列幅に基づいてリストボックスを自動的にリサイズすることを意味し、常に適切なサイズになります。高さは指定された行数に基づきます。デフォルトの行数は通常10です。そしてヘッダーデータを割り当てます。
これらをcreate_list_boxに引数として渡す必要があります。get_header_valueを使用するのがいかに簡単だったかがわかります。get_all_valueと同じで、そこから配列を返します。そして、セミコロンで区切られた列幅の文字列を追加します。
コードを実行すると、すべての列が正しくリサイズされ、モダンリストボックスがあるべき姿で表示されているのがわかります。例えば「logi」と入力して検索すると、Logitechが表示されます。再度入力して「prints smart」と入力すると、Prints Smartのサプライヤーが表示されます。名前でソート、IDでソート、カテゴリでソート、または任意の列でソートできます。
ユーザーフォームの背景を白に設定して、ユーザーインターフェースがより少しクリーンに見えるようにしましょう。コードを再実行すると、白い背景でユーザーフォームがより良く見えることがわかります。
これで、非常に強力な2つのコントロールであるメニューとリストボックスを、必要なコードをほとんど使わずに統合しました。このコードのほとんどは実際には設定だけでした。
複数データタイプの表示
次にやりたいことは、メニューアイテムをクリックするたびに関連データを表示することです。顧客をクリックすると顧客データが表示され、売上をクリックすると売上データが表示される、といった具合です。
製品の設定は既に行ったので、そのコードの多くを再利用できます。最初のステップは、データを追加することです。データクラスは既に定義しており、必要なデータを返すので、既存のコードをコピーしていくつかの調整を行うだけです。製品の参照を取得し、salesに置き換えます。検索と置換を使用して、すべて選択し、Ctrl+Hを押してproductをsalesに置換します。
supplierとcustomerについても同じことを繰り返し、すべてのデータ関数ができます。ユーザーフォームに移動してフレームを追加します。既にframe_productがあり、各データタイプのフレームを作成したいので、既存のものをコピーしてから、新しいフレームの名前を変更します。salesに1つ、customersに1つ、supplierに1つです。また、後で識別しやすくするためにフレームキャプションを更新します。
売上や顧客のような現在アクティブなページの名前を表示するラベルを追加し、フォントサイズを約24に設定し、ラベルをlabel_page_headingのような名前にします。今のところ、フォーム上で必要なのはこれだけです。
コードに戻り、変数を宣言します。customer、sales、supplierの変数を追加しましょう。リストボックスを作成する場所までスクロールダウンし、他の各リストボックスのcreate_list_boxを追加します。違いは、データを取得する場所、列幅、使用するフレームといったパラメータだけです。
ページ切り替え機能の実装
ユーザーがメニューアイテムをクリックしたときのページ切り替えを処理します。正しいリストボックスが表示され、初期化中にデフォルトページ（通常は最初のメニューアイテム）を設定したいと思います。これを行うために、set_pageというprivate subを作成します。
set_page内では、すべてのリストボックスに移動し、各リストボックスについて、row_textと一致するかどうかを確認し、一致する場合はvisibleに設定し、そうでない場合は非表示にします。このif文はExcelのif文のように機能します。選択されたメニューアイテムがsalesと一致する場合、salesリストボックスを表示し、そうでない場合は非表示にします。customer、supply、productなどについても同じことを行います。
row_text変数はメニューから値を取得するので、メニューに割り当て、メニューのselected_row_textプロパティに割り当てます。そして、ラベルキャプションをrow_textに設定します。
コードを実行すると、製品が正しく読み込まれるのがわかります。顧客をクリックすると、顧客データが表示され、売上なども同様です。ラベルを少し広くする必要があり、これは簡単に修正できます。すべてのフレームが整列されているかも確認したいと思います。各リストボックスを作成するときは、適切なフレームを使用して同じ場所に配置されるようにします。
フレーム位置データをこのように更新します。コードをもう一度実行すると、すべてが適切に整列され、データが正しい位置に読み込まれ、見出しが更新され、すべてが非常にスムーズに動作しています。ご覧の通り、このすべてのデータを追加して表示するのは非常にシンプルで、再利用可能なコード構造により、検索やソートなどの機能がすべてのページで動作します。
新規顧客追加フォームの作成
在庫を作成したので、顧客、製品、売上などを追加する方法が必要です。新しい顧客を追加する方法と、製品や売上を追加する方法はほぼ同じ方法で行われます。
作成したフォームです。このフォームは4つのテキストボックス、ステータス用のドロップダウン、都市用のテキストボックスとリストボックスで構成されており、これらを組み合わせて検索可能なドロップダウンを作成します。
最初に在庫フォームにコードを追加します。ボタンを追加し、このボタンをクリックするとそのフォームが表示されます。ボタンを少し見た目よく更新します。このボタンをダブルクリックすると、そのクリックイベントのコードに移動します。
このクリックイベントで必要なのは、単純にフォームを取得して表示することです。これによりそのフォームが表示されます。そのフォームが閉じられると次の行に移動し、次の行では顧客のリストボックスを再入力したいと思います。新しい顧客でスプレッドシートを更新した場合、これにより顧客リストが再度読み込まれ、すべての新しいレコードが含まれるはずです。
コードを実行して、これが正しく動作しているかを確認しましょう。「新規」をクリックすると、フォームが表示されたのがわかります。現在、フォームには都市やステータスが配置されていませんが、すぐにそれを修正します。
検索可能ドロップダウンの実装
検索可能ドロップダウンクラスをインポートします。これは少し前に作成したコントロールで、検索可能なドロップダウンを持つことができ、実装が非常に簡単で、多くのコードを追加する必要がありません。
上部にprivateメンバーがあり、dropdown_cityになります。これが私たちの検索可能ドロップダウンです。これを使用する場合、基本的に元のコントロールのイベントを上書きしているため、with eventsを使用して宣言する必要があります。
次にやりたいことは、ユーザーフォーム初期化を作成することです。左メニューからユーザーフォームを選択すると、常に空のユーザーフォームクリックサブが作成されますが、initializeが必要なので、clickを削除できます。イベント内にコードを配置するのではなく、イベントから呼び出されるサブを持つことが常に良い実践です。これにより、将来何かを変更したい場合にコードがより柔軟になります。
initialize_controlでは、dropdown_cityを設定します。新しい検索可能ドロップダウンに割り当てて、検索可能ドロップダウンのインスタンスを作成します。次に、それを2つのコントロールにアタッチします。2つのコントロールはtextbox_cityとlistbox_cityです。search_listboxをどのリストボックスになるかに設定し、listbox_cityになると言いました。テキストボックスでも同じことを行い、クラス内のコードが残りのすべてを処理してくれます。
次に、リストボックスの開始テキストを設定します。「都市のリストを表示するために入力してください」と設定します。ユーザーがそれを見たときに、何かを表示するために入力する必要があり、それが標準のテキストボックスではないことを理解できるようにするためです。そして、データを入力し、これを都市のリストに等しく設定します。
mod_dataでこれを書きます。cities_get_nameは、単純にルックアップワークシートからの都市のリストです。これまでと同様に見えますが、違いは、全体のDataBodyRangeではなく、都市の列だけを取得していることです。将来他の列を追加した場合に備えて、その列だけを取得したいからです。
ここのテーブルは1列だけですが、将来変更される可能性があるので、その列だけを指定している理由です。コードを実行して、現在の状況を確認しましょう。そこにテキストがあり、文字を入力すると都市のいくつかが表示され始め、まさに期待通りに動作しているのがわかります。
ステータスとIDの設定
ステータスのコードを追加します。ここではドロップダウンを行いません。なぜなら、これには2つのアイテムしかなく、それらはactiveとinactiveだからです。基本的に、これのリストを配列に等しく設定し、その配列には2つのアイテムがあり、activeとinactiveという文字列だけです。そして、これを最初のアイテム（リストインデックス0）に設定し、コードのそのセクションを終了します。
textbox_IDを次の値に設定したいと思います。起動するたびに、顧客の新しいIDを取得したいと思います。new_IDは、顧客テーブルから次に高い番号です。顧客の最大番号が12の場合、次の番号である13を取得します。
mod_dataにget_next_IDを追加します。これは単純にテーブルでVLOOKUPを行っています。このテーブルは基本的に異なるアイテムのテーブルで、それぞれの最大IDを持っています。最大IDは各個別テーブルから取得されます。例えば、ここで最大IDを取得し、それに1を加えると11になります。get_next_IDを実行するときに、基本的に探しているもの（この場合は顧客）を与え、次のIDである11を返してくれます。
ここでコードを実行し、新規顧客と言うと、IDに11が表示され、ステータスにactiveとinactiveがあるのがわかります。
データ保存機能の実装
最後にやりたいことは、保存のコードを追加することです。データを追加したら、明らかにワークシートに書き込みたいと思います。最初にcustomer_addを呼び出します。これによりワークシートに行が追加され、配列を与えているだけです。
customer_addが行うことは、テーブルcustomer_tableを取得し、list_rows_addを使用してそれに行を追加し、最後の行を取得することです。最後の行は基本的にlist_rows.countの位置にあり、配列をそれに割り当てます。適切な列数の1行の配列を与えるだけで、それが書き戻されます。
フォームに戻り、fill_arrayというサブを作成します。これは基本的にすべてのコントロールからすべてのデータを取得し、新しい配列に配置し、その配列をcustomer_addに与えて、ワークシートに書き出します。
これがfill_arrayです。1行で6列を持つ配列を作成します。ID、名前、ステータス、電話、住所、都市を設定し、最後にそのデータを返すのがわかります。その戻り値をcustomer_addサブに直接渡します。
それを行った後、コントロール内のすべてのデータをクリアします。これを行うかどうかは本当にオプションです。状況によっては、顧客を追加する場合、似たような詳細を持つ可能性があるため、毎回ワイプしたくない場合があります。本当にどのようにしたいかによりますが、この場合は表示するためだけに入れておきます。
コードを実行して顧客を追加し、実際にどのように動作するかを確認しましょう。電話番号を追加してから、住所に「7 High Street」と入力し、ドロップダウンから都市を選択します。最初のものを選択してから、「保存」をクリックすると、新しいIDに更新され、すべてのデータが消去されたのがわかります。
ここで、ユーザーが続行したいかどうかを確認するメッセージボックス、保存したいかどうか、そして新しい顧客が追加されたことを通知するものを入れることもできます。それをフォーム上のラベルとして配置することもできます。本当にどうしたいかによります。
ここで、Joe Blogsが記録のリストに含まれ、期待通りに追加されたのがわかります。ここを見ると、Joe Blogsがワークシートに表示されています。
この動画を楽しんでいるなら、今後の動画の通知を受け取るために購読をクリックしてください。
日付ピッカーの実装
作成したシンプルなコントロールである日付ピッカーを見てみましょう。通常の日付ピッカーはもっと複雑で、使用するのに少し重厚だと思います。個別に日、月、年を選択するこのスタイルの日付ピッカーが好きです。
例えば、ここで31日のように間違った日を選択し、「適用」を押すと、実際には有効な最高の日付を入力してくれます。2月を選択して同じ番号を選択すると、2月28日を入力してくれます。これは非常にシンプルなコントロールで、非常に有用で、ユーザーフレンドリーだと思います。コードでの実装も非常に簡単です。
コードとその方法を見てみましょう。基本的にフォームを使用します。form_dateというフォームがあり、好きなプロジェクトにドラッグするだけです。そのフォームができたら、これが日付ピッカーで使用するコードです。
selected_dateは単純に持つ予定の日付で、label_start_yearから取得します。年の範囲で、2020年から始まってshow_willでユーザーフォームを表示します。ユーザーはXをクリックしてキャンセルできるので、キャンセルの場合に備えてコードを配置する必要があります。日付を更新したくありませんが、キャンセルしない場合は、selected_dateプロパティを使用して日付を戻すだけです。
これが日付ピッカーです。ユーザーインターフェースで実装するのが非常に簡単で、実際に非常に有用だと思います。
この動画が有用だと感じたら、画面で見ることができるモダンリストボックスの動画を必ず楽しんでいただけるでしょう。今後の動画の通知を受け取るために必ず購読してください。次回お会いしましょう。
コメント