2021/04/16

データの重みを考慮したComboBox入力補助

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• ４．クラスモジュール（Class1）
• ５．ワークブックモジュール（ThisWorkbook）
• ６．フォーム（UserForm1）
• 　６－１．フォームの作成
• 　６－２．フォームモジュール（UserForm1）
• 　　６－２－１．フォーム起動の準備
• 　　６－２－２．トグルボタンの動作
• 　　６－２－３．スクロールバーの動作
• 　　６－２－４．ダイアログの終了
• ７．標準モジュール（Module1）
• 　７－１．変数・定数の宣言
• 　７－２．ダイアログ起動
• 　７－３．入力補助作成
• 　　７－３－１．処理実行有無の分岐とComboBoxの削除・作成
• 　　７－３－２．対象セル範囲のデータ取り込み
• 　　７－３－３．データ値とデータの重みをSortedList(1)に格納
• 　　７－３－４．Key値とValue値の入れ替えとComboBoxのリスト作成
• 　　７－３－５．リスト項目選択時の動きを予約
• 　７－４．リスト内から選択したデータをセルに反映
• 　７－５．ComboBox削除
• ８．アドインとしてExcelにマクロを登録
• ９．最後に

１．背景

例えば図１－１のような社員の出身地や血液型リストを作成する場合、１セルずつ手入力するのは大変です。ある決まったデータリストから選択・入力するシステムは多く存在しますが、システムのためには「リストを事前に作る」必要がありますし、またリストに無いデータを入力するのも大変です。Excelにも「入力規制」機能がありますが、同じデメリットを持っています。

図１－１（「疑似個人情報生成（https://hogehoge.tk/personal/generator）」で自動生成したリストを使用）

一方でブラウザでの検索やスマホのメールなどには、過去にユーザーが入力した文字列を解析し、「次に選ばれるであろう文字列」を表示・選択できる「オートコンプリート（入力補助）」機能が存在します。
今回は、このような入力補助システムを紹介します。

２．システム概要

今回システムは、Excelにアドインして使用することを前提に作成しています。
まずリボンに登録（登録の仕方は「８項」を参照下さい）したアドインボタン（図２－１の①）をクリックすると、操作ダイアログ②が表示されます。
なおサンプルファイルでは、すぐに試行できるようにするため、シート上に起動ボタンを配置しています。

図２－１

そのダイアログの「入力補助ボタン③」がON（赤色）になっていることを確認し、図２－２のようにセルを入力場所に移動④します。すると、そのセルに沿った形でコンボボックスが表示されます。

図２－２

図２－３の左側のように、コンボボックス右側の下三角ボタンをクリックすると、過去に入力したデータがリストとして表示されます⑤。
どれかを選択クリックすると、選択した値がセルに入力され、コンボボックスは一つ下のセルに移動します。

図２－３

図２－４のように、手入力したい場合は「そのままキー入力」すれば「コンボボックスが消えて通常通り入力可能⑥」です。これは「選択しているのはセル」で「コンボボックスはセルの上に乗っているだけ」の状態だからです。
入力完了しEnterキーを押せば、選択セルが１つ下に移動し、またコンボボックスが表示されます。

図２－４

ダイアログ上のスクロールバーを移動させると、その上の数値が変化します。この数値は図２－５のように「コンボボックスより上のセルの数」を表し、この対象セルの値を解析して選択リストに表示しています。
リストの表示順序は、「最近入力したもの（＝行位置が下のもの）」ほど上位に、また「範囲内で何回も出現したもの」ほど上位になるようにしています。
また設定したセル数が確保できない場合（例えばC5セルが選択セルなのに、対象セル数が10の場合）には、確保できる範囲の中で計算をしています。

図２－５

図２－６のように、ダイアログのボタンをクリックし「OFF（文字が黒色）」にする⑧と、コンボボックスは消え通常モードになります。
またシステムを終了するには、ダイアログ右上の×印⑨をクリックします。

図２－６

なお、コンボボックスでリスト化されるデータは、同種の型のデータ（例えば、文字列の種類・数値の種類・日付の種類など）のみを選択してリスト化しています。異種データが混ざっている時には、選択セルの一つ上のセルデータの型がリストのデータ型になります。
また、数式は計算結果としてリスト化の対象となりますが、空白セル・エラーとなっているセルは無視します。

３．プログラムの流れ

まず、起動プロシージャ（AutoComboBox_Start）を実行することで、ダイアログを表示させ、ユーザーの操作を待ちます。
ユーザーが選択セルを変更すると、SelectionChangeイベントが発生しますので、既存のComboBoxが存在すれば削除したのち、現在のセル位置に新たにComboBoxを作成します。
現在のセル位置を基準にして対象セル範囲のデータを取得し、そのデータをSortedListオブジェクト（１）に代入していきます。SortedListオブジェクトには「Key」と「Value」がありますが、セルデータは「Key」にデータの重みは「Value」に登録します。

図３－１

Keyにセルデータを登録することで「セルデータの重複」を避けることが可能になります。
一方「データの重み」については、図３－２のように「対象セル範囲の上の方から点数を割り振り」且つ「同じセル値の場合は点数を加算」します。この計算により、点数の多いもの順にコンボボックスのリストにしています。

図３－２

しかし「SortedListオブジェクトは、Valueでは並べ替えが出来ない」ので、SortedListオブジェクト（１）の状態では「重み点数での並べ替え」が出来ません。
ですので次に、SortedListオブジェクト（１）を、もう一つのSortedListオブジェクト（２）に「Key値とValue値を入れ替え」て代入します。このようにすることで（２）の方は「データの重み」がKeyになり「重み順にデータが並ぶ」ことになります。
これを取り出し、コンボボックスのリストにすることで「重複のないセルデータが、重み順に並ぶ」ことになります。

