2021/09/05

DVD等の内容・保管場所等管理システム

• １．背景
• ２．システム概要
• 　２－１．システム起動
• 　２－２．新規登録
• 　２－３．検索
• 　２－４．更新
• 　２－５．複製・登録
• 　２－６．削除
• ３．プログラムの流れ
• ４．標準モジュール(Module1)
• 　４－１．宣言部
• 　４－２．システム起動部
• 　４－３．テーブルの並べ替え
• 　　よりみち（ListObjectの基本操作）
• 　４－４．テーブルの絞り込み実行と解除
• 　４－５．テーブルのサイズ取得
• 　４－６．列内の最大値を取得
• 　４－７．データの追加・更新と削除
• 　４－８．可視セルデータの配列化
• 　４－９．列内の重複しないデータ一覧の作成
• 　４－１０．短時間だけ表示するメッセージボックス等
• 　　４－１０－１．MessageBoxTimeoutAを使用
• 　　４－１０－２．Popupメソッドを使用
• 　　４－１０－３．ステータスバーに表示
• 　　４－１０－４．フォームを使用
• 　４－１１．テーブルを新規作成
• ５．操作ダイアログ（UserForm1）
• 　５－１．フォーム上のコントロールの配置
• 　５－２．フォームモジュール
• 　　５－２－１．初期設定
• 　　５－２－２．検索ボタン
• 　　５－２－３．クリアボタン
• 　　５－２－４．登録／更新ボタン
• 　　　５－２－４－１．新規登録処理
• 　　　５－２－４－２．更新処理
• 　　５－２－５．削除ボタン
• 　　５－２－６．日付／終了ボタン
• 　　５－２－７．コンボボックスの作成・処理
• 　　５－２－８．数値用テキストボックスの処理
• 　　　　よりみち（フォーマットと検索文字）
• 　　５－２－９．リストボックスの作成・処理
• 　　５－２－１０．チェックボックスの処理
• 　　５－２－１１．挿入データの配列化
• 　　５－２－１２．検索条件の記憶・復元
• 　　よりみち（数値と文字列の混在改善、検索改善）
• ６．アドインとしてExcelにマクロを登録
• ７．最後に
• サンプルファイル

１．背景

気に入ったテレビ番組をレコーダで録画し、ブルーレイディスクやDVDディスク（昔だったらビデオ）として保管している方は多くいらっしゃると思います。私も映画やドラマなど、いつの間にか大量のディスクが色々な箱・棚に溜っています。
整理をしないといけない　とは思うのですが、どのように分類したら良いのか、また見たいものがすぐに取り出せるようにするにはどうすれば良いのか・・・色々考えた末、レコーダ内の録画分類を参考にして下記のようなものを作成しました。

もちろんジャンル（分類分け）等を見直せば、録画ディスクだけでなく音楽CD・書籍の管理にも使えると思います。また今回システム作成には、今まで使ったことのなかったExcelの「ListObject（テーブル）」機能を使ってみたい　との理由もありました。

なお、同じくListObjectを使った以下の項も参考にして頂けると助かります。
・先行予約可能な備品予約・貸出システム
・ToDoリストで個人タスク管理
・会社番号検索システム

２．システム概要

２－１．システム起動

本システムは、以下の２通りの使い方を想定しています。
　（A）アドイン登録を行い、Excel起動時ならいつでも利用可。
　（B）データファイル（.xlsm）を開き、シート上のボタンから起動。直接のデータ操作も可。
アドイン登録（登録方法は「アドインとしてExcelにマクロを登録」を参照下さい）をした場合には、図２－１のようにリボン上のボタン①をクリックすることで、操作ダイアログ②が表示されます。

図２－１

また一番下の「サンプルファイル」のようなデータファイルを直接開く場合は、図２－２のようにシート上のボタン①に起動マクロを登録することで、システムを起動します。

図２－２

２－２．新規登録

操作ダイアログを使用してデータを新規登録するには、図２－４のように各項目を入力・選択します。項目は、以下の５つです。
なお、必須入力項目は「タイトル」のみとしています。

項目	入力場所	入力方法・制限
タイトル	図２－４の③	文字種・文字数に制限はありません。 尚タイトルは必須項目で、「スペースのみ」のようなものもNGです。
シリーズ	図２－４の④	コンボボックスから選択します。 リストに無いものは、リスト最上段の「(新規)」をクリックし、表示されたインプットボックスに入力します（図２－４右の⑦⑧と同等）。また、コンボボックスのテキスト部に直接入力してもOKです。
作成日	図２－４の⑤	「yyyy/m/d」「yyyy-mm-dd」や「m/d」のような日付形式で入力して下さい。テキストボックスには文字種制限を掛けており、数字または「/」「-」しか受け付けません。 またテキストボックスを離れる時に日付チェックを行い、日付形式で無い場合は修正を求められます。
ジャンル	図２－５の⑨	１２種のジャンルの中から、対応するチェックボックスにレ点を付けて下さい。 検索時にはORで判定しますので、「対応しそうなジャンルにはチェック」する感覚で良いと思います
保管場所	図２－４の⑥	コンボボックスから選択します。 リストに無いものは、リスト最上段の「(新規)」をクリックし、表示されたインプットボックスに入力します（図２－４右の⑦⑧）。また、コンボボックスのテキスト部に直接入力してもOKです。

図２－３

図２－４

図２－５

なお「No.欄」にも入力は可能ですが、新規登録時には「無視」されます。「No.欄」は、次で説明する「検索」時には検索対象になります。

入力後、図２－５⑩の「登録／更新」ボタンをクリックすると、「登録した旨のメッセージ⑪」が一瞬（今回設定では0.5秒間）表示され、データがテーブルに登録⑫されます。同時にダイアログ下側のリストボックスに登録データが表示⑬されます。
（正確には入力項目で検索した結果をリスト化していますので、同じ内容のものを複数入力すると複数行が表示されます。）

図２－６

２－３．検索

検索を行うには、操作ダイアログの上部入力欄に「検索条件」を入力した後、「検索ボタン⑭」をクリックします。検索結果は、ダイアログ下部のリストボックス⑮に表示されます。

図２－７

項目	検索方法・制限
タイトル	タイトルの一部を入力することで、部分検索します。
シリーズ	コンボボックスのリストから選択します。また、コンボボックスのテキスト部に直接入力してもOKです。 なお｢相棒２｣｢相棒３｣の両方のシリーズを検索するには、リスト最上段の（新規）をクリックし、表示されたインプットボックスに「相棒」と登録すれば、「相棒」を含むシリーズが検索されます。
作成日	この欄は、日付形式のみしか受け付けません。「年だけの入力」や「年月の入力」「期間」には今回対応させていません。
No.	ディスクに登録してある「DVD番号」を入力します。DVD番号は正の整数のみです。
ジャンル	１２種の中から、検索するジャンルにレ点を付けて下さい。「ジャンル間はOR検索」しますので、例えば「ドラマ」と「映画」にチェックをすれば、「ドラマ」または「映画」が検索されます。
保管場所	コンボボックスのリストから選択します。この欄は、オリジナル状態では完全一致検索です。 部分一致に改造した場合は、コンボボックスのテキスト部への直接入力、またはリスト最上段の（新規）をクリックし、インプットボックスに検索文字列を入力して下さい。

図２－８

「ジャンル内はOR検索」ですが、「各項目間はAND検索」になります。
ですので例えばタイトルに「相棒」を、シリーズに「ブラタモリ系」を選択して検索すると、タイトルが「相棒」を含み、且つシリーズが「ブラタモリ系」であるデータが検出されます。（通常だと系統が異なっているため、リストボックスには何も表示されない事になります。）

検索条件に何も入力しない状態で「検索ボタン⑭」をクリックすると、無条件のため「全データ」がリストボックスに表示されることになります。データが多い場合には表示までに少し時間がかかります（約1000件の場合、私のPCで約5秒）。

検索ボタンの右側にある「クリア」ボタン（図２－９の⑯）をクリックすると、操作ダイアログ上部の入力欄、及び下部のリストボックスのデータは全て消去されます。

図２－９

２－４．更新

検索を行った後、図２－１０左のように、操作ダイアログ下段リストボックスの「検索結果」の１つをクリックすると、選択項目の内容がダイアログ上段に反映されます。ちょうど、新規にデータを入力した時と同様の状態になります。

図２－１０

その状態で上段の各項目を修正し、「登録／更新」ボタン⑰をクリックすると、データが修正されます。
例えば図２－１１の左のように、リストボックスで選択した項目のタイトルに修正を加えます。その後「登録／更新」ボタン⑰をクリックすると、一瞬（今回設定では0.5秒）更新をした旨のメッセージが表示されます。
データが修正された後の操作ダイアログは、上段の各項目欄は「元の検索状態（＝図２－１０の左側と同じ状態）」に戻り、下段の検索結果のリストには「データ修正後のリスト」に置き換わります。

図２－１１

なお、複数項目を同時に修正することもOKですし、シリーズや保管場所（コンボボックス）の変更、ジャンルのチェックのON-OFFも全て修正出来ます。
ただし「No.欄」だけは変更できないように「編集不可状態」にしてありますので、DVD番号の変更は不可です。

２－５．複製・登録

ディスクの中には、ほぼ同じ内容で一部だけちょっと違うものもあると思います。リスト内で選択した項目をクリックすると、ダイアログ上段で各項目の修正が可能になることは説明しましたが、その状態から更に、図２－１２左のようにリスト項目をダブルクリックすることで、上段のデータはそのままで「新規登録が出来る状態」にすることができます。

図２－１２

その状態から「ちょっとだけ違う部分」を修正した後、図２－１２右の「登録／更新⑱」をクリックすることで、図２－４～図２－５で各項目を最初から入力したのと同じように「新規登録」されます。
「データ更新」時は「DVD番号の修正」は出来ない旨を説明しましたが、このデータ複製・登録で「複製データ（DVD番号だけは別）」を作成し、古いDVD番号のデータを次で説明する「データ削除」することで、「DVD番号を修正」するのと同じことが可能になります。

２－６．削除

検索を行った後、図２－１３左のように、操作ダイアログ下段リストボックスの「検索結果」の１つをクリックすると、選択項目の内容がダイアログ上段に反映されるのは「データ更新」と同じですが、その状態で図２－１３右のように「削除ボタン⑲」をクリックすることで、選択した項目が削除されます。

図２－１３

削除ボタン⑲をクリックした途端にデータが削除される訳では無く、図２－１４左のような「削除しても良いか否か」のステップを踏むことで、誤って削除するミスを防止しています。なお削除された時には、図２－１４右のようなコメントが一瞬表示されます。

図２－１４

３．プログラムの流れ

まず、ユーザーがどのような流れでデータ入力・データ操作・ボタン操作を行うか、また、その時にどんなデータが表示されるかなどを図３－１にまとめてみました。システム起動直後は左下の「初期」の位置になります。

図３－１

複雑に見えますが、「システム概要」で示した使い方手順に合わせた流れになっています。この流れをボタン目線でまとめ、ボタンをクリックした時にどの順序でどの処理をしていくのか　を整理したのが図３－２になります。

図３－２

各ボタンを左端列に並べ、①②･･･は処理する順番を表します。またプロシージャ名の赤字は標準モジュール、黒字はフォームモジュールに置いてあります。
検索から更新・削除に矢印が出ているのは、「検索をした結果（リストボックス）」のデータをクリックし、クリックしたデータに対して「更新・削除の処理を行う」ことを示しています。

４．標準モジュール(Module1)

今回のプログラムコードは、「標準モジュール」と「フォームモジュール」に分けて置いています。
標準モジュール側には、システム起動の為のプロシージャ、及びシート上のデータを処理するプロシージャ等を置いています。一方フォームモジュール側には、フォーム上に関わる処理のプロシージャを置いています。
今回使用するUserFormは１つ（サンプルファイルにあるUserForm2は、図４－４９の「短時間メッセージ」紹介用）であり、処理の指示は全てUserForm側から行っていますので、起動プロシージャ以外の「シート上のデータを処理するプロシージャ」をフォームモジュールに記載することも可能です（その方がPrivateでプロシージャを宣言できるので、正しいかもしれません）。
しかし、システムを拡張してUserFormが増えた時を考え、「シート上のデータ処理のコードは標準モジュール」に置くことにしました。

また今回システムでは、データをテーブル（ListObjectオブジェクト）として管理しますので、例えばデータ最終行を求めるには、従来では「Cells(Rows.Count,1).End(xlUp)」などとする所を、テーブルでは「ListObjects(〇〇).Range.Row + ListObjects(〇〇).ListRows.Count」のように「テーブル全体・見出し部・データ部・データ行・データ列の単位」にアクセスし、「それぞれを１つのオブジェクトとして扱う」ことで、特定の位置・データに辿り着きます。
また、テーブルの最下行の１つ下の行にデータを追加することで、自動的にテーブル範囲が広がることも特徴です。
ListObjectの基本的な操作等については「よりみち」で説明します。

４－１．宣言部

標準モジュールの宣言部（プロシージャよりも前の部分）では、システム全体で使用する定数・変数の宣言、及びExcelには無い「一定時間で閉じるメッセージボックス」機能を使用するためのWindows APIへの参照宣言をします。

1. '==========　⇩(1)　宣言部　============
2. Public Const ShName As String = "sheet1"
3. Public Const TableName As String = "DVDmgt"
4. Public Const SearchGenre As String = "J1"
7. Public SearchCond(1 To 7) As Variant
8. Public Glist(1 To 12) As String
10. #If Win64 Then
11. 　Private Declare PtrSafe Function MessageBoxTimeoutA Lib "User32" ( _
12. 　　ByVal Hwnd As LongPtr, ByVal lpText As String, ByVal lpCaption As String, _
13. 　　ByVal uType As VbMsgBoxStyle, ByVal wLanguageID As LongPtr, ByVal dwMilliseconds As LongPtr) As LongPtr
14. #Else
15. 　Private Declare Function MessageBoxTimeoutA Lib "User32" ( _
16. 　　ByVal Hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, _
17. 　　ByVal uType As VbMsgBoxStyle, ByVal wLanguageID As Long, ByVal dwMilliseconds As Long) As Long
18. #End If

図４－１

２行目「Public Const ShName As String = "sheet1"」は、定数ShNameに対して「Sheet1」を設定しています。このExcelファイル（ThisWorkbook）の「データを置いているワークシート位置」を文字列で示します。Sheetオブジェクトとして宣言すれば、以下のプログラムコードがスッキリするのですが、今回はWorkbook_Openなどを使用せずに文字列設定としました。

３行目「Public Const TableName As String = "DVDmgt"」は、データであるテーブルの名前です。今回は「DVDをmanagement（mgt）するテーブル」という意味で命名しています。

４行目「Public Const SearchGenre As String = "J1"」は、「ジャンルで絞り込み」を行うために用意した「比較用ジャンル値」を置く場所（今回はJ1セル）を示しています。今回ジャンルは１２種で、それぞれON-OFFがありますので「１２ビットの数値（最大で4095）」でジャンルを表現し、各データ行に保存しています。
そのジャンル値を絞り込むには、ワークシート関数のBITANDを使用し「BITAND(各行のジャンル値 , 比較用ジャンル値)」（VBAなら「各行のジャンル値 And 比較用ジャンル値」）で１以上になるものを選別すれば良いことになります。今回テーブル内では６列目にその計算式を貼り付け、基準となる「比較用ジャンル値」を「検索の都度、SearchGenreが示すセル位置に貼り付ける」ことにしました。

７行目「Public SearchCond(1 To 7) As Variant」は、あたかも下段リストボックスで検索結果が保存されているかのように見せかけるために、「ユーザーが検索した条件」を一時保存しておくための変数です。今回の検索する種類は６種ですが、列の番号に合わせるため、計算式列（６列目）の分は使用しない要素になりますが７要素の配列としています。
また、新規入力時にも入力項目をそのまま残すために、このSearchCond変数を使用しています。

８行目「Public Glist(1 To 12) As String」はジャンルのリストです。フォーム上のCheckBoxの文字列を書き込む際に使用しています。

