2023年5月 プログラムの話 その1

EXCELでセルにデータを入力してもらう際に、予め決まった値だけしか入力を許可しないようにしたい！

そういうことって、割とあると思います。

こんな感じで、既に登録されてある値しか入力できないようにすること自体は、EXCELの『データの入力規則』という機能を利用することで簡単に実現できます。

今回、VBAのプログラムから指定されたセルの『入力候補』を知る必要がありまして、どうしたものかな？と、ツイッターでこぼしてみたところ、EXCEL達人の方々から様々なアドバイスをいただきました。

それらを参考にしてまとめてみたのがこちら！

データの入規則でリストが設定されている場合に入力候補を返す

Public Function GetListItems(ByRef cell As Range) As Variant

    ' 戻り値の初期化
    GetListItems = Empty
    
On Error GoTo ERROR_EXIT
    
    ' 対象セルがドロップダウンリストから選択か？
    If cell.Validation.Type <> xlValidateList Then
        Exit Function
    End If

    ' 対象セルの選択候補の取得
    Dim fml As String
    fml = cell.Validation.Formula1
    If Len(fml) = 0 Then
        Exit Function
    End If
    GetListItems = IIf(fml Like "=*", WorksheetFunction.Transpose(Application.Evaluate(fml)), Split(fml, ","))

ERROR_EXIT:

End Function

カンマ区切りで入力することも許されているので、範囲指定の場合とで処理を分岐させております。

セルの数式（Fomula1プロパティ）が = で始まっている文字列だったら範囲指定と判断して、Evaluateメソッドで数式の実行結果がVariant型の2次元配列で返ってくるので、それをTransposeメソッドで1次元配列に変換しております。

そうでない場合はカンマ区切りと判断して、Splitメソッドで文字列を分解しております。

方法が分かってしまえばなんてことはなかったのですが、それを知らなければここまで辿り着くことは出来ません。

こういう時の集合知というのは本当にありがたい存在であります。

ちなみに、テスト結果はこんな感じ。

E1のセルの入力候補が格納されています。