４．クラスモジュール（Class1）

入力したいワークシートに今回のマクロを組めば、確かに動作するのですが、その都度マクロを貼り付けるのは手間です。ですので今回はExcelのアドインにマクロを登録し、Excelで開いたワークシートであれば動作するものにしました。

そのため、作業するワークシート上のSelectionChangeイベントを拾い上げる必要があり、図４－１のようにWithEventsをクラスモジュールで宣言し、全ての開かれているExcelブックのSelectionChangeイベントを取得しています。

1. '==========　⇩(1)　WithEvents宣言　============
2. Public WithEvents App As Application
4. '==========　⇩(2)　SelectionChangeイベント取得部　============
5. Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
6. 　Call Sel_Cng(Sh, Target)
7. End Sub

図４－１

２行目は、WithEventsキーワードを使って Applicationオブジェクト（＝Excel）のイベントに対応するためのオブジェクト変数（App）を宣言しています。

５行目の「App_SheetSelectionChange」で、開いている全てのExcelブック（＝Appオブジェクト）のSelectionChangeイベントが取得できます。引数としては、シート「Sh」とセル範囲「Target」を受取ります。
このイベントプロシージャ内に、以降の処理コードを書き込んでも良いのですが、使い慣れた標準モジュール側で実行するために、６行目の「Call Sel_Cng(Sh, Target)」で、標準モジュール側へ処理を移しています。引数も、そのまま横流しします。

５．ワークブックモジュール（ThisWorkbook）

クラスモジュールに「全Excelブックに対するSelectionChangeイベント」のコードを置きましたが、これを有効にするためには、図４－１の２行目で宣言した「Appオブジェクト」をApplication（＝Excel）に設定する必要があります。

9. '==========　⇩(3)　インスタンス宣言・生成　============
10. Private X As New Class1
12. '==========　⇩(4)　AppオブジェクトをExceに設定　============
13. Private Sub Workbook_Open()
14. 　Set X.App = Application
15. End Sub

図５－１

実際にクラスモジュール（Class1）を使用するためにはインスタンス化が必要です。
１０行目の「Private X As New Class1」で、「Class1」クラスのモジュールレベル変数「X」の宣言とインスタンス生成（New句）を同時に行っています。

１３行目の「Private Sub Workbook_Open()」は、このブック（今回システム）を開いた時のイベントプロシージャですが、このファイルがExcelアドインに登録され、且つ有効になっていれば「Excelを起動した時には、Workbook_Openイベントが実行される」ことになります。
１４行目の「Set X.App = Application」では、Application（＝Excel）をモジュールレベル変数「X（つまり、Class1）」の「App変数」をApplication（＝Excel）に設定しています。

６．フォーム（UserForm1）

６－１．フォームの作成

フォーム上のコントロールのレイアウトは図６－１のようにしました。「入力補助」を実行・中断するToggleButton、対象範囲を変更するScrollBar、そしてScrollBarの値を表示するLabelです。

図６－１

各コントロールのプロパティはフォームモジュール内（Initializeイベント、Activateイベント）で行います。「対象範囲：」と書いてあるLabelについては、コントロール配置時に手動でCaptionプロパティを変更しています。
今回、入力補助は「ON または OFF」の二極ですのでトグルボタンを使用しました。実際に使用すると図６－１の赤吹き出しのように「ボタンが凸」＝OFF（Value値＝False）、「ボタンが凹」＝ON（Value値＝True）となります。

６－２．フォームモジュール（UserForm1）

６－２－１．フォーム起動の準備

フォームが起動する時に発生するInitializeイベント、Activateイベントが図６－２です。
今回はUserFormをHide状態にしないので、Activateイベントの内容をInitializeイベントに移動してもOKです。但し改造して、終了ボタン等を追加しフォームをHide状態にする場合は、起動時にすぐに使えるように「ToggleButton1.Value = True」のコードをActivateイベントに入れておいた方が良いと思います。

17. '==========　⇩(5)　フォーム初回起動時の準備　============
18. Private Sub UserForm_Initialize()
19. 　ScrollBar1.Max = MaxR
20. 　ScrollBar1.Value = 10
21. 　ScrollBar1.Min = 1
22. 　Me.Caption = "入力補助"
23. End Sub
25. '==========　⇩(6)　フォーム表示時の準備　============
26. Private Sub UserForm_Activate()
27. 　ToggleButton1.Value = True
28. End Sub

図６－２

１９～２１行目はスクロールバーの設定です。
１９行目の「ScrollBar1.Max = MaxR」では、スクロールバーのMax値を図７－１の６７行目で定数宣言したMaxR値にしています。
２０行目の「ScrollBar1.Value = 10」では、初期の対象セル範囲を「10」にしています。起動のたびに同じ初期値になります。
「ユーザーの前回設定値を復元」のような機能を持たせることも可能（設定した値をワークシートのどこかに記録しておく等）ですが、今回その機能を盛り込まなかったのは、ユーザーが毎回異なるデータを処理することを想定したからです。また、もし毎回同じようなデータを処理するのであれば、今回システムよりも、入力規制のように固定したリストの方が使い易いとも思います。
２１行目の「ScrollBar1.Min = 1」は、Min値を「１」に設定しています。

なお、この設定順序「Max値→Value値→Min値」には意味があります。Value値が動くとScrollBar1_Changeイベントが発生しますが、もし「Max値→Min値→Value値」の順で設定するとValue値は「0→1→10」の順序で動きますので、2回ScrollBar1_Changeイベントが発生してしまいます。一方今回の「Max値→Value値→Min値」だと「0→10→10」で１回のイベント発生となりますので、無駄なイベントを防いでいることになります。