１０～１８行目の「#If Win64 Then ･･･ #Else ･･･ #End If」では、Windows APIへの参照宣言をしています。今回仕様するWindows APIは「MessageBoxTimeoutA関数」で、「一定時間後に自動的に閉じるメッセージボックス」です。
Excelのバージョンで32ビット版と64ビット版とでは、参照宣言の内容が異なるため、条件付きコンパイル（#If Win64 Then）を使用してバージョンで分岐させ、マクロのコードをバージョン毎に変更しなくても済むようにしています。
ちなみに、32ビット版と64ビット版とで異なる部分は、64ビット版には「PtrSafe」を付けるところと、Long型を「LongPtr」とするところのみです。

４－２．システム起動部

システムの起動は、図４－２の「DVDmgtStart」プロシージャから行います。アドイン登録する場合の起動ボタン、及びシート上に起動ボタンには、このDVDmgtStartプロシージャをマクロ登録して下さい。

20. '==========　⇩(2)　システム起動　============
21. Public Sub DVDmgtStart()
22. 　Call MakeGlist
23. 　UserForm1.Show
24. End Sub

図４－２

２２行目「Call MakeGlist」では、図４－３を呼び出し、ジャンル種の文字列を変数Glistに代入しています。この作業は、フォーム作成時に各CheckBoxのCaptionにジャンル種を書き込んでしまえば不要になりますが、今回のDVD管理だけでは無く、他にも応用できるのでは　と考えて、マクロ側から文字列設定をするようにしています。

２３行目「UserForm1.Show」では、UserForm1（操作ダイアログ）を起動しています。今回はワークシートを直接操作しないシステムにしましたので、引数を付けず既定のモーダル（vbModal　値＝1）で起動しています。

図４－２の２２行目から呼び出されるのが、ジャンル種の文字列を変数Glistに代入するプロシージャ図４－３です。
このGlist変数に代入した文字列は、操作ダイアログのCheckBoxのCaption設定に使用しています。

26. '==========　⇩(3)　ジャンル種のデータ代入　============
27. Private Sub MakeGlist()
28. 　Glist(1) = "ニュース/報道"
29. 　Glist(2) = "スポーツ"
30. 　Glist(3) = "情報/ワイドショー"
31. 　Glist(4) = "ドラマ"
32. 　Glist(5) = "音楽"
33. 　Glist(6) = "バラエティ"
34. 　Glist(7) = "映画"
35. 　Glist(8) = "アニメ/特撮"
36. 　Glist(9) = "ﾄﾞｷｭﾒﾝﾀﾘｰ/教養"
37. 　Glist(10) = "劇場/公演"
38. 　Glist(11) = "趣味/教育"
39. 　Glist(12) = "福祉"
40. End Sub

図４－３

今回１２種のジャンルにしましたが、これは適当に分類した訳ではなく「デジタル放送に使用する番組配列情報標準規格」の大分類を使用しています。但し規格上では、更に「予備・予備・拡張・その他」が続いており全１６項目になっています。また番号はゼロから始まっていますので、今回は改変して使用していることになります。

４－３．テーブルの並べ替え

まずデータの並べ替えを行うのが図４－４です。これは、図５－２２の４７６行目、図５－４１の６８７行目から呼び出され、「最新のデータを一番上に表示」されるために、降順で並べ替えを行っています。

42. '==========　⇩(4)　ソート　============
43. Public Sub TableSort(key As String)
44. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName).Sort
45. 　　With .SortFields
46. 　　　.Clear
47. 　　　.Add key:=ThisWorkbook.Sheets(ShName).ListObjects(TableName).ListColumns(key).Range, _
48. 　　　　　　SortOn:=xlSortOnValues, _
49. 　　　　　　Order:=xlDescending, _
50. 　　　　　　DataOption:=xlSortNormal
51. 　　End With
52. 　　.Header = xlYes
53. 　　.Orientation = xlTopToBottom
54. 　　.Apply
55. 　End With
56. End Sub

図４－４

４４行目「With ThisWorkbook.Sheets(ShName).ListObjects(TableName).Sort」では、並べ替えの対象を設定しています。
今回の並べ替えの対象は、図４－６左の「ThisWorkbook.Sheets(ShName).ListObjects(TableName)」です。

４４行目の最後の「Sort」はオブジェクトです。このSortオブジェクトには設定可能なプロパティが、図４－５のように５つあります。

Sortの設定可能プロパティ

プロパティ	内容
Header	先頭行を見出しとするか
MatchCase	大文字小文字の区別
Orientation	並べ替えの方向
SortFields	並べ替えのキー値など
SortMethod	ふりがなを使うか

図４－５

この中の「SortFields」を４５～５１行目で設定します。４５行目「With .SortFields」でコードを簡略化しています。
SortFieldsは、手動で言うと図４－６の様にテーブル内のどこかのセルを選択しておき、「データ」タブ→「並べ替えとフィルター」グループ→「並べ替え」ボタンをクリックすると「並べ替えダイアログ」が表示されます。SortFieldsは、この「並べ替えダイアログ」の「並べ替えレベル」に相当します。

図４－６

この「並べ替えレベル」に対し、まず４６行目「.Clear」メソッドで、並べ替えレベルを初期化します。以前実行した並べ替えの条件をクリアしておかないと、狙いの通りの並べ替えが出来ないので必須です。

次に４７～５０行目の「.Add」メソッドで、新たな並べ替えレベルを作成します。Addには以下のパラメータを指定します。

Sort.Addのパラメータ

名前	型	内容
Key	Range	並べ替えの列(必須)
SortOn	Variant	並べ替えのキー
Order	Variant	並べ替え順序
CustomOrder	Variant	ﾕｰｻﾞｰ指定の並べ替え順序
DataOption	Variant	データオプション

図４－７

まず「Key」は必須で、今回はプロシージャが引数で受け取った「列位置Key」を指定します。テーブルでの列の指定方法は「よりみち」で詳細説明しますが、「key:=ThisWorkbook.Sheets(ShName).ListObjects(TableName).ListColumns(key).Range」となります。 次に「SortOn」は並べ替えのキーで、図４－６の並べ替えダイアログでは中央の「並べ替えのキー」に対応します。「キー」という言葉が使われているので「Key」との区別が分かりにくいのですが、指定する値は図４－８となり、通常は「値（xlSortOnValues）」で良さそうです。
今回も「SortOn:=xlSortOnValues」を設定していますが、既定値ですので設定を省略してもOKです。

SortOn

定数	値	内容
xlSortOnValues	0	値（既定値）
xlSortOnCellColor	1	セルの色
xlSortOnFontColor	2	フォントの色
xlSortOnIcon	3	アイコン

図４－８

「Order」では並べ替え順序を設定します。図４－６の並べ替えダイアログでは右側の「順序」に対応します。値としては、図４－９の値を設定し、今回は「新しいものを上に表示」するために「Order:=xlDescending」としました。

Order

定数	値	内容
xlAscending	1	昇順で並べ替え（既定値）
xlDescending	2	降順で並べ替え

図４－９

「CustomOrder」は、ユーザー指定の並べ替え順序を指定します。「CustomOrder:="第1四半期,第2四半期,第3四半期,第4四半期"」のように、カンマ区切りの文字列で指定します。または、ユーザー設定リストにリストを登録（既設定のリストを使用しても可）した後、その登録位置を数値で指定することも可能です。
図４－６の並べ替えダイアログでは右側の「順序」のドロップダウンリストの一番下の「ユーザー設定リスト」から指定するのと同等です。今回は、CustomOrder指定は行いませんでした。

最後の「DataOption」はデータオプションで、テキストを並べ替える方法を指定します。図４－１０の値を設定します。

DataOption

定数	値	内容
xlSortNormal	0	数値とテキストを別々に並べ替え（既定値）
xlSortTextAsNumbers	1	テキストを数値データとして並べ替え

図４－１０

「xlSortTextAsNumbers」は「文字列を数字として」扱いますので、例えば「文字列にした数値（先頭にアポストロフィを付いたもの）」が１から１０まで並んでいるテーブルでは、「文字列を数値」にしますので、ちゃんと「１から１０の順序」で並びます。
一方「xlSortNormal」を指定した場合には「文字列にした数値」は文字列ですので、まず先頭文字列で判断し「1,10,2,3,4,5,6,7,8,9」という順序で並べます。
今回はテーブルの並べ替えを行うのは「DVD番号」のみで、「DVD番号」は数値を文字列として書き込んではいませんので、既定値の「DataOption:=xlSortNormal」としています。もちろん既定値ですので設定を省略してもOKです。

ここまででSortField（図４－６の１行分の条件）への設定が完了です。複数の条件を設定（例えば１列目を第一優先でソートし、２列目を第二優先ソートする等）するには、更にAddメソッドを実行すれば良いのですが、今回は「第一優先のみ」としました。

図４－５のSortオブジェクトの設定可能プロパティの内、次のHeaderを５２行目「.Header = xlYes」で設定しています。
Headerプロパティは「先頭行を見出しとして使用するかどうか」であり、図４－１１の値を設定します。

Header

定数	値	内容
xlGuess	0	Excel が自動的に設定
xlYes	1	見出しとして使用(ﾃｰﾌﾞﾙの場合の既定値)
xlNo	2	見出しとして使用しない(既定値)

図４－１１

Headerに設定する値は、通常のSortの場合は「xlNo」が既定値になるのですが、ListObjectを使用する場合は「xlYes」が既定値となり、xlNoは選択できません。これは、手動で並べ替えを行う際にも、図４－１２のように「先頭行をデータの見出しとして使用する」のチェックを外すことができないことからも分かります。

図４－１２

実際に「Header = xlNo」を設定しようとすると実行時エラーとなります（xlGuessは使えるようです）。
逆に「Header = xlYes」が既定値ですから設定しなくてもOKですが、「マクロ記録」で取得した並べ替えのコードには、このHeaderと後で紹介するOrientationだけは記録されているのでそのまま載せています。なお既定値のプロパティであっても、重要なものは明示的に記した方が良いと思います。

次の「MatchCase」は、大文字と小文字を区別するか否かの設定です。設定値は図４－１３の値です。
特に区別する必要もないため、既定値のままとし、コードを省略しています。

MatchCase

定数	値	内容
-	True	大文字と小文字を区別する
-	False	大文字と小文字を区別しない(既定値)

図４－１３

次の「Orientation」は、並べ替えの方向の設定です。設定値は図４－１４の値です。

Orientation

定数	値	内容
xlSortColumns(又はxlTopToBottom)	1	行単位で並べ替え(既定値)
xlSortRows(又はxlLeftToRight)	2	列単位で並べ替え

図４－１４

このOrientationも、図４－１２のオプション「方向」のところで分かる様に「列単位（xlSortRows(又はxlLeftToRight)）」は選択できず、指定してしまうとエラーが発生します。よって、指定できるのは「行単位」のみですので、指定しなくてもOKです。
今回は、５２行目「.Orientation = xlTopToBottom」と、マクロ記録のままをコード化しています。

最後の「SortMethod」は、日本語を「ふりがなで並べるか、文字コードで並べるか」の設定です。設定値は図４－１５です。

SortMethod

定数	値	内容
xlPinYin	1	日本語をふりがなで並べる(既定値)
xlStroke	2	日本語を文字コードで並べる

図４－１５

例えば、漢字で「青」「赤」「秋」は、「あお」「あか」「あき」と「訓読みで入力する」と「訓読みのふりがな」が振られます（関数PHONETICを使用）。
一方、文字コードは図４－１６のように「訓読みの順番とは異なる」ことが分かります。これは文字コードは音読みの順番で振っているためのようです（全てがこの理論で並ぶ訳では無いようです）。

漢字	訓読み	音読み	文字コード(SJIS)
青	あお　①	セイ　②	0x90c2　②
赤	あか　②	セキ　③	0x90d4　③
秋	あき　③	シュウ　①	0x8f48　①

図４－１６

ですので、訓読みで漢字を入力している場合に「SortMethod = xlPinYin」で並べ替えれば「青」→「赤」→「秋」の順番で並びますし、「SortMethod = xlStroke」で並べ替えれば「秋」→「青」→「赤」の順番で並びます。

但し「青」「赤」「秋」という漢字を「セイ」「セキ」「シュウ」とタイプして入力した場合には、「SortMethod = xlPinYin」で並べ替えても「秋」→「青」→「赤」の順番で並ぶのです。
また「マクロ側からセルに文字を入力する（＝セル値に漢字を設定する）」と、ふりがな（関数PHONETIC）の戻り値が「漢字そのもの」になってしまいます。この状態で「SortMethod = xlPinYin」で並べ替えを行っても「文字コードの順番で並ぶ」結果となります。つまり「音読みで入力した」事になるようです。

今回システムでは、フォームを介して文字列をテーブルに書き込んでいるため、xlPinYinで設定しても結果は「文字コード並び」になってしまうので、設定を省略しました。

以上で、Sortオブジェクトに対するプロパティの設定は完了です。
設定が出来たところで５４行目のSortオブジェクトの「Applyメソッド」で並べ替えを実行します。

図４－４の４７行目で出てきた「ListObjects(TableName).ListColumns(key).Range」のようなListObjectオブジェクトの範囲指定は、従来のセルの指定方法とは勝手が異なりますので、ここで一通り説明します。 テーブルの作成と操作 まず既存の表（例：図４－１７のA1～G8セルの範囲）を手動でテーブルにするには、データ範囲の中の１セル（図４－１７ではA1セル）を選択①し、リボンの「挿入」タブ→「テーブル」グループ→「テーブル」ボタンをクリック②します。 すると、「テーブルの作成」ダイアログが表示されますので、データの範囲と先頭行を見出しとするか否かのチェックを確認し、OKボタン③をクリックします。 図４－１７ ①～③の操作により図４－１８のように、表はテーブル（ListObjectオブジェクト）として扱うことができるようになります。 図４－１８ テーブルは、外見上は見出し行（図４－１８の１行目）が濃い青の背景色＋白色文字で、データ行はまだらの背景色が設定されています。 また各見出しにはフィルター（下向き三角形の印）が自動的に設定され、クリックで表示されるダイアログ（図４－１８の右側）を使って「並べ替え」や「絞り込み」が出来ます。 テーブル範囲の右下角のセルには「テーブルの角」であるマークがあります。テーブル範囲に接触する行・列のセルに値を入力すると、テーブルの範囲は自動的に広がります。 逆に、テーブル範囲の最下段の「データを削除」しても自動的にテーブル範囲が狭まることはありません。データ行を削除し、正しいテーブル範囲を確保するには、図４－１９のように「削除したいデータ行のセルで右クリックし、メニューの中から「削除」→「テーブルの行」を選択するとデータ行が削除され、テーブルの範囲も行方向に縮小します。ちょうど「削除＋上方向にシフト」を選んだような形になります。 図４－１９ 一方マクロ操作で「EntireRow.Delete（行単位で削除）」を使っでもテーブル範囲は縮小できますが、１つのシートに複数のテーブルを置いてある場合には、他のテーブルに影響を与えてしまうため、「EntireRow.Delete」を使用するには注意が必要です。 テーブルの範囲の指定法 テーブルの「見出し＋データ」の部分を指定するには「Range」を使用します。従ってテーブル全体の範囲を示すには、図４－２０のように「ListObjects(テーブル名).Range」となります。（以降の説明ではテーブル名をDVDmgtとしています） 図４－２０ 一方、テーブルの「データのみ」の部分を指定するには、Rangeの代わりに「DataBodyRange」を使用します。テーブルの全データ範囲を示すには、図４－２１のように「ListObjects(テーブル名).DataBodyRange」となります。 図４－２１ テーブルの「見出しのみ」の部分を指定するには「HeaderRowRange」を使用します。テーブルの見出し範囲全てを示すには、図４－２２のように「ListObjects(テーブル名).HeaderRowRange」となります。 図４－２２ テーブルの列・行の指定法 テーブルの列の集合体は「ListColumns」コレクションで表します。その内の１つ（特定の列）を示すには、ListColumnsコレクションの引数に「見出しの文字列」または「テーブルの左側からの列位置」を指定します。 簡単に言えば、図４－２３のExcelシートのコレクションとオブジェクトの関係と同様に、ListColumnsの引数に「見出しの文字列」または「テーブルの左側からの列位置」を指定すれば、列を表すオブジェクトになります。 図４－２３ 例えば図４－２４のように、DvdNo列（＝テーブルとしては１列目）を指定するには「ListObjects(テーブル名).ListColumns("DvdNo").Range」または「ListObjects(テーブル名).ListColumns(1).Range」となります。 図４－２４ ここで、範囲名に「Range」を使用していますので、図４－２０と同様に「見出し＋データ」が範囲に含まれます。 これを「データ部分のみ」の「列範囲」にするには、図４－２１で使用した「DataBodyRange」に置き換え、図４－２５の様に「ListObjects(テーブル名).ListColumns("DvdNo").DataBodyRange」または「ListObjects(テーブル名).ListColumns(1).DataBodyRange」とします。 図４－２５ 一方、行を指定する場合は「行には行見出しが無い」ために「データの先頭からの行位置」を使って、図４－２６のように「ListObjects(テーブル名).ListRows(3).Range」とします。 図４－２６ この際、ListRowオブジェクトに対してのプロパティの中に「DataBodyRange」は存在しません。「Range」でセル範囲を指定します。

