【エクセルVBA】255文字を超えたらどうなるの？入力規則のリスト設定方法あれこれ

皆様こんにちは、ノグチです。

エクセルの入力規則は、使いこなせばとても便利な機能ですよね。

職場で使うエクセルツールで使用されることも多いのではないでしょうか。

中でもリストで作成するドロップダウンリストは、ツール使用者に入力させる文字列を限定でき、かつ使用者に文字列をキータイピングで入力させる必要がないので入力が楽、キータイピングが不要＝入力間違いがないという点で、結構使い勝手が良いものです。

以前下記の記事で、この入力規則のドロップダウンリストをVBAで作成したり、ドロップダウンリストに表示するテキストの更新方法を紹介しました。

【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法

ValidationオブジェクトのAddメソッドを使って、セルに入力規則のドロップダウンリストを作成する方法をご紹介しています。シートやブックに入力された値によって入力規則の種類を変えたり、ドロップダウンリストに表示する値を変えたい、という時に便利に使えますよ。

この記事の中で、さらっと「設定できる文字列の長さは255文字までですよ！」と言っているのですが、実際にこの文字制限を超えた場合、一体どうなるのか？までは触れていませんでした。

今回の記事では、実際に255文字を超えたらどうなるのか？、試してみましょう。

入力規則のリストで255文字を超えるとどうなるのか？
結果まとめ
最後に

入力規則のリストで255文字を超えるとどうなるのか？

手動で文字列を指定した場合

まずは、手動で255文字以上の文字列をにセットする場合を試してみます。

入力規則の条件の設定で「リスト」を選択して、「元の値」に適当に文字を入力していくと…

aaa,bbb,cccc,dddd…と入力していったところで、キーボード入力を受け付けなくなってしまいました。

どうやら手動でドロップダウンリストの文字列を入力する場合、255文字以上は入力できないようです。

手動で入力する場合は、そもそも255文字以上入力することができないので、255文字以上の文字列を入力してしまったらどうなるか？という心配は不要ですね。

セル指定の場合

お次は、255文字以上の文字列が入力されたセルを、「元の値」にセットした場合です。

エクセルシートのB3セルに、A列のリストを「,」でつなげて255文字以上にした文字列を作成しました。

念のため、B3セルの文字数を、C3セルのLEN関数でカウントしてあります。

文字数は270文字です。

このB2セルを、入力規則の「元の値」にセットしてみると…

ちゃんとセットできますね。

ドロップダウンリストから文字列を選択しても、問題なさそうです。

ただしこの方法だと、セル内の文字列がドロップダウンリストの1行に全てセットされてしまいます。

この方法なら255文字の制限を気にしなくてもよさそうですが、そもそも、1行しか表示できないならドロップダウンリストにする意味が無いですね。

VBAで指定した場合

では、VBAを使って「元の値」を更新する場合はどうなるか？を試してみましょう。

セットする文字列が入力されているセルを指定する場合

こちらのコードで、C5セルに入力規則のリストを作成して、B3セルの文字列を「元の値」に指定してみます。

Sub Create_Validation_List()
Dim strTxt As String

strTxt = ActiveSheet.Cells(3, 2).Value

With ActiveSheet.Range("C5").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=strTxt
End With

End Sub

このコードを実行してみると…

出ました。

実行時エラー”1004″。

エラーだけ見ると関係なさそうに見えますが、リストに指定した文字列が255文字を超えているとこのエラーになるようです。

VBA内でドロップダウンリストにセットする文字列を作成する場合

ちなみに、上のコード内でドロップダウンリストに指定する文字列を、以下のようにVBA内で作っても、同じ実行時エラーになってしまいます。

Sub Create_Validation_List()
Dim strTxt As Variant
Dim i As Long

With ActiveSheet
    
    i = 1
    
    Do Until .Cells(i, 1).Value = ""
        If strTxt = "" Then
            strTxt = .Cells(i, 1).Value
        Else
            strTxt = strTxt & "," & .Cells(i, 1).Value
        End If
        
        i = i + 1
    Loop

    With .Range("C5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:=strTxt
        .InCellDropdown = True
    End With
End With

相変わらずのエラーの分かりにくさですが、VBAでも文字列を直接「元の値」にセットする場合、255文字を超えていると受け付けられないようですね。