２２行目の「Me.Caption = "入力補助"」は、ダイアログ左肩のタイトルを変更しています。

Activateイベント内の２７行目「ToggleButton1.Value = True」は、トグルボタンを凹（入力補助ON）状態にしています。リボン上のアドインボタンをクリックすれば、すぐに入力補助が使えるようにするためです。

６－２－２．トグルボタンの動作

トグルボタンをクリックした時に呼び出されるのが図６－３です。

30. '==========　⇩(7)　トグルボタンのクリック　============
31. Private Sub ToggleButton1_Click()
33. 　If ToggleButton1.Value = True Then
34. 　　AutoComboBox_OnOff = True
35. 　　ToggleButton1.Caption = "入力補助ON"
36. 　　ToggleButton1.ForeColor = RGB(255, 0, 0)
38. 　Else
39. 　　AutoComboBox_OnOff = False
40. 　　Call CB_Del
41. 　　ToggleButton1.Caption = "入力補助OFF"
42. 　　ToggleButton1.ForeColor = RGB(0, 0, 0)
44. 　End If
45. End Sub

図６－３

トグルボタンはクリックするたびに凹凸を繰り返し、その変更後の状態になった後でClickイベントが発生します。ですのでちょっと見ると「普通のCommandButtonのコードとはIf文が逆」に見えるので注意が必要です。

ですので、３３行目の「If ToggleButton1.Value = True Then」は、凹型（ON状態）を表しています。凹型の場合は「入力補助ON」ですので、３５行目の「ToggleButton1.Caption = "入力補助ON"」でボタンの文字を「入力補助ON」にし、３６行目の「ToggleButton1.ForeColor = RGB(255, 0, 0)」で文字色を赤色にしています。

３４行目の「AutoComboBox_OnOff = True」は「入力補助を実行するか否か」の切り替えフラグの値を切り替えています。
ダイアログが表示されている時はもちろん、アドインが有効になっている状態では常に図４－１の５行目の「App_SheetSelectionChangeイベント」が有効になっており、セルが選択されるたびに図４－１の６行目が実行されます。
ですので、図７－３の８５行目の「If AutoComboBox_OnOff = False Then Exit Sub」で処理を分岐していますが、ON（AutoComboBox_OnOff = True）の状態では８５行目は成立しないため、その後の処理に進み、入力補助ComboBoxが表示されることになります。

一方、凸型の場合は「入力補助OFF」ですので、４１行目の「ToggleButton1.Caption = "入力補助OFF"」でボタンの文字を「入力補助OFF」にし、４２行目の「ToggleButton1.ForeColor = RGB(0, 0, 0)」で文字色を黒色にしています。

３９行目の「AutoComboBox_OnOff = False」は３４行目と逆のフラグ設定です。これにより、OFFの時は図７－３の８５行目「If AutoComboBox_OnOff = False Then Exit Sub」が成立し、入力補助処理をスルーしています。
また４０行目の「Call CB_Del」は、図７－１０の「CB_Delプロシージャ」を呼び出し、ComboBoxを削除しています。

今回システムのように、クラスモジュールを使って全てのブックのSelectionChangeイベントを取得する場合、Excelを開いている間はずっとイベント発生が有効になっています。 今回の「変数AutoComboBox_OnOff」は、図７－１でBoolean型で宣言していますので、既定値はFalseになります。ですので今回の「既定値Falseの時に、入力補助をスルーする設定」とは逆の「Trueでスルーする設定」にしてしまうと、「ONにしていないのに、システムが動く」ことになってしまいます。 また、何か他のマクロ（.xlsm）や他のアドイン（.xlam）と併行で使用している場合、今回プロジェクト（本マクロファイル）にフォーカスが当たっている時に、もし「AutoComboBox_OnOff = True」というコードが実行されてしまうと、意図せずに「入力補助ONモード」になります。 具体的には「ダイアログを選択状態」にして「イミディエイトウィンドウで入力」くらいしか思いつかないのですが、安全のため変数名として「AutoComboBox_OnOff」と他ではあまり使われないであろう文字列を使用しています。

６－２－３．スクロールバーの動作

スクロールバーを操作した時の処理が図６－５です。スクロールバー各部の呼び名は図６－４のようになっており、また各部を操作した時に発生するイベントは以下の通りです。
　・「スクロール矢印」と「レール」をクリックした時にはChangeイベントが発生。
　・「スクロールボックス」をマウスで操作している時にはScrollイベントが発生し、移動後マウスを離すとChangeイベントが発生。

図６－４

ですので、スクロールバーの「値（Value値）を変更」したら「必ずChangeイベントが発生」することになります。

47. '==========　⇩(8)　スクロールバーの操作　============
48. Private Sub ScrollBar1_Change()
49. 　Me.Label1.Caption = ScrollBar1.Value
50. 　ListRange = ScrollBar1.Value
51. End Sub
53. '==========　⇩(9)　スクロールボックスの操作　============
54. Private Sub ScrollBar1_Scroll()
55. 　Me.Label1.Caption = ScrollBar1.Value
56. End Sub

図６－５

Changeイベント（４８～５１行目）では、まず４９行目の「Me.Label1.Caption = ScrollBar1.Value」で、ダイアログのLabel1にスクロールバーの値を書込み、対象範囲の幅をユーザーの目に分かる様にしています。
次に５０行目の「ListRange = ScrollBar1.Value」で、Public変数のListRange（図７－１の６５行目で宣言）にスクロールバーの値を代入しています。処理の中では、このListRange値を使って、入力補助をする対象セル範囲を決めています。