４－４．テーブルの絞り込み実行と解除

次に、各列の絞り込みを行うプロシージャが図４－２７です。引数として以下の３つを受け取ります。
　・第一引数「key」：絞り込みを行う「列番号」
　・第二引数「Word」：絞り込みのテキスト
　・第三引数「LK」：第二引数の絞り込みテキストを部分一致検索にする場合は「*」を指定（LKはlikeの略）

58. '==========　⇩(5)　絞り込み実施　============
59. Public Sub TableFilter(key As Integer, Word As String, Optional LK As String = "")
61. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName).Range
62. 　　If Not Word = "" Then
63. 　　　.AutoFilter Field:=key, Criteria1:=LK & Word & LK
64. 　　End If
65. 　End With
66. End Sub

図４－２７

６１行目「With ThisWorkbook.Sheets(ShName).ListObjects(TableName).Range」で、絞り込みの対象を「テーブル全体」とするため、「.Range」を使用しています。
しかしこの絞り込み対象は、見出し行を除く「.DataBodyRange」を使って、対象をデータ部分のみに変更しても問題ありません。それどころか、テーブルの中の１セル（例えば「Sheets(ShName).Range("A1")」でもOKです。
しかしRange("A1")としたのでは、プログラムとして何を対象に絞り込んでいるのかが不明瞭になってしまうので、今回はテーブルのRangeを使っています。

今回システムでは操作ダイアログの各項目が空欄だった時、「データが空のものを選択する」という意味では無く、「その項目では絞り込みを実行しない」を意味することにしています。そのため、６２行目「If Not Word = "" Then」で「空欄の時には６３行目の絞り込みを実行しない」ようにしています。
もし、ダイアログの空欄を「空のものを選択する」と言う意味に用いるならば、６２行目・６４行目は削除して下さい。

６３行目「.AutoFilter Field:=key, Criteria1:=LK & Word & LK」は、AutoFilterメソッドの第一引数Keyの「Field（列位置）」に対して、絞り込みを行っています。絞り込みの内容は「Criteria1:=」の後ろ側に記載します。
ここでは、プロシージャの第二引数Wordの前後に第三引数のLKをくっつけています。

呼び出す側が、例えば「Call TableFilter(2,"相棒","*")」となっていれば、６３行目は「.AutoFilter Field:=2, Criteria1:="*相棒*"」となり、「２列目を『相棒』という文字を含んだ項目で絞り込む（部分一致）」という意味になります。
一方、TableFilterプロシージャの第三引数にはOptionalが付いており省略可ですので、「Call TableFilter(2,"相棒")」とした場合は「.AutoFilter Field:=2, Criteria1:="相棒"」となり、「２列目を、単に『相棒』となっている項目のみで絞り込む（完全一致）」という意味になります。

つまりプロシージャの第三引数は「部分一致検索」のための「*（アスタリスク）」になるのですが、この有無が重要な意味になるのは数値を絞り込む時です。
例えば、１列目に数値が文字列（先頭に'（アポストロフィ）が付いている等）として入っている場合に「Call TableFilter(1,"1","*")」とすると「.AutoFilter Field:=1, Criteria1:="*1*"」となり、絞り込まれるものは「1」「11」「12」・・「21」・・「100」・・と1の付くものが全て抽出されます。
一方、１列目が数値そのものの列の場合には、「.AutoFilter Field:=1, Criteria1:="*1*"」では数値は１つも抽出されません。このことから、今回の６３行目の式では「数値列に部分一致検索は使ってはいけない」ことが分かります。

次に「絞り込みの解除」を行うプロシージャが図４－２８です。１列ずつではなく、全列をまとめて解除する仕様です。

68. '==========　⇩(6)　絞り込み解除　============
69. Public Sub TableFilterOFF()
70. 　Dim i As Long 　　 '←カウンタ変数（列の数）
72. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName).Range
73. 　　For i = 1 To LastCol
74. 　　　.AutoFilter Field:=i
75. 　　Next i
76. 　End With
77. End Sub

図４－２８

７３行目「For i = 1 To LastCol」ではカウンタ変数ｉをテーブルの列数分だけ回しています。この中の「LastCol（図４－２９）」はテーブルの列数を戻してくれる関数です。
７４行目「.AutoFilter Field:=i」で、１列目から順番に絞り込みを解除しています。「AutoFilterメソッド」で「Fieldパラメータ」のみを指定すると、その列の絞り込みを解除することになります（元々絞り込んでいない列については、変化は起こりません）。

４－５．テーブルのサイズ取得

テーブルの列数を取得する関数プロシージャが図４－２９です。

79. '==========　⇩(7)　テーブル列数取得　============
80. Public Function LastCol() As Long
81. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
82. 　　LastCol = .HeaderRowRange.Count
83. 　End With
84. End Function

図４－２９

８２行目「LastCol = .HeaderRowRange.Count」で、テーブルの列数を取得し、関数プロシージャの戻り値にしています。
ここで「HeaderRowRange」は、図４－２２の「見出し部全体」を示します。見出しは１行ですので、そのセル数（Count）が列数に相当することになります。

テーブルのデータ行数を取得する関数プロシージャが図４－３０です。

86. '==========　⇩(8)　データ行数取得　============
87. Public Function LastLrow() As Long
88. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
89. 　　LastLrow = .ListRows.Count
90. 　End With
91. End Function

図４－３０

８９行目「LastLrow = .ListRows.Count」で、データ行数を取得し、関数プロシージャの戻り値にしています。
これは「ListRows」が、図４－２６の各データ行の集合体であることを利用しています。
また、図４－２１の「DataBodyRange（データ範囲全体）」を利用して、「.DataBodyRange.Rows.Count」としても同じことになりそうです。

しかし図４－３１のように、データが１つも無い状態もあります。テーブルを作ったばかりの時とか、データを全て削除してしまった状態です。

図４－３１

図４－３１を見ると、２行目が薄い青色の背景色になっているので「データ行がある」「DataBodyRangeが存在する」ように錯覚しますが、この薄い青色の行は「InsertRowRange」であり「DataBodyRange」ではありません。ですのでInsertRowRangeに関して言えば「ListObjects(〇〇).InsertRowRange.Address = $A$2:$G$2」となりますが、実データ部分については
　・ListObjects(〇〇).ListRows.Count = 0
　・ListObjects(〇〇).DataBodyRange Is Nothing = True
となります。
ですので、８９行目に「.DataBodyRange.Rows.Count」を使ってしまうと、データが無い状態ではエラーが発生してしまいますので、今回８９行目には「.ListRows.Count」を使用しました。

なおデータの絞り込みを行い、見かけ上の行数が少なくても、「データが無くなった訳ではないので、得られるデータ行数は同じ」となります。絞り込み後の見かけの行を取得するには「SpecialCells(xlCellTypeVisible)」などを使用する必要があります。

４－６．列内の最大値を取得

テーブルの数値列で、最大値を取得するのが図４－３２です。引数として「列の見出し名（key）」を受け取ります。
今回システムで新規にデータ登録する際、他のDvd番号と重複しない番号を割り当てるために、この関数プロシージャでDvdNo列の最大値を求め、その値に「+1」した番号を新DVD番号としています。

93. '==========　⇩(9)　列内データの最大値取得　============
94. Public Function TableMax(key As String) As Long
95. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
97. 　　If Not .ListRows.Count = 0 Then
98. 　　　TableMax = WorksheetFunction.Max(.ListColumns(key).DataBodyRange)
99. 　　End If
101. 　End With
102. End Function

図４－３２

９８行目「TableMax = WorksheetFunction.Max(.ListColumns(key).DataBodyRange)」で、ワークシート関数Maxを使い、引数keyで指定した列のデータの最大値を計算し、TableMax関数プロシージャの戻り値にしています。
但し、データが無い（DataBodyRange is Nothing = True）場合にはエラーが出ますので、９７行目「If Not .ListRows.Count = 0 Then」で「データが存在する時」のみ、Maxの計算をしています。
もしデータが存在しない場合は、９８行目の計算をしないため、関数プロシージャの戻り値（９４行目のLong型）の初期値である「ゼロ」が戻ることになります。
また、データ行は存在するが「データが入っていない」場合や、文字列の列の場合は、Maxの計算結果は「ゼロ」となります。

４－７．データの追加・更新と削除

テーブルにデータを新規追加、または既存データを上書き更新するのが図４－３３です。
第一引数（Lrow：ListRange Rowの略）として、データを入れる「テーブルの（見出し行を含めない）データ行位置」を受取ります。また、入れるデータは第二引数（Data）で配列の形で受け取ります。
Lrowの値によりテーブルの何行目にデータを入れるかが決まり、その値が「現テーブルの行数＋１」であれば新規追加となり、「現テーブルの行数以下」であれば、上書き更新となります。

104. '==========　⇩(10)　データの追加・更新　============
105. Public Sub TableDataIn(Lrow As Long, Data() As Variant)
106. 　Dim i As Long 　　 '←カウンタ変数（列の数）
108. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
109. 　　For i = 1 To LastCol
110. 　　　.ListColumns(i).Range(Lrow + 1) = Data(i)
111. 　　Next i
113. 　End With
114. End Sub

図４－３３

１０９行目「For i = 1 To LastCol」で、カウンタ変数ｉをテーブルの列数だけ回しながら処理をします。「LastCol」は図４－２９のLastCol関数プロシージャで、テーブルの列数を取得しています。
１１０行目「.ListColumns(i).Range(Lrow + 1) = Data(i)」では、書き込み位置の内、列位置を「ListColumns(i)」で１つずつ右側に移動させながら、指定行位置「Range(Lrow + 1)」にデータを書きこんでいきます。

まず書き込み位置の他の指定方法としては、「.ListColumns(i).DataBodyRange(Lrow)」や「.Listrows(Lrow).range(i)」などが考えられます。しかし「もしデータが１行も無い」場合には、エラーが発生してしまいます。
ですので、今回はRangeを使って「見出しも含めた縦方向位置」で指示するようにしました。テーブルには必ず見出しが１行存在しますので、＋１をし「.ListColumns(i).Range(Lrow + 1)」としました。

次に、第二引数として受け取った「貼り付けデータ配列」ですが、図４－３４のように、今回テーブルの１列目から順にデータを代入した配列になっています。配列のインデックスもテーブルの列位置と合わせてあります。
ですので、１１０行目の右辺の「 = Data(i)」で、各列にデータが書き込まれることになります。

図４－３４

別な貼り付け方法として、１０９～１１１行目のFor～Nextの代わりに「.ListColumns(1).Range(Lrow + 1).Resize(1, UBound(Data, 1)) = Data」で、一発で貼り付ける方法を試しましたが、「数式を貼り付ける（６列目）」所でエラーが出ました。
エラーの出た貼り付け数式は「"=BITAND([@Genre],$J$1)"」なのですが、どうも[@Genre]の部分が引っ掛かるようです。今のところ良い考えが思い浮かばないため、For～Nextで１つずつ貼り付けています。

テーブルのデータを１行単位で削除するのが図４－３５です。
引数（Lrow：ListRange Rowの略）として、削除データの「テーブルの（見出し行を含めない）データ行位置」を受取ります。

116. '==========　⇩(11)　データの削除　============
117. Public Sub TableDataDel(Lrow As Long)
118. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
119. 　　.ListRows(Lrow).Delete
120. 　End With
121. End Sub

図４－３５

１１９行目「.ListRows(Lrow).Delete」では「Lrow行目のデータを削除」しており、これは図４－１９で説明した手動削除と同等の方法です。
一方、テーブルを使用していない場合に良く使われる削除方法は「EntireRow」を使った行全体を削除する方法で、これを使うとすれば「.ListRows(Lrow).Range.EntireRow.Delete」になります。この方法は、１つのシートにテーブルが１つのみであれば問題はありませんが、複数テーブルがあると削除時に別なテーブルのデータを削除してしまう可能性があるため、注意が必要です。
今回は、複数のテーブルが存在しても問題無いようにするため、１１９行目のコードにしました。

４－８．可視セルデータの配列化

本システムでは、検索時および更新データ・削除データの行位置の取得には、テーブルの絞り込み機能を利用し、絞り込んだデータを配列の形で取得します。その配列データを加工してリスト化したり、更新・削除の行位置を指定したりしています。
その「絞り込んだデータを配列化」するのが、図４－３６のSearchList関数プロシージャです。

123. '==========　⇩(12)　可視セルデータの配列化　============
124. Public Function SearchList() As Variant
125. 　Dim buf As Variant 　　　　 '←戻し値の仮配列
126. 　Dim HeaderRow As Long 　　 '←Headerの行位置
127. 　Dim cnt As Long 　　　　　 '←カウンタ変数（配列の行数）
128. 　Dim i As Long 　　　　　　 '←カウンタ変数（エリアの数）
129. 　Dim j As Long 　　　　　　 '←カウンタ変数（列の数）
130. 　Dim k As Long 　　　　　　 '←カウンタ変数（エリア内の行数）
132. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
133. 　　HeaderRow = .HeaderRowRange.row
135. 　　On Error Resume Next
137. 　　With .DataBodyRange.SpecialCells(xlCellTypeVisible)
138. 　　　ReDim buf(1 To .Count / LastCol, 0 To LastCol)
139. 　　　cnt = 1
141. 　　　For i = 1 To .Areas.Count
142. 　　　　For k = 1 To .Areas(i).Rows.Count
143. 　　　　　buf(cnt, 0) = .Areas(i).Cells(k, 1).row - HeaderRow
144. 　　　　　For j = 1 To UBound(buf, 2)
145. 　　　　　　buf(cnt, j) = .Areas(i).Cells(k, j).Value
146. 　　　　　Next j
147. 　　　　　cnt = cnt + 1
148. 　　　　Next k
149. 　　　Next i
151. 　　End With
152. 　　On Error GoTo 0
153. 　End With
155. 　SearchList = buf
157. End Function

図４－３６

まず、１３３行目「HeaderRow = .HeaderRowRange.row」で、テーブルの見出し行の行絶対位置を取得します。この変数HeaderRowは、１４３行目で「データ行絶対位置を相対位置に変換するための補正」として使用されるので、あらかじめ変数に代入しておき定数的に使用するものです。
１３７行目「With .DataBodyRange.SpecialCells(xlCellTypeVisible)」では、テーブルを絞り込んだ後の「可視セル」範囲に基準を合わせています。
しかし、絞り込んだ時に「可視データが１つも無くなってしまった」ときにはエラーが発生してしまいます。ですので１３５行目「On Error Resume Next」で、エラーが発生した時には１３７～１５１行目までを無視し、１５５行目「SearchList = buf」で関数の戻り値をbufの初期値である「Empty」にし、プロシージャを終了します。

