【Excel VBA】プルダウンリスト（ドロップダウンリスト）作成方法とセル表示形式（R1C1形式）でエラーになった時の対処方法

Excelの設定からのプルダウンリスト作成のやり方はわかっていたのですが、VBAからプルダウンリストを設定（作成）する場合にはどうするんだという状態だったので備忘録として残しておきます。

VBAからプルダウンリストを作成するコードの記載内容は以下のようになります。

Rangeオブジェクト.Add(Type,Alertstyle,Operator,Formula1,Formula2)

ここで、それぞれのパラメータの内容は以下のようです。

パラメータ	必須／省略可能	説明
Type	必　　須	入力規則の種類を指定
Alertstyle	省略可能	入力規則でのエラースタイルを指定
Operator	省略可能	データ入力規則の演算子を指定
Formula1	省略可能	データ入力規則での条件式の最初の部分を指定値は 255 文字を超える事はできない
Formula2	省略可能	引数_Operator_がXlbetweenまたはxlnotbetweenの場合、データ入力規則の2番目の部分を指定それ以外の場合、この引数は無視

コードの記載例としてはたとえば以下のようなものになります。

プルダウンメニューの選択のもとになるデータは固定で「いちご」、「みかん」、「ぶどう」を入れています。

その選択内容で(2,1)～(11,1)にプルダウンリストを作成することとしています。

Range(Cells(2, 1), Cells(11, 1)).Select

With Selection.Validation

    .Add Type:=xlValidateList, _

        Formula1:="いちご,みかん,ぶどう"

End With

もう１つのプルダウンリストの作成例は以下のようです。

今度は、プルダウンリストの選択のもとになるデータはExcel上に配置してExcel上で変更可能となるようにしています。

この例では選択データは別シートsheet2のB3～B4に配置し、それを元に本シートの(2,1)～(11,1)にプルダウンリストを作成することとしています。

Range(Cells(2, 1), Cells(11, 1)).Select

With Selection.Validation

    .Add Type:=xlValidateList, _

        Formula1:="=sheet2!$B$3:$B$4"

End With

この例で、1つだけ気になったのは、Excelのセル表記の形式がA1形式（列番号がアルファベット）の時は問題なかったのですが、R1C1形式（列番号が数値）の場合で実行すると「$B$3:$B$4」のところがエラーになりました。

その場合は、以下のように記載するとエラーにならずに実行できました。

Range(Cells(2, 1), Cells(11, 1)).Select

With Selection.Validation

    .Add Type:=xlValidateList, _

        Formula1:="=sheet2!R3C2:R4C2"

End With

ただし、そうするとプルダウンリスト作成前にセルの表示形式を見てから記載内容を変更するような処理を加えなければならなくなると思います。

これがちょっと面倒かと思ったので、今回は以下のような処理を先頭に加えました。

特に問題なければということになるかもしれませんが、VBAの先頭で以下のようにA1方式に設定してから実行するということで最終的には対応しました。

'Excelの列番号表示形式設定：A1形式
Application.ReferenceStyle = xlA1