Scrollイベント（５４～５６行目）では、５５行目の「Me.Label1.Caption = ScrollBar1.Value」で、スクロールボックスを動かしている最中も「今、どこまで動かしているのか」が見えるように、Label1に値を書き込んでいます。
もしこのコードが無いと、「カンでスクロールボックスを動かし、違っていたらまた動かす」という操作になってしまいます。

６－２－４．ダイアログの終了

ダイアログ右上の×印をクリックした時に呼び出されるQueryCloseイベントが図６－６です。今回、終了ボタンを省略していますので、この操作がシステム終了の意味になります。

58. '==========　⇩(10)　変数宣言・設定部　============
59. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
60. 　AutoComboBox_OnOff = False
61. 　Call CB_Del
62. End Sub

図６－６

６０行目の「AutoComboBox_OnOff = False」で、常に発生するSelectionChangeイベントにより実行される入力補助処理をスルーさせます。
また６１行目の「Call CB_Del」で入力補助リストの役目をしているComboBoxを削除しています。
なお、この終了処理でダイアログを消しても、アドインが有効な状態では、図４－１の「App_SheetSelectionChange」イベントが生きていることに注意して下さい。もし、不要であれば「アドインを無効（レ点を消す）」にしておく方が良いと思います。

７．標準モジュール（Module1）

７－１．変数・定数の宣言

標準モジュールの宣言部が図７－１です。

64. '==========　⇩(11)　変数・定数の宣言　============
65. Public ListRange As Long 　　　　　　　　　　　 '←入力補助の対象セル範囲
66. Public AutoComboBox_OnOff As Boolean 　　 '←入力補助システムの実行フラグ
67. Public Const MaxR As Long = 100 　　　　　　 '←スクロールバーの最大値
68. Private CB As Object 　　　　　　　　　　　　　 '←今回ｼｽﾃﾑで作成するComboBoxｵﾌﾞｼﾞｪｸﾄ

図７－１

６５行目の「ListRange」は、今回の入力補正の元となるデータ範囲のセル幅（Height方向）になります。値としては、図６－５の５０行目の「ListRange = ScrollBar1.Value」で代入されます。初期値は図６－２の２０行目の「ScrollBar1.Value = 10」で与えられる「10」となります。

６６行目の「AutoComboBox_OnOff」は、入力補助を実行するか否かのフラグになります。ON-OFFは主にトグルボタンで切り替え、システム実行有無は図７－３の８５行目の「If AutoComboBox_OnOff = False Then Exit Sub」で切り替わります。

６７行目の「MaxR」は、スクロールバーの最大値の設定ですが、この値を図７－３の１１２行目の「SL1.Add ArrayRng(i, 1), i + i / (MaxR * 10)」でも使用しています。図７－３で詳細説明しますが、SortedListオブジェクトのKeyに「データの重み」を入れる際、条件によっては値が重複してしまう可能性があるため、行位置を小数点以下の数値に変換して重み値に加えることで、重複が発生しないようにしています。

６８行目の「CB」は、今回システムで作成するComboBoxオブジェクトで、この変数を使ってリストデータを入れたり、ComboBoxを削除したりしています。

７－２．ダイアログ起動

今回システムの起動は、図７－２の「AutoComboBox_Start」プロシージャから行います。アドイン設定後のボタンには、このプロシージャを登録して下さい。
内容としては、72行目の「UserForm1.Show 0」で、UserForm1をモードレス（ダイアログ表示中もシート操作可能）状態で起動します。

70. '==========　⇩(12)　ダイアログ起動　============
71. Public Sub AutoComboBox_Start()
72. 　UserForm1.Show 0
73. End Sub

図７－２

７－３．入力補助作成

開いているExcelブックのどこかでセル移動があった場合、図４－１の「App_SheetSelectionChange」イベントが発生し、その６行目から呼び出されるのが図７－３になります。
引数として「App_SheetSelectionChange」イベントと同じく、シート「Sh」とセル範囲「Target」を受け取ります。