「可視データが１行以上存在する」場合には、１３８行目「ReDim buf(1 To .Count / LastCol, 0 To LastCol)」で、戻し値となる配列bufのサイズを決めます。
配列bufには、図４－３７のように「絞り込んだデータ」を代入するため、サイズとしては「行方向＝絞り込んだ行数／列方向＝テーブルの列数」が必要です。それに加えて、絞り込んだデータの「テーブル上の行位置」をゼロ列目に代入できるよう、列サイズのインデックスは「ゼロ」からスタートさせています。

図４－３７

このサイズを決める際、列方向は「LastCol（図４－２９）」で得ることができます。
一方、行方向は簡単にはいきません。それは図４－３８のように「絞り込んだデータが『複数のエリア』に渡っている」場合があるためです。行数を取得するには「Areasの数」を調べ、その１つ１つの「各Areaの行数」を調べて足し合わせることで、可視行数が取得できます。

図４－３８

上記の「Areasの数＋各Areaの行数」の方法では無く、今回は図４－３９の方法で行数を得ることにしました。
「絞り込んだデータの個数」は、「ListObjects(〇〇).DataBodyRange.SpecialCells(xlCellTypeVisible).Count」で取得できるので、それを「テーブルの列数（今回はLastColで取得）」で割って行数を求めるのです。

図４－３９

これをコードにしたのが、１３８行目「ReDim buf(1 To .Count / LastCol, 0 To LastCol)」となります。セル総数を列数で割っている部分が「.Count / LastCol」です。また列方向がゼロ始まりなのは、ゼロ列目に「テーブルのDataBodyRangeとしての行位置」を代入するためです。

１３９行目「cnt = 1」は、配列bufの行位置を示しています。１行分の代入が完了した時点の１４７行目で、「cnt = cnt + 1」と配列bufの行位置を１つ下に移動させています。

配列bufにセルの値を代入しているのが１４１～１４９行目になります。ここでは、セルの行位置は「Areasの数＋各Areaの行数」で指定するしかありません。
１４１行目「For i = 1 To .Areas.Count」では、エリアの数だけカウンタ変数ｉを回します。図４－３８ではエリア数は「２」となります。
また１４２行目「For k = 1 To .Areas(i).Rows.Count」で、各エリア内の行数だけカウンタ変数ｋを回します。

これで目的の行位置に辿り着きましたので、まず１４３行目「buf(cnt, 0) = .Areas(i).Cells(k, 1).row - HeaderRow」で、配列bufのゼロ列目（buf(cnt, 0)）に「テーブルのDataBodyRangeとしての行位置」を代入しています。
「テーブルのDataBodyRangeとしての行位置」は、直接得られそうに無いので、今回はまず「ListObjects(〇〇).DataBodyRange.SpecialCells(xlCellTypeVisible).Areas(i).Cells(k, 1).row」と１列目のセルの「絶対行位置（.row）」を取得します。そして、テーブルの見出しの絶対行位置（HeaderRow：１３３行目で取得した値）との差から、テーブルのデータの行相対位置（DataBodyRangeの行位置）を計算し、配列bufのゼロ列目に代入します。

図４－４０

１４４行目「For j = 1 To UBound(buf, 2)」は、カウンタ変数ｊを配列の列数（＝テーブルの列数）だけ回しています。「For j = 1 To LastCol」でも良いですが、その都度「関数LastColを呼び出して計算」する必要がありますので、今回は配列の列サイズから回す回数を定めています。

１４５行目「buf(cnt, j) = .Areas(i).Cells(k, j).Value」では、絞り込んだ可視セルの値を配列bufに代入しています。この中で「.Cells(k, j)」は、「ListObjects(〇〇).DataBodyRange.SpecialCells(xlCellTypeVisible).Areas(i)」に対しての相対的セル位置になります。

１４７行目「cnt = cnt + 1」では、配列bufに代入する行位置を一つ下に移動させています。

配列bufへの値およびデータ行の行位置を代入完了したら、１５５行目「SearchList = buf」で関数プロシージャSearchListの戻り値に配列bufを設定します。

４－９．列内の重複しないデータ一覧の作成

操作ダイアログのコンボボックスのリストを作成するために、「指定列のデータを重複せず且つ昇順で並べた配列データ」を取得するのが図４－４１です。引数として、指定列の列名（Col）を受け取ります。

159. '==========　⇩(13)　列内の重複しないデータ一覧の作成　============
160. Public Function uniList(Col As String) As Variant
161. 　Dim buf1 As Variant 　　　　 '←列の全データの配列
162. 　Dim buf2 As Variant 　　　　 '←重複せず且つ昇順並びのデータの配列
163. 　Dim SL As Object 　　　　　 '←SortedListオブジェクト
164. 　Dim i As Long 　　　　　　　 '←カウンタ変数（行位置、SortedList個数）
166. 　With ThisWorkbook.Sheets(ShName).ListObjects(TableName)
167. 　　If .ListRows.Count = 0 Then Exit Function
169. 　　buf1 = .ListColumns(Col).Range
170. 　End With
172. 　Set SL = CreateObject("System.Collections.SortedList")
174. 　For i = 2 To UBound(buf1, 1)
175. 　　If Not IsEmpty(buf1(i, 1)) And Not IsNull(buf1(i, 1)) Then
176. 　　　If SL.containskey(CStr(buf1(i, 1))) = False Then
177. 　　　　SL.Add CStr(buf1(i, 1)), 0
178. 　　　End If
179. 　　End If
180. 　Next i
182. 　If SL.Count = 0 Then Exit Function
183. 　ReDim buf2(1 To SL.Count)
185. 　For i = 1 To SL.Count
186. 　　buf2(i) = SL.getkey(i - 1)
187. 　Next i
189. 　uniList = buf2
190. 　Set SL = Nothing
191. End Function

図４－４１

１６７行目「If .ListRows.Count = 0 Then Exit Function」では、作ったばかりのテーブルや全データを削除してしまったテーブルなど、データ行数がゼロ（.ListRows.Count = 0）の場合は、関数を抜け出します。
関数の戻り値には何も入れていませんので、Variant型の初期値であるEmptyが戻ることになります。

１６９行目「buf1 = .ListColumns(Col).Range」では、引数で指定された列名（Col）の「見出しを含めた全データ」を配列buf1に代入します。
ここで「見出し」を含めているのは大きな理由があります。１６９行目をもし「buf1 =.ListColumns(Col).DataBodyRange」とした場合を考えます。
１６７行目でデータが無い場合は外していますので、ここでは「１行以上のデータ」があるはずです。２行以上のデータがあれば、buf1には２つ以上のデータが入り「配列」となりますが、もし１行のみのデータだった場合、buf1には「１つのデータ」しか入らず「buf1が配列にならない」のです。
１行と２行以上でbub1が配列になるかならないか分かれてしまうと、その後の処理が非常に面倒になりますので、１行データの時でも必ず配列となるように、見出しも含めたデータを取得しています。もちろん見出しデータは使用しませんので、データ取り出しは配列の２番目以降となります。

１７２行目「Set SL = CreateObject("System.Collections.SortedList")」では、キーで自動的に並べ替えが実行されるSortedListオブジェクトを生成しています。

１７４～１８０行目では、配列buf1（指定列の見出しを含めた全データ）から重複を避けてSortedListデータのキーに登録していきます。
１７４行目「For i = 2 To UBound(buf1, 1)」で、カウンタ変数ｉを「２」から始めているのは、「見出し」のデータを除いた全データに対して処理をしているためです。
１７５行目「If Not IsEmpty(buf1(i, 1)) And Not IsNull(buf1(i, 1)) Then」は、データが「空欄（Empty）」の場合にはリスト化する意味が無いので、Notで省いています。
またSortedListデータのKeyに「Null」が入るとエラーが発生するため、「Not IsNull(buf1(i, 1))」で除去しています。但し、セル範囲の値を配列に代入するだけではNullは発生しないようなので、今回は必須では無いと思います。（データベースからデータをまとめて取り出した時にはNullが発生する場合があるので、注意が必要です。）

１７６行目「If SL.containskey(CStr(buf1(i, 1))) = False Then」は、処理するbuf1のデータが既にSortedListデータのキーに存在しない場合１７７行目の処理に進みます。
１７７行目「SL.Add CStr(buf1(i, 1)), 0」で、SortedListデータへの登録を行います。

１７６・１７７行目で、単に「buf1(i, 1)」とはせず「CStr関数」で文字列に変換しているのは、SortedListのキーとして「文字列と数値」の両方を登録するためです。
SortedListにキーを登録する際、例えば既に文字列が登録されている時に数値を登録しようとすると、エラーが発生します。逆も同じです。つまり「文字列と数値」を混在してキー登録は出来ません。
ですので「数字は文字列として登録」するために「CStr関数」で変更をしています。

ちなみに、文字列に変換された数値は、その後コンボボックスのリストデータになりますが、リストデータの状態では「文字列」の状態です。これが選択されて、ワークシートに貼り付けられた時に、Excelが解釈して数値として保存されます。ですので、文字列として数値を内部で扱うだけでしたら、問題はありません。（その数値を使って、計算をする時には注意が必要です）

また１７７行目の「SL.Add CStr(buf1(i, 1)), 0」では、値として「ゼロ」を登録しています。今回は単一列のリストを作るのが目的ですので、Keyだけが必要なので値は必要ありません。しかし値もセットで登録しないといけないので、適当なものを登録しています。「１」でも「"ABC"」でも構いません。

重複せず、また空欄も排除したSortedListデータへの登録が完了したら、１８２行目「If SL.Count = 0 Then Exit Function」で、SortedListデータが何個あるかを数え、ゼロ（全部空欄だった）の場合は関数プロシージャを抜け出します。
１個以上存在する場合には、１８３行目「ReDim buf2(1 To SL.Count)」で、変数buf2に配列サイズを与えます。

１８５～１８７行目では、SortedListデータからKeyを１つずつ取り出し、配列buf2に代入していきます。
１８６行目「buf2(i) = SL.getkey(i - 1)」の右辺が取り出すキーになります。その順番はゼロ始まりのため「－１」をしていますが、SortedListオブジェクトですのでデータは既に「昇順」に並んでいますので、配列buf2も昇順にデータが並ぶことになります。

最後に１８９行目「uniList = buf2」で、配列buf2を関数プロシージャの戻り値に設定します。ここまでの間に「Exit Function」した場合（データ行が無い、空欄しか無い）は、Variant型の初期値であるEmptyが戻ります。

４－１０．短時間だけ表示するメッセージボックス等

今回システムは、操作ダイアログ上の「登録」「更新」「削除」ボタンでデータが操作できますが、ユーザーに対し「データが変わった」ことをメッセージとして伝える必要があると考え、短い時間の間だけメッセージボックスを表示することを考えました。
４種類の方法を試してみましたが、最も良さそうな「MessageBoxTimeoutA関数」を今回システムには盛り込んであります。その他の方法についても、説明します。

４－１０－１．MessageBoxTimeoutAを使用

Windows APIの「MessageBoxTimeoutA関数」を使用する方法が、図４－４２です。プロシージャの引数として、表示する文字列（Word）を受取ります。

193. '==========　⇩(14)　MessageBoxTimeoutAを使用　============
194. Public Sub myMsgBox(Word As String)
195. 　MessageBoxTimeoutA Application.Hwnd, Word, "", vbOKOnly, 0, 500
196. End Sub

図４－４２

この「MessageBoxTimeoutA」を使用するには、Windows APIの参照宣言が必要です。そのため図４－１の１０～１８行目で宣言を行っています。
MessageBoxTimeoutAには、参照宣言からも分かる様に６つの引数を渡します。

引数	型	内容
Hwnd	Long	ｵｰﾅｰｳｨﾝﾄﾞｳのﾊﾝﾄﾞﾙ
lpText	String	表示する文字列
lpCaption	String	ﾒｯｾｰｼﾞﾎﾞｯｸｽのﾀｲﾄﾙ文字
uType	VbMsgBoxStyle	表示するﾎﾞﾀﾝとｱｲｺﾝ
wLanguageID	Long	文字列の言語
dwMilliseconds	Long	自動で閉じるまでのﾐﾘ秒

図４－４３

第一引数Hwndに設定する「オーナーウィンドウのハンドル」には、オーナーをExcelとするため「Application.Hwnd」を指定しています。このHwndに「0（Nullの意味）」を指定した場合には、メッセージボックスはオーナーウィンドウを持たないことになります。
オーナーを持つか持たないかの現象面の違いですが、「オーナーを持つ（Application.Hwndを設定）」場合は「Modelessでフォームを起動した感じ」になります。例えばメッセージが表示されている最中でも操作ダイアログ側にFocusを移すことは可能ですが、Excel上にはメッセージは残っています。
一方「オーナーを持たない（ゼロを設定）」場合は、「Excelとは異なるアプリのメッセージが表示されている感じ」になります。メッセージの表示中に操作ダイアログにFocusを移すと、メッセージはExcelの裏側に隠れてしまい、見失ってしまいます。
今回のシステムでは、「一瞬のメッセージ表示」なのでどちらでもOKと思いますが、メッセージがExcelの裏側に回らない「オーナーを持つ」設定にしました。

第二引数は「メッセージボックスに表示する文字列」ですので、引数のWordを設定します。
第三引数は「タイトル文字」ですが、今回は不要と考え「""（長さゼロの文字列）」としました。
第四引数は「表示するボタンとアイコン」で、今回は「OKボタン」のみが適切と考え「vbOKOnly」を設定しました。
第五引数の「文字列の言語」ですが、詳細は分かりませんでしたが「通常はゼロ」を設定するようです。他サイトの説明コードでも全てゼロ設定でした。たぶん設定値ゼロは「WindowsまたはExcelの言語と一緒」という意味なのかもしれません。

第六引数の「ミリ秒」は、色々試して0.5秒くらいが感覚的に良さそうなので「500（ミリ秒）」の設定としました。

４－１０－２．Popupメソッドを使用

Windows Script Host（WSH）の１つであるPopupメソッドを使ったメッセージ表示が図４－４４です。
引数としてメッセージ（Word）を受け取ります。

198. '==========　⇩(15)　Popupメソッドを使用　============
199. Public Sub myMsgBox2(Word As String)
200. 　Dim WSH As Object
202. 　Set WSH = CreateObject("Wscript.Shell")
203. 　WSH.Popup Word, 1
204. 　Set WSH = Nothing
205. End Sub

図４－４４

Popupメソッドでは、２０２行目「Set WSH = CreateObject("Wscript.Shell")」で、WSHへの参照を設定します。
Popupメソッドには４つの引数を設定します。

引数	型	内容
Text	String	表示する文字列(必須)
SecondsToWait	Variant	自動で閉じるまでの秒数
Title	Variant	ﾒｯｾｰｼﾞﾎﾞｯｸｽのﾀｲﾄﾙ文字
Type	Variant	表示するﾎﾞﾀﾝとｱｲｺﾝ

図４－４５

図４－４２のMessageBoxTimeoutAと大きく異なるのは「閉じるまでの時間」で、「秒数」を秒単位で指定します。しかも「１以上の整数」である必要があり、それ以外の１未満や小数のある値（0、0.5、1.5など）を指定すると「OKボタンを押さない限り開きっぱなし」になります。
なお例えば「1.0」を指定（コードを書き込むと「1#（Double型のマーク）」に自動変換される）しても「小数点有り」と判断されるようで、自動的には閉じてくれなくなります。

また例えば「１秒」を指定しても、１秒キッカリで閉じる訳では無く、私のPCで試してみたところ図４－４６のように、かなり多めの時間開いています。

設定秒数	平均(n=20)	範囲
1	1.52	1.49～1.58
2	4.85	4.13～7.00
3	5.28	5.13～7.84

図４－４６

今回システムでは「一瞬だけメッセージを表示」したかったので、この時間はあまりにも長すぎるため、Popupメソッドは採用しませんでした。

４－１０－３．ステータスバーに表示

