EXCEL VBA ユーザー設定リストに登録(追加)・削除 『オリジナルリストの作成・順序 AutoFill』
EXCEL VBA ユーザー設定リストに登録(追加)・削除 『新しいリスト・オリジナルリストの作成・順序 AutoFill』
EXCEL等でデータベースを作成する際に、AutoFill機能を使い、簡単にデータを作成する事が出来ますが、既定登録されているAutoFill機能では、「1月・2月・・・月・火・水・・」などの一般的な「リスト」のみ利用できますが、個人的に利用するリストは、オリジナルの「ユーザー設定リスト」を作成して登録を行います。登録されていないオリジナルの「ユーザー設定リスト」を登録し、有効利用する事でAutoFillや並び替え機能を効率よく利用する事が出来ます。それでは、オリジナルの「ユーザー設定リスト」の登録・削除等の利用方法を説明致します。
※ユーザー登録リストは、EXCELの画面より⇒「ファイル」⇒「EXCELのオプション」⇒「詳細設定」⇒「全般:ユーザー設定リストの編集」
〇『登録する配列を指定してユーザー設定リストに登録します。:Application.AddCustomList(配列) 』
『使用例』
配列 = Array(“りんこ”,”みかん”,”なし”)
Application.AddCustomList(配列) ‘・・・上記の配列がユーザー設定リストに登録に新たに登録されます。
〇『登録したユーザー設定リストの登録番号を返します。:Application.GetCustomListNum(配列) 』
『使用例』
A = Application.GetCustomListNum(配列) ‘・・・Aにリスト登録番号を返します。
〇『登録されているユーザー設定リスト番号でリストから削除します。:DeleteCustomList(リスト番号) 』
Application.DeleteCustomList (リスト番号) ‘・・・指定したリスト番号のユーザー設定リストを削除します。
登録する配列を指定してユーザー設定リストに登録:Application.AddCustomList(配列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub UserList01() Dim Userlist As Variant Dim ListNo As Long Userlist = Array("アメリカ", "中国", "日本", "ドイツ", "イギリス", "フランス", "インド", "イタリア", "ブラジル", "カナダ") Application.AddCustomList Userlist '上記の配列内容を'「ユーザー設定リスト」に新規登録する。(UserList) ListNo = Application.GetCustomListNum(Userlist) '「ユーザー設定リスト」に登録した登録番号を取得 MsgBox "ユーザー設定リストに新規登録した番号は、" & ListNo & "です。" End Sub |
ユーザー設定リストに登録したリストをAutoFill機能でセルに出力
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub UserList02() Dim Userlist As Variant Dim ListNo, ArrayCount As Long Userlist = Array("東京本店", "神奈川支店", "埼玉支店", "千葉支店", "茨木支店", "栃木支店") Application.AddCustomList Userlist '上記の配列内容を'「ユーザー設定リスト」に新規登録する。(UserList) ListNo = Application.GetCustomListNum(Userlist) '「ユーザー設定リスト」に登録した登録番号を取得 ArrayCount = UBound(Userlist) '配列で登録した件数を把握する。※6個 Range("A2") = "東京本店" 'AutoFill用の一件目を登録 Range("A2").AutoFill Range("A2:A" & ArrayCount + 2) 'AutoFill機能を使いA列に支店名を表示する(ユーザー設定リストに登録した分) Application.DeleteCustomList (ListNo) '登録した「ユーザー設定リスト」を削除 End Sub |
指定したセルデータからユーザー設定リストに登録
下記のサンプルプログラムは、I列に登録されている「勘定科目」一覧をユーザー設定リストに登録して、A列(A3)の表に項目データとして反映します。3行目(B3)には、既定値として登録されているユーザー指定リストを利用して「4月~9月」を表に反映させるプログラムです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub UserList03() Dim Userlist As Variant Dim ListNo, lRow As Long lRow = Cells(Rows.Count, "I").End(xlUp).Row 'I列「勘定科目」の最終行を取得 Userlist = Range("I4:I" & lRow) 'I列の勘定科目を配列に登録 Application.AddCustomList Userlist '「ユーザー設定リスト」に新規登録する。I列の「勘定科目」 ListNo = Application.GetCustomListNum(Userlist) '「ユーザー設定リスト」に登録した登録番号を取得 Range("A4") = "旅費交通費" 'AutoFill用の一件目を登録 Range("A4").AutoFill Range("A4:A10") 'AutoFill機能を使いA列に「ユーザー設定リスト」に登録した「勘定科目」を表示 Range("B3") = "4月" 'AutoFill用の一件目を登録 Range("B3").AutoFill Range("B3:G3") 'AutoFill機能を使い3行目に4月~9月を表示 Application.DeleteCustomList (ListNo) '登録した「ユーザー設定リスト」を削除 End Sub |
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。