75. '==========　⇩(13)　入力補助作成　============
76. Public Sub Sel_Cng(Sh As Object, Target As Range)
78. 　Dim ArrayRng As Variant 　　 '←入力補助に使うデータのセル範囲
79. 　Dim i As Long 　　　　　　　 '←カウンタ変数
80. 　Dim StartR As Long 　　　　 '←データセル範囲の最上段位置(現在セルからの相対位置)
82. 　Dim SL1 As Object 　　　　　 '←データを代入する為のSortedListオブジェクト
83. 　Dim SL2 As Object 　　　　　 '←並び替えをする為のSortedListオブジェクト
85. 　If AutoComboBox_OnOff = False Then Exit Sub 　　　　 '←Offの時は、ここで終了
87. 　Call CB_Del 　　　　　　　　 '←ComboBoxの削除
89. 　Set CB = Sh.DropDowns.Add _
90. 　　　　　　(Target.Left, Target.Top, Target(1).Width, Target(1).Height)
92. 　If Not Target.Row = 1 Then
93. 　　StartR = -1 * WorksheetFunction.Min(ListRange, Target.Row - 1)
94. 　　ArrayRng = Range(Target.Offset(StartR), Target.Offset(-1)).Value
95. 　End If
97. 　Set SL1 = CreateObject("System.Collections.SortedList")
98. 　Set SL2 = CreateObject("System.Collections.SortedList")
100. 　Select Case VarType(ArrayRng)
102. 　　Case Is >= 8192 　　　 '←配列の時
103. 　　　For i = UBound(ArrayRng, 1) To Step -1
105. 　　　　Select Case VarType(ArrayRng(i, 1))
106. 　　　　　Case 0, 10 　 '←空白(0)・ｴﾗｰ(10)の時
108. 　　　　　Case Else
109. 　　　　　　On Error Resume Next
111. 　　　　　　　If SL1.containskey(ArrayRng(i, 1)) = False Then
112. 　　　　　　　　SL1.Add ArrayRng(i, 1), i + i / (MaxR * 10)
113. 　　　　　　　Else
114. 　　　　　　　　SL1(ArrayRng(i, 1)) = SL1(ArrayRng(i, 1)) + i
115. 　　　　　　　End If
117. 　　　　　　On Error GoTo 0
118. 　　　　End Select
120. 　　　Next i
122. 　　Case 0, 10 　　 '←空白(0)､Targetが一番上(0)､ｴﾗｰ(10)の時
124. 　　Case Else 　　　　　 '←1セルで、値が入っていた時
125. 　　　SL1.Add ArrayRng, 1
127. 　End Select
129. 　For i = 0 To SL1.Count - 1
130. 　　SL2.Add SL1.getbyindex(i), SL1.getKey(i)
131. 　Next i
133. 　For i = SL2.Count - 1 To 0 Step -1
134. 　　CB.AddItem SL2.getbyindex(i)
135. 　Next i
137. 　CB.OnAction = "CB_NextSelect"
139. 　Set SL1 = Nothing
140. 　Set SL2 = Nothing
142. End Sub

図７－３

７－３－１．処理実行有無の分岐とComboBoxの削除・作成

まず８５行目の「If AutoComboBox_OnOff = False Then Exit Sub」で、変数AutoComboBox_OnOffがFalseの時（システム起動前、トグルボタンOFF時、システム終了後）に入力補助作成処理をスルーさせています。
次に８７行目の「Call CB_Del」で図７－１０のCB_Delプロシージャを呼び出し、今回システムで作成したComboBoxが存在すれば削除しています。ComboBoxを新たに作る前に削除することで、ComboBoxの操作対象を１つに絞ることができます。

なお今回システムで「作成したComboBoxが不要になる」場面は、①トグルボタンをOFFにした時、及び②ダイアログ右上×印をクリックした時です。①は図６－３の４０行目、②は図６－６の６１行目で「CB_Del」を呼び出してComboBoxを削除していますので、８５行目のON-OFF分岐のあとで「Call CB_Del」を実行すればOKです。

反対に、①②で「CB_Del」を呼び出さないのであれば、８５行目のON-OFF分岐の前に「Call CB_Del」が必要になります。
但しその方法だと、トグルボタンを押してもセルを移動するまではComboBoxが残っている、という現象にはなりますし、システムを使っていない時でも常に「CB_Del」を呼び出している状態になるため、今回のような順番・方法としています。

８９～９０行目の「Set CB = Sh.DropDowns.Add (Target.Left, Target.Top, Target(1).Width, Target(1).Height)」では、新たにComboBoxを作成し、そのコントロールを変数CBに代入しています。
シート上にフォームコントロールを追加する構文は、以下のようになり、その追加するコントロール種は図７－４から選びます。

　Worksheet.コレクション.Add(Left、 Top、 Width、 Height)　

コントロールの種類	コレクション名
ボタン	Buttons
コンボボックス	DropDowns
チェックボックス	CheckBoxes
スピンボタン	Spinners
リストボックス	ListBoxes
オプションボタン	OptionButtons
グループボックス	GroupBoxes
ラベル	Labels
スクロールバー	ScrollBars

図７－４

今回はドロップダウンのような形が合っているだろうと考え、「DropDowns」を使用しました。また貼り付けるシートは引数で得られた「シート（Sh）」で、また貼り付ける位置は引数で得られた「選択セル（Target）」に合わせています。なお、複数セルを選択している場合もあるため、その左上角の単一セルとするためTarget(1)としています。（LeftとTopは、(1)を付けても付けなくても同じなので、付けませんでした。）

７－３－２．対象セル範囲のデータ取り込み

９２～９５行目で、対象セルからデータを配列に取り込んでいます。
まず対象セル範囲の下端は、現在のセル位置（Target）の１つ上になりますが、現在位置が図７－５の右側のように「１行目」であった場合は「それより上の行は無い＝対象となるセルが無い」ことになりますので、９２行目の「If Not Target.Row = 1 Then」で除外しています。
除外することにより、１行目が選択セルの場合の「セルデータを格納する変数ArrayRng」は既定値のNothingになります。

それ以外の場合は２通りに分けられます。スクロールバーで設定したListRange値（＝対象セル範囲の個数）が確保できる場合（図７－５の左側）と、できない場合（図７－５の中央）です。

図７－５

できない場合（図７－５の中央）は、仕方ないので「シート上端（＝１行目）から選択セルの１つ上のセルまで」にすることにしました。
これを数式で表す方法はいくつもあると思いますが、今回は、９３行目の「StartR = -1 * WorksheetFunction.Min(ListRange, Target.Row - 1)」としました。
現在選択しているセルを基準とした場合、一番上のセルを表すには「Target.Offset(-1 * (Target.row - 1))」という式になります。また、ListRangeだけ上のセルは「Target.Offset(-1 * ListRange)」ですので、この式の中の「Target.row - 1」と「ListRange」の大きさを比べて「小さい方を対象セルの上限」に使うことにしました。
「小さい方」は、WorksheetFunctionのMin関数で計算できますので、９３行目ではその結果を変数StartR（Start Rowのつもり）に代入しています。