メッセージボックスではありませんが「ステータスバー」にコメントを出す方法が、図４－４７です。

207. '==========　⇩(16)　ステータスバーに表示　============
208. Public Sub myMsgBox3(Word As String)
209. 　Dim T As Single 　　　　 '←表示開始時の時刻
211. 　Application.StatusBar = Word
213. 　T = Timer()
214. 　Do While (T + 0.5) > Timer()
215. 　　DoEvents: DoEvents
216. 　Loop
218. 　Application.StatusBar = False
219. End Sub

図４－４７

表示のイメージとしては、図４－４８のようにExcel左下欄外のステータスバーの表示文字列が変わる方法です。

図４－４８

２１１行目「Application.StatusBar = Word」で、引数である表示文字列Wordをステータスバーに表示します。
２１３行目「T = Timer()」で現時刻を変数Tに代入します。Timer関数はSingle型の「１秒未満」も表してくれます。
２１４～２１６行目のDo～Loopで「0.5秒」待った後、２１８行目「Application.StatusBar = False」でExcel標準に戻します。

但し、ユーザーによっては「少しでも画面を広く使いたい」等の理由で、「ステータスバーを非表示」にしている場合もあります（オプションの設定等では表示・非表示の切り替えは出来ませんが、VBAからは非表示設定が可能）。その場合には、まず表示・非表示の状態を取得した後「Application.DisplayStatusBar = True」でステータスバーを表示にし、ステータスバーに文字列を表示させます。終わったら表示・非表示状態を元に戻す、と言うような処理が必要になると思います。

この方法は非常に簡単なのがメリットなのですが、操作ダイアログに視点がある状態でExcelの左下欄外の文字列が変化しても、あまり目立たないというデメリットがあります。そのため、この方法も採用しませんでした。

４－１０－４．フォームを使用

メッセージ表示専用のフォームを使うのが、以下の方法になります。
まずUserForm2に、図４－４９のようなフォームをデザインします。フォーム上にLabel1が１つあるだけのものです。

図４－４９

そのフォームモジュールの「Activateイベントプロシージャ」が、図４－５０です。

221. '==========　⇩(17)　フォームを使用　============
222. Private Sub UserForm_Activate()
223. 　Dim T As Single 　　　　 '←表示開始時の時刻
225. 　T = Timer()
226. 　Do While (T + 0.5) > Timer()
227. 　　DoEvents: DoEvents
228. 　Loop
230. 　Unload Me
231. End Sub

図４－５０

Activateイベントは、フォームが表示された直後に発生しますので、表示された時刻を２２５行目「T = Timer()」で取得し、それから0.5秒経過するまで２２６～２２８行目のDo～Loopで待つことで、メッセージボックスの表示時間を制御しています。
時間が経過したら、２３０行目「Unload Me」で、自分であるUserForm2を閉じます。

メッセージ用のユーザーフォームを起動するのが、図４－５１です。引数として表示する文字列Wordを受け取ります。

233. '==========　⇩(18)　フォームの起動プロシージャ　============
234. Public Sub myMsgBox4(Word As String)
235. 　UserForm2.Label1.Caption = Word
236. 　UserForm2.Show
237. End Sub

図４－５１

２３５行目「UserForm2.Label1.Caption = Word」で、フォーム上のLabel１に表示文字列（Word）を書き込みます。
次に２３６行目「UserForm2.Show」でダイアログを起動させています。
ダイアログが起動している0.5秒間は、Excelでの全ての作業が停止することになります。

今回試した中では「MessageBoxTimeoutA」と並んで「フォーム使用」が機能的には良かったです。フォームを作ったりするのが少し面倒ですが、分かり易さ・説明し易さから言えばフォームを使うのも良いと思います。

４－１１．テーブルを新規作成

操作ダイアログに関係したプロシージャではありませんが、テーブルを作るコードを図４－５２で紹介します。

239. '==========　⇩(19)　テーブルの作成　============
240. Private Sub MakeTable()
241. 　Const LTtable As String = "A1" 　　 '←テーブルの左上セル位置
242. 　Dim TitleArray As Variant 　　　　 '←列名の配列
243. 　TitleArray = Array("DvdNo", "Title", "Series", "CreateD", "Genre", "Gbit", "Storage")
244. 　Dim L As ListObject 　　　　　　 '←作成したテーブル
245. 　
246. 　With ThisWorkbook.Worksheets(ShName)
247. 　　.Range(LTtable).Resize(1, UBound(TitleArray, 1) + 1) = TitleArray
248. 　　Set L = .ListObjects.Add(SourceType:=xlSrcRange, _
249. 　　　　　　　　　　　　　　　Source:=.Range(LTtable).CurrentRegion, _
250. 　　　　　　　　　　　　　　　XlListObjectHasHeaders:=xlYes)
251. 　　L.Name = TableName
252. 　　L.ListColumns("CreateD").Range.NumberFormatLocal = "yyyy/mm/dd"
253. 　End With
255. End Sub

図４－５２

２４１行目「Const LTtable As String = "A1"」は、テーブルの左上位置（LTはLeft・Topの意）を示す定数です。
２４３行目「TitleArray = Array("DvdNo", "Title", "Series", "CreateD", "Genre", "Gbit", "Storage")」では、テーブルの列名（＝見出し文字）を配列化しています。今回は７列分を作成します。

テーブルを作成するシートは、図４－１の２行目で宣言したシート名「ShName」であるため、２４６行目は「With ThisWorkbook.Worksheets(ShName)」としています。
他のプロシージャ内でも「テーブルを指定」する際、「ThisWorkbook.Sheets(ShName).ListObjects(TableName)」などと「ThisWorkbook」を付けています。これはもちろん、操作するテーブルは「ThisWorkbook（＝マクロが書かれているブック）」に存在する、ということを表していますが、特に「Excelアドインにシステムを登録」し「別のブック上で操作ダイアログを動かす」時には必須になります。
しかし、MakeTableプロシージャは別ブックをアクティブにした状態では起動しないと思いますので、２４６行目のThisWorkbookは無くてもOKと思います。

２４７行目「.Range(LTtable).Resize(1, UBound(TitleArray, 1) + 1) = TitleArray」で、２４３行目で作成した列名の一次元配列を２４１行目で定数宣言した場所に貼り付けます。一次元ですから横方向に貼り付けられます。

２４８～２５０行目でListObjectを追加（.Add）します。
Addメソッドには、図４－５３のようにパラメータが６個あります。また、1つ目の「SourceType」パラメータは、図４－５４の中から選びます

パラメータ	型	内容
SourceType	XlListObjectSourceType	ソースの種類（図４－５４）
Source	Variant	xlSrcExternal：ｿｰｽへの接続を指定する文字列配列 xlSrcRange：ｿｰｽを表すRangeｵﾌﾞｼﾞｪｸﾄ xlSrcXml：？ xlSrcQuery：ODBC又はOLEDB接続文字列 xlSrcModel：WorkbookConnectionｵﾌﾞｼﾞｪｸﾄ
LinkSource	Boolean	外部ﾃﾞｰﾀｿｰｽをListObjectにﾘﾝｸするか否か
XlListObjectHasHeaders	Variant	列ﾗﾍﾞﾙの有無（図４－５５）
Destination	Variant	移動先のｾﾙ位置
TableStyleName	String	ﾃｰﾌﾞﾙ書式の名前

図４－５３

定数	値	内容
xlSrcExternal	0	外部ﾃﾞｰﾀｿｰｽ
xlSrcRange	1	Range範囲
xlSrcXml	2	XML
xlSrcQuery	3	クエリ
xlSrcModel	4	PowerPivotﾓﾃﾞﾙ

図４－５４

２４８行目では「SourceType」パラメータとして「xlSrcRange」を指定しています。これは２４７行目で列名配列を貼り付けたRange範囲を「テーブル」とするためです。（正確にはテーブルの見出しだけですが）
２４９行目では「Source」パラメータとして、２４７行目で列名を貼り付けたRange範囲を指定しています。２４７行目と同じように「.Range(LTtable).Resize(1, UBound(TitleArray, 1) + 1)」としても良いです。
２５０行目の「XlListObjectHasHeaders」パラメータは、図４－５５の中から「見出し有り」の「xlYes」を指定しています。

列ﾗﾍﾞﾙの有無

定数	値	内容
xlGuess	0	Excelが判断
xlYes	1	見出し有り
xlNo	2	見出し無し（既定値）

図４－５５

その他のパラメータの内、「LinkSource」は、SourceType:=xlSrcRangeを指定した場合は、必ず省略します。
また「Destination」パラメータは、SourceType:=xlSrcRangeを指定した場合は、何を設定しても無視されます。
「TableStyleName」パラメータは、省略すると図４－５６のような「横縞模様」の書式になります。今回は省略しています。

この２４８～２５０行目のAddメソッドでテーブルは作成されました。作られたテーブルは、２４８行目「Set L = ・・・」となってるので、Lというオブジェクトとして扱えることになります。
その作られたテーブルに名前を付けるのが２５１行目「L.Name = TableName」です。「TableName」は図４－１の３行目で「DVDmgt」という名前で定数宣言されています。

２５２行目「L.ListColumns("CreateD").Range.NumberFormatLocal = "yyyy/mm/dd"」は、「作成日」列（テーブルの４列目）の書式を「"yyyy/mm/dd"」にしています。
これはテーブルの見掛けだけでは無く、データをやり取りする操作ダイアログのTextBox2の書式と合わせることで「作成日での検索を可能」にさせています。ちなみにTextBox2の書式は、図５－１９の４５３行目で「"yyyy/mm/dd"」と同じ書式に整えています。

このテーブル製作プロシージャによって作られるテーブルは、図４－５６になります。

図４－５６

５．操作ダイアログ（UserForm1）

５－１．フォーム上のコントロールの配置

フォーム上のコントロールの配置は図５－１のようにしました。

図５－１

入力および検索項目は以下の通りです。

項目	列名	コントロール	注意点
DVD番号	DvdNo	TextBox3	枚数を考慮し4桁以上
タイトル	Title	TextBox1	ある程度長い文字列が入る幅に
シリーズ	Series	ComboBox1
作成日	CreateD	TextBox2	"yyyy/mm/dd"が入る幅に
ジャンル	Genre	CheckBox1～12	文字列の長さを考慮して配置
保管場所	Storage	ComboBox2

図５－２

また、検索結果を表示するListBox1をダイアログの下段に配置しています。
ボタンは「検索」「クリア」「登録／更新」「削除」「今日」「終了」の６つを配置しています。削除ボタンは誤クリックを防ぐために小さ目にしています。
その他の、説明用Label、及びFrameは、適当な位置に配置し、そのCaptionは配置時に書き込んでいます。

５－２．フォームモジュール

５－２－１．初期設定

図４－２（DVDmgtStartプロシージャ）の２３行目からUserForm1が呼び出され、起動時に最初に実行されるのが図５－３のInitializeイベントです。

257. '==========　⇩(20)　フォーム起動時の設定　============
258. Private Sub UserForm_Initialize()
259. 　Call MakeCKB 　　 '←CheckBoxのWidth設定＋Caption書込み
261. 　Me.CommandButton1.Caption = "検索"
262. 　Me.CommandButton2.Caption = "クリア"
263. 　Me.CommandButton3.Caption = "登録/更新"
264. 　Me.CommandButton4.Caption = "削除"
265. 　Me.CommandButton6.Caption = "終了"
267. 　Me.ListBox1.ColumnCount = LastCol + 1
268. 　Me.ListBox1.ColumnWidths = "0;30;125;70;55;0;0;60" 　 '←ColumnWidths合計=340,ListBox.Width=343
270. 　Me.TextBox2.IMEMode = fmIMEModeDisable
271. 　Me.TextBox3.IMEMode = fmIMEModeDisable
273. 　Call MakeComboList(Me.ComboBox1, "Series") 　　 '←シリーズBoxのリスト作成
274. 　Call MakeComboList(Me.ComboBox2, "Storage") 　　 '←保管場所Boxのリスト作成
276. End Sub

図５－３

２５９行目「Call MakeCKB」は図５－３０を呼出し、１２個のCheckBoxの幅を（文字列が全て見えるように）設定し、図４－３のジャンル文字列をCaptionに書き込んでいます。
フォーム配置時に設定しても良いのですが、DVD以外の管理にも展開する際には、後からマクロ設定した方が使い易いと考えました。

２６１～２６５行目は、CommandButtonの表面文字列を書き込んでいます。

２６７行目「Me.ListBox1.ColumnCount = LastCol + 1」は、検索結果を表示するListBox1の列数を設定しています。LastColはテーブルの列数ですので、今回は「７＋１」で８列のListBoxにしています。基本的には、テーブルの列数とListBoxの列数を合わせているのですが、＋１しているのは「ListBoxのゼロ列目に『データの行相対位置』を入れる」ためです。

この「データの行相対位置」をListBoxに設定しないと、ListBoxのデータ行を選択した際に得られるデータが、リストとして表示されているDVD番号などに限られます。テーブルのその行に対して処理を行うためには、DVD番号などを使って「データの行相対位置」を調べてからの処理になるため、二度手間になります。

２６８行目「Me.ListBox1.ColumnWidths = "0;30;125;70;55;0;0;60"」ではListBoxの各列の幅を指定しています。ListBoxに入るデータは先頭から「データの行相対位置」「DVD番号」「タイトル」「シリーズ」「作成日」「ジャンル」「ジャンル計算結果」「保管場所」ですが、リストとして表示するのは「DVD番号」「タイトル」「シリーズ」「作成日」「保管場所」の５つとしました。
それに合わせ、表示しない列は幅「ゼロ」とし、表示列は適当な幅としました。
一方、ListBox1の表示幅（.Width）は今回343ポイントでしたので、各列幅の合計は、その値から「－３」の340ポイントになるように調整しました。この関係性については「先入先出の入出庫管理システム」を参照下さい。

２７０行目「Me.TextBox2.IMEMode = fmIMEModeDisable」は、作成日であるTextBox2の入力時は「半角の数字他」に制限します。そのため、まずは「半角」のみにするため「日本語IME」をOFFの設定にしています。
２７１行目「Me.TextBox3.IMEMode = fmIMEModeDisable」のTextBox3（DVD番号）も同じ理由で、同じ設定をしています。

２７３行目「Call MakeComboList(Me.ComboBox1, "Series")」は、図５－１２のMakeComboListプロシージャを呼出しています。引数を２個渡し、テーブルの「Series（シリーズ）」列のデータを重複無く、「Me.ComboBox1」のリストに設定する　指示を出しています。
２７４行目「Call MakeComboList(Me.ComboBox2, "Storage")」も同様に、テーブルの「Storage（保管場所）」列のデータを重複無く、「Me.ComboBox2」のリストに設定しています。

５－２－２．検索ボタン

操作ダイアログ上の「検索ボタン」をクリックした時に呼び出されるのが図５－４です。ボタン操作によるマクロの流れは「プログラムの流れ」の図３－２の表のようになります。コードと併せて見比べると、分かり易いかと思います。

278. '==========　⇩(21)　検索　============
279. Private Sub CommandButton1_Click()
281. 　Call CondMemory 　　 '←検索条件の記憶
282. 　Call MakeListBox 　　 '←検索実施＋リスト化
283. End Sub

図５－４

２８１行目「Call CondMemory」で、図５－３７のCondMemoryを呼び出し、操作ダイアログ上段の現在の検索条件を記憶します。図３－２の検索の①に相当します。
ここで記憶した内容は、下段リストボックスの項目をクリックしてから操作をする「更新」「削除」を実行した後に復元します。復元する理由は、「下段リストボックスの項目をクリックすると、クリックした項目データが上段に反映され、元の検索条件が上書きされてしまう」ためです。粗い検索条件で複数項目をリスト化し、リストの項目を次から次へと処理することを想定し、このような仕様にしました。

２８２行目「Call MakeListBox」で、図５－２２のMakeListBoxを呼び出し、上段の各項目に入力した値を検索条件として、ダイアログ下段のリストボックスに検索結果を表示します。図３－２の検索の②に相当します。

