【VBA】ユーザー設定リストを作成や削除をおこなう

こんにちは、Ryoです。
Excelを扱う中でデータの並べ替えを行うこともありますが、元々組み込まれているリストは曜日や月なので独自のユーザー設定リストを作成したいことも多々あるかと思います。今回はそのリスト作成をVBAで行う場合（AddCustomListメソッド）や、削除（DeleteCustomListメソッド）する方法について書いていきます。

1.サンプル概要
2.サンプルコード
- ◆ユーザー設定リスト作成・登録
- ◆ユーザー設定リストの削除
3.まとめ

1.サンプル概要

サンプルとして以下画像の簡単な表を用意しています。

この表内にある「型式」A001,B001,C001をAddCustomListメソッドを使用しユーザー設定リストとして登録すると、以下のようにリスト内に表示されています。

次にDeleteCustomListメソッドを使用して、先程登録したリストを削除するとリストから消えます。

今回はこのようにユーザー設定リストの作成・登録、及び削除について書いていきます。

2.サンプルコード

◆ユーザー設定リスト作成・登録

本サンプルではセルA2～A4範囲をユーザー設定リストとして登録しますので、以下のようになります。

Sub Sample1()

 'セルA2～A4の値をユーザー設定リストに登録
 Application.AddCustomList ListArray:=Range("A2:A4")
 
End Sub

1行だけですが、この処理で指定した文字列がユーザー設定リストに登録されます。このようにリストを作成するにはAddCustomListメソッドを使用します。

オブジェクト.AddCustomList（ListArray,ByRow)

AddCustomListメソッドの引数ListArrayに作成したいリストのデータをセル範囲で指定するか、Array関数を使用して配列で指定します。

引数ByRowは省略可能です。ここにTrueを指定すると指定したセル範囲の各行から作成され、False指定で各列から作成されるようになります。

例えば複数のリストをまとめて登録する場合に、各列から作成するByRowを指定しますので参考としてサンプル表のA2～C4範囲を指定して以下のコードを実行してみます。

Sub Sample1_1()

  Application.AddCustomList _
  ListArray:=Range("A2:C4") _
  , ByRow:=False

End Sub

この結果は以下画像のように「型式」「品名」が各列毎にユーザー設定リストとして登録されています。ちなみに「数量」のC列はセルのデータが「数値」なので範囲指定したとしても登録されません。

また引数ListArrayにセル範囲を指定した場合は再度同じセル範囲のデータを登録しようとするとエラーになり、文字列の配列を指定した場合は同じものを登録しようとしても無視されますのでご留意ください。

◆ユーザー設定リストの削除

本サンプルコードでは最初にユーザー設定リストを登録し、その後に登録したリスト番号を取得⇒削除の流れになります。

Sub Sample2()

  Dim num As Long
  
  'ユーザー設定リストにセルA2～A4の値を登録
  Application.AddCustomList _
  ListArray:=Range("A2:A4")

  '上記処理で登録された設定リストの番号を取得
  num = Application.GetCustomListNum _
  (Array("A001", "B001", "C001"))
  '※ ユーザー設定リスト番号「1～11」は
  '　「既存リスト」なので削除不可となっています
  
  '取得した番号のユーザー設定リストを削除
  Application.DeleteCustomList num

End Sub

サンプルに示す通り、ユーザー設定リストを削除するにはDeleteCustomListメソッドとGetCustomListNumメソッドの二つが必要になります。

オブジェクト.DeleteCustomList(ListNum)

この構文にある引数ListNumには削除するユーザー設定リストの番号を指定する必要がありますので、その番号をGetCustomListNumメソッドで取得します。

またサンプルコード内のコメントでも書いていますが、ユーザー設定リスト番号「1～11」はExcelの既存リストなので削除することはできませんからご注意ください。

オブジェクト.GetCustomListNum(ListArray)

引数ListArrayには番号を取得する為のユーザー設定リストの文字列を配列で指定します。

このサンプルのようにリスト設定したい文字列が少なければ(Array(“A001”, “B001”, “C001”))のような記述の仕方でも問題ないのですが、数が多かったり内容を変更したい場合に不便なので、その場合は以下のように変数を利用すると良いと思います。

Sub Sample3()

  'ユーザー設定リスト文字列指定を簡略化

  Dim num As Long
  '指定元がセル範囲なのでVariant型指定
  Dim ListData As Variant
  
  'セルA2～A4の値を変数ListDataに取得
  'ListData = Array("A001", "B001", "C001") 'と同様
  ListData = Range("A2:A4")

  'ユーザー設定リストに変数ListDataの値を登録
  Application.AddCustomList _
  ListArray:=ListData

  'ユーザー設定リスト番号の取得
  '変数を活用すれば配列データを書き出さなくても可
  num = Application.GetCustomListNum _
  (ListArray:=ListData)
  
  '取得した番号のユーザー設定リストを削除
  Application.DeleteCustomList num

End Sub

ユーザー設定リストとして使用したデータは予めセルA2～A4に入力されているということを前提とし、それを変数ListDataに配列として取り込んで引数ListArrayに指定する流れになります。

こうしておくと元データを入力するセル内容さえ変更すれば対応出来るので使いやすいのではないかと思います。