９４行目の「ArrayRng = Range(Target.Offset(StartR), Target.Offset(-1)).Value」では、９３行目で得た「対象セル範囲の一番上のセル」から「対象セル範囲の一番下のセル（＝選択セルの１つ上）」でセル範囲を指定し、その範囲の値（Value値）を変数ArrayRngに代入しています。この操作で、対象セル範囲の値がArrayRngに代入されます。

対象セル範囲が複数セルの場合には、変数ArrayRngは配列になります。（そのため７８行目ではVariant型で宣言しています。）
一方、対象セル範囲が１つの場合もあります。ListRange値が１だったり、選択セル位置が２行目だったりした場合です。その時には変数ArrayRngは配列にはならず、何かが入っているセルだったらArrayRngには数値や文字列などが入ります。また空白セルだった時には、ArrayRngはNothingとなります。

７－３－３．データ値とデータの重みをSortedList(1)に格納

まず９７～９８行目の「CreateObject("System.Collections.SortedList")」で、SortedListオブジェクトを２つ（SL1、SL2）生成します。SortedListオブジェクトは、Dictionaryなどと同じように「Key」と「Value」を設定しますが、Keyは重複することができず、またKeyを設定すると自動的に昇順で取り出すことが出来るものです。

１００～１２７行目は、SL1オブジェクトに対して「データの値をKey」に、また「データの重みをValue」に格納していきます。
まず、変数ArrayRngの値により処理を分けるため、１００行目の「Select Case VarType(ArrayRng)」で分岐させています。
VarType関数は、引数のデータ型を調べるものです。図７－６のように得られる値から型が分かります。

定数	値	内容
vbEmpty	0	Empty値
vbNull	1	Null値
vbInteger	2	整数型
vbLong	3	長整数型
vbSingle	4	単精度浮動小数点数型
vbDouble	5	倍精度浮動小数点数型
vbCurrency	6	通貨型
vbDate	7	日付型
vbString	8	文字列型
vbObject	9	オブジェクト
vbError	10	エラー値
vbBoolean	11	ブール型
vbVariant	12	ﾊﾞﾘｱﾝﾄ型（ﾊﾞﾘｱﾝﾄ型配列にのみ使用）
vbDataObject	13	非OLEｵｰﾄﾒｰｼｮﾝｵﾌﾞｼﾞｪｸﾄ
vbDecimal	14	10進数型
vbByte	17	バイト型
vbArray	8192	配列

図７－６

また配列の場合は、非配列（0～17）と配列（8192）の値を加算します。例えば図７－７のように配列の型が分かります。

定数	値	内容
vbInteger + vbArray	2 + 8192 = 8194	Integer の配列
vbString + vbArray	8 + 8192 = 8200	String の配列
vbVariant + vbArray	12 + 8192 = 8204	Variant の配列

図７－７

今回の「ArrayRng」は７８行目でVarant型で宣言していますので、配列であればVarType関数の結果は8204が得られます。
１００行目のVarType(ArrayRng)での分岐は、１０２行目の「Case Is >= 8192」、１２２行目の「Case 0, 10」、１２４行目の「Case Else」の３つに仕訳けています。
１０２行目は「Case Is = 8204」でも良いのですが、8192以上は全て配列ですので「漏れがないように、>=8192」としました。
また、１２２行目以降は配列以外の単一セルでの評価です。

配列の時（＝対象セル範囲が複数の場合）は１０３～１２０行目を実行します。
１０３行目の「For i = UBound(ArrayRng, 1) To 1 Step -1」では、「配列の各値」を「下から（＝選択セルに近い方から）」処理をするようにしています。
このような順序で処理をしている理由は、「選択セルのすぐ上のセル」のデータ型を優先させるためです。

SortedListオブジェクトのKeyには何のデータ型でも入れられるのですが、異なる種類のデータを混ぜることが出来ないのです。例えば対象セル範囲内に数値と文字列が混ざっていた場合には、最初に入れたデータの型が「正しい型」と見なされ、後に異なる型の存在確認をしたりKeyに代入しようとするとエラーが発生し、データが格納されません。
（今回はエラーを無視して処理を進めるために、１０９行目の「On Error Resume Next」を置いているためエラーが出る訳ではありません。）

ですので、１０３行目を普通に「For i = 1 To UBound(ArrayRng, 1)」としてしまうと「重みの小さいデータが残る」ことになってしまい、「入力補助なのに、直近に入力した値が表示されない」という現象になる可能性があるのです。下からSortedListオブジェクトへデータを入れていけば、少なくとも「一番直近のデータは表示される」ことになります。

１０５行目の「Select Case VarType(ArrayRng(i, 1))」では、配列１つ１つの値についてVarType関数で型を調べ処理をします。配列の中を調べていますので、配列（8192）を除く0～17の値が得られることになります。
１０６行目の「Case 0, 10」では、空白セル（0）とエラー（10）が分岐され、１０７行目は何もコードがありませんのでスルーしたことになります。
0と10以外でも、例えば9のオブジェクトなどもリストには出来そうにありませんが、「セルの値としてオブジェクトは入っていないだろう」との推定から、除外の対象からは外しています。
その他の型の時は１０８行目の「Case Else」に分岐されます。

その他の型（データに出来る値）の場合は、１１１行目の「If SL1.containskey(ArrayRng(i, 1)) = False Then」で、既に格納したSortedListオブジェクトのKeyの中に「同じKeyが存在するか」を調べています。存在する場合はcontainskeyメソッドがTrueを返し、存在しない場合はFalseを返してきます。