５－２－３．クリアボタン

ダイアログ上の「クリア」ボタンをクリックした時が図５－５です。

285. '==========　⇩(22)　クリア　============
286. Private Sub CommandButton2_Click()
288. 　Me.TextBox1.Value = ""
289. 　Me.TextBox2.Value = ""
290. 　Me.TextBox3.Value = ""
291. 　Me.ComboBox1.ListIndex = -1
292. 　Me.ComboBox2.ListIndex = -1
293. 　Me.ListBox1.Clear
295. 　Call Bit2CKB(0)
297. 　Me.TextBox3.Enabled = True
299. End Sub

図５－５

２８８～２９３行目は、各TextBoxを空にし、各ComboBoxを未選択状態（ListIndex = -1）にし、、ListBox1をクリアしています。
２９５行目「Call Bit2CKB(0)」は、図５－３４の「Bit2CKB」プロシージャを呼び出しています。Bit2CKBは、１２個のCheckBoxのうち「引数で指定されたCheckBoxにレ点をつける」マクロです。「ゼロ」を指定することで「全てのCheckBoxのレ点を外す」ようにしています。

２９７行目「Me.TextBox3.Enabled = True」は、DVD番号欄のTextBox3をユーザー操作可能（Enabled = True）な状態に変更しています。
TextBox3は、検索後リストボックスの項目を選択すると同時に、ユーザー操作を不可（Enabled = False）にしていますが、これは「リスト選択状態は、選択した項目を「更新」または「削除」するため、DVD番号が変更できない」ようにするためです。
クリアボタンでリストボックスもクリアする訳ですから、TextBox3も検索可能なように操作可能な状態に変更しています。

５－２－４．登録／更新ボタン

操作ダイアログの「登録／更新」ボタン（CommandButton3）をクリックした時に呼び出されるのが図５－６です。

301. '==========　⇩(23)　登録／更新　============
302. Private Sub CommandButton3_Click()
304. 　Dim DvdNum As Long 　　　　 '←選択しているDVD番号
305. 　Dim RowNum As Long 　　　　 '←選択しているテーブル行相対位置
307. 　If Trim(Me.TextBox1.Text) = "" Then
308. 　　MsgBox "タイトルは入力必須項目です"
309. 　　Me.TextBox1 = ""
310. 　　Me.TextBox1.SetFocus
311. 　　Exit Sub
312. 　End If
314. 　If Me.ListBox1.ListIndex = -1 Then 　　 '←'新規登録状態
316. 　　Call CondMemory
318. 　　Call TableDataIn(LastLrow + 1, InData(TableMax("DvdNo") + 1))
319. 　　Call myMsgBox("新規登録しました")
321. 　Else 　　　　 '←更新状態
322. 　　RowNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
323. 　　DvdNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
325. 　　Call TableDataIn(RowNum, InData(DvdNum))
326. 　　Call myMsgBox("データ更新しました")
327. 　　Me.TextBox3.Enabled = True
329. 　End If
331. 　Call MakeComboList(Me.ComboBox1, "Series")
332. 　Call MakeComboList(Me.ComboBox2, "Storage")
333. 　Call CondRestore
334. 　Call MakeListBox 　　 '←上段の項目で検索実施、リスト化
336. End Sub

図５－６

本システムでは「DVDのタイトルは必須」としています。ですので３０７～３１２行目で、タイトルが入力されているか否かのチェックを行います。
３０７行目「If Trim(Me.TextBox1.Text) = "" Then」は、タイトル入力部に「スペースだけを入力」した時も逃さないように「Trim関数」を使って「先頭と末尾の半角／全角のスペース」を削除してから、空白（長さゼロの文字列）か否かをチェックしています。
空白の場合は、３０８行目「MsgBox "タイトルは入力必須項目です"」でコメントを出し、３０９行目「Me.TextBox1 = ""」で余分なスペースを取り除き、３１０行目「Me.TextBox1.SetFocus」でフォーカスをタイトル部に戻してから、３１１行目「Exit Sub」で「登録／更新処理を中止」しています。

タイトルが空白で無かった場合は、３１４行目「If Me.ListBox1.ListIndex = -1 Then」で「リストボックスが選択状態か否か」を確認します。
下段のリストボックスが選択されていれば、その情報詳細が上段の各項目に反映された状態です。その状態から「登録／更新」ボタンをクリックしているのですから、「更新」ということになります。
一方リストボックスが選択されていないのであれば、新たに各項目に入力した、またはリストをダブルクリックしてデータをコピーしたことになりますので、「新規登録」を表しています。

ということで、リストボックスが選択状態では無い（＝新規登録）場合は３１６～３１９行目を実行し、選択状態（＝更新）の場合は３２２～３２７行目を実行します。

５－２－４－１．新規登録処理

３１６行目「Call CondMemory」では、ダイアログ上段の入力内容を記憶させています。これは、図３－２の新規登録の①に相当します。

次に３１８行目「Call TableDataIn(LastLrow + 1, InData(TableMax("DvdNo") + 1))」で、標準モジュールのTableDataIn（図４－３３）を呼び出し、テーブルにデータを「新規データ」として追加します。
TableDataInプロシージャへは２つの引数を渡します。第一引数は「書き込むテーブルの行相対位置」です。
「新規データ」ですから、図５－７のように「テーブルの下端（LastLrow）の１つ下（＋１）」にデータを書きこむことで、自動的にテーブルの範囲も広がり、新規データがテーブルに取り込まれます。

図５－７

第二引数は「書き込む内容」で、操作ダイアログ上段の各項目を配列にしたものを渡します。
項目を配列化するのは「InData関数プロシージャ（図５－３６）」の役目ですが、このInData関数は「新規登録」だけでなく「更新」でも使用します。「新規登録」と「更新」では、上段の各項目を配列化することは同じなのですが、「DVD番号」だけは「既存には無い番号」か「既存の番号」と異なります。その異なるDVD番号を引数として渡すことで、関数プロシージャの共通化を図っています。

ここでは新規登録データ用のDVD番号を求めるため、まずテーブルの指定列の最大値を求める「TableMax関数（図４－３２）」を使い、引数に「計算する列名」である「"DvdNo"」を指定して、現在のDVD番号の列の中の最大値を取得します。そして得られた「DVD番号の最大値」に「＋１」した値を「DVD番号」としてInData関数に渡すことで、書き込み内容の配列を完成させ、TableDataInプロシージャの第二引数に渡しています。

TableDataInプロシージャは、図５－７のように「テーブルの下端の１つ下（第一引数）」に、「操作ダイアログで入力したデータ＋新DVD番号（第二引数）」の書き込み処理を行い、その後の３１９行目「Call myMsgBox("新規登録しました")」で、短い時間（今回設定＝0.5秒間）だけメッセージを出し、データが追加されたことをユーザーに伝えます。

その後は「更新」と同じコードを実行することになりますが、
３３１行目「Call MakeComboList(Me.ComboBox1, "Series")」で、シリーズ（ComboBox1）のリストを作り直し、３３２行目「Call MakeComboList(Me.ComboBox2, "Storage")」で、保管場所（ComboBox2）のリストを作り直しています。
作り直す理由は、新規の場合は「シリーズや保管場所が新しく追加」されている可能性がありますし、また更新の場合には「シリーズや保管場所の追加・変更・削除」が考えられるためです。

次に３３３行目「Call CondRestore」で、ダイアログ上段を元の入力状態に復元します。新規入力の場合は復元は必要なさそうに思えますが、実は入力時にComboBoxで選択・入力していても、３３１～３３２行目でComboBoxのリストを作り直してしまいますので、新規追加処理後は、ComboBoxが未選択状態になっています。
ですので、ComboBoxのリストの作り直し（３３１～３３２行目）の後に、３１６行目で記憶しておいたデータの復元が必要となります。
また「更新」時には、「検索」時に記憶しておいた内容を上段項目に復元します。

最後に３３４行目「Call MakeListBox」で、「ダイアログ上段の項目」を使ったデータ検索を行い、その結果を下段のリストボックスに表示させています。

５－２－４－２．更新処理

一方「更新」の時には、３２２行目「RowNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)」で、下段リストボックスの選択項目の、「ゼロ列目」の「テーブルの行相対位置」を変数RowNumに代入します。
また、３２３行目「DvdNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)」では、「１列目」の「DVD番号」を変数DvdNumに代入します。

３２５行目「Call TableDataIn(RowNum, InData(DvdNum))」では、TableDataInプロシージャ（図４－３３）を呼び出し、テーブルにデータを「更新データ」として上書きします。
TableDataInプロシージャへの第一引数は「書き込むテーブルの行相対位置」で、３２２行目で取得したRowNumとなります。
また、第二引数の「書き込む内容」は、InData関数プロシージャを通じて配列化しますが、その中のDVD番号には３２３行目で取得したDvdNumを使います。

本来は「DVD番号は変えずに、その他のデータを更新」すれば良いのですが、テーブルへのデータ記入のプログラムを新規登録と共通にするため、InData関数プロシージャの引数にDVD番号を指定する方法を考えました。

３２６行目「Call myMsgBox("データ更新しました")」で、一瞬だけメッセージを表示させたのち、３２７行目「Me.TextBox3.Enabled = True」で、ダイアログ上段の「DVD番号」のTextBox3をユーザー入力が可能な状態にしています。
この「DVD番号」欄は、検索後リストボックスの項目を選択した際に、図５－２７の５１９行目「Me.TextBox3.Enabled = False」で入力不可状態にしていますが、データ更新が完了した際には「リストボックスは未選択状態」になっていますので、TextBox3を入力可能状態に変更しています。

その後の３３１～３３４行目のコードは「新規登録時」と同じです。
３３１～３３２行目で２つのコンボボックスのリストを作り直し、３３３行目で「検索」時に記憶しておいた内容を上段項目に復元し、３３４行目で「上段の項目（＝一番最近の検索条件）」を元にデータ検索・リスト化を行うことで、一つ前の検索リストが復元され、都度検索をやり直す事無く、複数行の検索データでの連続した更新作業が可能となります。

５－２－５．削除ボタン

操作ダイアログの「削除」ボタンをクリックした時に呼び出されるのが、図５－８です。

338. '==========　⇩(24)　削除　============
339. Private Sub CommandButton4_Click()
340. 　Dim DvdNum As Long 　　　　 '←選択しているDVD番号
341. 　Dim RowNum As Long 　　　　 '←選択しているテーブル行相対位置
342. 　Dim Title As String 　　　　　 '←選択しているタイトル
343. 　Dim Msg As String 　　　　　 '←ﾒｯｾｰｼﾞ表示する文字列
345. 　If Me.ListBox1.ListIndex = -1 Then
346. 　　MsgBox "リストから削除する項目を選択後に、削除処理をして下さい。"
347. 　　Exit Sub
348. 　End If
350. 　RowNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
351. 　DvdNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
352. 　Title = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
353. 　Msg = "No=" & DvdNum & "｢" & Title & "｣" & vbNewLine & "を削除して良いですか？"
355. 　If MsgBox(Msg, vbYesNo) = vbYes Then
356. 　　Call TableDataDel(RowNum)
357. 　　Call myMsgBox("データ削除しました")
359. 　　Me.TextBox3.Enabled = True
361. 　　Call MakeComboList(Me.ComboBox1, "Series")
362. 　　Call MakeComboList(Me.ComboBox2, "Storage")
364. 　　Call CondRestore
365. 　　Call MakeListBox
366. 　End If
368. End Sub

図５－８

データを削除するには「削除する項目を指定」する必要があります。今回システムでは「下段のリストボックスで選択した項目が削除対象」としています。
３４５行目は「リストボックスの項目を選択しているか否か」を「If Me.ListBox1.ListIndex = -1 Then」で調べ、選択状態でなかったら、３４６行目「MsgBox "リストから削除する項目を選択後に、削除処理をして下さい。"」でコメントを出し、３４７行目「Exit Sub」で「削除工程を中止」させています。

３５０行目「RowNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)」は、下段リストボックスの選択項目の、「ゼロ列目」の「テーブルの行相対位置」を変数RowNumに代入します。
３５１行目「DvdNum = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)」は、「１列目」の「DVD番号」を変数DvdNumに代入します。
３５２行目「Title = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)」は、「２列目」の「タイトル」を変数Titleに代入します。
３５３行目「Msg = "No=" & DvdNum & "｢" & Title & "｣" & vbNewLine & "を削除して良いですか？"」は、上記「DVD番号」と「タイトル」を組合せて、メッセージボックスに表示する文字列を組み立てています。

３５５行目「If MsgBox(Msg, vbYesNo) = vbYes Then」では、メッセージボックスの文字列として３５３行目で組み立てた文字列を使い、またボタンとして「はい」「いいえ」の２つのボタン（vbYesNo）を設定します。メッセージは、図２－１４のような感じに表示されます。
メッセージボックスの「はい」（= vbYes）を押すと、３５６～３６５行目の削除処理を行います。なお「いいえ」を押した時はIf文を抜け、削除処理を中止します。

３５６行目「Call TableDataDel(RowNum)」では、標準モジュールのTableDataDelプロシージャ（図４－３５）を呼出し、引数で渡した「テーブルの行相対位置（RowNum）」のデータを削除します。
削除後は、３５７行目「Call myMsgBox("データ削除しました")」で、一瞬だけコメントを出します。

３５９行目「Me.TextBox3.Enabled = True」では、リストボックスの選択項目が削除されたのですから、リスト選択されてない状態になり、DVD番号欄のTextBox3をユーザー入力可の状態にしています。

項目が１つ削除されたので、コンボボックスのリストが変更になる可能性があります。ですので３６１行目「Call MakeComboList(Me.ComboBox1, "Series")」で、ComboBox1（シリーズ）のリストを作り直し、３６２行目「Call MakeComboList(Me.ComboBox2, "Storage")」で、ComboBox2（保管場所）のリストを作り直しています。

３６４行目「Call CondRestore」では、ダイアログ上段の各項目を検索時の状態に復元し、その項目を使って３６５行目「Call MakeListBox」でデータ検索をし、下段のリストボックスに表示させています。
前回検索時に複数行の検索結果が得られているのであれば、削除後は１つ減った項目がリスト表示されるはずです。前回１行だけが検索され、それを削除したのであれば、リストボックスは空になるはずです。

５－２－６．日付／終了ボタン

ダイアログの作成日欄（TextBox2）の右側にある「今日」というボタンをクリックすると図５－９が起動します。

370. '==========　⇩(25)　今日の日付ボタン　============
371. Private Sub CommandButton5_Click()
372. 　Me.TextBox2.Text = Format(Date, "YYYY/MM/DD")
373. End Sub

図５－９

３７２行目「Me.TextBox2.Text = Format(Date, "YYYY/MM/DD")」は、作成日欄（TextBox2）に「YYYY/MM/DD」の書式で「今日の日付」を入力します。
なお今回システムでは、正しい日付か否かを、TextBox2の「Exitイベントプロシージャ（図５－１９）」でチェックをしています。ですので作成日欄に日付では無い数字等を入力後、この「今日ボタン（CommandButton5）」を押しても、ボタンのClickイベントよりも前にTextBox2のExitイベントが発生してしまいますので、「『今日ボタン』をクリックしているのに、作成日欄に今日の日付が入らない」という現象になります。

ダイアログの「終了ボタン」をクリックしたときに呼び出されるのが、図５－１０です。

375. '==========　⇩(26)　終了ボタン　============
376. Private Sub CommandButton6_Click()
377. 　Unload Me
378. End Sub

図５－１０

３７７行目「Unload Me」でダイアログを閉じています。
こちらも、作成日欄に日付では無い数字等を入力後「終了ボタン（CommandButton6）」を押しても、ボタンのClickイベントよりも前にTextBox2のExitイベントが発生してしまいますので、「『終了』をクリックしているのに、ダイアログが閉じない」という現象になります。

ダイアログと閉じる最中、およびダイアログの右上×印で閉じようとした時に発生するイベントが図５－１１です。

