入力規則の設定
リストの入力規則をマクロで動的に設定したいことは、よくあります。しかも、選択した値そのものではなく、それに対応した値を取得したい、といった要求もよくあります。
以下は、Illustrator のバージョンを選択して、その結果に応じたコンポーネントの名称を取得するための入力規則の設定です。バージョン名（これがセル上に表示されている）を Key とし、コンポーネント名を Item とした連想配列を createAiVersionList() で作っておき、InstallAiVersionSelector() で Key を入力規則に設定します。選択されたバージョンに対応するコンポーネント名は、プロパティ AiApplicationName で取得できます。
標準モジュール InstallAiVersionSelector()
'----- 対応表の連想配列
Public AiVersionList As Object
'----- 入力を選択するセルの名前
Public Const AiVersionCellName = "IllustratorVersion"
'----- 入力規則を設定
Public Sub InstallAiVersionSelector()
createAiVersionList
With ActiveSheet.Range(AiVersionCellName).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(AiVersionList.keys, ",")
End With
End Sub
'----- イラストレータのバージョンとコンポーネントの一覧を作成
Private Sub createAiVersionList()
Set AiVersionList = Nothing
Set AiVersionList = CreateObject("Scripting.Dictionary")
'以下の対応表は、
'「ディザInDesign」さんのHP（http://indesign.cs5.xyz/vb/making.html）
'を引用させていただきました。
AiVersionList("未設定") = ""
AiVersionList("Ai CS2(12.0)") = "Illustrator.Application.3"
AiVersionList("Ai CS3(13.0)") = "Illustrator.Application.4"
：
：
AiVersionList("Ai CC2019(23.0)") = "Illustrator.Application.CC.2019"
AiVersionList("Ai 2020(24.0)") = "Illustrator.Application.24"
AiVersionList("Ai 2021(25.0)") = "Illustrator.Application.25"
End Sub
この一覧設定は、こういう風にだらだら書くのではなく、本当はどこかのワークシート上に一覧表を作成しておいて、それを順番に読むようにすると、格段に気持ちよくなりますが、ここでは説明のため割愛。
標準モジュール AiApplicationName
'----- 選択された AI コンポーネントの名前の取得
Public Property Get AiApplicationName()
createAiVersionList
AiApplicationName = _
Trim(AiVersionList(ActiveSheet.Range(AiVersionCellName).Text))
End Property
InstallAiVersionSelector() は、AiVersionList の Key を "," でつないだ文字列を Validatelist に登録することにしていますが、その際に、入力規則が合計２５５文字を超えるとエラーとなりシートが破損してしまいます！！ 上の例では、１８８文字なのでOKですが、２５５文字を超えそうな場合は、一度結合した文字列をどこかのセルに格納し、Formula1 にはそのアドレスを渡す、という方法をとるのが安全です。
このことは、「Qiita」の @thakaz さんの記事（https://qiita.com/thakaz/items/113c689ea9e8fed3080b）で教わりました。