存在しない場合（＝False）は１１２行目の「SL1.Add ArrayRng(i, 1), i + i / (MaxR * 10)」を実行します。
データを格納するには「SortedListオブジェクト.Add [Key] , [Value] 」の構文を使います。
keyはセルの値である「ArrayRng(i, 1)」、Valueは「i + i / (MaxR * 10)」と言うデータの重みを表す値です。

重みの内、前半の「ｉ」は図３－２で示した「選択セルに対する近さ」で、対象セル範囲の行位置です。
後半の「 i / (MaxR * 10)」は、対象セル範囲の行位置「ｉ」をスクロールバーMax値「MaxR」の１０倍で割った値を、最初にSortedListオブジェクトに格納するときだけ加えます。
図７－８はそれを具体的に計算しているものです。


図７－８

図７－８の左側は「行位置の重み」を文字列ごとに足し合わせたもので、この方法だけだと「重みが重なる」場合があります。重なってしまうと「SortedListオブジェクトのKeyには出来ない」ので、重みでの「並び替えが不可能」となってしまいます。
それに対して図７－８の右側は、左側の重みに「小数点以下の位置情報」を初回だけ加えることで、最終的に「重複がない重み」にすることができ、「重みを使って並べ替えが可能」になります。
なお、この手法を使うと「出現回数よりも、選択セルに近いか否か」の方が若干重要視される結果になるので注意が必要です。

「行位置を MaxR x１０で割る」意味ですが、MaxRは既定値として100（今回）としています。スクロールバーをMaxまで移動すれば「対象セル範囲は100セル」になります。
もし「行位置を MaxR で割る」としてしまうと、100/100で１となってしまい、行位置と出現回数の重みに影響が出てしまうと考え、影響の出ない「もう１桁小さな小数点」になるようにしてあります（少し考え過ぎかもしれません）。

SortedListオブジェクトの中に「同じKeyが存在する」場合は、containskeyメソッドがTrueを返しますので、１１４行目を実行します。
114行目の「SL1(ArrayRng(i, 1)) = SL1(ArrayRng(i, 1)) + i」では、元のValue値にデータの重みである「ｉ」を加えています。なお、ここでは１１２行目のような「重複を防ぐための小数点以下の位置情報」は加えません（加えると重複してしまいます）。

なお、このSortedListオブジェクトにセル値とデータの重みを格納している工程を、１０９行目の「On Error Resume Next」と１１７行目の「On Error GoTo 0」で囲んでいますが、エラー発生を無視するためです。
対象セル範囲内に異なるデータ型が混ざっている場合、「異なるデータ型同士の比較は不可能」であるため、１１１行目の「If SL1.containskey(ArrayRng(i, 1)) = False Then」でエラーが発生し、エラーを無視しているために１１２行目の「SL1.Add ArrayRng(i, 1), i + i / (MaxR * 10)」に進み、そこでもまたエラーが出てしまいます。
ですので、この「On Error Resume Next」があればエラーが無視され「SortedListオブジェクト」にデータが入らず、結果として「異なるデータが保存されない（＝保存されるのは同種データのみ）」ことになります。

１２２行目の「Case 0, 10」は、１２３行目に実行コードがありませんので、何もしないことになります。
VarType値がゼロになるのは、以下の２つの時です。
　・単一セル（対象セル範囲の設定値ListRange＝１の場合、選択セルが２行目の場合）で、セルが空白（長さゼロの文字列）
　・選択セルが１行目。（ArrayRngが初期値のままの状態）
またVarType値が10になるのは、単一セルでエラー値の場合です。
空白をリストに加えることは可能ですが意味がありませんので除外としました。またエラーはSortedListオブジェクトに格納できませんので、取り除いています。

１２４行目の「Case Else」で、単一セルのデータが取得できますので、１２５行目の「SL1.Add ArrayRng, 1」でKeyとしてセル値を入れ、Value値には「１」という数値を入れました。「Value値にはデータの重み」を入れていましたが、ここでは１つしかデータが入らないため、実際にはなんでもOKです。

ここまでで、SortedListオブジェクト（SL1）のKey側には重複していないセルの値、Value側にはデータの重みが格納されました。

７－３－４．Key値とValue値の入れ替えとComboBoxのリスト作成

１２９～１３１行目は、１００～１２７行目で対象セル範囲のデータをSortedListオブジェクトに格納した「SL1」を、同じくSortedListオブジェクトの「SL2」に「Key と Value を入れ替え」て格納します。
１２９行目の「For i = 0 To SL1.Count - 1」で、For～Nextで回しながら、SL1のデータを処理していきます。なお「SL1」のデータ数は「SL1.Count」で得られますが、SortedListオブジェクトの数え方はゼロから始まりますので終値は「－１」しています。

１３０行目の「SL2.Add SL1.getbyindex(i), SL1.getKey(i)」では、１つ１つのSortedListオブジェクト「SL1」のデータを「SL2」に入替えコピーしています。
Key値はgetKeyメソッド、Value値はgetbyindexメソッドで得られます。Addメソッドの第一引数はKey、第二引数はValueですので、「KeyとValueを入替え」ることになります。

１２９～１３１行目でSL1をSL2に入れ替えると、Keyに「データの重み」、Valueに「セルの重複していない値」が入ったことになります。SortedListオブジェクトにデータを入れていますので、入れ終わった時点で「Keyの順番（昇順：小さいもの順）」でデータが取り出せることになります。
このデータを１３３～１３５行目で順に取り出し、ComboBoxのリストデータに入れていきます。