380. '==========　⇩(27)　ダイアログを閉じる　============
381. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
382. 　Me.TextBox2 = ""
383. End Sub

図５－１１

３８２行目「Me.TextBox2 = ""」は、作成日欄（TextBox2）を空にしています。
ダイアログ右上×印で閉じる場合は、TextBox2のExitイベントよりも前にこのQueryCloseイベントが発生します。ですので、万一作成日欄に日付以外の数字等が入っていても、まずここで作成日欄を空にしてくれるため、Exitイベントでの日付チェックで引っ掛からなくなり、素直にダイアログが終了できます。

５－２－７．コンボボックスの作成・処理

起動時（図５－３）、及びデータに変更を加えた時（図５－６、図５－８）に呼び出され、今回２箇所のComboBoxのリストを作成するのが図５－１２です。
第一引数（CB）として「リストを作成するComboBoxコントロール」、第二引数として「テーブルの列名」を受け取ります。
なお今回システムのコンボボックスは、データが１列だけのリストで、且つ一番上に「（新規）」という項目を必ず置きます。「（新規）」をクリックするとInputBoxが起動し「新規項目を追加」できる仕様にしています。

385. '==========　⇩(28)　コンボボックスのリスト作成　============
386. Private Sub MakeComboList(CB As ComboBox, Col As String)
387. 　Dim buf As Variant 　　　　 '←指定列の重複の無いデータ配列
388. 　Dim i As Long 　　　　　　 '←カウンタ変数（リストの数）
390. 　CB.Clear
391. 　CB.AddItem "（新規）"
393. 　buf = uniList(Col)
394. 　If IsEmpty(buf) Then Exit Sub
396. 　For i = 1 To UBound(buf, 1)
397. 　　CB.AddItem buf(i)
398. 　Next i
400. End Sub

図５－１２

３９０行目「CB.Clear」は、例えば「UserForm1.ComboBox1.Clear」と同じですので、「対象のコンボボックスのリストを空」にしています。
３９１行目「CB.AddItem "（新規）"」は、まず１番目の項目として「（新規）」という項目をリストにします。

３９３行目「buf = uniList(Col)」は、図４－４１を呼出し、テーブルから「指定列名（引数であるCol）」の重複の無いデータ配列を取得し、変数bufに代入します。
３９４行目「If IsEmpty(buf) Then Exit Sub」では、もしデータが１つも無くEmptyが戻ってきた時には、対象コンボボックスのリスト作成を終了します。この時すでに「（新規）」はリストにAddItemされていますので、「（新規）」のみのリストとなります。

３９６～３９８行目のFor～Nextで、テーブルからの重複無いデータ配列を全てリストに追加していきます。
３９６行目「For i = 1 To UBound(buf, 1)」で、カウンタ変数ｉをデータ配列bufの行数分だけ回します。なお、uniList（図４－４１）から戻してくる配列のインデックスは、１から始めています。
３９７行目「CB.AddItem buf(i)」では、データ配列bufのデータを順にリストに追加しています。

シリーズ用のComboBox1のリストを選択（正確には、リストの選択を変更）した場合に発生するChangeイベントプロシージャが図５－１３です。

402. '==========　⇩(29)　ComboBox1のリストを選択した場合　============
403. Private Sub ComboBox1_Change()
404. 　Call ComboBoxChange(Me.ComboBox1, "シリーズ名")
405. End Sub

図５－１３

４０４行目「Call ComboBoxChange(Me.ComboBox1, "シリーズ名")」では、ComboBoxChangeプロシージャ（図５－１５）を呼び出しています。第一引数には「操作するコントロール（ここでは、自コントロール＝ComboBox1）」を、第二引数には「項目名」を文字列で渡します。第二引数は、コンボボックスに間違った入力をしようとした時に出される「コメント」の言葉となります。

保管場所用のComboBox2のリストを選択した場合に発生するChangeイベントプロシージャが図５－１４です。

407. '==========　⇩(30)　ComboBox2のリストを選択した場合　============
408. Private Sub ComboBox2_Change()
409. 　Call ComboBoxChange(Me.ComboBox2, "保管場所")
410. End Sub

図５－１４

４０９行目「Call ComboBoxChange(Me.ComboBox2, "保管場所")」もCombBox1と同様にComboBoxChangeプロシージャを呼び出します。ComboBox2は保管場所用ですので、第二引数は「保管場所」とします。

ComboBox1（図５－１３）、及びComboBox2（図５－１４）のChangeイベントプロシージャから呼び出されるのが図５－１５です。
第一引数として「処理をするコンボボックスコントロール」、第二引数として「コメントに使うコンボボックスの項目名」を受取ります。

412. '==========　⇩(31)　コンボボックスへの新規項目追加　============
413. Private Sub ComboBoxChange(CB As ComboBox, Word As String)
414. 　Dim Ans As String 　　　　　　 '←InputBoxからの戻り値
416. 　If CB.ListIndex = 0 Then
417. 　　Ans = InputBox("新しい" & Word & "を入力して下さい")
419. 　　If StrPtr(Ans) = 0 Then
420. 　　　CB.ListIndex = -1
421. 　　ElseIf Trim(Ans) = "" Then
422. 　　　MsgBox Word & "が無効です"
423. 　　　CB.ListIndex = -1
424. 　　Else
425. 　　　CB.AddItem Trim(Ans)
426. 　　　CB.ListIndex = CB.ListCount - 1
427. 　　End If
428. 　End If
429. End Sub

図５－１５

４１６行目「If CB.ListIndex = 0 Then」で、ユーザーがComboBoxのリストの１番上の項目（リストはインデックス＝ゼロから始まる）を選択した時のみ、４１７～４２７行目を実行します。「リストの１番上の項目」は、図５－１２の３９１行目で最初にリスト設定した「（新規）」の項目になります。
それ以外の選択時には何も実行せずに、普通のComboBoxの動作になります。

１番上の項目「（新規）」を選択した時は、まず４１７行目「Ans = InputBox("新しい" & Word & "を入力して下さい")」でInputBox（図２－４右）を表示します。
InputBoxには、操作する場所が３箇所（「OKボタン」「キャンセルボタン」「ダイアログの右上×印」）ありますが、「キャンセルボタン」および「ダイアログの右上×印」の場合には「値ゼロの文字列」を戻して来ます。
一方「OKボタン」の場合は、入力テキストボックスに入力した値が戻ってきます。

４１９～４２７行目のIf～End If文で、その戻り値を仕訳けるのですが、仕訳ける順番には注意が必要です。
ユーザー操作によってInputBoxの戻り値を判定した結果がどうなるか　を図５－１６に整理しました。
ここでは、戻り値を「Ans」とし、表中の記号が「〇はTrue、×はFalse」を表しています。

		Ans=""	Trim(Ans)=""	StrPtr(Ans)=0
①	何も入力せずにOK	〇	〇	×
②	ｽﾍﾟｰｽを入れOK	×	〇	×
③	文字列を入れOK	×	×	×
④	ｷｬﾝｾﾙで閉じる	〇	〇	〇
⑤	右上×印で閉じる	〇	〇	〇

図５－１６

イミディエイトウィンドウ等で表示されるAnsの値を見ると、①④⑤は全て「""」となっており見分けがつかない（Ans=""）のですが、「StrPtr関数」を使うと①と④⑤を仕訳けることが出来ます。
ですので、まず④⑤を分離するために、４１９行目「If StrPtr(Ans) = 0 Then」で仕訳けをします。ユーザーが「キャンセルボタン」や「ダイアログの右上×印」をクリックする理由は、「思った項目と違うのを選択してしまった」「気が変わった」等だと思いますので、特にメッセージは出さずに４２０行目「CB.ListIndex = -1」でComboBoxを未選択状態にしています。

次に①②をまとめて４２１行目「ElseIf Trim(Ans) = "" Then」で仕訳けています。①②は、ユーザーが「操作を間違えている」「意味の無いスペースを登録しようとしている」等と判断できるので、４２２行目「MsgBox Word & "が無効です"」でメッセージを出し、４２３行目「CB.ListIndex = -1」でComboBoxを未選択状態にしています。

最後に残った（４２４行目「Else」）のが③ですので、４２５行目「CB.AddItem Trim(Ans)」でリストの一番最後にInputBoxで入力した項目を追加し、４２６行目「CB.ListIndex = CB.ListCount - 1」で、その項目を選択状態にします。

５－２－８．数値用テキストボックスの処理

作成日用のTextBox2は、図５－３の２７０行目で「日本語IMEをOFF」にしているため、一応半角のみを受け付けるようになっています。（「一応」というのは、キーボードから入力した場合は制限できるが、コピペされると何の文字列でも許してしまうと言う意味です。このコピペにも対応しようとするのであれば「先入先出の入出庫管理システム」を参照下さい。）

しかし日付を入力するのに「A」や「B」などのアルファベットは不要ですし、「&」のような記号も使いません。日付で使うのは（＝Excelが日付と読み取ってくれるのは）、「2021/10/20」とか「2021-10-20」のように「数字」と「/」「-」だけです。
ですので、作成日用のTextBox2に入力できる文字種を「数字」「/」「-」に制限するのが図５－１７です。

431. '==========　⇩(32)　作成日欄の文字種制限　============
432. Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
433. 　If (Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9") And _
434. 　　　　(Chr(KeyAscii) <> "/" And Chr(KeyAscii) <> "-") Then
435. 　　KeyAscii = 0
436. 　End If
437. End Sub

図５－１７

このKeyPressイベントでは、キーボードで押したキーを引数KeyAsciiとして受け取ります。そのKeyAsciiの種類を調べ、通して良いキーであれば何もせず、通していけないキーの場合は「キーを無効」にするのが今回の手法です。

４３３～４３４行目は、以下の３つの式を「And」でつなげたものです。
　① (Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9")
　② Chr(KeyAscii) <> "/"
　③ Chr(KeyAscii) <> "-"
横軸をキーコードにして３つの式を図で示してみると図５－１８のようになります。

図５－１８

薄い青の部分は式が成立する範囲です。Andでつなげると言う事は「全ての式が成立する範囲」となりますので、①②③が全て薄い青である「濃い青の範囲」となり、目的とする「数字」「/」「-」以外の時に成立する式となります。
「数字」「/」「-」以外の時に実行されるのが、４３５行目「KeyAscii = 0」で、キーが無効になります。

作成日用のTextBox2は、入力された値が「日付」で無いと後から検索なども出来なくなってしまいます。日付か否かのチェックのタイミングとして「キー入力の都度、日付の並びに従っているかチェック」「入力欄を出る時にチェック」「登録／更新ボタンをクリックした時にチェック」など、色々なやり方があると思います。
今回は「日付欄を出る時」に、ちゃんと「日付として入力されたか」をチェックし、もし「日付で無かったら日付欄を抜け出せない」ようにしました。

具体的には、Exitイベント（TextBox2から他のコントロールに移動する時に発生）を使用した図５－１９になります。引数の「Cancel」にTrueを設定すると「ExitイベントがCancel（＝移動しない）」します。

439. '==========　⇩(33)　作成日欄の日付チェック　============
440. Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
441. 　If Trim(Me.TextBox2.Text) = "" Then
443. 　　Me.TextBox2.Text = ""
445. 　Else
447. 　　If Not IsDate(Me.TextBox2.Text) Then
448. 　　　MsgBox "日付ではありません。修正して下さい。"
449. 　　　Cancel = True
450. 　　　Me.TextBox2.SelStart = 0
451. 　　　Me.TextBox2.SelLength = Len(Me.TextBox2.Value)
452. 　　Else
453. 　　　Me.TextBox2.Text = Format(Me.TextBox2.Text, "YYYY/MM/DD")
454. 　　End If
456. 　End If
457. End Sub

図５－１９

４４１行目「If Trim(Me.TextBox2.Text) = "" Then」で、TextBox2が「空」または「スペースのみ」の場合は、４４３行目「Me.TextBox2.Text = ""」でTextBox2を「空」にして、Exitイベントプロシージャを抜け出します。ExitイベントをCancelせずに抜け出しますので、他のコントロールに移動できる（例：ボタンをクリック出来る）ことになります。
なお、TextBox2にキーボードから「スペース」は入力できませんので、コピペされた場合を想定しています。

その他（スペース以外の文字列がTextBox2に入力されている）の場合は、４４７～４５４行目を実行することになります。
４４７行目「If Not IsDate(Me.TextBox2.Text) Then」は、TextBox2の文字列が日付として成立しているかを確かめ、成立していなかった時に４４８～４５１行目を実行します。
TextBox2の文字列が日付では無い場合、４４８行目「MsgBox "日付ではありません。修正して下さい。"」でメッセージを出し、４４９行目「Cancel = True」で「TextBox2からの移動を中止」させます。

４５０～４５１行目は図５－２０の右上のように、ユーザーが入力した文字列を「選択状態」にして「間違えている所を強調」しています。この設定が無い場合には、図５－２０の右下のように「文字列の末尾にカーソル」がある状態になります。

図５－２０

４５０行目「Me.TextBox2.SelStart = 0」は、TextBox2の文字列の「ゼロ」文字目から選択状態にしています。
次に４５１行目「Me.TextBox2.SelLength = Len(Me.TextBox2.Value)」では、TextBox2の「全ての文字列の文字数分」を選択状態にします。
なお、４５０行目・４５１行目のどちらか一方が欠けても、文字列は選択状態になりません。

このシステムでは、ExitイベントをCancel（４４９行目）した状態から「文字列を選択（４５０～４５１行目）」していますので、TextBox2の文字列が選択状態になります。しかし、他のコントロールにFocusが有る状態から「TextBox2の文字列を選択」のコードを実行しても何も起こりません。
今回は必要ありませんが、もしその様な状況の時は、まず「Me.TextBox2.Focus」でFocusをTextBox2に移してから「TextBox2の文字列を選択」する必要があります。前述した「登録／更新ボタンをクリックした時に日付をチェック」した場合には、まずCommandButtonからTextBox2にFocusを移しておく必要があります。

TextBox2の文字列が「日付として成立」している時には、４５３行目「Me.TextBox2.Text = Format(Me.TextBox2.Text, "YYYY/MM/DD")」で、「TextBox2の文字」を「"YYYY/MM/DD"」のフォーマットでTextBox2に書き込んでいます。
フォーマットを整えているのは、テーブルに書き込むデータとして日付形式を整えているのでは無く、検索でのフォーマットを合わせるためです。
また別な目的としては、例えば「4/12」とするところを間違えて「41/2」と入力してしまった場合は、「2041/02/01」と変換されるため間違えに気付くのを期待しています。

テーブルに書き込むデータとしてここでフォーマットを整えなくても、テーブル側のフォーマットは図４－５２の２５２行目で整えていますので、書き込みには問題は出ないと思います。 しかし、この作成日欄は「検索」時にも使用します。もし検索する側と検索される側のフォーマットが異なっていると、「検索文字列とテーブル側データの文字列が違うためヒットしない」ことになります。テーブル側のフォーマットとTextBox2側のフォーマットを合わせる事は必須です。

DVD番号欄（TextBox3）の入力文字種を制限するのが、図５－２１です。

459. '==========　⇩(34)　DVD番号欄の文字種制限　============
460. Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
461. 　If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
462. 　　KeyAscii = 0
463. 　End If
464. End Sub

図５－２１

ほとんど図５－１７と同じですが、キーの種類を調べる４６１行目「If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then」だけが異なります。
DVD番号は、今回システムでは「正の整数」としていますので、入力して良いのは「０～９」のキーのみです。
そのため、４６１行目の条件式のみにしています。

５－２－９．リストボックスの作成・処理

検索が実行された時、および新規登録・更新・削除が実行された後に呼び出されるのが、図５－２２です。
このプロシージャは、操作ダイアログ上段の各項目の値を調べ、各値により絞り込まれたデータをダイアログ下段のリストボックスに表示するものです。

466. '==========　⇩(35)　リストボックスの作成　============
467. Private Sub MakeListBox()
468. 　Dim buf As Variant 　　　　 '←テーブルの絞込データの配列
469. 　Dim ckb As Long 　　　　　　 '←CheckBox12個の選択状態
470. 　Dim i As Long 　　　　　　　 '←カウンタ変数（絞込データの行数）
471. 　Dim j As Long 　　　　　　　 '←カウンタ変数（テーブルの列数）
473. 　Application.ScreenUpdating = False
475. 　　Call TableFilterOFF
476. 　　Call TableSort("DvdNo")
477. 　　Call TableFilter(1, Me.TextBox3.Text)
478. 　　Call TableFilter(2, Trim(Me.TextBox1.Text), "*")
479. 　　Call TableFilter(3, Trim(Me.ComboBox1.Text), "*")
480. 　　Call TableFilter(4, Me.TextBox2.Text)
481. 　　Call TableFilter(7, Trim(Me.ComboBox2.Text))
483. 　　ckb = CKB2Bit
484. 　　If Not ckb = 0 Then
485. 　　　ThisWorkbook.Sheets(ShName).Range(SearchGenre) = ckb
486. 　　　Call TableFilter(6, ">0")
487. 　　End If
489. 　　buf = SearchList
491. 　　Call TableFilterOFF
493. 　　Me.ListBox1.Clear
494. 　　If Not IsEmpty(buf) Then
495. 　　　For i = 1 To UBound(buf, 1)
496. 　　　　Me.ListBox1.AddItem ""
497. 　　　　For j = 0 To LastCol
498. 　　　　　Me.ListBox1.List(i - 1, j) = buf(i, j)
499. 　　　　Next j
500. 　　　Next i
501. 　　End If
503. 　Application.ScreenUpdating = True
505. End Sub

図５－２２

４７３行目「Application.ScreenUpdating = False」と５０３行目「Application.ScreenUpdating = True」で囲まれた範囲は、Excelの画面更新を中止しています。これは、テーブルの絞り込みとフィルター解除による画面ちらつきを抑えるためです。但し画面更新中止は、テーブルが見える状態の時に有効で、Excelアドインとして動かしている場合は、役に立っていないと思われます。

４７５行目「Call TableFilterOFF」では図４－２８を呼出し、テーブル全列のフィルター解除をしています。
これは絞込を開始する前に、どこかの列で絞り込みがされていると、絞込結果が誤ったものになるからです。一応今回システムの中では、絞込を行った後には「必ずTableFilterOFFの呼出し」によりフィルター解除をしていますが、念を入れて実行前にも解除を行っています。

次に４７６行目「Call TableSort("DvdNo")」で、テーブルの１列目（DvdNo列）で「降順」に並べ直しています。
これにより「新しい項目がリストボックスの上側に表示」されるようになります。

「各列の絞り込み」は、４７７～４８７行目で行います。
絞込には「完全一致」「前方一致」「後方一致」「部分一致」等の方法がありますので、各列にたいしてどの方法にするかを図５－２３に整理しました。

列	列名	ｺﾝﾄﾛｰﾙ	値	絞込方法
1	DvdNo	TextBox3	正の整数	完全一致
2	Title	TextBox1	(長い)文字列	部分一致
3	Series	ComboBox1	(長い)文字列	部分一致
4	CreateD	TextBox2	yyyy/mm/ddの日付	完全一致
5	Genre	CheckBox1～12	12ｼﾞｬﾝﾙのON-OFF	指定ｼﾞｬﾝﾙを含むものを抽出
7	Storage	ComboBox2	文字列?、整数?	完全一致

図５－２３

この中で「Storage（保管場所）」を「部分一致」が良いのか「完全一致」が良いのか迷いました。
例えば保管場所として「A-01」「B-02」などと分類するのでしたら、検索時に「A-」の付く場所単位で探す事があるでしょうから、部分一致の方が適している気がします。しかし「100」「101」など数字のみを使った場合は、例えば「10」で部分一致を期待して検索しても抽出されません（数値としてテーブルに保存されている場合）。また「101」とフルに指定しても、部分一致の場合には何も抽出されません。
一方、完全一致で検索するのであれば、数値の形で保管場所データが保存されていても抽出はされます。

ですので本来は、各列をどんな番号・文字列の体系にするのかをまず決め、その上で検出方法を考えることが重要です。今回は保管場所を文字列・数値どちらで設定しても、フルワードで検索さえすれば探し出せる「完全一致」としました。
「よりみち」に、文字列・数値混在の場合の改善策を考えましたので、そちらも参考にして下さい。

図５－２３の表に基づいて、４７７～４８１行目の絞込を実施します。呼び出すTableFilterプロシージャの第一引数にはテーブルの列番号、第二引数は、絞り込み文字列、第三引数（省略可）は、部分一致の「*（アスタリスク）」を指定します。
テーブルの2・3列目は図５－２３で「部分一致」としましたので「"*"」を第三引数に指定します。各項目の絞り込み指定は以下のようなコードになります。

列名	コード内容	ｺｰﾄﾞ番号
DvdNo	Call TableFilter(1, Me.TextBox3.Text)	４７７行目
Title	Call TableFilter(2, Trim(Me.TextBox1.Text), "*")	４７８行目
Series	Call TableFilter(3, Trim(Me.ComboBox1.Text), "*")	４７９行目
CreateD	Call TableFilter(4, Me.TextBox2.Text)	４８０行目
Storage	Call TableFilter(7, Trim(Me.ComboBox2.Text))	４８１行目

残っているのはテーブル5列目の「ジャンル」です。ジャンルの絞り込みをしているのは４８３～４８７行目です。
４８３行目「ckb = CKB2Bit」は、図５－３１のCKB2Bit関数プロシージャを呼び出し「１２個のCheckBoxのON-OFF状態」を数値に変換しています。
変換の方法は図５－２４のように、チェックボックスがONの時は「１」、OFFの時は「ゼロ」とし、１２個のチェックボックスの状態を順番に並べます。すると「１とゼロの並び」となりますので、それを「２進数」として読み取り１０進数の形にします。この操作により、１２個のチェックボックスの状態を１つの数値に置き換えることができます。

図５－２４

４８３行目では、操作ダイアログ上の１２個のCheckBoxの状態が、変数ckbに代入されます。
４８４行目「If Not ckb = 0 Then」では、そのckbの値がゼロで無い時に、４８５～４８６行目を実行します。「ckbの値がゼロ」とは「全てのCheckBoxがOFFの時」ですので、「１つ以上のCheckBoxにレ点が付いている時」ということになります。

一方テーブル側の方には、図５－２５のように、Gbit列（F列）に数式「=BITAND([@Genre]),$J$1」が埋め込まれています。

図５－２５

数式の「BITAND関数」は、引数である２つの数値のビット単位の論理積（AND）を求めるものです。手計算で言えば、２つの数値を「２進数」で表し、各桁ごとに「ビットが両方とも１」の時だけ、結果側が１（ゼロとゼロ、１とゼロ、ゼロと１　は全てゼロ）にする計算です。全桁が計算完了したら、それを１０進数に変換します。

今回のBITANDの引数は、１つ目は「@Genre]」で「同じデータ行のGenre列の値」（@は同じデータ行 の意味）、２つ目は「$J$1」で「J1セル」固定になっています。「J1」セルは、図４－１の４行目で定数「SearchGenre」と宣言しています。

４８５行目「ThisWorkbook.Sheets(ShName).Range(SearchGenre) = ckb」では、その「SearchGenre」で示すセル（＝J1セル）に、「検索する１２個のCheckBoxのON-OFF状態」の数値を書き込んでいます。
ということは図５－２６のように、Gbit列（F列）の数式「=BITAND([@Genre]),$J$1」は、「Genre列の値」と「検索するCheckBoxのON-OFF状態の値」のビット単位論理積を求める　ことになります。


図５－２６

Gbit列の数式の計算結果は、「１桁もビットが重なっていなかったらゼロ」「１桁でもビットが重なっていたら、１以上」になります。 「１桁以上のビットが重なる」ということは、「検索するジャンルの中に、１つ以上同じジャンルがある」ことです。
ですので４８６行目「Call TableFilter(6, ">0")」で、「テーブルの6列目（Gbit列）を ">0" で絞り込む」ことで、ジャンルで絞り込むことになります。

これで、テーブルの全列について絞り込みが出来ましたので、４８９行目「buf = SearchList」で、「絞り込んだデータ」を変数bufに代入しておいてから、４９１行目「Call TableFilterOFF」でテーブルの絞り込みを解除します。
変数bufは「絞り込んだデータ」が１列でもあれば、データの配列となりますが、絞り込んだ結果「１行も残らなかった」場合には「Empty」になっています。

４９３行目からはリストボックスの加工に入ります。
４９３行目「Me.ListBox1.Clear」で、一旦リストボックスのデータを全てクリアします。
４９４行目「If Not IsEmpty(buf) Then」では、「絞り込んだデータが１行以上ある（＝Not Empty）」場合に４９５～５００行目を実行し、ListBoxのリストを作成します。

４９５行目「For i = 1 To UBound(buf, 1)」では、カウンタ変数ｉを「絞り込んだデータの行数」分だけ回します。今回のListBox1は複数列（8列）のリストですので、まず４９６行目「Me.ListBox1.AddItem ""」で「空のリスト行」を作成します。
その上で、４９７行目「For j = 0 To LastCol」でカウンタ変数ｊをテーブルの列数分を回します。ここでカウンタｊは「ゼロ」から開始しています。ListBox1のゼロ列目は「テーブルの行相対位置」を入れるため、ゼロスタートとしています。
４９８行目「Me.ListBox1.List(i - 1, j) = buf(i, j)」で、実際にデータをリスト行に書き込んでいきます。

以上でListBox1のリストが完成です。なお、絞り込んだ結果１行も残らなかった場合でも４９３行目「Me.ListBox1.Clear」でリストのクリアだけは済ませていますので、空のリストボックスとなります。

完成したリストボックスをクリックした時に呼び出されるのが、図５－２７です。

507. '==========　⇩(36)　リスト内の１行を選択　============
508. Private Sub ListBox1_Click()
510. 　　With Me.ListBox1
511. 　　　Me.TextBox3.Text = .List(.ListIndex, 1) 　 '←DvdNo
512. 　　　Me.TextBox1.Text = .List(.ListIndex, 2) 　 '←タイトル
513. 　　　Me.ComboBox1.Text = .List(.ListIndex, 3) 　 '←シリーズ
514. 　　　Me.TextBox2.Text = .List(.ListIndex, 4) 　 '←作成日
515. 　　　Call Bit2CKB(CLng(.List(.ListIndex, 5))) 　 '←CheckBoxのON-OFF
516. 　　　Me.ComboBox2.Text = .List(.ListIndex, 7) 　 '←保管場所
517. 　　End With
519. 　　Me.TextBox3.Enabled = False
521. End Sub

図５－２７

下段のリストボックス内の項目を選択した場合、その項目を上段の各項目に反映する必要があります。選択したリストボックスのデータには、図５－２８のように、非表示のリスト列も含めて「全項目の情報」が収まっています。
そのデータを上段にデータ貼り付けをします。

図５－２８

５１１～５１６行目で、リストボックスデータを上段の各項目に貼り付けています。コードは以下のようになります。

項目	コード内容	ｺｰﾄﾞ番号
DvdNo	Me.TextBox3.Text = .List(.ListIndex, 1)	５１１行目
タイトル	Me.TextBox1.Text = .List(.ListIndex, 2)	５１２行目
シリーズ	Me.ComboBox1.Text = .List(.ListIndex, 3)	５１３行目
作成日	Me.TextBox2.Text = .List(.ListIndex, 4)	５１４行目
保管場所	Me.ComboBox2.Text = .List(.ListIndex, 7)	５１６行目

残りは、ジャンル用のCheckBoxです。
CheckBoxの状態を数値に変換するには図５－２４のように、各CheckBoxのON-OFFを1とゼロに置き換えて２進数の並びにしました。今度は逆に、ジャンルの数値を２進数にし、1となっている桁に相当するCheckBoxをONにすれば、ジャンルが再現できます。
それを行っているのが、図５－３４の「Bit2CKBプロシージャ」で、５１５行目「Call Bit2CKB(CLng(.List(.ListIndex, 5)))」で実行しています。Bit2CKBの引数には「.List(.ListIndex, 5)」とリストボックスデータの５列目の値を渡していますが、Bit2CKB側がLong型として引数を受けなければいけないので、CLng関数でLong型に型変換しています。

最後に、５１９行目「Me.TextBox3.Enabled = False」で、DVD番号欄を編集不可状態にしています。これは「リストボックスの項目を選択中は、データ更新　または　削除」のため、「DVD番号を変更されないようにする」ためです。

次に今回システムでは、「リストボックスの項目を選択」した後、その項目を「ダブルクリック」した時には、「選択項目が新規登録扱い」になるようにしています。これは「データのコピー＋修正」で、似たような内容のものなら手間を掛けずに登録できるようにするためです。
リストボックスをダブルクリックした時に呼び出されるのが、図５－２９です。

523. '==========　⇩(37)　リストの項目をダブルクリック　============
524. Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
525. 　Me.ListBox1.ListIndex = -1
526. 　Me.TextBox3 = ""
527. 　Me.TextBox3.Enabled = True
528. End Sub

図５－２９

５２５行目「Me.ListBox1.ListIndex = -1」で、リストボックスの選択を解除（何も選択していない状態）しています。
５２６行目「Me.TextBox3 = ""」で、DVD番号欄を空欄にします。
５２７行目「Me.TextBox3.Enabled = True」で、DVD番号欄を編集可の状態にし、検索も可能な状態にしています。

５－２－１０．チェックボックスの処理

システム起動時、図５－３の２５９行目から呼び出されるMakeCKBプロシージャが、図５－３０です。

530. '==========　⇩(38)　チェックボックスの作成　============
531. Private Sub MakeCKB()
532. 　Dim i As Long 　　　　 '←カウンタ変数（CheckBoxの数）
534. 　For i = 1 To UBound(Glist, 1)
535. 　　Me.Controls("CheckBox" & i).Width = 80
536. 　　Me.Controls("CheckBox" & i).Caption = Glist(i)
537. 　　Me.Controls("CheckBox" & i).Value = False
538. 　Next i
540. End Sub

図５－３０

５３４行目「For i = 1 To UBound(Glist, 1)」では、カウンタ変数ｉをチェックボックスの個数だけまわします。配列Glistは、図４－１の８行目で宣言し、図４－３でデータ代入しています。
５３５行目「Me.Controls("CheckBox" & i).Width = 80」は、CheckBoxの幅を設定しています。この値は、フォーム上の配置・文字数などから決めていますので、改造などするときは調整して下さい。
５３６行目「Me.Controls("CheckBox" & i).Caption = Glist(i)」で、各CheckBoxのCaptionに文字を書き込んでいます。
５３７行目「Me.Controls("CheckBox" & i).Value = False」で、起動時には全てのCheckBoxをOFF状態にしています。なお、フォーム上へのコントロールの配置時、CheckBoxの既定はOFF（.Value=False）ですが、念のためこのコードを入れています。
５３７行目の代わりに、「Call Bit2CKB(0)」をFor～Nextの外に出して実行させてもOKです。

チェックボックスのON-OFF状態を数値に変換する「CKB2Bit関数プロシージャ」が、図５－３１です。

542. '==========　⇩(39)　チェックボックスの状態を数値変換　============
543. Private Function CKB2Bit() As Long
544. 　Dim i As Long 　　　　 '←カウンタ変数（CheckBoxの数）
545. 　Dim buf As Long 　　　 '←戻り値の一時変数
547. 　For i = 1 To UBound(Glist, 1)
548. 　　If Me.Controls("CheckBox" & i).Value = True Then
549. 　　　buf = buf + (2 ^ (i - 1))
550. 　　End If
551. 　Next i
553. 　CKB2Bit = buf
554. End Function

図５－３１

図５－２４で、チェックボックスのON-OFF状態を１つの数値にする変換方法について説明しましたが、ここでは計算式としての変換の方法を、図５－３２のように「チェックボックスの１・３・９にレ点（.Value = True）」が付いていた場合で考えます。

図５－３２