１３３行目の「For i = SL2.Count - 1 To 0 Step -1」ではFor～Nextを「Step -1」で「後ろ側から」回しています。これはSortedListオブジェクトは「Keyの順番（昇順：小さいもの順）」で並んでいるため、「データの重みの大きい方から取り出す」ために後ろ側から取り出しているのです。
１３４行目の「CB.AddItem SL2.getbyindex(i)」で、「CB（ComboBox）」のリストに、SortedListオブジェクト（SL2）のValue値（入っているのはセル値）を追加していきます。リストの追加が終わると入力補助ComboBoxが完成します。

但し１３３行目のFor～Nextでは、SL2.Countの全てのデータをリストにしていますが、「そんなに多く表示されても探すのが大変」のような気もします。その場合には、リストの最大数を決めるなどしてリストを短くするのも良いかと思います。

７－３－５．リスト項目選択時の動きを予約

ComboBoxのリストのデータを選択したときには、OnActionプロパティに登録したプロシージャが実行されます。ですので、１３７行目の「CB.OnAction = "CB_NextSelect"」で、「CB_NextSelectプロシージャ（図７－９）」を登録します。
なお、OnTimeメソッドの時と同様に、プロシージャは文字列として登録する必要があるので、ダブルクォーテーションで囲みます

７－４．リスト内から選択したデータをセルに反映

ComboBoxのリストのデータを選択したときには、図７－３の１３７行目でOnAction登録した図７－９のCB_NextSelectプロシージャが実行されます。

144. '==========　⇩(14)　選択データ反映　============
145. Sub CB_NextSelect()
146. 　Selection(1).Value = CB.List(CB.ListIndex)
147. 　Selection(1).Offset(1).Select
148. End Sub

図７－９

１４６行目の「Selection(1).Value = CB.List(CB.ListIndex)」は、リストで選択した項目を現在のセル位置に書き込んでいます。
リスト選択された項目は「CB.ListIndex」というリストの順番で取得でき、その項目内容は「CB.List(CB.ListIndex)」で得られます。
なお書込み先であるセル位置として「Selection(1)」と「(1)」を付けているのは、複数セルを選択した場合でも「ComboBoxを置いたセル」のみに値を書き込むためです。もし「(1)」が無いと、複数セル全てに選択した項目が書き込まれることになります。

１４７行目の「Selection(1).Offset(1).Select」は、セル位置を１つ下に移動しています。これは「セル入力した後、Enterを押すとセルが下に移動する」様子を真似ています。なお、Enterキーで移動する方向は「Excelの設定」で変更可能ですが、初期設定である下方向にしました。
（変更方法：Excelオプション→「詳細設定」→「編集オプション」→「Enterキーを押したらセルを移動」）

なお、リストを無視して「直接キー入力」した場合にもComboBoxが下に移動しているように見えますが、単純にComboBoxを無視してセルに直接書込みEnterを押すことで、「セルへの書込み」→「選択セルを下に移動」と進んだところでSelectionChangeイベントが発生し、今回システムにより一旦ComboBoxが削除され、新たなセル（１つ下のセル）で新たなComboBoxが作成されている　だけです。

７－５．ComboBox削除

図６－３の４０行目、図６－６の６１行目、図７－３の８７行目から呼び出される「ComboBoxを削除」するプロシージャが図７－１０です。

150. '==========　⇩(15)　ComboBox削除　============
151. Sub CB_Del()
152. 　If Not CB Is Nothing Then
154. 　　On Error Resume Next
155. 　　　CB.Delete
156. 　　On Error GoTo 0
158. 　　Set CB = Nothing
159. 　End If
160. End Sub

図７－１０

１５２行目の「If Not CB Is Nothing Then」は、図７－３の８９～９０行目で作成した「入力補助ComboBox（CB）が」存在する時　という意味です。その時に、１５４～１５８行目を実行します。
なお、このComboBoxを「手動で削除」した時には「実体は無くなったが、CBというオブジェクトは削除されていない」ことになりますので、IF文が成立することになります。

１５５行目の「CB.Delete」では「ComboBoxを削除」しています。これで「実体を削除」したことになります。
なお、手動で削除してしまった場合には１５５行目で「削除する実体が見当たらない」ことになり、エラーが発生してしまいます。ですので１５４行目の「On Error Resume Next」と１５６行目の「On Error GoTo 0」で囲み、エラーを抑えています。

最後に１５８行目の「Set CB = Nothing」で「オブジェクトを削除（初期化）」しています。

８．アドインとしてExcelにマクロを登録

このマクロをExcelの機能の１つに登録し、Excel上部のリボンのボタンを押せばシステムを起動できるようにできます。
その方法については「年賀状リスト等の宛名検索と追記　アドイン登録」を参照下さい。

９．最後に

データを手作業で入力するような作業をしていると「非効率」と言われそうですが、それでもまだまだ多い作業だと思います。
ロジックとしては、セル範囲のデータを処理するには配列を使うことが多いと思いますが、今回システムでは「SortedListオブジェクト」を使って一気に並び替えをしてみました。そのために「異種型が混在するデータ」ではリスト化する際に選別をしなければならなくなりましたので、もし混在するデータに対応させるためには、並び換えまで含めて配列を使うしかないかもしれません。

また今回の「SortedListオブジェクト」は、私も初めて使用したため、充分理解していないまま使っています。今回紹介した方法よりも良い方法があるかもしれませんが、その時はお許し下さい。

なお、SortedListオブジェクトを使用するには「.NET Framework」がインストールされている必要があるようです。Windows10なら標準でインストールされているようですが、もし動かない（エラーが出る）場合は、インストール有無を調べてみて下さい。


データの重みを考慮したComboBox入力補助（it-056.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。